Testing FAILOVER when primary database is not available

Send redo data from Primary database if MOUNT is possible.

ALTER SYSTEM FLUSH REDO TO target_db_name;

At Standby, if Flashback Database is not already enabled:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
ALTER DATABASE FLASHBACK ON;

Create a restore point BEFORE_FAILOVER, The restore point will be used later to restore the database again to the same state before FAILOVER scenario.

set lines 300
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

Testing FAILOVER on physical standby database STBY.

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

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- -------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

ALTER DATABASE FAILOVER TO STBY;

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

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- -------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

STBY 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
----------------------------------------
7284214

Because flashback logs are available it’s possible to restore primary database STBY 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 STBY and conversion into physical standby

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_failover;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Start old primary database STBY to have the same state as before FAILOVER scenario

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

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- ----------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

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
---- -------------- ---------- ------------------- -------------
STBY STBY ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY