All of the Streams processes use SCNs to keep track of what change transactions they have processed and they share this information to coordinate who gets what, who still needs what, and what can be ignored (because it has already processed). This is why coordinating the Instigation SCN at the start is so important.
Capture and Apply object instantiation are not the only components of Streams that rely on SCN synchronization. The Capture process must also coordinate it's SCNs with the LogMiner process and available archived logs to ensure data integrity over time. This is done via FIRST_SCN, START_SCN and REQUIRED_CHECKPOINT_SCN.
The Capture process relies on a valid LogMiner Data Dictionary to access database object structure for redo capture to build LCRs. This LogMiner Data Dictionary is separate from the Database Data Dictionary, but is a "picture" of the Database Data Dictionary at the time the Capture process is created. Each Capture process either builds a new LogMiner Data Dictionary or accesses an existing one when it first starts. To build a LogMiner Data Dictionary, the Capture process must have access to the "picture" of the Database Data Dictionary from the redo logs at the time of the SCN from which it must first start capturing. This picture of the Database Data Dictionary is created in the redo logs by running the DBMS_CAPTURE_ADM.BUILD procedure. This procedure must be one at least once in the database before a Capture process can be created. The BUILD creates a picture of the Database Data Dictionary in the current redo log and records an entry in the V$ARCHVIED_LOG view indicating that the redo log contains a Data Dictionary information (DICTIONARY_BEGIN='YES') as of the SCN (FIRST_CHANGE#) at the time of the BUILD. The FIRST_SCN of the Capture process must correspond to a FIRST_CHANGE# for a BUILD. For the Capture process to start for the first time, the redo log for that FIRST_CHANGE# must be available to the database instance. The BUILD procedure can be run multiple times, and different Capture processes can use any one of these builds when it is created by specifying one of the FIRST_CHANGE# values for a build for the Capture process FIRST_SCN parameter (as long as the necessary redo logs are available to the instance). The Capture process will access the redo log containing the Dictionary information, and build its LogMiner Data Dictionary if needed. You can find eligible FIRST_SCN values by querying V$ARCHIVED_LOGS for FIRST_CHANGE# values generated by a build.
select distinct NAME, FIRST_CHANGE# from V$ARCHIVED_LOG where DICTIONARY_BEGIN = 'YES';
The NAME column has the name of the redo log(s) in which the BUILD resides. All redo logs from this redo log forward, must be available for the Capture process to first start.
If you specify a FIRST_SCN for a new Capture process from a BUILD for which another Capture process has already built a LogMiner Data Dictionary, the new Capture process will use the existing LogMiner Data Dictionary.
If you do not specify a FIRST_SCN (default is NULL) when creating a Capture process, the creation will call DBMS_CAPTURE_ADM.BUILD procedure, and set the FIRST_SCN for the Capture process to the FIRST_CHANGE# generated by the build. If you create a Capture process using one of the procedures in the DBMS_STREAMS_ADM package, the FIRST_SCN parameter is automatically set to NULL, forcing the capture creation to do a BUILD.
The following synopsizes the SCNs of a Capture process; how they are used, and rules of usage.
The lowest SCN at which the Capture process can begin capturing
Must correspond to a
FIRST_CHANGE#value inV$ARCHVIED_LOGfor a Data DictionaryBUILDin the redo logsPoints the Capture process to the redo log(s) that hold the Data Dictionary information from which to build its LogMiner Data Dictionary if necessary, and begin scanning redo for changes on the Capture process first startup
If
REQUIRED_CHECKPOINT_SCNis 0, the Capture process will begin scanning atFIRST_SCNon subsequent capture startupsIt can be changed by the following:
Manually using
DBMS_CAPTURE_ADM.ALTER_CAPTUREAutomatically by
CHECKPOINT_RETENTION_TIMEpurge process
Change can only be to a value greater than the current
FIRST_SCNvalueFIRST_SCNcannot be greater thanREQUIRED_CHECKPOINT_SCNwhenREQUIRED_CHECKPIONT_SCNis greater than 0
The SCN at which the Capture process will actually begin capturing changes on startup
START_SCNmust be greater than or equal toFIRST_SCNIf a Capture process's
FIRST_SCNis changed (either manually or viaCHECKPOINT_RETENTION_TIMEpurge process) to a value greater than itsSTART_SCN, theSTART_SCNis automatically increased to the newFIRST_SCNvalueSTART_SCNcan be changed manually usingDBMS_CAPTURE_ADM.ALTER_CAPTURESTART_SCNcan be set to a value lower than its current value, as long as the new value is not less than theFIRST_SCNvalue for the Capture processSTART_SCNis usually only manually changed if a point-in-time recovery has been performed at a destination site, and the point-in-time recovered to requires changes to be resent to the destination site
Note
If the point-in-time recovery requires an SCN before the Capture process FIRST_SCN, that process cannot be used to send changes to the recovered site. If a Data Dictionary BUILD is available in the archived logs with a FIRST_CHANGE# less than or equal to the SCN required for the point-in-time recovery, a new Capture process can be created specifying the appropriate FIRST_CHANGE# for the FIRST_SCN. Otherwise, the Streamed objects must be re-instantiated from the source at the destination.
Set to 0 (zero, zed) when the Capture process is created
Incremented by the Capture process LogMiner checkpoint process
Value determined by the lowest
APPLIED_SCNfor all destination sites for the Capture process queueThe lowest SCN that the Capture process must be able to access from the redo logs to be able to restart
The redo log that includes this SCN and all subsequent redo logs must be available to the Capture process database instance, for the Capture process to successfully start
If value > 0 (zero, zed), the Capture process starts scanning from this SCN when restarted
The
REQUIRED_CHECKPOINT_SCNis only changed when a checkpoint is completed. This happens either by:Automated by LogMiner Checkpoint process
Manually via command
DBMS_CAPTURE_ADM.SET_PARAMETER('<capture_name>', '_checkpoint_force','Y')
The SCN at which a Capture process must stop capturing changes and disable
The Capture process will disable when it reaches this upper limit SCN
Changes with and SCN greater than or equal to the
MAXIMUM_SCNare not captured by the Capture processIf the value is infinite (default), the Capture process captures changes without upper limit
The SCN at which the latest checkpoint was taken.
Note
For more detailed information on how FIRST_SCN, START_SCN and REQUIRED_CHECKPOINT_SCN are used by the Capture process, please refer to the The LogMiner Data Dictionary for a Capture Process, Capture Process Checkpoints, and Multiple Capture Processes for the Same Source Database sections in Chapter 2: Oracle Streams Information Capture of the Oracle Streams Concepts and Administration guide 11g.
A Propagation process really only tracks one SCN value. This is the ACKED_SCN which is the SCN sent to the Propagation process from the destination for which the Apply process has acknowledged by all Apply queue subscribers as successful dequeued and processed. This means the dequeued LCR was either successfully applied or successfully committed to the Apply error queue. This value is used by the Capture checkpoint to help determine its REQUIRED_CHECKPOINT_SCN.
The following synopsizes the SCN's of an Apply process; how they are used, and rules of usage.
The SCN at which an Apply process must stop applying changes and disable
The Apply process will disable when it reaches this upper limit SCN
Changes with and SNC greater than or equal to the
MAXIMUM_SCNare not applied by the Apply processIf the value is infinite (default), the Apply process applies changes without upper limit
This is the latest SCN of a received LCR that was successfully dequeued and applied
In the case where a point-in-time recovery is performed on the destination, this value should be used to reset the
START_SCNfor the associated Capture process at the source site to recapture changesDoes not pertain to synchronously captured changes received
There are a myriad other SCNs that have used the Apply process internally. The SCNs listed above are the ones you gain the most for understanding. You can find detailed information on Apply SCN and transaction tracking in the Oracle Streams Replication Administrators' Guide.
As you can see, if your SCNs are out of sync between the LogMiner Dictionary, Capture, and Apply processes your Streams may not work as expected; or even not at all. Obeying the following formula when implementing your Streams environment will keep you out of SCN SYNC-hole.
Note
Apply SCN >= OI SCN >= CP START_SCN >= CP FIRST_SCN
Where OI = Object Instantiation and CP = Capture
Once you have implemented Streams, avoid changes to SCNs unless it is necessary to compensate for a destination site point-in-time recovery or an unrecoverable archive log.
The Capture process keeps track of the lowest SCN that has been recorded by its Propagation processes as greatest SCN that has been acknowledged by its Apply destination as being applied. The Capture process cannot set its REQUIRED_CHECKPIONT_SCN great than this SCN or there is a potential for data loss. This is controlled by checkpointing.
The Capture process will conduct checkpoints in which it coordinates its SCNs. By default these checkpoints happen with the capture of 10 MB of redo and the checkpoint metadata is retained in the database for 60 days. You can also force a checkpoint if the need arises. These checkpointing options are all controlled by the following capture parameters:
_CHECKPOINT_FREQUENCY:The number of megabytes captured which will trigger a checkpoint. Default value is 10 but can be changed withDBMS_CAPTURE_ADM.SET_PARAMETER().CHECKPOINT_RETENTION_TIME:Number of days to retain checkpoint metadata. Default 60 but can be changed withDBMS_CAPTURE_ADM.ALTER_CAPTURE()procedure._CHECKPOINT_FORCE:This will force a Capture checkpoint. Accomplished viaDBMS_CAPTURE_ADM.SET_PARAMETER, pass inYfor the value. It will set itself back toNwhen the checkpoint is complete.
You can determine the current values for these parameters by querying the DBA_CAPTURE_PARAMETERS view.
The following lists captures views that contain helpful information concerning your Capture processes:
When a Capture process starts up, it will check its REQUIRED_CHECKPOINT_SCN (if it's 0, it will use the FIRST_SCN) and look for the redo log that contains that SCN and begin scanning at the redo log forward. If the SCN is in an archived log that is no longer available; or if any subsequent redo logs (archived or online) from that SCN forward are no longer available, the Capture process will not start. You can overcome this by either of the following:
Restoring the required archive logs
Dropping and recreating the Capture process
This leads to the obvious question of "what happens when my archive logs are in my flash_recovery_area and are aged out?" The obvious answer here is, "It will break your Capture process if/when the archive log containing your Capture FIRST_SCN/REQUIRED_CHECKPOINT_SCN is aged out". This would be why Oracle documentation specifically and highly recommends that you do not use the flash_recovery_area as your only archive log repository if you are using Streams. If you use the flash_recovery_area, configure a separate archive log destination to accommodate the archive redo logs needed by Streams. Now, if you really want to only have archive logs in the flash_recovery_area, take pity on the on-call DBA and make sure that your Capture process checkpoint_retention_time intervals are set within the archive log retention period of the flash_recovery_area.
The following views can be used to help determine what archived redo logs are required by the Capture process and which can be purged:V$ARCHVIED_LOG
DBA_REGISTERED_ARCHIVED_LOGDBA_LOGMNR_PURGED_LOG