Set up Oracle Data Guard Faster Than Getting Your Morning Coffee With This Guide

Step-by-Step Guide to Implementing a Physical Standby Database Using Data Guard Broker in Oracle 21c

Introduction

Oracle Data Guard is an essential feature for ensuring high availability, data protection, and disaster recovery. With Oracle 21c, setting up a physical standby database is simplified using the Data Guard Broker. This guide provides a step-by-step approach to configuring a physical standby database using Data Guard Broker in an Oracle 21c environment.

Prerequisites

Before proceeding, ensure you have:

• Two servers (Physical or VMs) running Oracle Linux 8.
• Oracle Database 21c is installed on both servers.
• A Primary Database that is up and running.
• A Standby Database with software installed but not configured.
• Unrestricted communication between servers on port 1521.

For this guide:

• Primary Server: primarydb.localdomain
• Standby Server: standbydb.localdomain
• Primary Database Name: CDB1
• Standby Database Name: CDB1_STBY

Step 1: Prepare the Primary Database

Oracle 21c introduces the PREPARE DATABASE FOR DATA GUARD command, which simplifies configuration.

Run the following on the Primary Database:

mkdir -p $ORACLE_BASE/fast_recovery_area

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

Enable Archivelog Mode (sql)

If not already enabled, switch to ARCHIVELOG mode:

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

Enable Force Logging (sql)

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

Create Standby Redo Logs (sql)

Ensure standby redo logs exist on both servers. These logs should be at least as large as the online redo logs.

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

Enable Flashback Database (Optional)

Flashback Database helps with faster failovers.

ALTER DATABASE FLASHBACK ON;

Configure Initialization Parameters

Ensure DB_NAME and DB_UNIQUE_NAME are set:

SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;

The Primary DB should have:

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step 2: Configure the Network

Update the TNS configuration (tnsnames.ora) on both servers.

Primary Server (tnsnames.ora)

CDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CDB1)
    )
  )

CDB1_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standbydb.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CDB1)
    )
  )

Standby Server (tnsnames.ora)

CDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CDB1)
    )
  )

CDB1_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standbydb.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CDB1)
    )
  )

Configure the Listener (listener.ora)

Modify Primary Server (listener.ora):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb.localdomain)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CDB1)
      (ORACLE_HOME = /u01/app/oracle/product/21c/dbhome_1)
      (SID_NAME = CDB1)
    )
  )

Restart the listener:

lsnrctl reload

Step 3: Duplicate the Primary Database to Standby

On the Standby Server, start the database in nomount mode:

STARTUP NOMOUNT;

Use RMAN from the Primary Server to duplicate:

rman TARGET sys@CDB1 AUXILIARY sys@CDB1_STBY

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

Step 4: Enable Data Guard Broker

On Primary Server (sql):

ALTER SYSTEM SET DG_BROKER_START=TRUE;

On Standby Server:

ALTER SYSTEM SET DG_BROKER_START=TRUE;

Step 5: Configure Data Guard Broker

Create Broker Configuration

On Primary Database (in Dataguard Broker):

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

Verify Configuration (in Dataguard Broker)

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

Step 6: Perform a Switchover (in Dataguard Broker)

To test, perform a switchover:

dgmgrl /
SWITCHOVER TO 'CDB1_STBY';
EXIT;

Then, check the status:

dgmgrl /
SHOW CONFIGURATION;
EXIT;

Validating Standby Database Synchronization with Primary

Once the standby database is built, ensure that it stays in sync with the primary by following these steps.

1. Verify Redo Log Archival on Primary

Run the following SQL query on the primary database to determine the current redo log sequence numbers:

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

Example output:

THREAD#   SEQUENCE#  ARC  STATUS
--------  ---------  ---  ------
       1        947  YES   ACTIVE
       1        948  NO    CURRENT

Check the most recently archived redo log file:

SELECT MAX(sequence#) FROM v$archived_log;

Example output:

MAX(SEQUENCE#)
--------------
          947

2. Validate Redo Log Shipping to Standby

Check the archive destinations to confirm logs are being sent to the standby:

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

Example output:

DESTINATION         STATUS  ARCHIVED_THREAD#  ARCHIVED_SEQ#
------------------  ------  ----------------  -------------
/private1/prmy/lad   VALID                 1            947
standby1             VALID                 1            947

• Ensure the ARCHIVED_SEQ# is the same across all destinations.
• If the status is not VALID, investigate further.

3. Verify Log Gaps at Standby

Run the following SQL on the standby database:

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

• Ensure that the sequence number matches the primary.

4. Check Apply Lag on Standby

On the standby, check the last applied redo log:

SELECT thread#, sequence#, applied FROM v$archived_log WHERE applied='YES';

• If there is a lag, check the MRP (Managed Recovery Process) status:

  SELECT process, status, sequence# FROM v$managed_standby WHERE process='MRP0';
  

5. Check Standby Database Status

Ensure the standby is in recovery mode:

SELECT database_role, open_mode FROM v$database;

Expected output:

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

If it's not in MOUNTED mode, start recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6. Check Data Guard Configuration

SELECT * FROM v$dataguard_status;

• Look for any errors or inconsistencies.

Conclusion

With these steps, you have successfully set up Oracle Data Guard using Data Guard Broker in Oracle 21c. This provides an automated failover mechanism and ensures high availability.