Streams/CDC Question? Help…

Yes, a question indeed, looking for help through the blog!  I recently got into a streams issue, have not supported streams in a live environment  until recently (when you work on your virtual desktop streams and golden gate and everything works perfect, isn’t!!)…

Ok, here is the question… need to give you some background… To protect the innocent, I have changed the database, host names…

Apparently Streams was set up on the database for SAP Data Services BI application on this database, and was working fine until Aug 21, until the database cycled. This is visible in the alert log, and can see messages such as this until just seconds before the database shutdown. BI also confirmed they were receiving changes from Oracle fine.

 

After initiating the normal database shutdown, I can also see messages of a clean streams shutdown as well…

 

And during the database startup, I can see streams coming up as well…

But no capture happening. Ran streams healthcheck, and it showed not having the archived log files registered with logminer, so manually registered the log files using command

They showed up in dba_registered_archived_log properly. Stopped and started the capture, but nothing happening. Still the message is:

Increased memory from 30M to 50M, no change. Traced (based on metalink note 313279.1), and found in the trace file:

And the log file it is looking for is from July 31st!

And these are the SCN numbers found in DBA_CAPTURE (date when they are generated in brackets for clarity).

FIRST_SCN – 8653644859909 (July 30)

APPLIED_SCN – 8657881991134 (Aug 21)

CAPTURED_SCN – 8657881991134 (Aug 21)

REQUIRED_CHECKPOINT_SCN (July 31)

STATUS_CHANGE_TIME is Aug 21

Found that the streams parameter for capture, CHECKPOINT_RETENTION_TIME was the default 60… so I can understand why FIRST_SCN did not move from where it was.

Changed the retention time to 2 days using:

exec dbms_capture_adm.alter_capture(‘CDC$C_RD_EBS’, checkpoint_retention_time => 2);

Stopped and started CDC capture several times, no change in status, still waiting for dictionary redo with required SCN, l an SCN generated on July 31.

So, there is something I do not understand is happening here in streams.

Since the captured_scn and applied_scn are both from Aug 21, why is required_checkpoint_scn from July 31? What changed during the database cycle? Is there any manual checkpoint advance we need to do before database shutdown?

Also, RMAN has not deleted any file from archivelog location yet (after backup) since Aug 21 as it is thinking that the CDC process requires them. We will have to force delete the archivelogs soon, as we might run out of room soon.

Thanks to you in advance for all help!

 

 

 

 

One Response to Streams/CDC Question? Help…

  1. Pingback: Streams Answer… » Biju's Oracle Notes

Leave a Reply

%d bloggers like this: