Unable to launch discoverer because of Missing Required Permissions Manifest Attribute In Main Jar & Security Exception, JVM Shared, not allowed to set security manager

After client java upgrade for EBS R12 from jre 1.6.0_33 to 1.7.0_51 we have been warned , clients getting below error whenever invoking discoverer.

Missing Required Permissions Manifest Attribute In Main Jar :

For this error we have applied 17347648 ,17303613 and 17874742 sequentially. At last below error occured ,

Security Exception, JVM Shared, not allowed to set security manager

More inclusive patch 18219024 adviced from support.But not able to patch because of this error,

Running prerequisite checks... 

Prerequisite check "CheckPatchApplicableOnCurrentPlatform" failed. 

The details are: 

Patch ( 18219024 ) is not applicable on current platform. 

Platform ID needed is : 226 

Platform IDs supported by patch are: 46 

[ Error during Prerequisite for apply Phase]. Detail: OPatch failed during prerequisite checks: Prerequisite check "CheckPatchApplicableOnCurrentPlatform" failed. 

System is intact, OPatch will not restore the system 

Despite Oracle published patch is generic platform , i could not apply patch to linux platform. Oracle support has a solution which is  changing  <platform name="" > entry to proper value in  PATCH_LOCATION/etc/config/inventory.xml file.

I have changed 
 <platform name="Linux Intel" id="46"/>
to
 <platform name="Linux Intel" id="226"/>

With this solution patch has been applied successfully and discover problem has gone.

Instant Data file growth - KTSJ - SMCO (Space Management Coordinator)

I have encountered an issue last weekend which was all datafiles in tablespace extended without reason. There was lots of wasted space approximetaly 125 GB.Firstly i have thougth that there was a procedure or job which processed big data and it caused this instant growth. But no clear point or anything like this i could not find in AWR reports in related time.

In order to detect this issue firstly i have queried exact time when this occured. Below sql helped me to find it.

select f1.snap_id,

       f1.sumts,f2.sumts2,((f1.sumts-f2.sumts2)*8)/1024/1024 diff

  from (select d1.snap_id, sum(tablespace_size) sumts

          from dba_hist_tbspc_space_usage d1, v$tablespace v1

         where d1.tablespace_id = v1.TS#

           and rtime >= '06/22/2014'

           and rtime < '06/23/2014'

         group by d1.snap_id) f1 ,       (select snap_id,sum(tablespace_size) sumts2

          from dba_hist_tbspc_space_usage d1

         group by d1.snap_id) f2   

where f2.snap_id=f1.snap_id-1  

This sql gave me issue occured between 05:00 AM - 06:00 AM. I have to find which session(s) caused this and best way to find was dba_hist_active_session_history with below sql

select d3.sample_id,

       d3.sample_time,

       d3.session_type,

       d5.USERNAME,

       d3.session_id,

       d3.session_serial#,

       d3.sql_opname,

       d3.sql_id,

       d3.event,

       d3.module,

       d3.action,

       d3.delta_read_io_requests,

       d3.delta_write_io_requests,

       d3.delta_read_io_bytes,

       d3.delta_write_io_bytes,

       d4.sql_text

  from dba_hist_active_sess_history d3, dba_hist_sqltext d4, dba_users d5

 where d3.sample_time >=

       to_date('22/06/2014 05:00:00', 'dd/mm/yyyy hh24:mi:ss')

   and d3.sample_time <

       to_date('22/06/2014 06:00:00', 'dd/mm/yyyy hh24:mi:ss')

   and d3.sql_id = d4.sql_id(+)

   and d3.user_id = d5.USER_ID(+)

 order by d3.sample_id

By ordering delta_write_io_bytes column ,interesting session  KTSJ Slave listed with highest delta_write_io_bytes . Sum of the delta_write_io_bytes of KTSJ was 119 GB and this was what i was looking for. Key word  was KTSJ or KTSJ Slave. After googling and with Oracle support , lastly i have found myself reading SMCO (Space Management Coordinator) master note :) 

Duty of this coordinator is coordinating execution of space management tasks , such as proactive space allocation and space reclamation. If datafiles have autoextend Yes property this coordinator can extend datafilesize to its max size even it has not reached maxsize. This is of course fine that sessions need not wait for space allocation that has already done. But instant growing like this may  make storage admin angry :) To be in safe disable option is available.

"Tablespace-level space (Extent) pre-allocation" feature provided by SMCO process can be disabled by setting "_enable_space_preallocation" to 0 as below,

alter system set "_enable_space_preallocation"=0;

To enable this feature, parameter can be set to 3 which is the default value,

alter system set "_enable_space_preallocation"=3;

Replicat process abends with OGG-01091 Unable to open file... (Error 2, no such file or directory)

After abnormal crash of server , all databases has been restarted and existing goldengate replicat processes abended with below error,

 ERROR   OGG-01091  Oracle GoldenGate Delivery for Oracle, RANK1.prm:  Unable to open file <trail_file> (error 2, No such file or directory)

Whenever start of replicat process everytime same error occured. Although trail file exists in related directory, process could not start. To fix this error , related replicat process has been altered to begin with specific trail file which seems absent.

alter replicat <REPLICAT_NAME>,extseqno <trail file seq no> ,extrba 0

trail file seq no is absent file no

After this changement replicat process began successfully.

ORA-07445 [kkpamIsPKeyFKeyJoin()+154]

On database 12cr1 we have seen ORA-07445 [kkpamIsPKeyFKeyJoin()+154]  error when reference partitioning and join are used between tables. Actually error has not occured for all sql statemens except one.

For Oracle support setting parameter "_full_pwise_join_enabled" to false can be used to avoid this error.I have used hint for specific sql like select /*+ OPT_PARAM('_full_pwise_join_enabled' 'false') */ .

By breaking large joins into small joins , memory is used better and this process is distributed amaong parallel server processes effective. So Benefits of partition-wise join must be thought before turn off this parameter at database level.

uln_register "This system profile has already been registered"

Whenever cloned or previously registered machine would like to be registered again via uln_register to linux support , "This system profile has already been registered" may occur. These two possibilites of error can be avoided regenerate system uuid .

From ULN web interface related system information can be deleted from System tab. If you could not find any related system on list , following steps can be done.

With "uuidgen -r" command random id is generated ,with "uuidgen -t" generated id is based on system MAC address.For several versions of linux support , following actions can be done.

For Oracle Linux 4 and 5

Output of uuidgen -t is replaced the existing value of rhnuuid parameter of /etc/sysconfig/rhn/up2date-uuid file. up2date is run again.

For Oracle Linux 6 

Following command writes generated uuid into the related file which is "/etc/sysconfig/rhn/up2date"

echo "uuid=`uuidgen -t`" >> /etc/sysconfig/rhn/up2date

uln_register is run again.

ora-00942 resolution with errorstack trace

Whenever developer warned me that application took ORA-00942 -  table or view does not exist error , i have asked which sql . From one reason related sql could not be captured , so i have decided to open tracing for specific error on level 3.

For ora-00942 -  table or view does not exist error , i have executed ,

alter system set events '942 trace name errorstack level 3';

Error entries were in the alert log file , and it was showing related trace files. But more trace files occured in dump directory that i expect.Most of them were indicating 1 table , but this table was not the cause of error . I have used "grep" command to eliminate irrelevant files in order to detect actual table.

In every trace files there was a entry which is "about to signal 942" helped me to query all related files and "Name:" entry at the end of file showing table name.

grep "about to signal 942" * | grep "^Name:" * | grep -v "TABLE2"

Output ,

testdb_ora_23427.trc:Name: TABLEX

testdb_ora_2626.trc:Name: TABLEX

testdb_ora_2692.trc:Name: TABLEX

testdb_ora_2708.trc:Name: TABLEX

testdb_ora_29230.trc:Name: TABLEX

testdb_ora_31956.trc:Name: TABLEX

Table2 was irrelevant table and with "-v" parameter command only executed for other tables.As a result relevant table has been determined.

After resolution , you can disable errostack tracing,

alter system set events '942 trace name errorstack off';

lots of audit files under ASM home

Yesterday, I had as issue which was standby log shipping giving below error ,

PING[ARC2]: Heartbeat failed to connect to standby '<stbyDb>' Error is 1033.

On Standby server , db side was giving below error ,

NOTE: Deferred communication with ASM instance

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 28: No space left on device

ASM side was giving also,

OS Audit file could not be created; failing after 6 retries

Oracle support has a note about this issue which is "Large Number of Audit Files Generated by Oracle Restart or Grid Infrastructure (Doc ID 1378881.1)"  . Oracle restart deamons is checking availability of registered services within  a CHECK_INTERVAL parameter duration.Default it is 1 second. 

crsctl status res ora.asm -p command shows that checking frequency of asm service availability is 1 second. Modifying value of parameter can avoid execessive generation of audit files. 

crsctl modify res ora.asm -attr "CHECK_INTERVAL=60"

But this change will provide checking service availability every minute and unavailability will be detected one minute later in worst case.If it is acceptable , parameter can be changed. I have not changed parameter. I have solved issue by adding script into crontab which work everyhour and deletes ASM audit files

1 * * * * rm -rf /oracle/app/grid/product/12.1.0/grid/rdbms/audit/*.aud