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.
FIRST_CHANGE#
value in V$ARCHVIED_LOG
for a Data Dictionary BUILD
in the redo logs REQUIRED_CHECKPOINT_SCN
is 0, the Capture process will begin scanning at FIRST_SCN
on subsequent capture startups DBMS_CAPTURE_ADM.ALTER_CAPTURE
CHECKPOINT_RETENTION_TIME
purge process FIRST_SCN
valueFIRST_SCN
cannot be greater than REQUIRED_CHECKPOINT_SCN
when REQUIRED_CHECKPIONT_SCN
is greater than 0START_SCN
must be greater than or equal to FIRST_SCN
FIRST_SCN
is changed (either manually or via CHECKPOINT_RETENTION_TIME
purge process) to a value greater than its START_SCN
, the START_SCN
is automatically increased to the new FIRST_SCN
valueSTART_SCN
can be changed manually using DBMS_CAPTURE_ADM.ALTER_CAPTURE
START_SCN
can be set to a value lower than its current value, as long as the new value is not less than the FIRST_SCN
value for the Capture processSTART_SCN
is 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 siteIf 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.
APPLIED_SCN
for all destination sites for the Capture process queue REQUIRED_CHECKPOINT_SCN
is only changed when a checkpoint is completed. This happens either by:DBMS_CAPTURE_ADM.SET_PARAMETER('<capture_name>', '_checkpoint_force','Y')
MAXIMUM_SCN
are not captured by the Capture processThe SCN at which the latest checkpoint was taken.
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.
MAXIMUM_SCN
are not applied by the Apply process START_SCN
for the associated Capture process at the source site to recapture changesThere 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.
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 with DBMS_CAPTURE_ADM.SET_PARAMETER()
.CHECKPOINT_RETENTION_TIME:
Number of days to retain checkpoint metadata. Default 60 but can be changed with DBMS_CAPTURE_ADM.ALTER_CAPTURE()
procedure._CHECKPOINT_FORCE:
This will force a Capture checkpoint. Accomplished via DBMS_CAPTURE_ADM.SET_PARAMETER
, pass in Y
for the value. It will set itself back to N
when 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:
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
.
DBA_REGISTERED_ARCHIVED_LOG
DBA_LOGMNR_PURGED_LOG