Stage-4 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;
Standby side
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;
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database
DGMGRL> SHOW DATABASE VERBOSE ‘prime’;
Database – prime
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prime
Properties:
DGConnectIdentifier = ‘prime’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
RedoRoutes = ”
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘4’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/u01/app/oracle/oradata/STAND/datafile, /u01/app/oracle/oradata/PRIME/datafile’
LogFileNameConvert = ‘/u01/app/oracle/oradata/STAND/onlinelog, /u01/app/oracle/oradata/PRIME/onlinelog’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=PRIME_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
SUCCESS
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 ‘stand’;
Database – stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
stand
Database Status:
SUCCESS
Step3:-Confirm That the Database Is Ready for a Role Change
12c New Feature
Prior to performing a role change, you can use the VALIDATE DATABASE command to perform
an exhaustive set of checks on the database to confirm that it is ready for a role change.
DGMGRL> VALIDATE DATABASE ‘prime’;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
prime: Off
DGMGRL> VALIDATE DATABASE ‘stand’;
Database Role: Physical standby database
Primary Database: prime
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
prime: Off
stand: Off
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 10 seconds ago)
Step4:-Issue the Switchover Command
Issue the SWITCHOVER command to swap the roles of the primary and standby databases
DGMGRL> switchover to ‘stand’;
Performing switchover NOW, please wait…
Operation requires a connection to instance “stand” on database “stand”
Connecting to instance “stand”…
Connected as SYSDBA.
New primary database “stand” is opening…
Operation requires start up of instance “prime” on database “prime”
Starting instance “prime”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “stand”
Step5:-Show the Configuration
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
stand – Primary database (Now primary database is Stand)
prime – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 28 seconds ago)
Successfully Switchover completed using DGMGRL Utlility
Check both side
select name,open_mode,database_role,protection_level from v$database;
Standby side
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;