Step by Step Converting a Failed Primary Into a Standby Database Using Flashback Database

Step by Step Converting a Failed Primary Into a Standby Database Using Flashback Database



On Primary

Flashback must enable on the primary database to bring back the failed primary database in the data guard configuration without rebuilding it. Also minimum flashback logs must be available to flashback the failed primary database to a SCN (ie standby_became_primary_scn;)

SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   READ WRITE           PRIMARY
SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /apxrdbms/product/oracle/flash_recovery_area                                            
db_recovery_file_dest_size           big integer 20G
If db_recovery_file_des and db_recovery_file_dest_size   is not set follow the below step to set it.
SQL> alter system set db_recovery_file_dest_size=10G;
SQL> alter system set db_recovery_file_dest =< some location where the flashback logs will be created>
Check whether the flashback is enabled or not. And enable the flashback if it not enabled
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Enable the flashback at database level
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
(Note:-Before simulating the failover makes sure your primary and physical standby is in sync to avoid any data loss. In real scenarios we don’t have manual control on this step as the failover can occur at any time. And we need not to do the failover manually)
SQL> shut abort
ORACLE instance shut down.

ON PHYSICAL STANDBY
On physical standby database check the media recovery is going on , if not start it and apply the maximum possible archives.
SQL>  recover managed standby database disconnect from session;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PRIMARY
Note :- if you check the alertlog of standby database you will get a line like this "Standby became primary SCN: 7497799799226"
SQL>  ALTER DATABASE OPEN;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   READ WRITE           PRIMARY
Note:- At this point your physical standby database has become primary and the original primary is no longer available
Now start your application from DR site and enjoy….Let us after 10Days you got your primary back and you want it bring it back in DC/DR configuration without rebuilding it. Just follow the below steps.
Identify the STANDBY_BECAME_PRIMARY_SCN from the current primary database  and note the SCN
SQL>  SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
7497799799226

On the Old primary database (the original primary database that was failed)

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 7497799799226;ßThis is the SCN that you got in the above step
Flashback complete.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PHYSICAL STANDBY
You are done. The failed primary now successfully became the standby of the current primary. Now configure the log shipping from the current primary to the current standby and start the media recovery on the standby.
SQL>  recover managed standby database disconnect from session;
Media recovery complete.
Check the log progress on the standby.
select process,sequence#,status from v$managed_standby;
PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
ARCH               7 CLOSING
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
MRP0               8 WAIT_FOR_LOG
RFS                0 IDLE
RFS                0 IDLE
RFS                0 IDLE
RFS                8 IDLE

At this point of time your original primary is in standby mode and the original standby is in primary mode.
If you want to bring the original primary as a PRIMARY and the original standby as STANDBY only do a switchover.
Steps to perform the switchover will be covered in the next post….Enjoy JJ

Leave your questions/comments here. I will be happy to answer your queries


1 comment: