Capture the SCN

Owned by KrupeshD (Deactivated)
Last updated: Jun 27, 2014 by Chris Lawless (Deactivated)
4 min read

Oracle SCN (System Change Number)

The Oracle SCN (System Change Number) is very important in replication. It is the primary mechanism to determine the start of the replication and to maintain consistency during replication. Dbvisit Replicate uses the SCN to mark the starting point of where to start replicating from. Only changes made from the SCN specified are replicated. Also changes pending in non committed transactions from this SCN will be lost. Each table can have a different SCN to replicate from, or a single SCN can be used to mark the beginning of the replication of all tables. 

The setting INSTANTIATE_SCN determines which SCN is used. The values are:

  • NOW (default) - It uses the SCN as of now at this moment in time.

    INSTANTIATE_SCN NOW

  • RESETLOGS - It uses the SCN of the last resetlogs at the target database. It obtains the resetlogs_change# value from v$database.. This is handy if a standby database is used to create the initial starting point and a resetlogs is used to open the standby database in READ WRITE mode.

    INSTANTIATE_SCN RESETLOGS

  • NOW_NOLOCK -  This is used when using the no-lock option in step 2 of the SETUP WIZARD.  This will not lock the tables, it will capture the SCN of the oldest active transaction at that time.  This has a pre-requisite of running the pre-all.sh script prior to running the all.sh script.

    INSTANTIATE_SCN NOW_NOLOCK

  • manually entered numeric value - A manually entered SCN can be used. This is done by editing the *.dbvrep script and change the word "RESETLOGS" to the actual SCN number. Then run *all.sh or *all.bat script. 

    INSTANTIATE_SCN 14382713



When the INSTANTIATE_SCN NOW is used Dbvisit Replicate will capture the SCN using either of the following methods:
  • When there is an ENGINE LOCK command, it captures the SCN at the start of the LOCK command. 
  • When there is no ENGINE LOCK command, it captures the SCN at the start of each PREPARE command.

Single SCN Prepare



The simplest way of operation of Dbvisit Replicate to replicate tables is to prepare (for replication) each table one by one. This means locking the tables individually in sequence and setting the instantiation SCN for each table separately. This shortens the overall lock time (and thus the impact on the database users), but requires a more complicated approach to the initial data copy (data instantiation) through the provided datapump method. This is because each table has a different SCN and this may lead to foreign key issues during the initial data copy.

The default method is to use a single SCN (INSTANTIATE_SCN) for all the tables to be replicated (prepared). This setting can be enabled during the setup wizard. With the single SCN method, all tables are locked at the same time to provide the single SCN. The initial impact is higher because the tables will be locked (see note 1) all at once and there could be a wait while obtaining the locks on tables with existing transactions that need to complete before the locks can be taken. There may also be a small outage required by the application so that the tables are freed up for the locks to be taken. See note 2 for the actual difference between one-by-one and single-scn mode.

The advantage of a single SCN is that all the data being replicated will be at the same point in time enabling simpler methods of creating the initial data copy. The methods for single SCN to create the initial data copy are:

  • DataPump with networklink
  • DataPump with data export
  • Pre-datapump export/import
  • RMAN tablespace point-in-time recovery (TSPITR) - restore an auxiliary copy to the SCN and then use transportable tablespace
  • RMAN duplicate/point-time recovery - duplicate the database to the SCN given
  • Use a standby database and then activate the database to the SCN given

Note that the purpose of locking is to ensure that no transactions are active on the replicated tables as of the SCN. This is necessary to ensure that no uncommitted changes are lost. This is true even more for RMAN/standby scenarios: if there are active transactions as of the activation SCNs, they most probably were committed on primary, but they get rolled back on the recovered database by instance recovery. It is thus imperative to use the ENGINE LOCK command for the RMAN/standby approach (or administer an application outage).

The methods for a separate SCN per table (default) to create initial data copy are:

  • DataPump with networklink
  • DataPump with data export
  • Pre-datapump export/import

  

Note 1

The locking of tables is done in SHARE mode which means that no transaction may be pending on the tables (reading is unaffected). To prevent deadlocks with users, it is recommended to specify the tables in the same sequence as they are used by the application. Alternatively a small outage on the application while the Dbvisit Replicate setup script is run to ensure the tables are not modified will also be sufficient.

  

Note 2

The chosen option between per table or single SCN is reflected in the setup script. If single SCN is selected, additional commands are used: "ENGINE LOCK TABLES/SCHEMAS" (put before PREPARE commands) and "ENGINE LOCK RELEASE ALL" (put after PREPARE command). They do the actual locking to a single SCN.