Data Guard

How to Configure 19c Data Guard Broker

Suppose you have duplicated a 19c physical standby already, it’s time to build the data guard relationship between the primary and standby databases. In this post, we directly enable data guard broker to implement 19c data guard environment.

Standby Redo Logs

We have to add standby redo logs on both primary and standby database for real-time apply before enabling data guard broker.

Primary Database

[oracle@primary-19c ~]$ sqlplus / as sysdba
...
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo04.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo05.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo06.log' size 200m;

Database altered.

Check locations of all redo logs.

SQL> column group# format 9;
SQL> column type format a10;
SQL> column member format a50;
SQL> select group#, type, member from v$logfile order by 1;

GROUP# TYPE       MEMBER
------ ---------- --------------------------------------------------
     1 ONLINE     /u01/app/oracle/oradata/COMPDB/redo01.log
     2 ONLINE     /u01/app/oracle/oradata/COMPDB/redo02.log
     3 ONLINE     /u01/app/oracle/oradata/COMPDB/redo03.log
     4 STANDBY    /u01/app/oracle/oradata/COMPDB/redo04.log
     5 STANDBY    /u01/app/oracle/oradata/COMPDB/redo05.log
     6 STANDBY    /u01/app/oracle/oradata/COMPDB/redo06.log

6 rows selected.

Standby Database

[oracle@standby-19c ~]$ sqlplus / as sysdba
...
SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo04.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo05.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/COMPDB/redo06.log' size 200m;

Database altered.

Configure Static Listener

The broker requires a special static service used for connecting remote idle databases in order to switch over the primary role back and forth more smoothly. That is, you have to add it to listener.ora and restart listener on both sides.

Modify Primary Listener.ora

[oracle@primary-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PRIMDB)
      (GLOBAL_DBNAME=PRIMDB_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Then restart it.

[oracle@primary-19c ~]$ lsnrctl stop; lsnrctl start

Modify Standby Listener.ora

[oracle@standby-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=STANDB)
      (GLOBAL_DBNAME=STANDB_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Then restart it.

[oracle@standby-19c ~]$ lsnrctl stop; lsnrctl start

For RAC environment, you should add those static services to the listeners at grid-level.

Enable Data Guard Broker

We have to enable data guard brokers on both database. First of all, check the configuration of data guard broker. Take the primary database for an instance.

SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1PRIMDB.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2PRIMDB.dat
dg_broker_start                      boolean     FALSE
use_dedicated_broker                 boolean     FALSE

Please make sure the locations of broker configuration files are existing and allowable to be written, otherwise, you will get ORA-16571 when creating a broker configuration. Next, set DG_BROKER_START to TRUE on both sides so as to enable the broker to work for data guard.

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

System altered.

Check the broker parameters again.

SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1PRIMDB.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2PRIMDB.dat
dg_broker_start                      boolean     TRUE
use_dedicated_broker                 boolean     FALSE

Configure Data Guard Broker

Create a Broker Configuration

There’re scenarios using the DGMGRL command-line interface including creating a broker configuration.

[oracle@primary-19c ~]$ dgmgrl /
...
Connected to "PRIMDB"
Connected as SYSDG.
DGMGRL> create configuration drconf as primary database is PRIMDB connect identifier is PRIMDB;
Configuration "drconf" created with primary database "primdb"
DGMGRL> add database STANDB as connect identifier is STANDB maintained as physical;
Database "standb" added
DGMGRL> enable configuration;
Enabled.

We may have to wait for a few minutes or so to let them fill the gap.

Configuration Status

DGMGRL> show configuration verbose;

Configuration - drconf

  Protection Mode: MaxPerformance
  Members:
  primdb - Primary database
    standb - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'COMPDB_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

Primary Database Status

DGMGRL> show database primdb;

Database - primdb

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

Database Status:
SUCCESS

Standby Database Status

DGMGRL> show database standb;

Database - standb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 652.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STANDB

Database Status:
SUCCESS

By configuring data guard broker, a 19c data guard environment has been built as we saw.

Listener Status

The best thing is that data guard broker will automatically publish a configuration-wide service named <DB_NAME>_CFG to listeners at all parties, which does not affected by role transition.

For Oracle database developers or administrators, it’s pretty helpful to design connection descriptors (strings) for scenarios in failover, switchover or real-time query.

By the way, the new feature for data guard broker begins from 12.2.

Primary Listener

[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB_DGMGRL" has 1 instance(s).
  Instance "PRIMDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Listener

[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDBXDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB_DGMGRL" has 1 instance(s).
  Instance "STANDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As you can see, PRIMDB_DGMGRL and STANDB_DGMGRL are static services used by the broker.

Although configuring broker can save most of our jobs on data guard configuration, at least two things that you have to take care of them by yourself.

  1. System-Level
  2. Setting STANDBY_FILE_MANAGEMENT to AUTO in parameter file before duplication, this can replay new data files creation on the standby database.

    SQL> alter system set standby_file_management=auto;
  3. Database-Level
  4. Enabling force logging in control file before duplication, this can propagate all block changes to the standby database without missing anything.

    SQL> alter database force logging;

Next, maybe we should take a look at how we switchover to the standby database in 19c by data guard broker.

Leave a Reply

Your email address will not be published. Required fields are marked *