High Level Guide to Implementing a Physical Standby Database Using Data Guard Broker in Oracle 21c

Introduction

Oracle Data Guard is a vital feature for high availability, data protection, and disaster recovery in enterprise databases. In Oracle 21c, setting up a physical standby database is streamlined with the Data Guard Broker, making management and monitoring significantly easier. This guide walks you through the complete process of configuring a Physical Standby Database using Data Guard Broker in Oracle 21c.

Prerequisites

Before you begin, ensure you have the following:

Two servers (Physical or Virtual Machines) running Oracle Linux 8
Oracle Database 21c installed on both servers
A Primary Database that is fully operational
A Standby Database with only the software installed (not yet configured)
Unrestricted communication on port 1521 between the servers

Server and Database Details

For this guide, we will use:

Primary Server: prod-db.example.com
Standby Server: standby-db.example.com
Primary Database Name: PRODDB
Standby Database Name: PRODDB_STBY

Step 1: Prepare the Primary Database

Use the PREPARE DATABASE FOR DATA GUARD Command

Oracle 21c simplifies Data Guard setup with a single command:

mkdir -p $ORACLE_BASE/recovery_area

DGMGRL /
prepare database for data guard
  with db_unique_name is PRODDB
  db_recovery_file_dest is "$ORACLE_BASE/recovery_area"
  db_recovery_file_dest_size is 20G;
EXIT;

Enable Archive Log Mode

If not already enabled, switch the database to ARCHIVELOG mode:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enable Force Logging

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

Create Standby Redo Logs

Ensure the standby redo logs exist on both the primary and standby servers.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 100M;

Enable Flashback Database (Optional but Recommended)

ALTER DATABASE FLASHBACK ON;

Configure Initialization Parameters

Ensure DB_NAME and DB_UNIQUE_NAME are correctly set:

SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;

Set standby file management to AUTO:

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step 2: Configure the Network

Update `tnsnames.ora` on Both Servers

Primary Server (tnsnames.ora):

PRODDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod-db.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PRODDB)
    )
  )

PRODDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby-db.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PRODDB)
    )
  )

Standby Server (tnsnames.ora):

(Same as above, swapping the HOST values accordingly.)

Modify `listener.ora` on Primary Server

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod-db.example.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRODDB)
      (ORACLE_HOME = /u01/app/oracle/product/21c/dbhome_1)
      (SID_NAME = PRODDB)
    )
  )

Restart the listener:

lsnrctl reload

Step 3: Duplicate the Primary Database to Standby

On the Standby Server, Start in NOMOUNT Mode

STARTUP NOMOUNT;

Use RMAN to Duplicate from the Primary Database

Run the following command from the Primary Server:

rman TARGET sys@PRODDB AUXILIARY sys@PRODDB_STBY

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='PRODDB_STBY'
SET LOG_ARCHIVE_DEST_2='SERVICE=PRODDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDB_STBY'
SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Step 4: Enable Data Guard Broker

On both Primary and Standby Servers, enable Data Guard Broker:

ALTER SYSTEM SET DG_BROKER_START=TRUE;

Step 5: Configure Data Guard Broker

Create Broker Configuration on Primary

dgmgrl /
CREATE CONFIGURATION 'DGBROKER' AS PRIMARY DATABASE IS 'PRODDB' CONNECT IDENTIFIER IS 'PRODDB';
ADD DATABASE 'PRODDB_STBY' AS CONNECT IDENTIFIER IS 'PRODDB_STBY' MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
EXIT;

Verify the Configuration

dgmgrl /
SHOW CONFIGURATION;
SHOW DATABASE VERBOSE 'PRODDB_STBY';
EXIT;

Step 6: Perform a Switchover

To test failover functionality, perform a switchover:

dgmgrl /
SWITCHOVER TO 'PRODDB_STBY';
EXIT;

Check the status after the switchover:

dgmgrl /
SHOW CONFIGURATION;
EXIT;

Validating Standby Database Synchronization

Verify Redo Log Archival on Primary

SELECT thread#, sequence#, archived, status FROM v$log;

Example output:

THREAD#   SEQUENCE#  ARC  STATUS
--------  ---------  ---  ------
       1        501  YES   ACTIVE
       1        502  NO    CURRENT

Check Archive Log Shipping to Standby

SELECT destination, status, archived_thread#, archived_seq#
FROM v$archive_dest_status
WHERE status <> 'DEFERRED' AND status <> 'INACTIVE';

Ensure the ARCHIVED_SEQ# matches across all destinations.

Verify Log Apply on Standby

SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#;

Ensure that the sequence number matches the primary.

Check Standby Database Status

SELECT database_role, open_mode FROM v$database;

Expected output:

DATABASE_ROLE    OPEN_MODE
---------------  ------------
PHYSICAL STANDBY MOUNTED

If the database is not in mounted mode, start recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Conclusion

By following this guide, you have successfully set up Oracle Data Guard using Data Guard Broker in Oracle 21c. This configuration ensures a robust disaster recovery solution while allowing seamless failover and switchover operations, improving database availability and resilience. 🚀

Home