Active Dataguard Switch-Over, Switch-Back Process about Primary RAC 2 node and Standby ADG 2 node at oracle 19c
Oracle 19c 버전, RAC 2 node 에서 Standby ADG 2 node 로 Switch-Over, Switch-Back 하는 방법을 소개한다.
Version : Oracle 19.19
Primary #1, #2 (RAC)
Standby #1, #2 (RAC)
========================== Swtich Over Scenario ===================
Primary : ORCL1, ORCL2
Standby : ORCLDG1, ORCLDG2
===================================================================
========================== Check DB Status ========================
-- # verify gap in standby database
-- [Primary, ORCL1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
VALID NO GAP
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- [Standby, ORCLDG1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- # Verify data guard status in standby database
-- [Standby, ORCLDG1]
column NAME FORMAT A24
column VALUE FORMAT A16
column DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 01/07/2024 12:51:50
apply lag +00 00:00:00 01/07/2024 12:51:50
apply finish time +00 00:00:00.000
estimated startup time 63
-- # Check the status of the database
-- [Primary, ORCL1]
set LINESIZE 200
SELECT INSTANCE_NAME, STATUS, VERSION, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE A, GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCL1 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
ORCL2 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
-- [Standby, ORCLDG1]
set LINESIZE 200
SELECT INSTANCE_NAME, STATUS, VERSION, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE A, GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCLDG1 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
ORCLDG2 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
-- # Verify Primary and Standby temp files match
-- [Primary, ORCL1]
col NAME FOR A50
SELECT TS#,NAME,STATUS FROM V$TEMPFILE;
TS# NAME STATUS
---------- -------------------------------------------------- -------
3 +DATA/ORCL/TEMPFILE/temp.410.1155932163 ONLINE
29 +DATA/ORCL/TEMPFILE/report_temp.408.1155932163 ONLINE
29 +DATA/ORCL/TEMPFILE/report_temp.409.1155932163 ONLINE
.....................
-- [Standby, ORCLDG1]
-- # Verify Primary and Standby temp files match
col NAME FOR A50
SELECT TS#,NAME,STATUS FROM V$TEMPFILE;
TS# NAME STATUS
---------- -------------------------------------------------- -------
3 +DATA/ORCLDG/TEMPFILE/temp.433.1157601691 ONLINE
29 +DATA/ORCLDG/TEMPFILE/report_temp.432.1157601691 ONLINE
29 +DATA/ORCLDG/TEMPFILE/report_temp.431.1157601691 ONLINE
.....................
-- # Verify that the target standby database is ready for switchover
-- [Primary, ORCL1]
ALTER DATABASE SWITCHOVER TO ORCLDG VERIFY;
============================ Restart primary database ===================
-- # Restart primary database before switchover
-- [Primary, ORCL1]
srvctl status database -d ORCL -v
srvctl stop database -d ORCL
srvctl start database -d ORCL
set LINESIZE 200
col NAME FOR A20
SELECT NAME,STATUS,DB_UNIQUE_NAME,OPEN_MODE FROM GV$DATABASE A, GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
NAME STATUS DB_UNIQUE_NAME OPEN_MODE
-------------------- ------------ ------------------------------ --------------------
ORCL OPEN ORCL READ WRITE
ORCL OPEN ORCL READ WRITE
=============================== Start Swithover ============================================
-- # Start performing switchover
-- #Enable tracing on both primary and standby to diagnose in case of any issue
-- [Primary, ORCL1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=8191 SID='*';
-- [Standby, ORCLDG1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=8191 SID='*';
----------------------------------------------------------
-- [Primary, ORCL1]
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- # verify gap in standby database
-- [Primary, ORCL1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
VALID NO GAP
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
318 2
350 1
-- [Standby, ORCLDG1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
318 2
350 1
-- # Start switchover
-- [Primary, ORCL1]
sqlplus / as sysdba
SELECT NAME,DB_UNIQUE_NAME,STATUS,SWITCHOVER_STATUS FROM GV$DATABASE A,GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
NAME DB_UNIQUE_NAME STATUS SWITCHOVER_STATUS
-------------------- ------------------------------ ------------ --------------------
ORCL ORCL OPEN TO STANDBY
ORCL ORCL OPEN TO STANDBY
-- Switch Over DC -> DR (ORCL -> ORCLDG)
ALTER DATABASE SWITCHOVER TO ORCLDG;
SQL> ALTER DATABASE SWITCHOVER TO ORCLDG;
Database altered.
-- Check alert_SID.log file in both server.
-- # Check database configuration
================== modify database ==================
-- [New Primary, ORCLDG1] if i need to change.
srvctl config database -d ORCLDG
srvctl modify database -d ORCLDG -s "open" -r PRIMARY
-- [New Standby, ORCL1] if i need to change.
srvctl config database -d ORCL
srvctl modify database -d ORCL -s "read only" -r PHYSICAL_STANDBY
======================================================
-- #Check database status
-- [New Primary, ORCLDG1]
sqlplus / as sysdba
SELECT STATUS FROM GV$INSTANCE; -- mounted
STATUS
------------
MOUNTED
MOUNTED
-- #Restart the new primary database
-- [New Primary, ORCLDG1]
srvctl stop database -d ORCLDG
srvctl start database -d ORCLDG
-- [New Standby, ORCL1]
srvctl start database -d ORCL
-- sqlplus / as sysdba
-- STARTUP MOUNT;
-- ALTER DATABASE OPEN READ ONLY;
=========== Put new standby can synchronize data from new primary database =============
-- [New Standby, ORCL1]
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- [New Standby, ORCLDG2]
-- srvctl status -d ORCLDG -v
-- srvctl start instance -d ORCLDG -i ORCLDG2
=============================== verify after Swithover ============================================
-- # Verify status both server
-- [New Primary, ORCLDG1]
set LINESIZE 200
SELECT INSTANCE_NAME, STATUS, VERSION, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE A, GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCLDG1 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
ORCLDG2 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
-- [New Standby, ORCL1]
set LINESIZE 200
SELECT INSTANCE_NAME, STATUS, VERSION, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE A, GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCL1 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
ORCL2 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
-- # Verify that the new primary shipping logs to standby and standby is applying
-- [New Primary, ORCLDG1]
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT DEST_ID,ERROR,STATUS FROM GV$ARCHIVE_DEST WHERE DEST_ID=2;
DEST_ID ERROR STATUS
---------- ------ ---------
2 VALID
2 VALID
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
319 2
351 1
-- [New Standby, ORCL1]
SELECT MAX(SEQUENCE#),THREAD# FROM GV$ARCHIVED_LOG GROUP BY THREAD#;
SELECT PROCESS,SEQUENCE#,THREAD#,STATUS FROM V$MANAGED_STANDBY; -- MRP0-Applying archive log
PROCESS SEQUENCE# THREAD# STATUS
--------- ---------- ---------- ------------
MRP0 363 1 APPLYING_LOG
-- #Set Trace to Prior Value
-- [New Primary, ORCLDG1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 SID='*';
-- [New Standby, ORCL1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 SID='*';
===============================Restart new primary database ==================
-- # Restart new primary database for fix issue Block Count ( no need disconnect sync)
-- [New Primary, ORCLDG1]
srvctl stop database -d ORCLDG
srvctl start database -d ORCLDG
sqlplus / as sysdba
set linesize 500
col name for a10
col db_unique_name for a10
SELECT NAME,STATUS,DB_UNIQUE_NAME,OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE a, GV$INSTANCE b WHERE a.inst_id = b.inst_id;
-- # need check again
-- [New Primary, ORCLDG1]
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT DEST_ID,ERROR,STATUS FROM GV$ARCHIVE_DEST WHERE DEST_ID=2;
DEST_ID ERROR STATUS
---------- ------ ---------
2 VALID
2 VALID
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
320 2
352 1
-- [New Standby, ORCL1]
SELECT MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
320 2
352 1
SELECT PROCESS,SEQUENCE#,THREAD#,STATUS FROM GV$MANAGED_STANDBY WHERE PROCESS = 'MRP0'; -- MRP0-Applying archive log
PROCESS SEQUENCE# THREAD# STATUS
--------- ---------- ---------- ------------
MRP0 352 1 APPLYING_LOG
======================================================================================================
======================================================================================================
========================== Swtich Back Scenario ===================
Primary : ORCLDG1, ORCLDG2
Standby : ORCL1, ORCL2
===================================================================
========================== Check DB Status ========================
-- # verify gap in standby database
-- [Primary, ORCLDG1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
VALID NO GAP
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- [Standby, ORCL1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- # Verify data guard status in standby database
-- [Standby, ORCL1]
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 01/07/2024 12:51:50
apply lag +00 00:00:00 01/07/2024 12:51:50
apply finish time +00 00:00:00.000
estimated startup time 63
SQL>
-- # Check the status of the database
-- [Primary, ORCLDG1]
set linesize 200
select instance_name, status, version, open_mode, database_role, switchover_status
from gv$database a, gv$instance b where a.inst_id = b.inst_id;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCLDG1 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
ORCLDG2 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
-- [Standby, ORCL1]
set linesize 200
select instance_name, status, version, open_mode, database_role, switchover_status
from gv$database a, gv$instance b where a.inst_id = b.inst_id;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCL1 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
ORCL2 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
-- # Verify Primary and Standby temp files match
-- [Primary, ORCLDG1]
col name for a50
select ts#,name,ts#,status from v$tempfile;
-- [Standby, ORCL1]
-- # Verify Primary and Standby temp files match
col name for a50
select ts#,name,ts#,status from v$tempfile;
-- # Verify that the target standby database is ready for switchover
-- [Primary, ORCLDG1]
ALTER DATABASE SWITCHOVER TO ORCL VERIFY;
============================ Restart primary database ===================
-- # Restart primary database before switchover
-- [Primary, ORCLDG1]
srvctl status database -d ORCLDG -v
srvctl stop database -d ORCLDG
srvctl start database -d ORCLDG
set linesize 200
col name for a20
SELECT NAME,STATUS,DB_UNIQUE_NAME,OPEN_MODE FROM GV$DATABASE a, GV$INSTANCE b WHERE a.inst_id = b.inst_id;
NAME STATUS DB_UNIQUE_NAME OPEN_MODE
-------------------- ------------ ------------------------------ --------------------
ORCL OPEN ORCLDG READ WRITE
ORCL OPEN ORCLDG READ WRITE
=============================== Start Swithover ============================================
-- # Start performing switchover
-- #Enable tracing on both primary and standby to diagnose in case of any issue
-- [Primary, ORCLDG1]
alter system set log_archive_trace=8191 sid='*';
-- [Standby, ORCL1]
alter system set log_archive_trace=8191 sid='*';
----------------------------------------------------------
-- [Primary, ORCLDG1]
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- # verify gap in standby database
-- [Primary, ORCLDG1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
VALID NO GAP
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- [Standby, ORCL1]
SELECT STATUS, GAP_STATUS FROM GV$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
317 2
349 1
-- # Start switchover
-- [Primary, ORCLDG1]
sqlplus / as sysdba
SELECT NAME,DB_UNIQUE_NAME,STATUS,SWITCHOVER_STATUS FROM GV$DATABASE A,GV$INSTANCE B WHERE A.INST_ID = B.INST_ID;
NAME DB_UNIQUE_NAME STATUS SWITCHOVER_STATUS
-------------------- ------------------------------ ------------ --------------------
ORCL ORCLDG OPEN TO STANDBY
ORCL ORCLDG OPEN TO STANDBY
-- Switch Back DR -> DC (ORCLDG -> ORCL)
ALTER DATABASE SWITCHOVER TO ORCL;
SQL> ALTER DATABASE SWITCHOVER TO ORCL;
Database altered.
-- Check alert_SID.log file in both server.
-- #Check database status
-- [New Primary, ORCL1]
sqlplus / as sysdba
SELECT STATUS FROM GV$INSTANCE; -- mounted
-- # Check database configuration
================== modify database ==================
-- [New Primary, ORCL1] if i need to change.
srvctl config database -d ORCL
srvctl modify database -d ORCL -s "open" -r PRIMARY
-- [New Standby, ORCLDG1] if i need to change.
srvctl config database -d ORCLDG
srvctl modify database -d ORCLDG -s "read only" -r PHYSICAL_STANDBY
======================================================
-- #Restart the new primary database
-- [New Primary, ORCL1]
srvctl stop database -d ORCL
srvctl start database -d ORCL
-- [New Standby, ORCLDG1]
srvctl start database -d ORCLDG
-- sqlplus / as sysdba
-- STARTUP MOUNT;
-- ALTER DATABASE OPEN READ ONLY;
=========== Put new standby can synchronize data from new primary database =============
-- [New Standby, ORCLDG1]
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- [New Standby, ORCLDG2]
-- srvctl status -d ORCLDG -v
-- srvctl start instance -d ORCLDG -i ORCLDG2
=============================== verify after Swithover ============================================
-- # Verify status both server
-- [New Primary, ORCL1]
set linesize 200
select instance_name, status, version, open_mode, database_role, switchover_status
from gv$database a, gv$instance b where a.inst_id = b.inst_id;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCL1 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
ORCL2 OPEN 19.0.0.0.0 READ WRITE PRIMARY TO STANDBY
-- [New Standby, ORCLDG1]
set linesize 200
select instance_name, status, version, open_mode, database_role, switchover_status
from gv$database a, gv$instance b where a.inst_id = b.inst_id;
INSTANCE_NAME STATUS VERSION OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------------- ------------ ----------------- -------------------- ---------------- --------------------
ORCLDG1 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
ORCLDG2 OPEN 19.0.0.0.0 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
-- # Verify that the new primary shipping logs to standby and standby is applying
-- [New Primary, ORCL1]
ALTER SYSTEM SWITCH LOGFILE;
SELECT DEST_ID,ERROR,STATUS FROM GV$ARCHIVE_DEST WHERE DEST_ID=2;
SELECT MAX(SEQUENCE#),THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
-- [New Standby, ORCLDG1]
SELECT MAX(SEQUENCE#),THREAD# FROM GV$ARCHIVED_LOG GROUP BY THREAD#;
SELECT PROCESS,SEQUENCE#,THREAD#,STATUS FROM V$MANAGED_STANDBY; -- MRP0-Applying archive log
-- #Set Trace to Prior Value
-- [New Primary, ORCL1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 SID='*';
-- [New Standby, ORCLDG1]
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 SID='*';
===============================Restart new primary database ==================
-- # Restart new primary database for fix issue Block Count ( no need disconnect sync)
-- [New Primary, ORCL1]
srvctl stop database -d ORCL
srvctl start database -d ORCL
sqlplus / as sysdba
set linesize 500
col name for a10
col db_unique_name for a10
SELECT NAME,STATUS,DB_UNIQUE_NAME,OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS
FROM GV$DATABASE a, GV$INSTANCE b WHERE a.inst_id = b.inst_id;
-- # need check again
-- [New Primary, ORCL1]
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT DEST_ID,ERROR,STATUS FROM GV$ARCHIVE_DEST WHERE DEST_ID=2;
SELECT MAX(SEQUENCE#),THREAD# FROM GV$LOG_HISTORY GROUP BY THREAD#;
-- [New Standby, ORCLDG1]
SELECT MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
SELECT PROCESS,SEQUENCE#,THREAD#,STATUS FROM V$MANAGED_STANDBY; -- MRP0-Applying archive log반응형
댓글