This article presents SWITCHOVER and FAILOVER methods available for physical standby database in Oracle Database 12C release 1.

This article is based on following article: Configure physical standby database Oracle Database 12C release 1 (12.1)

Optional step – Turn on flashback logs on standby and primary database

• Flashback on primary – Useful however not necessary to test FAILOVER. It allows to restore previous primary database to consistent state just before FAILOVER so it can be used as standby again

--primary database
SYS@ORA12C> 
ALTER DATABASE FLASHBACK ON;

SELECT flashback_on
FROM v$database;

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

• Flashback on standby – enables to switch standby database to READ WRITE. Standby database still receives changes from primary database but doesn’t apply them as long as standby work in READ WRITE mode. To turn flashback on standby database recover process must be turned off.

--standby database
SYS@SORA12C> 

--cancel recovery mode and turn on flashback log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE FLASHBACK ON;

SELECT flashback_on
FROM v$database;

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

--restore recovery mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
DISCONNECT FROM SESSION;

SWITCHOVER

It switches roles of databases. It’s possible only if standby database can be recovered to the same state as primary database. It means there are no lost transactions.

Verify primary database ORA12C if it’s ready for switchover

SYS@ORA12C>

ALTER DATABASE SWITCHOVER TO sora12c VERIFY;

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- -------------
ORA12C ORA12C         ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

check status on standby database SORA12C

SYS@SORA12C>

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- ----------------
ORA12C SORA12C        ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

initiate switchover on primary database ORA12C

SYS@ORA12C>

ALTER DATABASE SWITCHOVER TO sora12c;

open old standby database SORA12C

SYS@SORA12C>

ALTER DATABASE OPEN;

SELECT
  name, db_unique_name, log_mode, protection_mode, 
  database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- -------------
ORA12C SORA12C        ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

old primary ORA12C must be started in mount mode and initiate recovery and becomes now standby database

SYS@ORA12C>

STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

SELECT client_process, process, thread#, sequence#, status 
  FROM v$managed_standby 
 WHERE client_process='LGWR' or process='MRP0'
ORDER BY process;

CLIENT_PROCESS PROCESS      THREAD#  SEQUENCE# STATUS     
-------------- --------- ---------- ---------- ------------
N/A            MRP0               1         40 WAIT_FOR_LOG 
LGWR           RFS                1         40 IDLE

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- ----------------
ORA12C ORA12C         ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

FAILOVER

WARNING – this time after previous SWITCHOVER we have following situation:

• primary database is SORA12C
• standby database is ORA12C

FAILOVER scenario is used when primary database is lost and just converts physical standby database into primary database.

Check status for both databases before running FAILOVER

SYS@ORA12C>

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database; 

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- ----------------
ORA12C ORA12C         ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

SYS@SORA12C>

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- -------------
ORA12C SORA12C        ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

FAILOVER scenario is used when primary database is not available so current primary database can go down.

SYS@SORA12C> 

SHUTDOWN ABORT;

In release 11.2.0.2 it’s possible to send redo data from primary database if MOUNT is possible so in real scenario it should be done

ALTER SYSTEM FLUSH REDO TO target_db_name;

Before converting physical standby ORA12C database into primary database restore point BEFORE_FAILOVER is created. The restore point will be used later to restore the database again to the same state before FAILOVER scenario.

SYS@ORA12C> 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

CREATE RESTORE POINT before_failover
GUARANTEE FLASHBACK DATABASE; 

SELECT scn, guarantee_flashback_database, name
  FROM v$restore_point;

    SCN GUARANTEE_FLASHBACK_DATABASE NAME            
------- ---------------------------- ----------------
2619073 YES                          BEFORE_FAILOVER

Time to test FAILOVER on physical standby database ORA12C. For FAILOVER old primary database can’t be used as physical standby. It’s recommended to create new physical standby to secure our new primary database.

SYS@ORA12C> 

ALTER DATABASE FAILOVER TO ora12c;

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- -------------
ORA12C ORA12C         ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

ORA12C is now primary database. Following query shows how to check when the database was converted from physical standby into primary database.

SELECT to_char(standby_became_primary_scn) scn
  FROM V$DATABASE;

SCN                                     
----------------------------------------
2732031

Because flashback logs are available it’s possible to restore primary database ORA12C again into physical standby database. Flashback can be executed using two methods SCN number of restore point

--method with SCN number
FLASHBACK DATABASE TO SCN standby_became_primary_scn;

--method with restore point
FLASHBACK DATABASE TO RESTORE POINT restore_point_name;

Flashback primary database ORA12C and conversion into physical standby

SYS@ORA12C>

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO RESTORE POINT before_failover;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

as last step start old primary database SORA12C to have the same state as before FAILOVER scenario

SYS@ORA12C>

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database; 

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- ----------------
ORA12C ORA12C         ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

SYS@SORA12C>

STARTUP;

SELECT 
  name, db_unique_name, log_mode, 
  protection_mode, database_role
FROM v$database;

NAME   DB_UNIQUE_NAME LOG_MODE   PROTECTION_MODE     DATABASE_ROLE
------ -------------- ---------- ------------------- -------------
ORA12C SORA12C        ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

Have a fun 🙂

Tomasz