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;

 

Convert Physical Standby into Snapshot Standby

 

Enable testing on standby with Oracle Snapshot Standby.

You can easily switch between a Snapshot and Physical Standby as needed. Learn more about transitioning from Physical to Logical Standby or leveraging Active Data Guard for real-time read access.

Convert physical standby to snapshot standby: We will now convert the physical standby database to snapshot standby

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; 		>> make sure its mounted
SQL> alter database convert to snapshot standby;
SQL> alter database open;				>> open the DB
SQL> select name, open_mode, database_role from v$database;

Verifying snapshot standby: Now you must be able to read-write on snapshot standby. Meanwhile, we can even check the standby alert log. The archives received from primary are not applied on standby. We can even check that there is a guaranteed restore point has been created. So that when you convert snapshot back to physical standby, it will be used.

Also Note: For this snapshot standby, you do not need Flashback database enabled.

On standby:
===========
SQL> select name, guarantee_flashback_database from v$restore_point;
SQL> create table employees(eno number(2), s_name varchar2(10));
SQL> insert into employees values(1,'KAB');
SQL> insert into employees values (2,'SAM');
SQL> commit;
SQL> select * from employees
;

Revert back snapshot standby to physical standby: Once application testing is done, you can revert back snapshot standby to same point when it was converted from physical standby to snapshot standby

On standby:
===========
SQL> select name, open_mode, database_role from v$database;
SQL> shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
SQL> select * from student;