SWITCHOVER USING DGMGRL BROKER IN ORACLE DATAGUARD
Switchover is an activity which acts as a role reversal with guaranteed protection to data.
Here primary database acts as a standby and standby database acts as a primary!!
We can perform switchover in situations like maintenance of primary database like patching,migration of a database from one server to another etc..
Switchover using broker is easy process compared to manual switchover. One command in broker and switchover is completed.
Prechecks:
Check the primary database status and parameters
DGMGRL> show database verbose dbx00
Database - dbx00
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
dbx00_1
Properties:
DGConnectIdentifier = 'dbx00'
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 = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbx00_DGMGRL)(INSTANCE_NAME=dbx00_1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Check standby database status
DGMGRL> show database dbx01
Database - dbx01
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: 11.00 KByte/s
Real Time Query: OFF
Instance(s):
dbx01
Database Status:
SUCCESS
Validate the database for switchover readiness of primary and standby database. Thanks to 12c feature. Below error on standby can be safely ignored for standby redologs as it is a reported bug.
DGMGRL> validate database dbx00
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
dbx00: Off
DGMGRL> validate database dbx01
Database Role: Physical standby database
Primary Database: dbx00
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
dbx00: Off
dbx01: On
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(dbx00) (dbx01)
1 8 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(dbx01) (dbx00)
1 8 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on dbx00
Check the configuration at last
DGMGRL> show configuration
Configuration - dgb
Protection Mode: MaxPerformance
Members:
dbx00 - Primary database
dbx01 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 29 seconds ago)
While switchover:
Issue the below command for a hassle free switchover
DGMGRL> switchover to dbx01
Performing switchover NOW, please wait...
Operation requires a connection to instance "dbx01" on database "dbx01"
Connecting to instance "dbx01"...
Connected as SYSDBA.
New primary database "dbx01" is opening...
Oracle Clusterware is restarting database "dbx00" ...
Switchover succeeded, new primary is "dbx01"
DGMGRL> show configuration
Configuration - dgb
Protection Mode: MaxPerformance
Members:
dbx01 - Primary database
dbx00 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 111 seconds ago)
To switch back to old primary database, issue switchover command to primary database
DGMGRL> switchover to dbx00
Performing switchover NOW, please wait...
Operation requires a connection to instance "dbx00_1" on database "dbx00"
Connecting to instance "dbx00_1"...
Connected as SYSDBA.
New primary database "dbx00" is opening...
Operation requires start up of instance "dbx01" on database "dbx01"
Starting instance "dbx01"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.197)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbx01_DGMGRL)(INSTANCE_NAME=dbx01)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up and mount instance "dbx01" of database "dbx01"
For completeness of switchback, start the standby database in mount state and enable MRP if not enabled
kish@STDBY>startup mount;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 989859744 bytes
Database Buffers 503316480 bytes
Redo Buffers 13848576 bytes
Database mounted.
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
Check the synchronization between primary and standby
kish@STDBY>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 108
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 109
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 109
9 rows selected.