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;

perform Manual Switchover and Failover on Physical Standby

1.Perform Manual Switchover on Physical Standby

-----------------------------------------------------------

Connect to test database via client and keep querying below

sqlplus sys/sys@test as sysdba

 

select name, open_mode, db_unique_name, database_role from v$database;

 

NAME      OPEN_MODE      DB_UNIQUE_NAME         DATABASE_ROLE

--------- -------------- ---------------------- --------------

test    READ WRITE     test                 PRIMARY

Check primary and standby for any gaps

On primary:

===========

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

 

On standby:

===========

select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

We will first convert primary to standby and later standby to primary

On primary:

===========

select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE

SQL>alter database commit to switchover to physical standby with session shutdown;

 

SQL>startup mount;

At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary.

select name, open_mode, db_unique_name, database_role from v$database;

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

select SWITCHOVER_STATUS from V$DATABASE;

 

SQL>alter database commit to switchover to primary with session shutdown;

 

SQL> alter database open;

At this stage, the client query would execute successfully!

On new standby – Initially your primary database: Start MRP

SQL>alter database recover managed standby database disconnect;

Revert back: Once again follow the above process from top and re-execute steps in proper databases to revert back to original setup.

2. Performing Manual Failover on Physical Standby

---------------------------------------------------------

Failover is when your primary database is completely lost. When there is a failover, standby is converted into primary but primary is not converted into standby as it is lost. If you do not have Flashback enabled on primary, you must re-create primary from scratch (Using RMAN duplicate method). In this example, we have already enabled flashback on both primary and standby.

 

Our current physical standby server overview

Database SID	Database Role
Test	Primary Database
test_s	Physical Standby

Crash Primary database

Let’s crash primary (test): In order to simulate failure, we will shut down the primary server test. As root user, shutdown the server without shutting down DB.

Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary

SQL>select name, open_mode, db_unique_name, database_role from v$database;

3. Perform Failover to Standby

---------------------------------

On standby:     switch physical standby to primary

===========

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

then finish the database recovery and is no more required

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

at this stage database is ready to be open and converted to primary so if you don't finish it it won't be able to be converted to primary. here how we check it

SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary

SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

Check client query: Check the query you executed in step 2 on client, it must get executed