Flashback Database after a Data Guard Failover

After a data guard failover operation has been performed, instead of recreating the original primary database as the new standby database, we can use the FLASHBACK DATABASE to SCN command instead. This is particulary important in case the size of the primary database would mean a long backup and restore RMAN operation to recreate the standby during which we have no DR in place.

In this test case we will simulate a failure on the Primary node by shutting down the current Primary database.

We will then perform the standard Failover procedure. Before performing a Failover, we first try and ensure that there is no archive log gap between the Primary and Standby databases. If there is one (and if the Primary server is still accessible), we can register any ‘missing’ archivelog files using the ALTER DATABASE REGISTER PHYSICAL LOGFILE command.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Database altered.

These are the relevant lines taken from the alert log ….

Identified End-Of-Redo for thread 1 sequence 19
Terminal Recovery: Updated next available block for thread 1 sequence 19 lno 4 to value 92
Wed Jul 22 12:43:53 2009
Incomplete recovery applied all redo ever generated.
Recovery completed through change 5003771
Wed Jul 22 12:43:53 2009
MRP0: Media Recovery Complete (testdb)

 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

The alert log will confirm the switchover operation ….

Standby terminal recovery start SCN: 5003412
RESETLOGS after complete recovery through change 5003771
Standby became primary SCN: 5003411

Complete the switchover operation by shutting down and starting the database

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  2083304 bytes
Variable Size             142607896 bytes
Database Buffers          113246208 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

Note the SCN at which the former standby has now become the primary

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
5003411

On the original primary, we will now flashback the database using the SCN obtained above.

SQL> flashback database to scn 5003411;

Flashback complete.

from the alert log ….

Incomplete Recovery applied until change 5003412
Flashback Media Recovery Complete
Completed: flashback database to scn 5003411

We will now turn off the flashback logging to delete existing falshback log files. Since we will be restoring a standby control file as well in the next step, these flashback logs will become obsolete.

SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

From the alert log we can see that the flashback logs are being deleted.

Stopping background process RVWR
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56dydp02_.flb
.......
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0pz1n_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0q51v_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0qmw4_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0qsk9_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0r312_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0rkro_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB_DG/flashback/o1_mf_56f0rtqq_.flb
Flashback Database Disabled

We will now create a standby control file and overwrite the existing control files with the standby control file.

SQL>  ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control01.ctl
[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control02.ctl
[DEV] testdb:/tmp > cp control01.ctl /u02/oradata/testdb/control03.ctl

We then mount the standby database, turn on flashback logging and the managed recovery process on the standby database

SQL> startup mount;
ORACLE instance started.

Total System Global Area  390070272 bytes
Fixed Size                  2084272 bytes
Variable Size             364905040 bytes
Database Buffers           16777216 bytes
Redo Buffers                6303744 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.


SQL> recover managed standby database disconnect;
Media recovery complete.