In the previous three parts of this series a lot of preparation work, needed for the configuration of Data Guard, was performed. In this part of the mini-series they all come to fruition. Using the Data Guard broker a switchover operation will be performed. A couple of new features in 12c make this easier. According to the “Changes in This Release for Oracle Data Guard Concepts and Administration” chapter of the 12.1 Data Guard Concepts and Administration guide:
When [you, ed.] perform a switchover from an Oracle RAC primary database to a physical standby database, it is no longer necessary to shut down all but one primary database instance.
I have always wanted to test that in a quiet moment…
I have previously blogged about another useful change that should make my life easier: the static registration of the *_DGMGRL services in the listener.ora file is no longer needed. Have a look at my Data Guard Broker Setup Changes post for more details and reference to the documentation.
NOTE: As always, this is just a demonstration using VMs in my lab, based on my notes. Your system is most likely different, so in real-life you might take a different approach. The techniques I am using here were suitable for me, and my own small scale testing. I tried to make sure they are valid, but you may want to allocate more resources in your environment. Test, test, test on your own environment on test kit first!
Now let’s get to it.
Step 1: Check the status of the configuration
In the first step I always check the configuration and make sure I can switch over. Data Guard 12c has a nifty automatic check that helps, but I always have a list of tasks I perform prior to a switchover (not shown in this blog post).
The following commands are somewhat sensitive to availability of the network – you should protect your sessions against any type of network failure! I am using screen (1) for that purpose, there are other tools out there doing similar things. Network glitches are too common to ignore, and I have come to appreciate the ability to resume work without too many problems after having seen the dreaded “broken pipe” message in my terminal window…
[oracle@rac12sec1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ncdbb
Password:
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - ractest
Protection Mode: MaxPerformance
Members:
NCDBA - Primary database
NCDBB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> validate database 'NCDBB'
...
The command to check for switchover readiness is new to 12c as well and called “validate database”. I don’t have screen output from the situation at this point-just take my word that I was ready :) Don’t switch over if you have any concerns or doubts the operation might not succeed! “Validate database” does not relieve you from your duties to check for switchover readiness – follow your procedures.
Step 2: Switch Over
Finally, the big moment has come! It takes just one line to perform the switchover:
DGMGRL> switchover to 'NCDBB'
Performing switchover NOW, please wait...
New primary database "NCDBB" is opening...
Oracle Clusterware is restarting database "NCDBA" ...
Switchover succeeded, new primary is "NCDBB"
DGMGRL>
DGMGRL> show database 'NCDBA';
Database - NCDBA
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: 9.00 KByte/s
Real Time Query: ON
Instance(s):
NCDBA1
NCDBA2 (apply instance)
Database Status:
SUCCESS
DGMGRL> show database 'NCDBB';
Database - NCDBB
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
NCDBB1
NCDBB2
Database Status:
SUCCESS
DGMGRL>
Well that was easy! Did you notice Data Guard Broker telling us that ‘Oracle Clusterware is restarting database “NCDBA” …’ ? I like it.
If you get stuck at this point something has gone wrong with the database registration in the OCR. You shouldn’t run into problems though, because you tested every aspect of the RAC system before handing the system over to its intended users, didn’t you?
Validating the new standby database shows no issues. I haven’t noticed it before but “validate database” allows you to get more verbose output:
DGMGRL> validate database verbose 'NCDBA';
Database Role: Physical standby database
Primary Database: NCDBB
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Capacity Information:
Database Instances Threads
NCDBB 2 2
NCDBA 2 2
Temporary Tablespace File Information:
NCDBB TEMP Files: 1
NCDBA TEMP Files: 1
Flashback Database Status:
NCDBB: On
NCDBA: Off
Data file Online Move in Progress:
NCDBB: No
NCDBA: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
NCDBB Standby Redo Log Files: Cleared
NCDBA Online Redo Log Files: Cleared
NCDBA Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(NCDBB) (NCDBA)
1 2 3 Sufficient SRLs
2 2 3 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(NCDBA) (NCDBB)
1 2 3 Sufficient SRLs
2 2 3 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(NCDBB) (NCDBA)
1 50 MBytes 50 MBytes
2 50 MBytes 50 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(NCDBA) (NCDBB)
1 50 MBytes 50 MBytes
2 50 MBytes 50 MBytes
Apply-Related Property Settings:
Property NCDBB Value NCDBA Value
DelayMins 0 0
ApplyParallel AUTO AUTO
Transport-Related Property Settings:
Property NCDBB Value NCDBA Value
LogXptMode ASYNC ASYNC
RedoRoutes <empty> <empty>
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error NCDBB NCDBA
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO
DGMGRL>
Isn’t that cool? That’s more information at my fingertips than I can shake a stick at! It’s also a lot more than I could think of (eg online datafile move!).
Interestingly the Broker reports that I have “Sufficient SRLs”. I have seen it complain about the number of Standby Redo Logs in the past and blogged about this Interesting observation about standby redo logs in Data Guard
Summary
After 4 (!) posts about the matter I have finally been able to perform a switchover operation. Role reversals are a much neglected operation a DBA should be comfortable with. In a crisis situation everyone needs to be clear about what needs to be done to restore service to the users. The database is usually the easier part … Success of Data Guard switchover operations also depends on the quality of change management: it is easy to “forget” applying configuration changes on the DR site.
In today’s busy times only few of us are lucky enough to intimately know each and every database we look after. What’s more common (sadly!) is that a DBA looks after 42 or more databases. This really only works without too many issues if procedures and standards are rock solid, and enforced.
