ORA-28138 while using Fine Grained Auditing

If you would like you use Oracle Fine Grained Auditing  with some audit condition , do not write condition with multiple arguments.It is not possible to use AND,OR , IN operators in audit condition.If it is used , ORA-28138 error will be thrown.

To avoid this a simple function which returns single value is written into audit_condition and checked its value.

Before function audit_condition was like ,

SYS_CONTEXT('USERENV','HOST') <> 'myhost' and SYS_CONTEXT('USERENV','user') <>'TEST'

New function,

  function isConditionValid  return number is

    i number;

  begin

    if SYS_CONTEXT('USERENV','HOST') <> 'myhost' and SYS_CONTEXT('USERENV','user') <>'TEST'

      i:=1;

    else

      i:=0;

    end if;

    

    return i;

  end;

New audit_condition,

isConditionValid=1

Unique Constraint Violated on FND_LOBS

User has declared that while using EBS import in specific form , below error occured,

oracle.apps.fnd.framework.OAException: oracle.jbo.DMLConstraintException: JBO-26048: Constraint "SYS_C00155937" violated during post operation:"Insert" using SQL Statement  "INSERT INTO FND_LOBS(FILE_ID,FILE_NAME,FILE_CONTENT_TYPE, UPLOAD_DATE,PROGRAM_NAME,FILE_DATA,ORACLE_CHARSET,FILE_FORMAT) VALUES (:1,:2,:3,:4,:5,:6,:7,:8)".

This is ORA-00001 error and unique constraint has been violated. FND_LOBS table has unique constraint on FILE_ID column and it take its own value from FND_LOBS_S sequence.

It seems new taken file_id values are already in table.Because of this situation related program throws ORA-00001 error.To solve this issue sequence next value is taken over maximum file_id value in table.

1. Get max value

select max(file_id) from fnd_lobs;

2. Alter sequence's increment value,

alter sequence APPLSYS.FND_LOBS_S increment by 100;

3. Call sequence nextval function to change sequence current value until it will be greater than max(file_id)

select  APPLSYS.FND_LOBS_S.nextval from dual;

4. Run following queries to verify sequence value(last_number) is greater than max(file_id)

select last_number from dba_sequencies where sequence_name='FND_LOBS_S'
select max(file_id) from fnd_lobs;

5. Alter sequence increment by value back to 1

alter sequence APPLSYS.FND_LOBS_S increment by 1;

Jar Signing For E-Business Suite

Before completion of validity of Code-Signing Certificate , I have done signing process for E-Business Suite. This process provided , prevent blocking of Java based forms screen because of not valid certificate.

To do this ,

1. List certificates in JKS file to detect alias

    keytool -list -keystore mycert.jks
    Output should like below

   1, 23.Jan.2017, PrivateKeyEntry,
   Certificate fingerprint (SHA1): DA::****************

2. You can change the alias of key,

   keytool -changealias -keystore mycert.jks -alias 1 -destalias myalias

   For this scenario my alias was "1" and changed it to "myalias". After change of alias output would like below,

   myalias, 23.Jan.2017, PrivateKeyEntry,
   Certificate fingerprint (SHA1): DA:****************

3. Upload edited JKS (mycert.jks) to EBS application tier $APPL_TOP/admin folder

4. Import new jks into existing adkeystore.dat

   keytool -importkeystore -srckeystore mycert.jks -destkeystore adkeystore.dat

5. Change keypass of new key to the E-Business Suite expected value

 keytool -keypasswd -keystore adkeystore.dat -keypass <YourKeyStorePass> -new <EBSStorePassword> -alias myalias 

  <YourKeyStorePass> defines  your keystore password for code-signing certificate which is given from Official CA like Verisign etc.

  <EBSStorePassword> defines EBS store (adkeystore.dat) password which can taken from below script if you do not know,

declare
  spass varchar2(30);
  kpass varchar2(30);
begin
  ad_jar.get_jripasswords(spass,kpass) ;
  dbms_output.put_line(spass);
  dbms_output.put_line(kpass);
end;

6. Edit adsign.txt file which will show new key alias . With this change , while regeneration of jar files  adadmin will use new key alias.

7. Regenerate all jar files via adadmin , after stopping application tier services.

discard dynamic "testdb" : cannot determine its home

After installing Oracle Enterprise Manager Control agent for any host, adding database could not be completed successfully.According to log file of agent (emagent_perl.trc) Oracle database services are found but, ORACLE_HOME could not.

oracledb.pl: Mon Sep  5 08:51:18 2016: INFO:  DB_LISTENER_DISCOVERY:  found dynamic sid="testdb" service =testdb.localdomain port = 1521 host =testdb01 home=/oracle/app/oracle/product/11.2.0/dbhome_2
oracledb.pl: Mon Sep  5 08:51:18 2016: WARN:  DB_LISTENER_DISCOVERY:  discard dynamic "testdb" : cannot determine its home

After adding entry for testdb into oratab like below , problem has gone and related database has been added to Oracle EM Cloud Control successfully.

testdb:/oracle/app/oracle/product/11.2.0/dbhome_2:N

Change default export path for BI Discoverer

Whenever user would like to export results of Discoverer report , report is saved to default export path which is taken from pref.txt. In order to change this parameter , set the value for DefaultExportPath from pref.txt file.

10g:$ORACLE_HOME/discoverer/util/pref.txt

11g:$MW_HOME/asinst_1/config/PreferenceServer/Discoverer_asinst_1/pref.txt

To save exported files to client's home default directory ,set DefaultExportPath to "". Otherwise you can set value like "c:\temp".

After changing value , applypreferences.sh or applypreferences.bat(Windows) is executed which is under following directories.

10g:$ORACLE_HOME/discoverer/util

11g:$MW_HOME/asinst_1/Discoverer/Discoverer_asinst_1/util

Connected to idle instance while database is up

While database is up , whenever i would like to connect database with sqlplus it gives me "Connected to idle instance" message.ORACLE_SID enviroment variable seems true by checking pmon process with this command

[bash$]# ps -ef | grep pmon

oracle   30607     1  0 Jun09 ?        00:01:35 ora_pmon_testdb

[bash$]# echo $ORACLE_SID

testdb

But ORACLE_HOME enviroment variable is not the same as that the instance was started with.Oracle user which owns the database has ORACLE_HOME enviroment variable

/oracle/product/11.2.0/dbhome_2

But instance was started with ,

/oracle/product/11.2.0/dbhome_2/

In order to check which enviroment variables has been used when Oracle instance started ,following commands give detail.These commands are run with root user.

Pid of process is determined at OS level,

[bash$]# ps -ef | grep pmon

Enviroments of process (pmon) are get

[bash$]# cat /proc/<pid of process taken above>/environ

Output of above command gives all detail about enviroment variable of smon process.ORACLE_SID,ORACLE_HOME,ORA_CRS_HOME can be seen at output.In my case i have seen ORACLE_HOME env variable was with slash at the end of variable.After setting ORACLE_HOME with slash on oracle user , I can be able to login to the instance.

Restore 11g Full BAckup To 12c

Rman can restore a backup taken on older database version into new version.Important point is after restore and recover process , database must not be opened and manual upgrade operation must be done.This method can be used for out-of place upgrades.In my case I have a full backup from 11g and it will be restored into 12c version on new host.

Before start , in older version pre-upgrade and post-upgrade procedures should be completed for successfull upgrade.

Basically ,

1. Copy preupgrd.sql and utluppkg.sql files from new 12c $ORACLE_HOME/rdbms/admin to old 11g $ORACLE_HOME/rdbms/admin
2. Execute  preupgrd.sql under 11g to detect pre-upgrade issues that must be completed.
3. Backup full database 
4. Copy backup files and archive logs to new host 
5. Copy current init file and password file to new 12c $ORACLE_HOME/dbs
6. Startup database in nomount mode
7. Restore controlfile.
8. Open database in mount mode.
9. Restore and recover database
10. Shutdown database
11. Open database in upgrade
12. SQL> startup upgrade
    In this step i have encountered error ,
    
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
    ORA-1589 signalled during: ALTER DATABASE OPEN MIGRATE...
    
    In order to pass over this error  following command can be used.
    
    alter database open resetlogs upgrade 
13. For 12c new catctl.pl perl script is executed
    cd $ORACLE_HOME/rdbms/admin
    $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql
     
14. Run post-upgrade status tool $ORACLE_HOME/rdbms/admin/utlu121s.sql to display the summary of upgrade process.If any error(s) declared in output these errors must be exmained.
15. Upgrade log file which is located at $ORACLE_HOME/diagnostics/catupgrd0.log is checked for "BEGIN catuppst.sql" to verify catuppst.sql ran during upgrade process.If this sql has not run then execute it .
16. Run utlrp.sql to recompile invalid objects.
17. Change parameter compatible in init.ora to "12.0.0"
18. Shutdown database
19. Recreate password file
20. Startup database

After these steps new db version is 12c and it has been ugraded successfully. If you have a downtime and necessary disk size , this method can be used for upgrade to new versions.