Connect CDC (SQData) Change Data Capture was designed to be both robust and forgiving in that it takes a simple and conservative approach when deciding where in the log to start capturing data when first started or upon being restarted following either a scheduled interruption of the capture or an unplanned disruption in a production environment.
The first time the Oracle Capture agent is started, it will use the current Oracle SCN (system change number) as starting point by default. Capture can also be started for the first time at a specific point-in-time by explicitly specifying the start SCN. The current SCN is determined using the column CURRENT_SCN of V$DATABASE on Oracle 10 and later or CHECKPOINT_CHANGE# from V$DATABASE for Oracle 9. Other transactions that started before that SCN but not already been committed or that are subsequently rolled back are considered in-flight units-of-work and will be ignored along with all other transactions that committed prior to that SCN.
The starting Oracle SCN is specified in the capture .cab configuration file. The SCN can be set at a global capture agent level; all tables in the configuration file and all Subscribing Engines/Targets or for individual Subscriptions/Targets. An SCN of 0 indicates that capture should start from the current point in the Oracle log. This is used when starting the capture agent for the first time or in a test environment when skipping ahead to the current log point may be desirable. Once the global SCN is established, it would typically never be altered for normal production operation. The capture agent continuously updates the global SCN and the individual Subscription/Target SCN's in the configuration file as changed data is processed. Each time the capture agent starts, data capture is automatically resumed from the last SCN committed by the Subscribing Engines, which guarantees the re-capture of all in-flight transactions.
When adding a new table to an existing configuration file, that table will be captured from the current point in the Oracle log. In a test environment resetting the Global SCN to 0 will start the entire capture from the current point in time.
There may also be certain times where you may want to alter the SCN in the configuration file. For example, you may want to perform a point-in-time recovery, where you go back and recapture the changes made from a few hours earlier. Determining the appropriate SCN can be done by querying the catalog table SYS.smon_scn_time. In Oracle 10G and higher, you can also use the scn_to_timestamp function in combination with the pseudo-column ora_rowscn.
In a production environment, the initial start point should been chosen to be a quiet point. The safe approach is the following:
- Make sure the objects you need to capture are read-only if at all possible.
- Determine the current SCN, and set the capture agent SCN to that value.
- Unload the object to be captured using a utility or a specially configured unload Engine.
- Release the lock on the source objects.
- Refresh the target with the unloaded data.
- Start the capture agent.
Setting the SCN at the global level (i.e. all tables) can be done with the SQDCONF modify command. The following example uses the SQDCONF utility to update the capture configuration, setting the capture agent level SCN to 0 (the "current" SCN).
$ sqdconf modify <SQDATA_VAR_DIR>/oracdc1/oracdc1.cab --scnN=0