Convert a Failed Primary Into a Physical Standby Database Using Flashback Database.

When a failover occurs in Data Guard configuration and we have activated our standby database as our new primary database then our old primary database cannot participate again in data guard configuration unless it is recoverd as a new standby database in our new configuration.

We can recover our old primary database using FLASHBACK as well as RMAN backups

PFB steps to recover a failed primary database using Flashback database :-

1. Mount the Old Primary Database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 26 09:48:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

Database mounted.

2. Find out the SCN at which old standby database becomes new primary :-

login to Old standby which is now primary in new configuration

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 26 09:48:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
1120211

3. Login to OLD primary database(failover primary) and run Flashback :-

SQL> flashback database to scn 1120211;

Flashback complete.

You will get below mentioned output after running flashback :-

flashback database to scn 1120211

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

 started logmerger process

Parallel Media Recovery started with 24 slaves

Flashback Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_4_8z3x5913_.arc

Flashback Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_5_8z3xbv5f_.arc

Flashback Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_6_8z3xrgo0_.arc

Flashback Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_7_8z3xvf84_.arc

Flashback Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_8_8z3xvg7v_.arc

Fri Jul 26 09:49:00 2013

Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0

  Mem# 0: /pacs/oracle/TEST/TEST/redo03.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0

  Mem# 0: /pacs/oracle/TEST/TEST/redo01.log

Incomplete Recovery applied until change 1120212 time 07/26/2013 09:45:33

Flashback Media Recovery Complete

Completed: flashback database to scn 1120211

5. Once old primary database has been recovered start the process to change it into new physical standby database :-

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

This command will dismount the database and convert the control file to standby controlfile;

6. Shutdown the newly physical standby database and open it in mount mode.

SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

Database mounted.

7. Start redo transport to the new physical standby database:-
`
Login to new Primary database and check current state of archive destinations :-


SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

   DEST_ID DEST_NAME                      STATUS    PROTECTION_MODE      DESTINATION                    ERROR                          SRL
---------- ------------------------------ --------- -------------------- ------------------------------ ------------------------------ ---
         1 LOG_ARCHIVE_DEST_1             VALID     MAXIMUM PERFORMANCE  /oracle/oracle/ora11g/product/                                NO
                                                                         dbs/arch

         2 LOG_ARCHIVE_DEST_2             VALID     MAXIMUM PERFORMANCE  TEST                                                          YES

if LOG_ARCHIVE_DEST_2 is set to new standby database then check redo transport and if not then PFB steps to configure it :-

SQL >ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TEST NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL> Alter system  switch logfile;

system altered 

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

Error column has to be blank for LOG_ARCHIVE_DEST_2.

Now login to new standby database and start redo apply :-

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

PFB output in alert log once redo apply process started :-


RFS[9]: Assigned to RFS process 59572450
RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 56098968
RFS[9]: Selected log 5 for thread 1 sequence 10 dbid 2119844559 branch 821789263
Fri Jul 26 09:57:23 2013
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Archived Log entry 77 added for thread 1 sequence 10 ID 0x7e5b2743 dest 1:
Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_7_8z3yo8wh_.arc
Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_8_8z3yo989_.arc
Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_9_8z3yoclf_.arc
Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_26/o1_mf_1_10_8z3yocxs_.arc
Media Recovery Waiting for thread 1 sequence 11 (in transit)

Failed Primary successfully converted to new Physical standby database. If we want we can again switchover the roles of newly created primary and physical standby databases to their original pre-failure roles.

PFB link to Convert a Failed Primary Into a Physical Standby Database Using RMAN Backups.

I hope this article helped you.

Regards,

Amit Rath