DG Broker – Why and How?

Версия 3

    Introduction

     

    Are you using the Data Guard (DG) Broker in your Oracle standby configuration? People typically shy away from implementing the DG Broker for one of two reasons. Either they are not aware of the benefits the DG Broker provides or they are leery of the additional complexity to their configuration. This paper will discuss the Broker’s benefits and hopefully make it seem easy to implement.

     

    Why Use the Broker?

     

    You can certainly create a Data Guard configuration without implementing the DG Broker. The Broker is not required and your life may go on forever without it. So why do you want to use it? There is only one reason why you want to implement the Broker. The DG Broker makes your life easier. Yes, you do have to configure it and it does have additional management overhead. There is also a learning curve to it. But in the end, the additional work easily pays for itself. Once you’ve learned how to work with the DG Broker, you’ll wonder why you ever lived without it. Below is a list of some of the benefits the DG Broker provides for you.

     

    • Automatic start of managed recovery – You no longer need to write a post-startup script to automatically enable managed recovery. The Broker will handle that for you. It will also remember your last managed recovery configuration and resume operations at that level the next time.
    • One command for Failover/Switchover – If you do not have the Broker configured, a switchover operation requires multiple steps. You have to use SQL*Plus connected to the primary, and another session connected to the standby, and then perform a series of commands in order to cause a switchover. With the DG Broker, it becomes as simple as SWITCHOVER DATABASE. One command…that’s it.
    • Integration with Enterprise Manager – If you like to point and click your way through database administration, then you likely have Enterprise Manager 13c configured for your environment. The only thing EM knows about when it comes to standby databases is Broker commands. If you want to perform a switchover operation in EM13c with the click of a button, you will need the Broker configured. EM13c cannot issue the corresponding SQL statements, only Broker commands.
    • Single-stop configuration – If you want to change the primary/standby configuration, you can sign on to the DG Broker’s command line utility from either the primary or the standby server and enter the command there. Even if the command is for the other system’s database, the Broker will route the command properly for you. The Broker can even handle multiple standby databases with ease.
    • Single-stop monitoring – The DG Broker provides a one-stop shop for monitoring your standby environment. The Broker can determine the configuration health, standby performance, transport and apply lag all from a single location. The alternative is to query different dynamic performance views.
    • RAC aware – The Broker is RAC aware. It knows if your primary and/or your standby is an Oracle RAC database and can handle all of the RAC details for you with the same commands as single-instance databases.

     

    Many DBAs use the Broker’s command line utility, DGMGRL. As stated above, if the DG Broker is setup and you want to use a GUI utility, you interact with the Broker through EM13c.

     

    Hopefully by now, you are convinced that using the DG Broker is a good thing to do. It provides many benefits. The benefits definitely outweigh the cost of administering one more component in the architecture and the learning curve required to use it.

     

    How to Configure the Broker

     

    Hopefully by now, you are convinced the DG Broker is a good thing. This section will discuss how to get the Broker up and running in your environment. For this paper, I am using the same Data Guard setup I created in this paper: How to Create a Physical Standby Database

     

    Before I can setup the Broker, I need to make sure that both the primary and the standby have aliases in the TNSNAMES.ORA configuration file. The Broker is just another application that will want connect to the primary and the standby databases so we need to provide the TNS aliases. I make sure that both primary and standby servers have the following in the TNSNAMES.ORA file:

     

    ORCL =

      (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prim)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = orcl)

        )

      )

    ORCLS =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = stdby)(PORT = 1521))

    (CONNECT_DATA =

    (SERVICE_NAME = orcls)

        )

      )

     

    ORCL is the primary and ORCLS is the standby.

     

    One of the benefits of the Broker is simplified switchover. When a switchover is performed, the Broker will restart the standby instance. If the standby database relies on dynamic registration with the listener, the Broker will not be able to make a SYSDBA connection to start the instance in MOUNT mode. We need to use static registration so that the Listener knows about the standby’s service name even if the standby instance is down. Similarly, we want the Listener on the primary to use static registration. The standby server will have a LISTENER.ORA config file that looks like the following:

     

    LISTENER = (DESCRIPTION =

    (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=stdby.localdomain)

    (PORT=1521))))

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = stdby_dgmgrl)

    (SID_NAME = stdby)

    (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2)

        )

      )

     

    Notice that the GLOBAL_DB_NAME has “dgmgrl” at the end of it. This is required for the DG Broker to work correctly. A “lsnrctl status” execution has the following output (trimmed for brevity):

     

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdby.localdomain)(PORT=1521)))

    Services Summary...

    Service "orclXDB" has 1 instance(s).

      Instance "orcls", status READY, has 1 handler(s) for this service...

    Service "orcls" has 1 instance(s).

      Instance "orcls", status READY, has 1 handler(s) for this service...

    Service "stdby_dgmgrl" has 1 instance(s).

      Instance "stdby", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

     

     

    We can see the standby database’s service “orcls” as well as the “stdby_dgmgrl” service used by the Broker. We will have a similar configuration for the primary because if we switchover, we want to be able to switch back. For Oracle RAC implementations, the above needs to be on all nodes.

     

    One other requirement is that both the primary and the standby must be using the SPFILE for initialization parameters. I’ll leave it up to the reader to ensure the SPFILE is being used and if not, convert to using one.

     

    Next, we need to define a few initialization parameters. The DG Broker has two multiplexed configuration files. Ideally, these would be on different disk devices but for my testbed, they will be in the same directory. I define the Broker config files in both the primary and the standby databases as follows:

     

    SQL> alter system set dg_broker_config_file1='/u01/app/oracle/oradata/orcl/dg_config1.dat' scope=both;

     

    System altered.

     

    SQL> alter system set dg_broker_config_file2='/u01/app/oracle/oradata/orcl/dg_config2.dat' scope=both;

     

    System altered.

     

    If any of these are an Oracle RAC system, the Broker config files must be on shared storage, accessible by all nodes in the cluster. ASM can be used if desired.

     

    The next thing to do is to start the Broker. This is done by setting the following initialization parameter.

     

    SQL> alter system set dg_broker_start=true scope=both;

     

    System altered.

     

    The DG_BROKER_START parameter defines if the Broker will start up. We set this to TRUE in the SPFILE so that the Broker starts when the instance comes up. You can manually set this to FALSE if you want to shutdown the Broker. We can confirm the Broker startup in the Alert Log.

     

    Wed Oct 12 19:33:23 2016

    ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

    Wed Oct 12 19:33:23 2016

    DMON started with pid=32, OS id=2834

    Starting Data Guard Broker (DMON)

    Starting background process INSV

    Wed Oct 12 19:33:31 2016

    INSV started with pid=40, OS id=2837

     

    The DMON process is the DG Broker. We can see in the Alert Log the process starts once I set the parameter value. The INSV process is the DG Broker’s Instance Slave Process and will also be present. For Oracle RAC implementations, each instance will have a running DMON and INSV process when the Broker is running.

     

    The Broker is now running and I can connect to it with the command line utility, DGMGRL. But at this point, the Broker isn’t doing anything. Before we’re operational with the Broker, we need to complete a few steps. They are as follows:

     

    1. Create a Broker Configuration.
    2. Add the standby to the configuration.
    3. Enable that Configuration.

     

    So let’s proceed with our steps. I only need to do these steps on one system. I’m going to run this from the primary host, but you could run it from the standby host just fine. First, I’m going to launch the DGMGRL utility and make a connection.

     

    [oracle@prim admin]$ 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 /

    Connected as SYSDG.

     

    Notice that I do not specify “as sysdba” on my connection. Doing so would result in an error. Now let’s create that configuration.

     

    DGMGRL> create configuration orcl_orcls_config as

    > primary database is orcl

    > connect identifier is 'orcl';

    Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

     

    I specify a configuration name of 30 characters or less. I typically use the form “primaryname_standbyname_config” for my configuration name, but you can use anything you want. The second line specifies the primary database’s name. The primary database needs to be named when the configuration is created. The third line tells the Broker the TNSNAMES.ORA alias we created earlier that points to the primary database.

     

    Notice that I received an ORA-16698 error when creating the configuration. This will happen if you are creating a configuration in Oracle 12c. You will not receive this error for earlier versions. According to the documentation, “any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared”. So on both the primary and the standby databases, I need to clear this archive destination as follows:

     

    SQL> alter system set log_archive_dest_2='' scope=both;

     

    System altered.

     

    In DGMGRL, I can now create the configuration.

     

    DGMGRL> create configuration orcl_orcls_config as

    > primary database is orcl

    > connect identifier is 'orcl';

    Configuration "orcl_orcls_config" created with primary database "orcl"

     

    Next, I’ll add the standby database to the configuration.

     

    DGMGRL> add database orcls as

    > connect identifier is 'orcls';

    Database "orcls" added

     

    And then I enable that configuration. Do not forget this step. It's too easy to think that once you've defined the primary and the standby you're done. Or maybe I should say that I've forgotten to enable the configuration more than once and then had to hunt around to find out why its not working for me.

     

    DGMGRL> enable configuration;

    Enabled.

     

    Now let’s examine our config.

     

    DGMGRL> show configuration

     

    Configuration - orcl_orcls_config

     

    Protection Mode: MaxPerformance

      Members:

    orcl  - Primary database

        orcls - Physical standby database

     

    Fast-Start Failover: DISABLED

     

    Configuration Status:

    SUCCESS   (status updated 22 seconds ago)

     

    We can see the primary and the physical standby are listed correctly. We can see in the Alert Log that the Broker goes back in and updates LOG_ARCHIVE_DEST_2 that we had to clear.

     

    Wed Oct 12 19:53:04 2016

    ALTER SYSTEM SET log_archive_dest_2='service="orcls"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="orcls" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

     

    I use the Broker to get a status of both the primary and the standby similar to the following:

     

     

    DGMGRL> show database orcl

     

    Database - orcl

     

    Role:               PRIMARY

      Intended State:     TRANSPORT-ON

    Instance(s):

        orcl

     

    Database Status:

    SUCCESS

     

    The role of ‘orcl’ is the PRIMARY and log transport is on. The status is SUCCESS which means the primary is not having any problems transporting logs to the standby. For the standby:

     

    DGMGRL> show database orcls

     

    Database - orcls

     

    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: 1.81 MByte/s

      Real Time Query:    ON

    Instance(s):

        orcls

     

    Database Status:

    SUCCESS

     

    This database has Log Apply set to ON and SUCCESS for the status. If there were problems with the Log Apply, then I would see an error here.

     

     

    Some Quick Commands

     

    You’ve seen how easy it is to get a quick status of the primary and the standby with the SHOW DATABASE command. And this is done from the same DGMGRL session. I did not have to start one session for the primary and one session for the standby. I’ll provide some other quick commands to get you up to speed on the DG Broker.  For example, to stop and then start Log Transport, do the following:

     

    DGMGRL> edit database orcl set state=transport-off;

    Succeeded.

    DGMGRL> edit database orcl set state=transport-on;

    Succeeded.

     

    To stop then start Log Apply, do the following:

     

    DGMGRL> edit database orcls set state=apply-off;

    Succeeded.

    DGMGRL> edit database orcls set state=apply-on;

    Succeeded.

     

    Simple, easy commands, all from one session. To perform a switchover, its an easier operation.

     

    DGMGRL> switchover to orcls;

    Performing switchover NOW, please wait...

    New primary database "orcls" is opening...

     

    The rest of the output was removed for brevity. But we can see one simple switchover command which is much, much easier than doing this in SQL*Plus. We can confirm the config now matches our switchover.

     

    DGMGRL> show configuration

     

    Configuration - orcl_orcls_config

     

    Protection Mode: MaxPerformance

      Members:

      orcls - Primary database

    orcl  - Physical standby database

     

    Fast-Start Failover: DISABLED

     

    Configuration Status:

    SUCCESS   (status updated 15 seconds ago)

     

    ORCLS is the primary and ORCL is the standby. I perform a simple “switchover to orcl” to get back running where I intend to be, with ORCL as the primary.

     

    Next, I want to add a four hour (240 minute) apply delay on the standby. For my thoughts on why I might want an apply delay, check out this blog post: RealTime Apply Considerations

     

    DGMGRL> edit database orcls set property DelayMins=240;

    Property "delaymins" updated

    DGMGRL> show database orcls DelayMins

      DelayMins = '240'

     

    Adding the Apply Delay is done by changing one of the standby database’s properties. I will discuss these properties in more detail in a future paper. I can revert back to Real Time Apply by setting the delay to 0 minutes. I will also show in a future paper how easy much of this is when using Oracle’s Enterprise Manager 13c Cloud Control, something I could not do until I configured the DG Broker.

     

    Conclusion

     

    Hopefully this paper sold you on the idea that the DG Broker is a very good thing to use. There is a learning curve to be sure. But the time spent learning it will pay off very quickly because the Broker handles much of your standby configuration. To see what I mean, implement a switchover and then look at everything that goes on in your Alert Log on both the primary and the standby.

     

    Update (Jan 11, 2017 - BSP):  I added a DG Broker Primer paper here: DG Broker Primer

    Please read that next to get you more comfortable with the DG Broker commands..