Configuring Oracle Data Guard Broker for High Availability

 

Set up Data Guard Broker for enhanced availability.

Oracle Data Guard is an Oracle Database feature that provides high availability, disaster recovery, and data protection by maintaining one or more standby databases synchronized with a primary database.

In simple terms

• Primary DB → actively used by applications

• Standby DB → continuously receives and applies changes

• If the primary fails, the standby can quickly take over with minimal downtime and data loss

When you setup Data Guard, you will have to manually monitor the log shipping, log apply and resolve any gaps. Even for switchover and failover, you need to perform the activities manually.

In a data guard configuration, you will observe

• One primary database and combination of standby databases

• Connected by Oracle Net service

• Primary & standby may reside in different geographic locations

• Standby stays in sync with primary by applying redo

Configure Data Guard Broker

Follow below steps to configure Data Guard broker.

Note: At the time of writing article, below steps were performed on existing Primary and Standby setup that was created manually.

Edit listeners

If you look at the listener configuration file, there is a dedicated service we have to create for DGMGRL. This is required in order enable Data Guard Broker. If this is not set, add below entry (test_DGMGRL and it has to be exactly in the same format <SID>_DGMGRL) and restart listener on both primary and standby

su - grid

cd $ORACLE_HOME/network/admin

cat listener.ora

 

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

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

     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

   )

 )

 

SID_LIST_LISTENER=

 (SID_LIST=

   (SID_DESC=

     (GLOBAL_DBNAME=test)

     (SID_NAME=test)

     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

   )

   (SID_DESC=

     (GLOBAL_DBNAME=test_DGMGRL)

     (SID_NAME=test)

     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

   )

 )   

Stop MRP on standby

We would like to manage our data guard configuration using Data Guard Broker. Stop MRP and clear Log_Archive_dest_2 parameter

On standby:

===========

SQL> alter database recover managed standby database cancel;

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

 

On primary:

===========

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

Enable broker

We need to start the broker by enabling it on both primary and standby

On primary:

===========

SQL> alter system set dg_broker_start=true;

SQL> show parameter dg_broker_start;

 

On standby:

===========

SQL> alter system set dg_broker_start=true;

SQL> show parameter dg_broker_start;

Register primary with broker

On primary, connect to DGMGRL utility and register the primary database with broker

On primary:

===========

dgmgrl sys/sys@test

DGMGRL> create configuration test as primary database is test connect identifier is test;

DGMGRL> show configuration;

Register standby with broker

In the same DGMGRL utility, register standby from primary server itself

DGMGRL> add database test_s as connect identifier is test_s;

DGMGRL> show configuration;

Enable Data Guard broker

Once primary and standby are registered, we must enable broker

DGMGRL> ENABLE CONFIGURATION;

DGMGRL> SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE test;

DGMGRL> SHOW DATABASE test_s;

Manage Redo Apply via Broker

Like how we start / stop MRP manually, we can start / stop redo apply on standby using broker

Stop log apply:

===============

dgmgrl sys/sys@test

DGMGRL> show configuration;

DGMGRL> show database test_s;

DGMGRL> edit database test_s set state=APPLY-OFF;

DGMGRL> show database test_s;

 

Start log apply:

================

dgmgrl sys/sys@test

DGMGRL> show configuration;

DGMGRL> show database test_s;

DGMGRL> edit database test_s set state=APPLY-ON;

DGMGRL> show database test_s;

Start/stop log shipping via Broker

How we can manually enable log shipping from primary to standby, the same way we can use broker to enable log shipping

Disable log shipping/transport:

===============================

dgmgrl sys/sys@test

DGMGRL> show configuration;

DGMGRL> show database test;

DGMGRL> edit database test set state=TRANSPORT-OFF;

DGMGRL> show database test;

 

Enable log shipping/transport:

==============================

dgmgrl sys/sys@test

DGMGRL> show configuration;

DGMGRL> show database test;

DGMGRL> edit database test set state=TRANSPORT-ON;

DGMGRL> show database test;