Oracle Step by Step Manual Data Guard Switchover and SwitchBack - Oracle RAC Manual Switchover and SwitchBack Steps
-----------------------------------------------------------------------------------------------------------------------
###################
Swicth over steps
###################
1) Cancel the recover on DR server
SQL>recover managed standby database cancel;
SQL>select NAME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,RESTORE_POINT_TIME from gv$restore_point;
2) Create the restore point on DR first
SQL> CREATE RESTORE POINT BEFORE_SWITCHOVER_ACTIVITY GUARANTEE FLASHBACK DATABASE;
SQL>select NAME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,RESTORE_POINT_TIME from gv$restore_point;
3) Start the MRP process on DR server
SQL>alter database recover managed standby database disconnect nodelay;
4) Create the restore point on Primary
SQL>show parameter DB_RECOVERY_FILE_DEST
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 100G
SQL>select NAME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,RESTORE_POINT_TIME from gv$restore_point;
SQL> CREATE RESTORE POINT BEFORE_SWITCHOVER_ACTIVITY GUARANTEE FLASHBACK DATABASE;
SQL>select NAME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,RESTORE_POINT_TIME from gv$restore_point;
5) Very the standby/DR database sync status, Ensure standby databbase must sync with primary
SQL> select scn_to_timestamp(current_scn) from v$database;
6) On primary side stop the RMAN backup if it is running
7) Check the Primary ready status on primary server
SQL> select name,open_mode ,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- --------------------
ERPDB READ WRITE TO STANDBY ---->>>> this should be standby
8) Now. Switch primary database to Standby
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
9) on DR server Query the SWITCHOVER_STATUS column of the V$DATABASE view on the DR database:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO PRIMARY
10) Now Switchover the standby/DR database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Open the new primary database/DR
SQL> ALTER DATABASE OPEN;
SQL> shut immediate
start the database using SRVCTL
srvctl start database -d ERPDBDR
7. Verify the database status
srvctl status database -d ERPDBDR
11)Optional: check the HA service on DR database if it Configured. Use below link for Configuring HA service Between Primary and DR site (https://nizamappsdba.blogspot.com/2019/07/best-practice-for-configuring-dataguard.html)
srvctl status service -d erpdbdr -service erpdb_ha
If HA service is not running on DR server . please start the HA service
srvctl start service -d erpdbdr -service erpdb_ha
12) Restart the new DR (primaryserver1,primaryserver2(Old primaryservers))
shut bort
srvctl start database -d ERPDB
13) Check and Stop the HA serive on DR servers (primaryserver1,primaryserver2(Old primaryservers))
Optional: check the HA service on DR database if it Configured. Use below link for Configuring HA service Between Primary and DR site (https://nizamappsdba.blogspot.com/2019/07/best-practice-for-configuring-dataguard.html)
srvctl status service -d ERPDBA -service erpdba_ha
If HA service is running on new DR server stop HA service.
srvctl stop service -d erpdbdr -service erpdb_ha
14) start the recover
alter database recover managed standby database disconnect nodelay;
check the LOG_ARCHIVE_DEST is both DR and Primary cirrectly set or not, if not please set it accordingly
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ERPDB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPDB' scope=both sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=DEFER SCOPE=BOTH sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=enable SCOPE=BOTH sid='*';
alter system set log_archive_dest_state_3=defer scope=both SID='*';
alter system set log_archive_dest_state_3=enable scope=both sid='*';
15) Check the sync status
select scn_to_timestamp(current_scn) from v$database;
16) Drop the restore point on both primary and DR
drop restore point BEFORE_SWITCHOVER_ACTIVITY
##################
Swicth back
###################
Perfom the same steps as above. Brified the steps below
=========================================================================
>>> SWITCHBACK STEPS:
=========================================================================
1] Switchover the new primary to a standby database:-
------------------------------------------------------
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
Database altered.
2] Verify that the standby database can be switched to the primary role, (Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database:)
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
3] Switchover the new standby database to a primary:-
------------------------------------------------------
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
4] Open the new primary database:-
----------------------------------
SQL> ALTER DATABASE OPEN;
Database altered.
5] Start the new standby:-
--------------------------
SQL> STARTUP MOUNT; <--- In New Standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
AFTER COMPLETION OF ACTIVITY, DROP GUARANTEED RESTORE POINTS:
=============================================================
Step – 1 Drop any Switchover Guaranteed Restore Points, On all databases where a Guaranteed Restore point was created.
On New Standby:-
----------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> DROP RESTORE POINT BEFORE_SWITCHOVER_ACTIVITY
Restore point dropped.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
On New Primary:-
---------------------
SQL> DROP RESTORE POINT BEFORE_SWITCHOVER_ACTIVITY
Restore point dropped.