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.
