Monday, December 8, 2008

Keeping CDC from falling apart catastrophically

CDC, especially the log-based implementations such as "asynchronous autolog" can be a really nice effective way to maintain a DSS system with low impact on a production OLTP data source.

One problem with CDC is that it uses Streams, specifically the logminer capture and apply components, which can be challenging to make work successfully. A constant danger is the CDC staging database encounterin a problem that makes it unable to continue processing changes. In development, we find ourselves rebuilding the change source and sets frequently to overcome bugs, mistakes and mysterious failures.

In production, however, rebuilding the CDC environment requires rebuilding the entire DSS reporting system or data warehouse. Each change from the production source must be captured by CDC or there will be data loss. Rebuilding CDC implies losing transactions.

As it turns out, everything hinges on the availability fo a recent logminer data dictionary build in the redo logs. With CDC, you can reinstantiate the CDC environment as of any point in time, as long as you have a source archivelog with a dictionary build, and all subsequent logs to the current pont in time. Thus my #1 recommendation for people using async autolog CDC:

Generate a data dictionary into the source redologs daily using a script similar to this:

SQL> variable dicscn number
SQL> exec
SQL> column discn format 9999999999999999999999
SQL> print dicscn

(Record this SCN)

If you ever need to reinstantiate the CDC environment, re-create the change source as of the SCN of the last available dictionary build:

SQL> exec dbms_cdc_publish.create_autolog_change_source( -
change_source_name=>'MYSOURCE', -
description=>'My OLTP Source', -
source_database=>'MYDB', -
first_scn=>'', -

Then ceate a change set that begins processing changes as of the time that the previous failed CDC staging system failed:

SQL> exec dbms_cdc_publish.create_change_set( -
change_set_name=>'MYSET', -
description=>'My Change Set', -
change_source_name=>'MYSOURCE', -
begin_date=>to_date('2008-12-09 18:00:00','yyyy-mm-dd hh24:mi:ss'), -

Finally create your change tables and subscriptions as usual. The changes in the change tables will beg as of the date specified in create_change_set.


vbarun said...

Your #1 recommendation should not only apply to async autolog CDC but to any solution that uses streams.

Also, DBA_REGISTERED_ARCHIVED_LOG and V$ARCHIVED_LOG keeps track of which archived logs contain dictionary information, in case you loose the dicscn :)

Jeremiah Wilton said...

Thanks for the comment. It is useful that you don't have to specify the exact dictionary SCN, but can just specify the first_change# of the archivelog with dictionary_begin='YES'.

Shad said...

Hi Jeremiah,

I am evaluating CDC as an option to capture table changes. Do you happen to know which flavor of asynchronous CDC is the most stable and least prone to issues (bugs)? I have not come across much documentation pertaining to the stability of the different options. Latency is not an issue at this point in time, as I will not require near real-time data.


Jeremiah said...


If your goal is source database stability then async autolog abstracts nearly all activity away from the source/production system.

Shad said...

Thanks for responding. I just setup an Autolog Archive CDC demo environment. We'll see if the latency requirements change during the evaluation.