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. Step-by-Step Guide to Implementing a Physical Standby Database Using Data Guard Broker in Oracle 21c Step-by-Step Guide to Implementing a Physical Standby Database Using Data Guard Broker in Oracle 21c Introduction 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. Data Guard Broker step-by-step physical standby database Prerequisites 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. Two servers (Physical or VMs) running Oracle Linux 8 . Oracle Linux 8 Oracle Database 21c is installed on both servers. 21c A Primary Database that is up and running. Primary Database A Standby Database with software installed but not configured. Standby Database Unrestricted communication between servers on port 1521 . port 1521 For this guide: Primary Server: primarydb.localdomain Standby Server: standbydb.localdomain Primary Database Name: CDB1 Standby Database Name: CDB1_STBY Primary Server : primarydb.localdomain Primary Server primarydb.localdomain Standby Server : standbydb.localdomain Standby Server standbydb.localdomain Primary Database Name : CDB1 Primary Database Name CDB1 Standby Database Name : CDB1_STBY Standby Database Name CDB1_STBY Step 1: Prepare the Primary Database Step 1: Prepare the Primary Database Oracle 21c introduces the PREPARE DATABASE FOR DATA GUARD command, which simplifies configuration. PREPARE DATABASE FOR DATA GUARD Run the following on the Primary Database : Primary Database mkdir -p $ORACLE_BASE/fast_recovery_area 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; 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) Enable Archivelog Mode (sql) If not already enabled, switch to ARCHIVELOG mode : ARCHIVELOG mode SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Enable Force Logging (sql) Enable Force Logging (sql) ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SWITCH LOGFILE; ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SWITCH LOGFILE; Create Standby Redo Logs (sql) 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; 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) Enable Flashback Database (Optional) Flashback Database helps with faster failovers. ALTER DATABASE FLASHBACK ON; ALTER DATABASE FLASHBACK ON; Configure Initialization Parameters Configure Initialization Parameters Ensure DB_NAME and DB_UNIQUE_NAME are set: DB_NAME DB_UNIQUE_NAME SHOW PARAMETER DB_NAME; SHOW PARAMETER DB_UNIQUE_NAME; SHOW PARAMETER DB_NAME; SHOW PARAMETER DB_UNIQUE_NAME; The Primary DB should have: Primary DB ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; Step 2: Configure the Network Step 2: Configure the Network Update the TNS configuration ( tnsnames.ora ) on both servers. TNS configuration tnsnames.ora Primary Server ( tnsnames.ora ) 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) ) ) 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 ) 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) ) ) 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 ) Configure the Listener ( listener.ora Modify Primary Server ( listener.ora ): 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) ) ) 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 lsnrctl reload Step 3: Duplicate the Primary Database to Standby Step 3: Duplicate the Primary Database to Standby On the Standby Server , start the database in nomount mode : Standby Server nomount mode STARTUP NOMOUNT; STARTUP NOMOUNT; Use RMAN from the Primary Server to duplicate: Primary Server rman TARGET sys@CDB1 AUXILIARY sys@CDB1_STBY 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; 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 Step 4: Enable Data Guard Broker On Primary Server (sql): On Primary Server (sql): ALTER SYSTEM SET DG_BROKER_START=TRUE; ALTER SYSTEM SET DG_BROKER_START=TRUE; On Standby Server: On Standby Server: ALTER SYSTEM SET DG_BROKER_START=TRUE; ALTER SYSTEM SET DG_BROKER_START=TRUE; Step 5: Configure Data Guard Broker Step 5: Configure Data Guard Broker Create Broker Configuration Create Broker Configuration On Primary Database (in Dataguard Broker) : 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; 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) Verify Configuration (in Dataguard Broker) dgmgrl / SHOW CONFIGURATION; SHOW DATABASE VERBOSE 'CDB1_STBY'; EXIT; dgmgrl / SHOW CONFIGURATION; SHOW DATABASE VERBOSE 'CDB1_STBY'; EXIT; Step 6: Perform a Switchover (in Dataguard Broker) Step 6: Perform a Switchover (in Dataguard Broker) To test, perform a switchover: dgmgrl / SWITCHOVER TO 'CDB1_STBY'; EXIT; dgmgrl / SWITCHOVER TO 'CDB1_STBY'; EXIT; Then, check the status: dgmgrl / SHOW CONFIGURATION; EXIT; dgmgrl / SHOW CONFIGURATION; EXIT; Validating Standby Database Synchronization with Primary 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 1. Verify Redo Log Archival on Primary Run the following SQL query on the primary database to determine the current redo log sequence numbers: primary SELECT thread#, sequence#, archived, status FROM v$log; SELECT thread#, sequence#, archived, status FROM v$log; Example output: THREAD# SEQUENCE# ARC STATUS -------- --------- --- ------ 1 947 YES ACTIVE 1 948 NO CURRENT 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; SELECT MAX(sequence#) FROM v$archived_log; Example output: MAX(SEQUENCE#) -------------- 947 MAX(SEQUENCE#) -------------- 947 2. Validate Redo Log Shipping to Standby 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'; 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 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. Ensure the ARCHIVED_SEQ# is the same across all destinations. ARCHIVED_SEQ# If the status is not VALID , investigate further. VALID 3. Verify Log Gaps at Standby 3. Verify Log Gaps at Standby Run the following SQL on the standby database: standby SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#; SELECT thread#, MAX(sequence#) FROM v$archived_log GROUP BY thread#; Ensure that the sequence number matches the primary. Ensure that the sequence number matches the primary. sequence number 4. Check Apply Lag on Standby 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'; 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'; If there is a lag, check the MRP (Managed Recovery Process) status: SELECT process, status, sequence# FROM v$managed_standby WHERE process='MRP0'; If there is a lag, check the MRP (Managed Recovery Process) status: MRP SELECT process, status, sequence# FROM v$managed_standby WHERE process='MRP0'; SELECT process, status, sequence# FROM v$managed_standby WHERE process='MRP0'; 5. Check Standby Database Status 5. Check Standby Database Status Ensure the standby is in recovery mode : recovery mode SELECT database_role, open_mode FROM v$database; SELECT database_role, open_mode FROM v$database; Expected output: DATABASE_ROLE OPEN_MODE --------------- ------------ PHYSICAL STANDBY MOUNTED DATABASE_ROLE OPEN_MODE --------------- ------------ PHYSICAL STANDBY MOUNTED If it's not in MOUNTED mode, start recovery: MOUNTED ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 6. Check Data Guard Configuration 6. Check Data Guard Configuration SELECT * FROM v$dataguard_status; SELECT * FROM v$dataguard_status; Look for any errors or inconsistencies. Look for any errors or inconsistencies. Conclusion 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 . Oracle Data Guard Data Guard Broker automated failover mechanism high availability