Dataguard Switchover Best Practices using DGMGRL (Dataguard Broker Command Prompt)
Alireza Kamrani
08/07/2024
Target:
This Document 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:
In this document, I have tested this scenario using Oracle 12c, but these steps can be used in higher versions as well.
Dg configuration : 12c
Primary db_unique_name : orcl
Standby db_unique_name : pop
SOLUTION
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.
I. Pre-Switchover Checks
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>;
DGMGRL> show configuration;
Configuration - 12c
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
pop - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database orcl;
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): orcl
Database Status:
SUCCESS
DGMGRL> show database pop
Database - pop
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): pop
Database Status:
SUCCESS
NOTE : Further troubleshoot use:
DGMRL>show database ‘<>’ logxptstatus;
DGMGRL>show database ‘<>‘ 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 orcl
Database Role: Primary database
Ready for Switchover: Yes <--Ready***
Flashback Database Status:
orcl: Off
DGMGRL> validate database pop
Database Role:Physical standbydatabase
Primary Database: orcl
Ready for Switchover: Yes<--This confirms Switchover readiness***
Ready for Failover:Yes (Primary Running)
Flashback Database Status:
orcl: Off
pop: Off
Future Log File Groups Configuration:
Thread #
Online Redo Log Groups
Standby Redo Log Groups
(pop) (orcl)
1 4 4
NOTE : For further troubleshoot use validate database verbose.
DGMGRL>validate database verbose orcl
DGMGRL>validate database verbose pop
Preset Redo Transport related parameter
Verify below parameters are set based on PROTECTION MODE.
LogXptMode, NetTimeout, StandbyArchiveLocation, AlternateLocation, and RedoRoutes.
II. SWITCHOVER
Disable Apply Delay
To increase the speed of switchover disable delaymins property.
DGMGRL>edit database pop set property delaymins=0;
Turn on Data Guard tracing on primary and standby
DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> edit database orcl set property LogArchiveTrace=8191;
DGMGRL> edit database pop set property LogArchiveTrace=8191;
for RAC instance,
DGMGRL> EDIT INSTANCE * ON DATABASE 'orcl' SET PROPERTY LogArchiveTrace=8191;
Tail Alert Logs and DRC (optional) on all instances
Tail alert log and DRC<SID> log which is present in same location of alert log(text) from all the nodes of primary and standby.
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 pop
Performing switchover NOW, please wait...
Operation requires a connection to instance "pop" on database "pop"
Connecting to instance "pop"...
Connected as SYSDBA.
New primary database "pop" is opening...
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "pop"
--Primary Alert log- alert_orcl.log--
ALTER DATABASE SWITCHOVER TO 'pop'
Starting switchover [Process ID: 10402]
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10402] (orcl)
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 'pop'
--Standby Alert log -alert_pop.log--
SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
ALTER DATABASE SWITCHOVER TO PRIMARY (pop)
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/orcl/orcl/trace/drcorcl.log"--
SWITCHOVER TO pop
Broker Configuration: "12c"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
Primary Database: pop (0x02010000) <--pop as Primary***
--Standby DRC log - "/<path>/diag/rdbms/orcl/pop/trace/drcpop.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
III. 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 orcl reset property logarchivetrace;
DGMGRL> edit database pop reset property logarchivetrace;
Verify Broker Configuration
DGMGRL> show configuration;
Configuration - 12c
Protection Mode: MaxPerformance
Databases:
pop - Primary database
orcl - Physical standby database <-----orcl is Physical Standby
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Sincerely,
Alireza Kamrani.
Database Technical Consultant.
No comments:
Post a Comment