In a previous document, DG Broker - Why and How?, I discussed why you should use the Data Guard Broker and how to set it up. At the end of that document, I included a section showing some quick commands to begin using the Broker. In this document, I’m going to cover Broker commands in more detail. As I said in that earlier document, there is a bit of a learning curve to using the Broker. I often find in my discussions with others that this learning curve is the only thing stopping them from implementing the DG Broker in their environment. Hopefully, this document will reduce that learning curve for many.
Connecting and Status
The first thing to do is to start the Broker command line utility, DGMGRL, and connect to a database in the configuration, which is either the primary or a standby. The command line utility’s name is a throwback to a now defunct Oracle utility called SRVMGRL. Today’s Oracle DBA is familiar with the SQL*Plus command line utility. They may not be familiar with SRVMGRL, which stands for SeRVer ManaGeR command-Line. In Oracle 7 up to 8i, the DBA had two utilities, SRVMGRL and SQLPLUS. SRVMGRL was used to start and stop the instance, create the database, initiate recovery, and a few other things. At that time, SQLPLUS was used only to query the database. As new versions came out, Oracle rolled the SRVMGRL functionality into SQLPLUS and today we only have the one utility. At the time SRVMGRL existed, Oracle introduced the Data Guard standby databases in Oracle 8i. A command line interface was needed so Oracle named it similarly to the existing Server Manager utility, which is why we have DGMGRL today, the Data Guard ManaGeR command-Line utility.
The DBA needs to set their environment to execute the DGMGRL utility, just like they do for SQLPLUS or any other utility. With the environment set, simply start the utility and then connect to the database. The connection must be made as a user with SYSDBA privileges on both the primary and standby databases. Most people use the SYS account for this. Unlike SQLPLUS, specifying “as sysdba” is redundant and actually causes an error as can be seen below.
[oracle@host01 ~]$ dgmgrl
DGMGRL for Linux: Version 220.127.116.11.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / as sysdba
CONNECT <username>/<password>[@<connect identifier>]
DGMGRL> connect /
Connected as SYSDG.
The initial connection request was in error and DGMGRL showed the correct syntax. Notice that you can use a TNS alias to connect to a remote database if desired.
Now that we’re connected to the database instance, we can see a summary of the current configuration with the SHOW CONFIGURATION command. If you created the configuration, this information should be known to you. However, if you’re called into to a client site and asked to work on their system and no one there knows the configuration, this is a great place to start.
DGMGRL> show configuration
Configuration – orcl_orcls_confg
Protection Mode: MaxPerformance
orcl - Primary database
orcls - Physical standby database
Fast-Start Failover: DISABLED
SUCCESS (status updated 53 seconds ago)
The output shows me the configuration name (orcl_orcls_confg), the protection mode, and the members of the configuration. In the output above, we can see a primary database and one physical standby database. If your configuration was designed with more than one standby database, all of them would be discovered with this command.
The next thing I typically do is to get an overview of the databases involved in the configuration with the SHOW DATABASE command. Now that I know the database names in this configuration, I can check out each one. First, I’ll look at the primary.
DGMGRL> show database orcl
Database - orcl
Intended State: TRANSPORT-ON
With this command, I can quickly see that the primary is a four-node Oracle RAC database. Redo transport has been turned on. If there were errors in redo transport, each instance would show the error similar to the following:
DGMGRL> show database orcl
Database - orcl
Intended State: TRANSPORT-ON
Error: ORA-16737: the redo transport service for standby database "orcls" has an error
Next, I’ll check out the standby database. This is the command I issue the most often for reasons we’ll see below.
DGMGRL> show database orcls
Database - orcls
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 4 hours 14 minutes 2 seconds (computed 0 seconds ago)
Average Apply Rate: 1.17 MByte/s
Real Time Query: OFF
orcls1 (apply instance)
I can see that the standby is a three-node RAC database. I can also see that Managed Recovery is on one instance named the “apply instance”. The intended state is to have Redo Apply be ON. One of the reasons this is my most often used command is the information that follows the Intended State. I can see the Transport Lag is zero seconds and the Apply Lag is just over 4 hours. This standby database has a four hour apply delay which I will show later, so this is expected. From these two lines, I can quickly see that my standby database is pretty up to date with redo transport and that it’s apply lag is close to my definition.
If you read the paper on setting up the Broker, you’ll know one of the benefits is the integration with Oracle’s Enterprise Manager. Oracle EM just issues broker commands like the one above. The same output from the standby database is seen below in this screen shot from EM13c.
The primary reason for a standby database is to provide access to the data during outages, planned or unplanned. During a planned outage, the DBA may perform a switchover operation so that applications can access the data at the disaster recovery site. After the planned outage is over, the DBA will most likely switch back primary operations to the regular data center. During unplanned outages, the DBA may perform a failover operation to the standby database. Earlier in this document, we used the SHOW CONFIGURATION command to help understand which standby databases exist in our environment. We can simply switchover to the standby database with one command.
DGMGRL> switchover to orcls;
Performing switchover NOW, please wait...
New primary database "orcls" is opening...
The rest of the output was removed for brevity. To perform a failover, the command is basically the same with the only change being the keyword.
DGMGRL> failover to orcls;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcls"
That’s all there is to it! Role transitions are very easy with the DG Broker in place. Compare the paragraph above to the steps required if you do not have the DG Broker configured. The Oracle documentation has devoted an entire chapter to the subject.
The whole point of the Data Guard solution is to send changes, via redo records, from the primary and have those changes applied to a standby. There are times where the DBA needs to take down the standby database for maintenance. If the primary database is configured in MAX PERFORMANCE mode, we can do this without downtime in the primary but it’s a good idea to stop log transport. The DBA may also decide that they do not want changes applied to the standby. Maybe someone accidentally dropped a very large table in the primary and the DBA wants to open the standby in READ ONLY mode and recover the table’s contents. If the DBA stops redo apply, they can get the data before the DROP TABLE command gets applied there. To start or stop redo transport or apply, we use the following command:
EDIT DATABASE db_name SET STATE=desired_state;
In the above, the desired state is either TRANSPORT-ON or TRANSPORT-OFF for primary databases and APPLY-ON or APPLY-OFF for standby databases. For example, to stop redo transport, we modify the primary database as follows:
DGMGRL> edit database orcl set state=transport-off;
The DG Broker will help you configure your environment. Do you want real time apply or an apply lag? Do you want to change the archived redo log file name format? Do you want to use redo compression? Do you want to set up automatic failover? All of these are configurable properties within the DG configuration. If you have implemented the DG Broker, you will only want to change this configuration through the DGMGRL utility. If you change a property through SQL*Plus, the Broker will detect the change and will revert it back to what the Broker thinks it should be. Some properties apply to the primary, some to the standby, some to both.
To see the current property settings, use the VERBOSE clause in the SHOW DATABASE command. For example, here is a short list of some of the properties for my primary database. Many properties were omitted for brevity so try the command in your environment for a complete list.
DGMGRL> show database verbose orcl
Database - orcl
Intended State: TRANSPORT-ON
DGConnectIdentifier = 'orcl'
LogXptMode = 'ASYNC'
DelayMins = '0'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '1200'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
ArchiveLagTarget = '3600'
(*) - Please check specific instance for the property value
Notice the last line. Some properties are instance-specific for Oracle RAC databases. To see the instance-specific properties, you would use the “SHOW INSTANCE VERBOSE instance_name” command in DGMGRL.
This paper cannot cover all of the properties out there. The Oracle documentation has this covered in the Data Guard Broker guide. http://docs.oracle.com/database/121/DGBKR/toc.htm
Look in Chapter 8, Oracle Data Guard Broker Properties for a description of each one. A description of each property is given along with valid values, default values, and more.
To change a property, we use the following command:
EDIT DATABASE db_name SET PROPERTY property_name=value;
For example, the following command is used to implement the four hour apply delay. The documentation tells me this property specifies the number of minutes of apply delay, and four hours is 240 minutes.
DGMGRL> edit database orcls set property DelayMins=240;
Property "delaymins" updated
To be honest, the hardest part of using the DG Broker is understanding what properties are out there and what each one does. The properties are the biggest hurdle people face in their learning curve. That’s where reading the documentation helps. Please read the documentation for the DG Broker as it will help immensely.
It is also helpful to understand that some properties are changeable and others are read-only properties, showing you the status of a specific component in the architecture. The documentation points out which ones are read-only and which ones are configurable. The DelayMins property is configurable as we changed it above. The LogXptStatus property is read-only and shows me any error messages in redo transport. For example, when redo transport errors exist, there could be any number of reasons. If we query the LogXptStatus property for the primary, we will see the exact Oracle error that we can use to diagnose the root cause of the problem.
DGMGRL> show database orcl LogXptStatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
orcl2 ncps ORA-16086: Redo data cannot be written to the standby redo log
I now know the reason for the redo transport errors is the ORA-16086 error message for instance ORCL2. At this point I now have the proper direction in which to look for a resolution to this problem.
Here is a short list of the most commonly used properties in the DG Broker environment with a short description of each.
- CommunicationTimeout - how long before DG Broker commands will timeout if no response received from the configuration member.
- InconsistentProperties - properties in the Broker config that do not match the SPFILE.
- LogXptStatus - status of redo transport
- LogXptMode - sets the redo transport service mode, i.e. SYNC or ASYNC.
- RecvQEntries - shows log files received at the standby but not yet applied.
- SendQEntries - shows log files not successfully sent to the standby
- ApplyLagThreshold - generates a warning if the apply lag exceeds a specific value
- TransportLagThreshold - similarly, generates a warning if the transport lag exceeds a specific value
- DbFileNameConvert - used if the standby has a different file system naming convention than the primary
- DelayMins - Defines the apply delay on the standby.
- LogArchiveFormat - defines the archived redo log file name format.
- PreferredApplyInstance - sets which instance apply should be performed on for RAC standby databases.
- RedoCompression - determines if redo compression should be used or not. Note, this is an Advanced Compression option.
Hopefully this paper has helped reduce the learning curve with the DG Broker and its command line interface, DGMGRL. This learning curve is what typically stops people from implementing the Broker. Once you start working with the DG Broker, you'll learn to appreciate how it can really help in your environment. The Broker is an indispensable tool for any Data Guard deployment.