Implications on Shareplex of Shared pool corruption on source database. (4301131)

1. Shared Pool corruption occurs on the source database as evident from the following entries in the Oracle alert log:

Errors in file /<path>/trace/bdump/aaa_cjq0_26859.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")

Thu Mar 11 14:47:44 2010

Â

2. Around the same time the following errors are observed related to Read process in the event log:

Errors in file /<path>/trace/bdump/aaa_cjq0_26859.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")

Thu Mar 11 14:47:44 2010

3. They are followed by the following entries below that indicate that Read process had problems fetching keys from the database:

Â

Notice   2010-03-11 15:54:33.193117 4380 1 Reader: 11009 - GetKeyValues failed: ORA-00604: error occurred at recursive SQL level 1.ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select type#,blocks,extents,...","Typecheck","kgghteInit").  (from SID) [module ord]

Notice   2010-03-11 15:54:33.738758 4380 1 Reader: ORA-00604: error occurred at recursive SQL level 1.ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select user#,type# from user...","Typecheck","kgghteInit").  (from SID) [module osp]

Â

4. Finally the Capture process also encountered problems with Shared Pool  as indicated in the entries below:

Â

Â

Notice   2010-03-11 15:54:45.755263 7682 2 Capture: ORA-00604: error occurred at recursive SQL level 2.ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select user#,type# from user...","Typecheck","kgghteInit").  (capturing from SID) [module osp]

Error    2010-03-11 15:54:45.755909 7682 2 Capture: Unexpected Or

Shared Pool corruption on source database can result in the out of sync on target database.

While it is beyond the scope of this solution to delve on reasons for occurrence of Shared Pool corruption and the preventive measures, suffice it to say that such corruption can cause out of sync on target tables.  This can be due to Capture missing some transactions and also due to Read processing failing to fetch keys from the database.  The resolution to this issue is to keep looking for occurrence of out of sync on target tables by issuing â€show syncc†periodically from target sp_ctrl and fixing such out of sync using any suitable method such as copy, compare, complete refresh of target database, etc.