| Initial synch of CDC subscriber [message #304643] |
Thu, 06 March 2008 00:10  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
