DATAGUARD SWITCHOVER SWITCHBACK

Pre-Switchover Steps:-
---------------------
1.Blackout Databases
2.Freeze Jobs
3.Stop Streams

------------------------------------------------------------------------------------------------------------------
PRIMARY NODE 1 (n1pr1)
----------------------
connect / as sysdba

set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select

name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE,PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_c

har(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,gv$instance;

column role format a7 tru
column name format a10 wrap
select name,database_role,log_mode,protection_mode,protection_level from v$database;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status

where dest_id in (1,2);

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

show parameter log_archive_dest_2;

select flashback_on from v$database;
YES
if not, enable using,
alter database flashback ON;

create restore point <before_test_ddmmyy> guarantee flashback database;

set linesize 121
col scn format 99999999
col time format a32

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;

STANDBY NODE 1 (n1pr1)
----------------------
connect / as sysdba

set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select

name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE,PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_c

har(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,gv$instance;

column role format a7 tru
column name format a10 wrap
select name,database_role,log_mode,protection_mode,protection_level from v$database;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status

where dest_id in (1,2);

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

create restore point <before_test_ddmmyy> guarantee flashback database;

set linesize 121
col scn format 99999999
col time format a32

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;

------------------------------------------------------------------------------------------------------------------
check Sync:
----------
DR SYNC STATUS
------------------------------------------------------------------------------------------------------------------

SWITCH OVER TO STANDBY
========================
@PRIMARY
--------
PRIMARY NODE 1 (p1nr1)
----------------------

SELECT * FROM v$block_change_tracking;
STATUS     FILENAME BYTES
---------- -------- -----

if ENABLED,
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

srvctl stop instance -d <database> -i <instance_n2pr2> -o immediate

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY          ----------------> NEW STANDBY

@STANDBY
--------
PHYSICAL STANDBY 1 (n1dr1)
--------------------------

SELECT * FROM v$block_change_tracking;
STATUS     FILENAME BYTES
---------- -------- -----

if ENABLED,
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

srvctl stop instance -d <database> -i <instance_n2dr2> -o immediate

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY         

alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY          ----------------> NEW PRIMARY          
------------------------------------------------------------------------------------------------------------------
Switchback to the old Primary 
======================
@NEW STANDBY
------------
PRIMARY NODE 1 (n1pr1)
----------------------
srvctl start instance -d <database> -i <instance_n2pr2> -o mount
connect / as sysdba
SELECT * FROM v$block_change_tracking;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY          ---------------->BACK TO PRIMARY

@NEW PRIMARY
------------
PHYSICAL STANDBY NODE 1 (n1dr1)
-------------------------------
srvctl start instance -d <database> -i <instance_n2dr2> -o mount
connect / as sysdba
SELECT * FROM v$block_change_tracking;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY          ---------------->BACK TO STANDBY            

alter database recover managed standby database disconnect from session;
        
------------------------------------------------------------------------------------------------------------------
check Sync:
----------
DR SYNC STATUS
------------------------------------------------------------------------------------------------------------------

Post-Switchback Steps:-
---------------------
1.Un-Blackout Databases
2.Un-Freeze Jobs
3.Start Streams
4.Drop restore points created at both Primary and Standby after confirmation from application team.
drop restore point <before_test_ddmmyy>;

set linesize 200
col scn format 99999999
col time format a35

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;