Home » Server Options » Replication » Initial synch of CDC subscriber (10g / Solaris)
Initial synch of CDC subscriber [message #304643] Thu, 06 March 2008 00:10 Go to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm trying to work out how to synchronise a source and target database in an Oracle CDC implementation. Let me explain:

We're in the architecture design stage of a near-real-time Operational Data Store style solution.

Both the ODS and our pilot source system will be Oracle 10g. Our plan is to use Oracle Asynch Hotlog Change Data Capture to capture change data in near-real-time so that it can be applied to the ODS.

I understand the CDC apply process once the ODS and Source System are synchronised: DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW to release the next window of change data, select from the publish views, then DBMS_CDC_SUBSCRIBE.PURGE_WINDOW to register the change data is no longer required.

But how do we do the initial synchronisation if the source system is live and contains data and the ODS is new (and empty)?

The easiest way would be to somehow flag EVERY row as change data. eg. Truncate every table and import. This would not be so good for existing CDC subscribers.

A more logical way would be to:
- Take a hot backup of the live prod database
- Activate CDC (DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION) on the source system to start tracking changes
- Manually build the ODS from the snapshot
- Start applying changes from CDC

But this has a problem: if the source system is live, how can we GUARANTEE that the first 2 steps (snapshot and ACTIVATE_SUBSCRIPTION) are performed at EXACTLY the same time (ie. same SCN)?

Ross Leishman
Re: Initial synch of CDC subscriber [message #304828 is a reply to message #304643] Thu, 06 March 2008 18:12 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've had an idea. I'll run it up the flagpole and see who salutes.

When you create a subscription, you get a row in DBA_SUBSCRIPTIONS. This view contains the following columns:
EARLIEST_SCN NUMBER NOT NULL Subscription window low boundary 
LATEST_SCN   NUMBER NOT NULL Subscription window high boundary


After initialising the subscription, we can use the EARLIEST_SCN either in Datapump Export or Flashback Query to get a point-in-time view of the data with which we seed the ODS. Once the seeding process is complete, the ODS should be current as-at the EARLIEST_SCN and we can start applying change-sets.

Any thoughts?

Ross Leishman
Previous Topic: Replication solutions with Oracle 8i
Next Topic: Unable to create Materialized view.
Goto Forum:
  


Current Time: Thu Mar 28 07:58:48 CDT 2024