If you are using an Oracle Database Enterprise Edition chances are that there is at least one environment in your estate making use of Data Guard. And if you are using Data Guard, why not use the broker? I have been using Data Guard broker for a long time now, and it has definitely improved a lot over the first releases, back in the day. I like it so much these days that I feel hard done by if I can’t make use of it. This is of course a matter of personal preference, and I might be exaggerating a little :)
One of the nice additions to the broker in Oracle 12.1 was the ability to validate a database before a role change. This is documented in the Data Guard broker documentation. I certainly don’t solely rely on the output of the command, I have my own checks I’m running that go over and above what a validate can do.
During research I ran the command on my 12.2 system and noticed much enhanced output. This is super exciting and worth blogging about. Well, that is, to me at least-I hope you find this post useful. I love discovering little changes like this, they aren’t always advertised on slide 1 in “What’s new in release X” presentations, but nevertheless great boosts to productivity.
This post was nearly complete, but then rested in my drafts folder for just a little too long and 18c has been released. Time for an update!
The environment
Before moving on, here’s the stack in case you find this via a search engine:
- Oracle Linux 7.4 powering 2 VMs: server1 and server2
- Oracle 18.3.0, single instance, no Oracle Restart
- Data Guard Broker configuration managing 2 databases: NCDBA and NCDBB
The broker is quite happy with my setup, at least for now.
DGMGRL> show configuration Configuration - test Protection Mode: MaxAvailability Members: NCDBA - Primary database NCDBB - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 51 seconds ago) DGMGRL>
This is my setup, YMMV as always. Refer to your standard documents or other relevant documentation for more details about your configuration
New things to validate in 18c
With 12.2 it was possible to validate a datafile in addition to validating the database. Oracle 18c enhances the validate command quite a bit more:
DGMGRL> help validate Performs an exhaustive set of validations for a member Syntax: VALIDATE DATABASE [VERBOSE] ; VALIDATE DATABASE [VERBOSE] DATAFILE OUTPUT=; VALIDATE DATABASE [VERBOSE] SPFILE; VALIDATE FAR_SYNC [VERBOSE] [WHEN PRIMARY IS ]; VALIDATE NETWORK CONFIGURATION FOR { ALL | }; VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | }; DGMGRL>
In this post I am going to focus on the verbose output generated by validate database, if I can find the time I’ll write about the other new options as well.
Validate database in Oracle 18c
Let’s have a look at the output of validate database verbose … Looking at the configuation status, NCDBB is currently running in recovery mode with NCDBA acting as the primary database. It makes sense to start with the validation of NCDBB first.
The output of the command is rather comprehensive as you will see, I have decided to annotate the output so you don’t have to scroll up and down that much.
DGMGRL> validate database verbose 'NCDBB' Database Role: Physical standby database Primary Database: NCDBA Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: NCDBA : Off NCDBB : Off Capacity Information: Database Instances Threads NCDBA 1 1 NCDBB 1 1 Managed by Clusterware: NCDBA : NO NCDBB: NO Validating static connect identifier for database NCDBA... The static connect identifier allows for a connection to database "NCDBA".
The first part of the output is related to the database’s role and status. Oracle reckons my standby database is ready for a role change (which I’ll double-check using my own tools and scripts). You can see that flashback database is not enabled (for reasons that don’t matter for this post).
Since both members are single instance databases it makes sense for them to have a single redo thread.
Another important piece of information can be found in the “managed by Clusterware” section. In releases prior to 12.1.0.2 you always had to statically register your databases with the listener for use with the broker. This has changed in 12.1.0.2: another one of these productivity boosters :) In modern releases you don’t need to statically register your databases with the listener provided Clusterware manages them. See MOS DocID 1387859.1 for all the details.
You read in the introduction that I’m using single instance Oracle databases without any Grid Infrastructure at all so this shiny new feature does not apply. There’s something else that might help though: Oracle validates the static connection identifier for you. It doesn’t simply ping the tns alias, the broker actually establishes a connection to the database. I noticed this in the listener.log: a program named dgmgrl connects to the database using the static conncetion identifier (as per “show database memberName staticconnectidentifier”). Output is formatted for readability:
2018-08-14 10:54:16.377000 +01:00 14-AUG-2018 10:54:16 * (CONNECT_DATA=(SERVICE_NAME=NCDBB_DGMGRL)(INSTANCE_NAME=NCDBB)(SERVER=DEDICATED) (STATIC_SERVICE=TRUE)(CID=(PROGRAM=dgmgrl)(HOST=server1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp) (HOST=192.168.100.21)(PORT=27049)) * establish * NCDBB_DGMGRL * 0
Let’s continue with the output of the validate database command:
Temporary Tablespace File Information: NCDBA TEMP Files: 1 NCDBB TEMP Files: 1 Data file Online Move in Progress: NCDBA: No NCDBB: No
This little section compares the number of temp files and warns you of any online data file move operations.
Standby Apply-Related Information: Apply State: Running Apply Lag: 0 seconds (computed 0 seconds ago) Apply Delay: 0 minutes Transport-Related Information: Transport On: Yes Gap Status: No Gap Transport Lag: 0 seconds (computed 0 seconds ago) Transport Status: Success Log Files Cleared: NCDBA Standby Redo Log Files: Cleared NCDBB Online Redo Log Files: Not Cleared NCDBB Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (NCDBA) (NCDBB) 1 2 3 Sufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (NCDBB) (NCDBA) 1 2 3 Sufficient SRLs Current Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (NCDBA) (NCDBB) 1 200 MBytes 200 MBytes Future Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (NCDBB) (NCDBA) 1 200 MBytes 200 MBytes
This section is quite interesting as well as it allows you to have a gander at the transport lag and apply lag respectively. I can also see that I have standby redo logs both for my primary as well as the standby database. Sometimes the broker doesn’t get the number of standby redo logs right in “future log file groups configuration”. I found this to rectify itself after a switchover and switch-back.
Another reassuring fact is presented in the current and future configuration log file sizes: I follwed th documentation carefully and created all my (online and standby) redo logs the exact same size.
Apply-Related Property Settings: Property NCDBA Value NCDBB Value DelayMins 0 0 ApplyParallel AUTO AUTO ApplyInstances 0 0 Transport-Related Property Settings: Property NCDBA Value NCDBB Value LogXptMode sync sync Dependency DelayMins 0 0 Binding optional optional MaxFailure 0 0 MaxConnections 1 1 ReopenSecs 300 300 NetTimeout 30 30 RedoCompression DISABLE DISABLE LogShipping ON ON
The section just above compares apply and transport related settings between the primary and standby databases. These are stored in Data Guard broker properties eventually mapping back to database initialisation parameters.
Automatic Diagnostic Repository Errors: Error NCDBA NCDBB 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>
And finally, it appears as if the Automatic Diagnostic Repository (ADR) didn’t show any issues. This doesn’t mean there aren’t, maybe there is still something looming in your configuration that hasn’t been detected yet.
Summary
The validate database command is really quite useful in my opinion performing basic checks before role changes. It doesn’t take the responsibility off you for ensuring the role change will be a success but I found it to be a useful first indicator.