Posted on by Biju

Streams Answer…

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

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.