I posted the question few days back on a streams issue I have been working on…

https://bijoos.com/oraclenotes/oraclenotes/2013/199

It looks like there was a open or long transaction, and before that transaction was committed or rollback, the archivelog file got deleted by the RMAN backup and cleanup job…  or we just hit a bug (patch 13853654)… so, here is what I learned…

Let me know if anything should be different.

Reference Documents

• Streams Recommended Patches (Doc ID 437838.1)
• Streams Configuration Report and Health Check Script (Doc ID 273674.1)
• Master Note for Streams Recommended Configuration (Doc ID 418755.1)
• Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)
• Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1)
• Streams Complete Reference FAQ (Doc ID 752871.1)
• Different States of Capture & Apply Process (Doc ID 471713.1)
• Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process (Doc ID 290143.1)

Recommended Changes

Following may be considered at a minimum for better performance and maintenance. Other best practice recommendations can be considered from the above documents.

• Apply database patches based on Doc ID 437838.1: Analysis need to be done which ones are applicable other than the first one. The first one needs to be applied to avoid the issue we saw on our database.
  • Patch 13853654  Required checkpoint scn not moving forward
  • Patch 13878246  Streams rule AND_CONDITION=:dml.get_command_type()=”DELETE” doesn’t work
  • Patch 14055108  Apply parallel processing impacted by constraints
  • o   Patch 14138823  Propagation receiver slow between 10.2 and 11.2 databases
  • If you plan to use MAINTAIN_CHANGE_TABLE or statement handlers, they should apply the following two additional patches:
    • Patch 13882311  MAINTAIN_CHANGE_TABLE failing with ORA_29285, ora-6502
    • Patch 13932732  Apply ORA-1 insert into source table if stmt handler name is 30 char

• Since we will never be able to pull in 60 days’ worth of archive logs and restart capture process, the default value of 60 days retention must be changed to a realistic value. Based on the thresholds and BI expectations of seeing the data on the other side, the value must be set to less than 7 days. Set exec dbms_capture_adm.alter_capture(‘CDC$C_RD_EBS’, checkpoint_retention_time => 2);

• AQ_TM_PROCESS database parameter must be set to auto-tune itself (if not already). Do alter system reset aq_tm_processes scope=spfile sid=’*’;

DB Monitoring

The following additional DB monitoring may need to be setup from the DBA standpoint to make sure CDC/Streams working properly.

• Archived log files generated after REQUIRED_CHECKPOINT_SCN are on disk, under their original location.
• Alert Action: DBA Restore the archive logs required; force a checkpoint; if issue not cleared stop and start capture process.
• REQUIRED_CHECKPOINT_SCN time is not more than 6 hours behind current time.
• Alert Action: Force a checkpoint; if issue not cleared stop and start capture process.
• Look for STATUS in DBA_CAPTURE. If not ENABLED, raise an alert.