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

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.