Failover with No DG Broker using Flashback

Failover with No DG Broker using Flashback Database

Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Environment
1. Pre-requisites
2. Simulate failover
3. Steps to perform Failover with Physical Standby database


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

On Primary

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

On Standby

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>


1. Pre-quesites

- Configre Physical Standby database. Please click below for step by step configuration.

Please click here

- Make sure that flashback database is enabled on both primary and standby database.


2. Simulate failover: Bring down the primary database w5005pr

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Steps to perform Failover with Physical Standby database

On Standby (w5005prg)

SQL> alter database recover managed standby database cancel;
 
Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

Note: If Any error from above step then only follow below command.

alter database activate physical standby database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL>

Find SCN on the new primary database (w5005prg) 

SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
                   1618441

SQL>


On OLD Primary (W5005PR)

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> 

SQL> select name,INSTANCE_NAME from v$database,v$instance;

NAME       INSTANCE_NAME
--------- ----------------
W5005PR     w5005pr

SQL> flashback database to scn 1618441;  <---

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 nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>

SQL>  select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING               4
ARCH      CONNECTED             0
ARCH      CLOSING               5
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING               6
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
MRP0      APPLYING_LOG          7 <--
RFS       IDLE                  7
RFS       IDLE                  0

37 rows selected.

SQL> 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.