Description:-
- We are going to switch the role of the primary database and a standby database using the SWITCHOVER command
- A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss and is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
- All participating databases are in good health, without any errors or warnings present.
- The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON
- Standby redo log files are configured on the primary database.
- The broker configuration file is updated to reflect the change in roles.
- Restarts the new standby database and begins the redo apply service.
- The new primary database is opened in read/wirte mode and the redo transport service is started.
Environment Details:-
Note:-Now primary database is chennai after the switchover primary database is delhi
Let’s Start the Demo:-
Step1:-Check the Primary Database
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database
DGMGRL> SHOW DATABASE VERBOSE ‘chennai’;
Database – chennai
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
chennai
Properties:
DGConnectIdentifier = ‘chennai’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘SYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘dev19c’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.125.155)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=CHENNAI_DGMGRL)(INSTANCE_NAME=chennai)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/chennai/chennai/trace/alert_chennai.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/chennai/chennai/trace/drcchennai.log
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 VERBOSE ‘delhi’;
Database – delhi
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: 0 Byte/s
Active Apply Rate: 1.32 MByte/s
Maximum Apply Rate: 1.34 MByte/s
Real Time Query: ON
Instance(s):
delhi
Properties:
DGConnectIdentifier = ‘delhi’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘SYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘devdr19c’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.125.156)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/delhi/delhi/trace/alert_delhi.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/delhi/delhi/trace/drcdelhi.log
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE ‘delhi’;
Database – delhi
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
delhi
Database Status:
SUCCESS
Step3:-Confirm That the Database Is Ready for a Role Change (on Primary Side)
New Feature From 12c
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 ‘chennai’;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
chennai: NO
Validating static connect identifier for the primary database chennai…
The static connect identifier allows for a connection to database “chennai”.
Database level to Verify that the primary database can be switched to the standby role
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
——————–
TO STANDBY
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 34 seconds ago)
Step4:-Issue the Switchover Command
Issue the SWITCHOVER command to swap the roles of the primary and standby databases
DGMGRL> switchover to ‘delhi’;
Performing switchover NOW, please wait…
Operation requires a connection to database “delhi”
Connecting …
Connected to “delhi”
Connected as SYSDBA.
New primary database “delhi” is opening…
Operation requires start up of instance “chennai” on database “chennai”
Starting instance “chennai”…
Connected to an idle instance.
ORACLE instance started.
Connected to “CHENNAI”
Database mounted.
Database opened.
Connected to “CHENNAI”
Switchover succeeded, new primary is “delhi”
Old Primary
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN chennai PHYSICAL STANDBY MAXIMUM AVAILABILITY
New Primary:-
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN delhi PRIMARY MAXIMUM AVAILABILITY
Note:-open both side alertlog and monitor the Activity.
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
delhi – Primary database
chennai – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 36 seconds ago)
Successfully Switchover Dataguard Switchover Using DGMGRL Utility
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
