ORA-01378 The logical block size error

For some reason client asked me to open production database in another environment until specific time.After successfull restore and recover process , clear logfile gave the below error ,

ORA-01378: The logical block size (4096) of file +RECO is not compatible with the disk sector size (media sector size is 512 and host sector size is 512)

Production system 's redo log files have 4K sector size and clearing log file on 512 byte is not possible.

Recreating controlfiles without redo logs statements which are 4K size has solved our issue.I have added new redo logfiles to system before recreating controlfile which area group 11,12,13

create controlfile reuse database "testdb" noresetlogs force logging archivelog

  maxlogfiles 

  max...

  ...

logfile2

   group 2('+RECO/TESTDB/log2a','+RECO/TESTDB/log2b') size 100M blocksize 4096,

   group 11('+RECO/TESTDB/log11a','+RECO/TESTDB/log11b') size 100M blocksize 512,

   group 12('+RECO/TESTDB/log12a','+RECO/TESTDB/log12b') size 100M blocksize 512

   group 13('+RECO/TESTDB/log13a','+RECO/TESTDB/log13b') size 100M blocksize 512

datafile

...

...

New Controlfile script : 

create controlfile reuse database "testdb" noresetlogs force logging archivelog

  maxlogfiles 

  max...

  ...

logfile2

   group 11('+RECO/TESTDB/log11a','+RECO/TESTDB/log11b') size 100M blocksize 512,

   group 12('+RECO/TESTDB/log12a','+RECO/TESTDB/log12b') size 100M blocksize 512

   group 13('+RECO/TESTDB/log13a','+RECO/TESTDB/log13b') size 100M blocksize 512

datafile

...

...

/usr/bin/ld: cannot find -ljava

When applying patch to Oracle EBS , below error occured while relinking. Because of clone system , some enviroments were still pointing old home enviroments

/usr/bin/ld: cannot find -ljava

For this error , entry "JRE_LIB_DIR" in $ORACLE_HOME/sysman/lib/env_sysman.mk has been checked if it points true directory.After adjusting to proper directory , relink has been completed successfully.

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