Configuring DataGuard Broker on Oracle 19c

Introduction

Oracle provides a tool to manage, configure, and monitor the components of a DataGuard environment called DataGuard Broker , according to the documentation:

The Oracle Data Guard Broker is a distributed management system that automates and centralizes the creation, maintenance, and monitoring of Oracle Data Guard configurations.

Most of the activities required to manage and monitor databases in the configuration can be performed using DGMGRL commands.

Here we will see how we can enable the DataGuard Broker and use it through the DGMGRL command line interface .

Prerequisites

We recently built a DataGuard environment by creating a Physical Standby for our Oracle 19c multitenant installation in the note Create an Oracle DataGuard Physical Standby . We will reuse that installation by adding what is needed to activate the DataGuard Broker.

Enabling the DataGuard Broker

Configuring Database Parameters

First we need to prepare the instances that are part of the DataGuard environment so that the Broker's internal processes are activated. In the Primary instance, modify the parameter DG_BROKER_START:

[oracle@patodgprmy ~]$ sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both;

System altered.

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /opt/oracle/product/19c/dbhome
                                                 _1/dbs/dr1prmydb.dat
dg_broker_config_file2               string      /opt/oracle/product/19c/dbhome
                                                 _1/dbs/dr2prmydb.dat
dg_broker_start                      boolean     TRUE
        

In the Standby instance we also modify the parameter DG_BROKER_STARTand we must clean the parameter log_archive_dest_n:

[oracle@patodgstby ~]$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both;

System altered.

SQL> alter system set log_archive_dest_2=" ";

System altered.
        

Add a Static Service to the Listener

The DataGuard Broker needs a static service to be able to restart the instances, so we need to add a service called db_unique_name_DGMGRLon listener.oraboth servers:

[oracle@patodgprmy ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=prmydb)
     (GLOBAL_DBNAME=prmydb_DGMGRL)
     (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
     (ENVS="TNS_ADMIN=/opt/oracle/product/19c/dbhome_1/network/admin")))
        

[oracle@patodgstby ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=stbydb)
     (GLOBAL_DBNAME=stbydb_DGMGRL)
     (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
     (ENVS="TNS_ADMIN=/opt/oracle/product/19c/dbhome_1/network/admin")))
        

Connecting to the Command Line Interface

Send the command dgmgrlon the Primary instance to access the DataGuard Broker, then connect to the database with the user SYSDG:

[oracle@patodgprmy ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 15 19:17:09 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> CONNECT sysdg;
Password:
Connected to "prmydb"
Connected as SYSDG.
DGMGRL>
        

Alternatively the user can be used SYSif a user has not been configured for DataGuard in the orapwd file.

Creating a Configuration in the DataGuard Broker

Once connected we need to create a new DataGuard configuration specifying the DB_UNIQUE_NAMEand service name for the Primary database. And then add the Standby database specifying its DB_UNIQUE_NAMEand service name:

DGMGRL> CREATE CONFIGURATION dgpato AS PRIMARY DATABASE IS prmydb CONNECT IDENTIFIER IS prmydb;
Configuration "dgpato" created with primary database "prmydb"

DGMGRL> ADD DATABASE stbydb AS CONNECT IDENTIFIER IS stbydb;
Database "stbydb" added

DGMGRL> show configuration

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  prmydb - Primary database
    stbydb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED
        

Enabling Configuration in the DataGuard Broker

We need to enable this setting in order to control and monitor this DataGuard environment:

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  prmydb - Primary database
    stbydb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 56 seconds ago)
        

And that's it, we have the DataGuard Broker activated and the configuration enabled to control and monitor our DataGuard environment.

Monitoring DataGuard

Show Database Status

You can issue the command SHOW DATABASEto view relevant information about the databases and the status of the Transport (Primary) and Apply (Standby) processes:

DGMGRL> show database prmydb

Database - prmydb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    prmydb

Database Status:
SUCCESS

DGMGRL> show database stbydb

Database - stbydb

  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: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    stbydb

Database Status:
SUCCESS
        

Validate that the Database is Ready

You can send the command VALIDATE DATABASEto validate if the database is ready for a possible Switchover and Failover :

DGMGRL> validate database stbydb

  Database Role:     Physical standby database
  Primary Database:  prmydb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    prmydb:  NO
    stbydb:  NO
    Validating static connect identifier for the primary database prmydb...
    The static connect identifier allows for a connection to database "prmydb".
        

Validate Connectivity

You can send the command VALIDATE NETWORKto check that the connectivity between both databases (password, service, network) is working properly:

DGMGRL> VALIDATE NETWORK CONFIGURATION for all
Connecting to instance "prmydb" on database "prmydb" ...
Connected to "prmydb"
Checking connectivity from instance "prmydb" on database "prmydb to instance "stbydb" on database "stbydb"...
Succeeded.
Connecting to instance "stbydb" on database "stbydb" ...
Connected to "stbydb"
Checking connectivity from instance "stbydb" on database "stbydb to instance "prmydb" on database "prmydb"...
Succeeded.

Oracle Clusterware is not configured on database "prmydb".
Connecting to database "prmydb" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prmydb_DGMGRL)(INSTANCE_NAME=prmydb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "prmydb".

Oracle Clusterware is not configured on database "stbydb".
Connecting to database "stbydb" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stbydb_DGMGRL)(INSTANCE_NAME=stbydb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "stbydb".
        

Controlling DataGuard

Stop/Start Archivelogs Application

Using the DataGuard Broker we can disable replication on the Standby by stopping the application of the archivelogs:

DGMGRL> edit database stbydb set state='APPLY-OFF';
Succeeded.

DGMGRL> edit database stbydb set state='APPLY-ON';
Succeeded.
        

These commands are equivalent to starting/stopping recovery manually with the commands: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSIONor ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL.

Stop/Start Transferring Archivelogs

We can also prevent archivelogs from being sent from the Primary to the Standby by stopping the transport of archivelogs:

DGMGRL> edit database prmydb set state='TRANSPORT-OFF';
Succeeded.

DGMGRL> edit database prmydb set state='TRANSPORT-ON';
Succeeded.
        

These commands are equivalent to manually enabling/deferring the destination of archivelogs with the commands: alter system set log_archive_dest_state_2=DEFERor alter system set log_archive_dest_state_2=ENABLE.

Conclusion

We have successfully configured the DataGuard Broker to control and monitor our DataGuard environment on Oracle 19c, and have used the DGMGRL command line interface to configure, monitor and control both the Primary and Standby databases.