In this guide, we will perform a switchover to switch the roles of the primary and the standby database.
Note: PROD - DB_UNIQUE_NAME of primary database. PRODDR - DB_UNIQUE_NAME of physical standby database.
Step 1 – Determine if the primary and standby database are ready for role transition.
1a – Query the v$archive_dest_status view on the Primary to verify that are no errors or gaps at the standby.
SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;
STATUS GAP_STATUS
--------------- ---------------
VALID NO GAP
1b – Verify the primary can be switched over to standby.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY
Correct any problems you find before proceeding.
Note: A SWITCHOVER_STATUS of “NOT ALLOWED” on the standby database is expected at this stage. Once we initiate the switchover on the primary, the SWITCHOVER_STATUS on the standby will change from “NOT ALLOWED” to either “TO PRIMARY” or “SESSIONS ACTIVE”.
Step 2 – Switchover from primary to standby.
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
Step 3 – Shutdown and mount the old primary.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size 2229944 bytes
Variable Size 1560283464 bytes
Database Buffers 503316480 bytes
Redo Buffers 21950464 bytes
Database mounted.
Step 4 – The standby should now have the SWITCHOVER_STATUS of “TO PRIMARY” or “SESSIONS ACTIVE”.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------------------------------------------------
TO PRIMARY
Step 5 – Switchover from standby to primary.
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
Step 6 – Open the new primary.
SQL> alter database open;
Database altered.
SQL> select open_mode, database_role, db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------------- -------------------- ---------------
READ WRITE PRIMARY PRODDR
Step 7 – Start redo apply on the new standby.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.