Oracle Data Guard Physical Standby switchover using DGMGRL:-
Performing a Oracle Data Guard Switchover Using DGMGRL
Step1:-Check the Primary Database
Check both side:-
Select name,open_mode,database_role,protection_level from v$database;
Check for archive log gap on standby database:-
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Check the configuration on primary database:-
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database
DGMGRL> connect sys/5#HLe7jSPf@PROD_DGP
Connected to “PROD_DGP”
Connected as SYSDBA.
DGMGRL> SHOW DATABASE VERBOSE ‘PROD_DGP’;
Step2:-Check the Standby Database That is the Target of the Switchover:-
Use the SHOW DATABASE command to check the status of the standby database that is the target of the switchover
DGMGRL> SHOW DATABASE ‘PROD_DGS’;
Step3:-Confirm That the Database Is Ready for a Role Change:-
DGMGRL> VALIDATE DATABASE ‘PROD_DGP’;
DGMGRL> SHOW CONFIGURATION;
Step4:-Issue the Switchover Command:-
Issue the SWITCHOVER command to swap the roles of the primary and standby databases
DGMGRL> switchover to ‘PROD_DGS1’;
Step5:-Show the Configuration:-
DGMGRL> SHOW CONFIGURATION;
Check both side:-
selectname,open_mode,database_role,protection_level from v$database;
Standby side:-
ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE ‘MRP%’;
select name,open_mode,database_role from v$database;
