This post offers Step by Step method to perform Switchover using Dataguard Broker command prompt DGMGRL. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
DG configuration : 12c
Primary db_unique_name : Sydney
Standby db_unique_name : Washington
SWITCHOVER Considerations
1. DG Broker will not downgrade the PROTECTION mode during the process of Switchover.
2. Configure suitable REDO transport mode on current standby that becomes Primary after switchover.
3. Configure SRL (Standby Redo Logs) and local archiving destination on current Primary.
Broker switchover command verifies the availability of SRL on current Primary Database and suitable Redo transport method on current Standby.
DB Restart Considerations
1. If the switchover target is Physical Standby then Broker shutdown the Primary as a process of switchover.
2. If the switchover environment in MAX_PROTECTION mode and primary has only one Standby then broker shuts down both primary and standby as a process of switchover. If primary has multiple standby then broker shuts down only the new Primary.
Pre-Switchover Checks
1. Verify Dataguard Broker Configuration – Use following command s to verify broker status before switchover.
DGMGRL>show configuration; DGMGRL>show database [primary]; DGMGRL>show database [standby]; DGMGRL>show database verbose [primary]; DGMGRL>show database verbose [standby];
Example outputs in our setup:
DGMGRL> show configuration; Configuration - 12c Protection Mode: MaxPerformance Databases: sydney - Primary database washington - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
DGMGRL> show database sydney; Database - sydney Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): sydney Database Status: SUCCESS
DGMGRL> show database washington Database - washington Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Apply Rate: 0 Byte/s Real Time Query: ON Instance(s): washington Database Status: SUCCESS
NOTE: For further troubleshooting, use:
DGMRL>show database db_name logxptstatus; DGMGRL>show database db_name inconsistentlogxptprops;
Validate Database
Validate database verify following, no need to explicitly check whether ORLs/SRLS cleared.
- Whether there is missing redo data on a standby database
- Whether flashback is enabled
- The number of temporary tablespace files configured
- Whether an online data file move is in progress
- Whether online redo logs are cleared for a physical standby database
- Whether standby redo logs are cleared for a primary database
- The online log file configuration
- The standby log file configuration
- Apply-related property settings
- Transport-related property settings
DGMGRL> validate database sydney Database Role: Primary database Ready for Switchover: Yes <-----------Ready for Switchover Flashback Database Status: sydney: Off
DGMGRL> validate database washington Database Role: Physical standby database Primary Database: sydney Ready for Switchover: Yes <--------This confirms Switchover readiness Ready for Failover: Yes (Primary Running) Flashback Database Status: sydney: Off washington: Off Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups (washington) (sydney) 1 4 4
NOTE: For further troubleshoot use validate database verbose.
DGMGRL> validate database verbose sydney DGMGRL> validate database verbose washington
Preset Redo Transport related parameter
Verify below parameters are set based on PROTECTION MODE.
- LogXptMode
- NetTimeout
- StandbyArchiveLocation
- AlternateLocation
- RedoRoutes
SWITCHOVER
Disable Apply Delay
To increase the speed of switchover disable delaymins property.
DGMGRL>edit database washington set property delaymins=0;
Turn on Data Guard tracing on primary and standby:
DGMGRL> edit configuration set property tracelevel=support; DGMGRL> edit database sydney set property LogArchiveTrace=8191; DGMGRL> edit database washington set property LogArchiveTrace=8191;
for RAC instance,
DGMGRL> EDIT INSTANCE * ON DATABASE 'sydney' SET PROPERTY LogArchiveTrace=8191;
Tail Alert Logs and DRC (optional) on all instances
Tail alert log and DRC
tail –f [alert log of primary] tail –f [alert log of standby] tail –f [drc[SID] log of primary] tail –f [drc[SID] log of standby]
Perform Switchover
Connect to sysdg:
DGMGRL> connect sysdg Password: Connected as SYSDG.
DGMGRL> switchover to washington Performing switchover NOW, please wait... Operation requires a connection to instance "washington" on database "washington" Connecting to instance "washington"... Connected as SYSDBA. New primary database "washington" is opening... Operation requires startup of instance "sydney" on database "sydney" Starting instance "sydney"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "washington"
Sample Alert Logs
Primary Alert log - alert_sydney.log:
ALTER DATABASE SWITCHOVER TO 'washington' Starting switchover [Process ID: 10402] ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10402] (sydney) ... Switchover End-Of-Redo Log thread 1 sequence 131 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x310a3a ARCH: Noswitch archival of thread 1, sequence 131 ARCH: End-Of-Redo Branch archival of thread 1 sequence 131 ARCH: Evaluating archive log 1 thread 1 sequence 131 ... Switchover complete. Database shutdown required Completed: ALTER DATABASE SWITCHOVER TO 'washington'
Standby Alert log - alert_washington.log:
SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. ALTER DATABASE SWITCHOVER TO PRIMARY (washington) ... Standby became primary SCN: 3213880 <----------Record this SCN ... Switchover: Complete - Database mounted as primary SWITCHOVER: completed request from primary database.
Primary Drc log - /path/diag/rdbms/sydney/sydney/trace/drcsydney.log:
SWITCHOVER TO washington ... Broker Configuration: "12c" Protection Mode: Maximum Performance Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0 Primary Database: washington (0x02010000) <-------washington as Primary
Standby DRC log - /path/diag/rdbms/washington/washington/trace/drcwashington.log:
Notifying Oracle Clusterware to prepare target standby database for switchover Notifying DMON of db close Notifying RSM0 of db close ... Deferring associated archivelog destinations of sites permanently disabled due to Switchover Notifying Oracle Clusterware to buildup primary database after switchover
Post-Switchover Check
Reset Delaymins property
Change the delaymins property to prior on new standby after switchover based on business requirement.
DGMGRL> edit database [new standby] set property delaymins=[old MRP delay value];
Set Trace to Prior Value
DGMGRL> edit configuration reset property tracelevel; DGMGRL> edit database sydney reset property logarchivetrace; DGMGRL> edit database washington reset property logarchivetrace;
Verify Broker Configuration
DGMGRL> show configuration; Configuration - 12c Protection Mode: MaxPerformance Databases: washington - Primary database sydney - Physical standby database <-----sydney is Physical Standby Fast-Start Failover: DISABLED Configuration Status: SUCCESS