6a. Data Guard Physical Standby Setup in Oracle Database 11g Release 2

 

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Introduction

Oracle Data Guard is Oracle's comprehensive standby database solution for disaster recovery and high availability. It maintains a synchronized copy of a production database (the primary) at one or more remote locations (the standby), ensuring business continuity even in the event of a disaster.

This guide provides a complete, step-by-step walkthrough for setting up a physical standby database in Oracle Database 11g Release 2. It covers two setup approaches — manual (backup-based) and DUPLICATE (active duplicate) — along with role transitions, protection modes, and advanced features like Active Data Guard and Snapshot Standby.

Recommendation: For production environments, consider using the Data Guard Broker to configure and manage your standby database. It simplifies administration and provides a single command interface.

Quick Start: If you want to quickly set up a demo environment using VirtualBox and Vagrant, refer to the GitHub repository mentioned in the original article.

---

Overview of the Setup Process

Phase	Activity	Description
1	Primary Server Setup	Configure logging, initialization parameters, and services
2	Backup Primary Database	Create a backup for the standby (manual method)
3	Create Standby Controlfile and PFILE	Prepare the control file and parameter file
4	Standby Server Setup (Manual)	Copy files, start listener, restore backup, create redo logs
5	Standby Server Setup (DUPLICATE)	Use RMAN DUPLICATE for a faster setup
6	Start Apply Process	Begin applying redo to the standby
7	Test Log Transport	Verify that redo is being shipped and applied
8	Protection Mode	Choose and configure the protection mode
9	Switchover / Failover	Test role transitions
10	Advanced Features	Flashback, Active Data Guard, Snapshot Standby

---

Assumptions

Before you begin, ensure the following:

• You have two servers (physical or virtual machines) with an operating system and Oracle installed

• In this example, Oracle Linux 5.6 and Oracle Database 11.2.0.2 are used

• The primary server has a running database instance

• The standby server has a software-only installation (no database yet)

---

Phase 1: Primary Server Setup

Step 1.1: Verify Archivelog Mode

Check that the primary database is in archivelog mode:

sql

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

If it is in noarchivelog mode, switch it to archivelog mode:

sql

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

Step 1.2: Enable Forced Logging

Enable forced logging to ensure all changes are logged, even in nologging operations:

sql

ALTER DATABASE FORCE LOGGING;

Step 1.3: Check DB_NAME and DB_UNIQUE_NAME

Check the current settings:

sql

SQL> show parameter db_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_name                  string   DB11G

SQL> show parameter db_unique_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_unique_name           string   DB11G

Important: The DB_NAME of the standby database will be the same as the primary, but it must have a different DB_UNIQUE_NAME. In this example, the standby will have the value DB11G_STBY.

Step 1.4: Configure Log Archive Config

Set the LOG_ARCHIVE_CONFIG parameter to include both databases:

sql

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';

Step 1.5: Set Remote Archive Log Destinations

Configure the remote archive log destination for the standby:

sql

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Note: The SERVICE and DB_UNIQUE_NAME reference the standby location.

Step 1.6: Set Additional Parameters

Set the log archive format, max processes, and password file:

sql

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

Step 1.7: Prepare for Role Transition

Make the primary ready to switch roles to become a standby:

sql

ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Important: Some of these parameters are not modifiable at runtime, so the database will need to be restarted before they take effect.

Step 1.8: Configure TNS Names

Add entries for both the primary and standby databases in the $ORACLE_HOME/network/admin/tnsnames.ora files on both servers:

text

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

Tip: You can create these using the Network Configuration Utility (netca) or manually.

---

Phase 2: Backup Primary Database

Note: This step is unnecessary if you plan to use an active duplicate to create the standby database. For a backup-based duplicate or a manual restore, take a backup of the primary database.

bash

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

---

Phase 3: Create Standby Controlfile and PFILE

Step 3.1: Create the Standby Controlfile

On the primary database:

sql

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';

Step 3.2: Create the Standby PFILE

sql

CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;

Step 3.3: Amend the PFILE for the Standby

Edit the PFILE and update the following parameters:

text

*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

---

Phase 4: Standby Server Setup (Manual Method)

Step 4.1: Create Required Directories

On the standby server:

bash

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Step 4.2: Copy Files from Primary to Standby

bash

# Standby controlfile to all locations
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

# Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

# Parameter file
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

# Remote login password file
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Important: If your backups are not held within the FRA, you must copy them to the standby server and make them available from the same path as used on the primary server.

Step 4.3: Start the Listener on Standby

bash

$ lsnrctl start

Step 4.4: Create the SPFILE and Restore the Backup

bash

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';

$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Step 4.5: Create Online Redo Logs

Create online redo logs on the standby, matching the primary configuration:

sql

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step 4.6: Create Standby Redo Logs

Create standby redo logs on both the standby and the primary (for switchover):

sql

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Rule of Thumb: Standby redo logs should be at least as large as the largest online redo log, and there should be one extra group per thread compared to the online redo logs.

---

Phase 5: Standby Server Setup (DUPLICATE Method)

Step 5.1: Create Required Directories

bash

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Step 5.2: Copy Files from Primary to Standby

bash

# Standby controlfile
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

# Parameter file
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

# Remote login password file
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Step 5.3: Configure the Static Listener

When using active duplicate, the standby server requires a static listener configuration in listener.ora:

text

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Start the listener:

bash

$ lsnrctl start

Step 5.4: Create Standby Redo Logs on the Primary

The DUPLICATE command automatically creates standby redo logs on the standby. However, to ensure the primary is ready for switchover, create standby redo logs on the primary as well:

sql

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Step 5.5: Start the Auxiliary Instance

On the standby server:

bash

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';

Step 5.6: Run the DUPLICATE Command

Connect to RMAN with full connect strings for both TARGET and AUXILIARY:

bash

$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY

Run the DUPLICATE command:

text

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
    SET FAL_SERVER='DB11G' COMMENT 'Is primary'
  NOFILENAMECHECK;

Clause Explanation

Clause	Purpose
FOR STANDBY	Tells DUPLICATE to create a standby (no DBID change)
FROM ACTIVE DATABASE	Creates the standby directly from source datafiles (no backup step)
DORECOVER	Includes recovery to bring the standby up to the current point in time
SPFILE	Allows resetting SPFILE values during the copy
NOFILENAMECHECK	Skips destination file location checks

---

Phase 6: Start the Apply Process

On the standby server, start the apply process:

sql

-- Foreground redo apply. Session never returns until cancel.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Background redo apply. Control is returned to the session once started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Cancel the apply process:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Apply with a delay:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Real-time apply (requires standby redo logs):

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

---

Phase 7: Test Log Transport

On the primary server, check the latest archived redo log and force a log switch:

sql

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

On the standby server, verify the new archived redo log has arrived and been applied:

sql

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Result: If the applied column shows YES, the standby is successfully applying redo.

---

Phase 8: Protection Mode

There are three protection modes for the primary database:

Mode	Description
Maximum Availability	Transactions don't commit until redo is written to at least one standby. If no standby is available, it acts like maximum performance until a standby becomes available.
Maximum Performance	Transactions commit as soon as redo is written to the online redo log. Transfer to standby is asynchronous. (Default)
Maximum Protection	Transactions don't commit until redo is written to at least one standby. If no standby is available, the primary shuts down.

Check the Current Protection Mode

sql

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

Switch Protection Modes

Maximum Availability:

sql

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Maximum Performance:

sql

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Maximum Protection:

sql

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

---

Phase 9: Database Switchover

A switchover allows the primary and standby databases to swap roles without data loss.

On the original primary:

sql

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby:

sql

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

Note: After the switchover, test the log transport again. To return to the original configuration, perform another switchover (a switchback).

---

Phase 10: Failover

If the primary database is unavailable, activate the standby as the new primary:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Important: Since the standby is now the primary, back it up immediately. The original primary can be reconfigured as a standby — easily if Flashback Database was enabled, or by following the full setup process again.

---

Advanced Features

Flashback Database

Enabling Flashback Database on the primary (and standby) allows a failed primary to be flashed back and quickly converted to a standby after a failover, rather than being scrapped and recreated.

Read-Only Standby

A standby can be opened in read-only mode for reporting:

sql

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

Resume managed recovery:

sql

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Note: While in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby becomes increasingly out of date.

Active Data Guard

Introduced in 11g, Active Data Guard allows the standby to be open in read-only mode while still applying redo. This means the standby is available for querying and remains up to date.

sql

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Note: There are licensing implications for Active Data Guard. Ensure you have the appropriate license before using it.

Snapshot Standby

Introduced in 11g, Snapshot Standby allows the standby to be opened in read-write mode. When switched back to standby mode, all changes made while in read-write mode are lost. This is achieved using flashback database.

sql

-- Ensure the instance is in MOUNT mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- Cancel managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Convert to snapshot standby
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;

Convert back to physical standby (losing all changes):

sql

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

---

The Complete Data Guard Flow

text

┌─────────────────────────────────────────────────────────────────┐
│  1. PRIMARY SERVER SETUP                                        │
│     Archivelog mode, force logging, parameters, tnsnames        │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  2. BACKUP PRIMARY DATABASE (manual method)                     │
│     RMAN> BACKUP DATABASE PLUS ARCHIVELOG;                      │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  3. CREATE STANDBY CONTROLFILE AND PFILE                        │
│     ALTER DATABASE CREATE STANDBY CONTROLFILE;                  │
│     CREATE PFILE FROM SPFILE;                                   │
└──────────────────────────┬──────────────────────────────────────┘
                           │
              ┌────────────┴────────────┐
              ▼                         ▼
┌─────────────────────────┐  ┌─────────────────────────┐
│  4. MANUAL SETUP        │  │  5. DUPLICATE SETUP     │
│  Copy files, restore    │  │  RMAN DUPLICATE         │
│  backup, create logs    │  │  FROM ACTIVE DATABASE   │
└────────────┬────────────┘  └────────────┬────────────┘
             │                            │
             └────────────┬───────────────┘
                          ▼
┌─────────────────────────────────────────────────────────────────┐
│  6. START APPLY PROCESS                                         │
│     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;            │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  7. TEST LOG TRANSPORT                                          │
│     Force log switch, verify applied on standby                 │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  8. PROTECTION MODE                                             │
│     Maximum Availability / Performance / Protection             │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  9. SWITCHOVER / FAILOVER                                       │
│     Role transitions for planned or unplanned events            │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│  10. ADVANCED FEATURES                                          │
│      Flashback, Read-Only Standby, Active Data Guard,           │
│      Snapshot Standby                                           │
└─────────────────────────────────────────────────────────────────┘

---

Best Practices

Practice	Description
Use Data Guard Broker	For production environments, use the Broker for easier management
Enable Force Logging	Ensures all changes are logged, even nologging operations
Use Standby Redo Logs	Required for real-time apply and switchover
Match Redo Log Sizes	Standby redo logs should be at least as large as online redo logs
One Extra Standby Group	Have one more standby redo log group per thread than online groups
Enable Flashback Database	Allows easy reconversion after a failover
Test Switchover Regularly	Verify role transitions work as expected
Monitor Log Transport	Regularly check V$ARCHIVED_LOG for gaps
Choose the Right Protection Mode	Balance performance vs. data protection
Document the Configuration	Keep a record of all parameters and settings

---

Troubleshooting Common Issues

Issue	Possible Cause	Solution
Redo not shipping	Wrong TNS entry	Verify tnsnames.ora and listener status
Apply process not running	Managed recovery not started	Run ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
Log gap detected	Network issue or standby down	Check V$ARCHIVE_GAP and resolve
Switchover fails	Standby redo logs missing	Create standby redo logs on both servers
Failover loses data	Async transport	Use sync transport for maximum protection
Snapshot standby fails	Flashback not enabled	Enable flashback database or use RESTORE POINT ONLY
Listener not starting	Wrong listener.ora	Verify static listener configuration
DUPLICATE fails	Wrong connect strings	Use full connect strings, not OS authentication

---

Summary

Setting up a Data Guard physical standby in Oracle Database 11g Release 2 involves the following key phases:

1. Primary Server Setup: Configure archivelog mode, force logging, parameters, and TNS entries

2. Backup Primary Database: Create a backup for the manual method

3. Create Standby Controlfile and PFILE: Prepare the control file and parameter file

4. Standby Server Setup (Manual or DUPLICATE): Copy files, restore backup, create redo logs

5. Start Apply Process: Begin applying redo to the standby

6. Test Log Transport: Verify that redo is being shipped and applied

7. Protection Mode: Choose and configure the protection mode

8. Switchover / Failover: Test role transitions

9. Advanced Features: Flashback, Active Data Guard, Snapshot Standby

Key Points to Remember

• DB_NAME is the same on primary and standby; DB_UNIQUE_NAME must differ

• Standby redo logs are required for real-time apply and switchover

• Forced logging ensures all changes are captured

• Protection modes balance performance and data protection

• Switchover is a planned role transition; failover is unplanned

• Flashback Database enables easy reconversion after failover

• Active Data Guard allows read-only queries while applying redo

• Snapshot Standby allows read-write access temporarily

Why Data Guard Matters

Oracle Data Guard is essential for:

• Disaster Recovery: Provides a synchronized copy of the database at a remote site

• High Availability: Enables fast failover with minimal data loss

• Data Protection: Protects against data corruption, site failures, and human error

• Reporting Offload: Allows read-only queries on the standby to reduce primary load

• Zero Data Loss: Maximum protection mode ensures no data loss

• Planned Maintenance: Switchover allows zero-downtime maintenance

By mastering Data Guard, you can ensure your Oracle Database environment is protected against disasters and meets the highest standards of availability and data protection.

---

References

• Oracle Database Documentation: Data Guard Concepts and Administration

• Oracle Database Documentation: Data Guard Broker

• My Oracle Support: Data Guard Best Practices

• Related Articles: Data Guard Quick Links (11gR2, 12cR1, 12cR2, 18c, 19c, 21c, 26ai)

• GitHub Repository: Data Guard Demo with VirtualBox and Vagrant