Dataguard Failover

Failover is a one way process where your primary database goes down due to some reasons and to get back the production live without any loss, you convert your existing Physical Standby database to start behaving as Primary database.

I have my primary database as PRPRIM and standby database as PSSTBY

Primary Database: PRPRIM

Standby Database: PSSTBY

Primary Database Server:

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

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- -----------------------
OPEN   prprim        PRIMARY

SQL> select switchover_status from v$database;

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

Standby Database Server:

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

STATUS  INSTANCE_NAME DATABASE_ROLE
------- ------------- -----------------------
MOUNTED psstby        PHYSICAL STANDBY

Now to simulate the failover, I bring down the primary database PRPRIM

Primary:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

On the standby database (PSSTBY) perform the below steps:

STEP 1:

Cancel the Managed Recovery Process

SQL> alter database recover managed standby database cancel;

Database altered.

Step 2:

Inform the standby database that the recovery is finished forever.

SQL> alter database recover managed standby database finish;

Database altered.

STEP 3:

Switchover the standby database to Primary role.

SQL> alter database commit to switchover to primary with session shutdown;
Database altered.

SQL> select status,instance_name from v$instance;

STATUS   INSTANCE_NAME
-------- --------------------
MOUNTED  psstby

SQL> alter database open;

Database altered.

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

STATUS  INSTANCE_NAME   DATABASE_ROLE
------- --------------- -----------------
OPEN    psstby          PRIMARY

Here above, you can see that the instance PSSTBY which was in the standby role earlier, has now been converted to behave as
Primary. Now, since PSSTBY is Primary database, there is no standby database available for it. A new standby database will have to be created for PSSTBY.

If flashback was enabled on both PRPRIM and PSSTBY instances, then now we can get back PRPRIM instance to behave as Standby database for PSSTBY which is behaving as Primary database.

Here are the steps to bring back old primary (PRPRIM) as standby database:

On the new Primary instance (PSSTBY):

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

STATUS   INSTANCE_NAME  DATABASE_ROLE
-------- -------------  ----------------------
OPEN     psstby         PRIMARY

SQL> select flashback_on from v$database;

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

STEP 1:

Note down the SCN on the new primary database (PSSTBY) at which it started behaving as the Primary database:

SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
2023466

The SCN at which PSSTBY started behaving as Primary database is 2023466

STEP 2:

Now mount the old primary (PRPRIM) database:

[oracle@dev ~]$ sqlplus sys/oracle@prprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 25 21:02:23 2012

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 910266368 bytes
Fixed Size 2231808 bytes
Variable Size 851444224 bytes
Database Buffers 50331648 bytes
Redo Buffers 6258688 bytes
Database mounted.
SQL>
SQL> select flashback_on from v$database;

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

STEP 3:

Flashback the old primary (PRPRIM) database to the SCN at which PSSTBY became primary database.

SQL> flashback database to scn 2023466;

Flashback complete.

STEP 4:

Now convert the old primary (PRPRIM) to behave as Standby database for PSSTBY (new primary database)

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 910266368 bytes
Fixed Size 2231808 bytes
Variable Size 851444224 bytes
Database Buffers 50331648 bytes
Redo Buffers 6258688 bytes
Database mounted.
SQL>

STEP 5:

Start the Managed Recovery Process (MRP) on the new standby database(PRPRIM) and check if MRP is started or not.

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

Database altered.

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

STATUS  INSTANCE_NAME   DATABASE_ROLE
------- --------------- --------------------
MOUNTED prprim          PHYSICAL STANDBY

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

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

Here above, we can see that MRP is not present under the Process column which in turn means that MRP has not been started.
Let us check the alert log file of my new standby database (PRPRIM)

Sat Aug 25 21:09:59 2012
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/prprim/archivelog/2012_08_25/thread_1_seq_165.423.792277639
Identified End-Of-Redo (failover) for thread 1 sequence 165 at SCN 0x0.1ee02c
Resetting standby activation ID 0 (0x0)
Incomplete Recovery applied until change 2023468 time 08/25/2012 21:00:03
MRP0: Background Media Recovery applied all available redo. Recovery will be restarted once new redo branch is registered
Errors in file /u01/app/oracle/diag/rdbms/prprim/prprim/trace/prprim_mrp0_7388.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply&lt
Recovery interrupted!

It says that MRP has been cancelled (Recovery interrupted). For this, we need to start the MRP using the keyword “Through All Switchover“.

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

PROCESS   STATUS       SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING      3
ARCH      CONNECTED    0
ARCH      CONNECTED    0
ARCH      CONNECTED    0
RFS       IDLE         0
RFS       IDLE         0
RFS       IDLE         4
RFS       IDLE         0

8 rows selected.

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

Database altered.

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

PROCESS   STATUS         SEQUENCE#
--------- ------------   ----------
ARCH      CLOSING        3
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
RFS       IDLE           0
RFS       IDLE           0
RFS       IDLE           4
RFS       IDLE           0
MRP0      APPLYING_LOG   1

9 rows selected.

Here above, we can see that MRP has been started on the new standby database (PRPRIM) and MRP is applying log sequence 1.
In 11g, when we perform Flashback operation, the log sequence would get started from sequence 1 on both Primary and Standby database just as when the database would be opened with RESETLOGS.

On the new Primary database (PSSTBY) perform a few log switches and check if they are getting applied on the new standby
database (PRPRIM).

PSSTBY:

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode            Archive Mode
Automatic archival           Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   4
Next log sequence to archive 6
Current log sequence         6

PRPRIM:

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

PROCESS      STATUS        SEQUENCE#
---------    -----------   ----------
ARCH         CLOSING       3
ARCH         CONNECTED     0
ARCH         CONNECTED     0
ARCH         CLOSING       5
RFS          IDLE          0
RFS          IDLE          0
RFS          IDLE          6
RFS          IDLE          0
MRP0         APPLYING_LOG  6
9 rows selected.

So, the log sequence 6 generated on the PSSTBY is getting applied to PRPRIM.

On PSSTBY, check the switchover status.

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

STATUS   INSTANCE_NAME    DATABASE_ROLE SWITCHOVER_STATUS
-------- ---------------- ------------- ------------------------
OPEN     psstby           PRIMARY       TO STANDBY

Here we go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.