Setup DataGuard Broker on Oracle 19c

July 15, 2020 5-minute read

Oracle • DataGuard • Standby

Introduction

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

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

You can perform most of the activities required to manage and monitor the databases in the configuration using DGMGRL commands.

Here we are going to see how we can enable the DataGuard Broker and use it through command-line interface DGMGRL.

Prerequisites

We recently build a DataGuard environment creating a Physical Standby for our Oracle 19c multitenant installation in the note Create an Oracle DataGuard Physical Standby.
We are going to reuse that setup adding the necessary to activate the DataGuard Broker.

Enable DataGuard Broker

Configure Database Parameters

First we need to prepare the instances part of the DataGuard environment so the internal Broker processes can be 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_START and we need to clear 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 Static Service to Listener

The DataGuard Broker needs a static service to be able to restart the instances, so we have to add a service named db_unique_name_DGMGRL to the listener.ora in both 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")))

Connect to Command Line Interface

Issue the command dgmgrl in the Primary instance to access the DataGuard Broker, then connect to the database with the SYSDG user:

[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 you can use the SYS user if you didn’t configure a user for DataGuard in the orapwd file.

Create DataGuard Broker Configuration

Once connected we need to create a new DataGuard configuration specifying the DB_UNIQUE_NAME and the service name for the Primary database.
And then add the Standby database specifying its DB_UNIQUE_NAME and the 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

Enable DataGuard Broker Configuration

We need to enable this configuration to be able 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)

That’s it, we have the DataGuard Broker activated and the configuration enabled to control and monitor our DataGuard environment.

Monitor DataGuard

Show Database Status

You can issue the SHOW DATABASE command to see relevant information about the databases and the status of the Transport (Primary) and the 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 Database Readiness

You can issue the VALIDATE DATABASE command to 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 issue the VALIDATE NETWORK command to 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".

Control DataGuard

Disable/Enable Archivelog Apply

Using the DataGuard Broker we can disable the replication on the Standby by disabling the archivelog apply:

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

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

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

Disable/Enable Archivelog Transport

Also we can avoid the archivelogs to be sent from the Primary to the Standby by disabling the archivelog transport:

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

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

Those commands are equivalent to enabling/deferring the archivelog destination manually with the commands:
alter system set log_archive_dest_state_2=DEFER or
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 we have used the command-line interface DGMGRL to setup, monitor and control both the Primary and Standby databases.