🧩 How to Configure Oracle Data Guard (Step-by-Step Guide)

 🧩 How to Configure Oracle Data Guard (Step-by-Step Guide) 🧩

🚀 In today’s How to Do Oracle Database series, we’ll learn how to configure Oracle Data Guard — one of the most powerful Oracle features for ensuring high availability, disaster recovery, and data protection.

Whether you want to protect your production database or build a DR site for learning and testing, this step-by-step guide will walk you through both Physical and Logical Data Guard setups 🧠

🧠 What is Oracle Data Guard?

Oracle Data Guard is a feature that maintains one or more standby databases as exact copies (or logical copies) of your primary database.

If the primary database fails, you can quickly switch or fail over to the standby — keeping your business online with minimal downtime.

⚙️ Types of Data Guard Configurations

🔹 Physical Standby Database:

A block-for-block copy of the primary. Redo logs are shipped and applied using Redo Apply.

Best for disaster recovery and high availability.

🔹 Logical Standby Database:

A logical copy where redo data is transformed into SQL statements and applied using SQL Apply.

It allows read/write operations while still applying changes — great for reporting or read-only workloads.

🏗️ Prerequisites Before Configuration

✅ Both primary and standby servers should have the same Oracle version

✅ Both must use the same DB_NAME, but different DB_UNIQUE_NAME

✅ Ensure ARCHIVELOG mode is enabled on the primary

✅ Force logging must be enabled

✅ Passwords must match for SYS and any users used for redo transport

✅ Configure network connectivity and ensure both databases can connect via TNS

✅ Ensure sufficient disk space for redo logs and archived logs

🧩 Step-by-Step: Setting Up Data Guard

1️⃣ Enable ARCHIVELOG and Force Logging on Primary

sqlplus / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

ALTER DATABASE FORCE LOGGING;

2️⃣ Configure Initialization Parameters on Primary

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)';

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/arch1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY';

ALTER SYSTEM SET log_archive_dest_2='SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

ALTER SYSTEM SET fal_server=STANDBY;

ALTER SYSTEM SET fal_client=PRIMARY;

ALTER SYSTEM SET standby_file_management='AUTO';

3️⃣ Create a Standby Control File and Backup Primary Database

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/standby.ctl';

Then take a full backup of the primary database using RMAN or OS-level copy and transfer it to the standby server.

4️⃣ Prepare the Standby Initialization File (pfile or spfile)

Change parameters for the standby instance:

DB_UNIQUE_NAME=STANDBY

CONTROL_FILES='/u01/app/oracle/oradata/STANDBY/control01.ctl'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'

FAL_SERVER=PRIMARY

FAL_CLIENT=STANDBY

STANDBY_FILE_MANAGEMENT=AUTO

5️⃣ Start the Standby Database in Mount Mode

STARTUP NOMOUNT PFILE='/path/to/initSTANDBY.ora';

ALTER DATABASE MOUNT STANDBY DATABASE;

6️⃣ Start Redo Apply (for Physical Standby)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To enable real-time apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

7️⃣ Convert to Logical Standby (if desired)

If you want a Logical Standby, stop redo apply and use:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

Then transform it into a logical standby using Data Guard Broker or the DBMS_LOGSTDBY package.

Example:

EXEC DBMS_LOGSTDBY.BUILD;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

🔄 Switchover and Failover Commands

🔹 Switchover (Planned Role Change)

-- On primary

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN;

-- On standby

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE OPEN;

🔹 Failover (Unplanned Role Change)

If the primary is lost:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER DATABASE OPEN;

🧾 Monitoring Data Guard

Check configuration status:

SELECT DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

To manage with Broker:

DGMGRL

CONNECT sys/password@PRIMARY

SHOW CONFIGURATION;

💬 Best Practices

✅ Always use ARCHIVELOG + Force Logging

✅ Set up Data Guard Broker (DGMGRL) for easier management

✅ Keep standby redo logs (SRLs) same size & number as online logs

✅ Test switchover/failover regularly

✅ Monitor lag and redo apply rates

💡 Like & Follow for More Oracle Tutorials!

👉 Follow my Facebook Page: https://www.facebook.com/share/19Dfe1fqVJ/

👉 Visit my Blog for Full Articles: https://oracleinlinuxbyabosalma.blogspot.com