Synchronising Recovery of two databases

A recent forums question was about backup and recovery to be synchronised for two databases.

To reply, I used my knowledge from the "Distributed Databases" documentation in V7 and how we used to implement Advanced Replication.

When Oracle initiates a Distributed Transaction, it synchronises the SCNs for the databases that are part of the transaction. Essentially, a "Global SCN", which is the highest SCN amongst all those databases participating in the transaction, is selected and this is set in all the databases. Each database may subsequently follow it's own frequency of updating the SCN (i.e. based on the rate of transactions) until the next Distributed Transaction occurs, when the SCNs are again reset to the highest in the group.

Note : SCN synchronization occurs when
a. A connection occurs using a DBLink
b. A distributed SQL statement occurs (yes, even a SELECT !)
c. A distributed transaction commits

Even the V7 Administrator's Guide covered how important it was for Co-ordinated Recovery of Distributed Databases --- using SCNs.


Here's a short, simple demonstration of the synchronisation.
Lines in Italics are on database 'ORCL'.
Lines in BOLD are on database 'DUPDB'.

Initially, the SCN in 'DUPDB' was 6395626, far below the 6501471 in 'ORCL'.
However, as soon as I began distributed transactions, the SCNs were synchronized -- 'DUPDB' caught up with 'ORCL'.

Later when 'DUPDB's SCN had gone to a much higher 6501805 (because there were other "local" transactions in this database) and ran a distributed transaction to bring the SCN to 6502089, it was 'ORCL's turn to catch-up on the SCN


SQL> create database link DUPDB connect to hemant identified by dupdb using 'dupdb';

Database link created.

SQL>
SQL> create database link ORCL connect to hemant identified by orcl using 'ORCL';

Database link created.

SQL>
SQL> create table IN_ORCL (col_1 number, col_2 timestamp, col_3 varchar(30));

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6501471

SQL>
SQL> create table IN_DUPDB (col_1 number, col_2 timestamp, col_3 varchar(30));

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6395626

SQL>
SQL> insert into in_dupdb@dupdb values (1,systimestamp,'FROM ORCL');

1 row created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6501531

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6501537

SQL>
SQL> select systimestamp, current_scn, name from v$database;

SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_SCN NAME
----------- ---------
28-JAN-11 11.59.05.566584 PM +08:00
6501585 ORCL


SQL>
SQL> l
1* select systimestamp, current_scn, name from v$database
SQL> /

SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_SCN NAME
----------- ---------
28-JAN-11 11.59.59.867294 PM +08:00
6501805 DUPDB


SQL> insert into in_orcl@orcl values (1,systimestamp,'FROM DUPDB');

1 row created.

SQL> select systimestamp, current_scn, name from v$database;

SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_SCN NAME
----------- ---------
29-JAN-11 12.00.46.522963 AM +08:00
6502089 DUPDB


SQL>
SQL> select systimestamp, current_scn, name from v$database;

SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_SCN NAME
----------- ---------
29-JAN-11 12.00.49.642064 AM +08:00
6502089 ORCL





Note : It does not matter if the database "initiating" a distributed transaction has a lower or a higher SCN than the other database. Oracle just selects the highest SCN (higher of the two in a 2-database transaction, higher of the 'n' in an n-database transaction) and applies it to all the databases.

.
.
.