- Posted by Gavin Soorma
- On July 22, 2009
- 1 Comments
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.