This website use cookies to help you have a superior and more admissible browsing experience on the website.
Loading...
By:
Oracle Data Guard is Oracle’s built-in high availability (HA) and disaster recovery solution. It keeps one or more standby databases in sync with your primary (production) database by continuously shipping and applying redo data — the record of every change made to the database.
If the primary database goes down due to hardware failure, human error, or a site-level disaster, Data Guard can promote a standby to take over, minimizing both downtime and data loss.
Types of Standby Databases
Data Guard supports three standby types. Choosing the right one depends on your recovery goals and how you plan to use the standby:
| Standby Type | How It Works | Best For |
|---|---|---|
| Physical Standby | Applies redo data as direct block-level changes — an exact, byte-for-byte copy of the primary. | Disaster recovery and HA failover |
| Logical Standby | Converts redo data into SQL statements and applies them. The standby can be open for reads and have a different schema structure. | Reporting and live migrations |
| Snapshot Standby | A temporary, writable copy derived from a physical standby. Redo is received but not applied until it converts back. | Dev/test against real production data |
Before running any commands, make sure your environment is properly prepared. Missing these steps is one of the most common reasons Data Guard configuration fails.
The primary and standby servers should be as closely matched as possible. Differences in OS versions or patch levels can cause unexpected errors during redo apply.
prod_pri and prod_stdby).Before starting the configuration, confirm these initialization parameters are set correctly on the primary database:
Setting up Data Guard requires a specific sequence of steps. Start by preparing the primary database so it can generate and ship redo data to the standby.
The primary database should run in ARCHIVELOG mode — this ensures redo data is saved and available for transport to the standby. You also need to enable FORCE LOGGING, which prevents operations like direct path loads from bypassing the redo logs and causing the standby to fall out of sync.
Primary Database – SQL*Plus
-- Switch to ARCHIVELOG mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE OPEN;
-- Verify
SELECT log_mode, force_logging FROM v$database;
Standby Redo Logs (SRLs) store redo data received from the primary. They are required for Real-Time Apply, which lets the standby apply changes as they arrive rather than waiting for a full archived log.
A standard best practice: create one more SRL group than your number of online redo log groups, at the same size. If you have 3 online log groups, create 4 SRL groups.
Primary Database – SQL*Plus
ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4 ('/u01/app/oracle/oradata/srl04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE
GROUP 5 ('/u01/app/oracle/oradata/srl05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE
GROUP 6 ('/u01/app/oracle/oradata/srl06.log') SIZE 200M;
The primary and standby databases communicate through Oracle Net Services. You need to configure two files: tnsnames.ora and listener.ora.
Update tnsnames.ora
Add entries for both databases on both servers. This allows each side to locate the other on the network.
Primary and Standby Servers – tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pri_server_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary_db_service)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby_server_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby_db_service)
)
)
Configure the Listener
The standby needs a static listener entry in listener.ora. When the standby is first created, it runs in NOMOUNT or MOUNT state — dynamic registration won’t work at this stage. Without a static entry, the listener won’t recognize the service and the primary won’t be able to connect.
Standby Server – listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby_db_service)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = standby_sid)
)
)
Test Connectivity
After updating both files, reload the listener (lsnrctl reload) and test from the primary server:
Primary Server – Shell
tnsping standby
To initialize the standby, you need a consistent copy of the primary database. RMAN (Recovery Manager) is the standard tool for this — it captures both the data files and the archive logs needed to bring the standby up to date.
Primary Database – RMAN
RMAN TARGET /
BACKUP DATABASE PLUS ARCHIVELOG;
With the backup files in place on the standby server, you can now restore the database. Start the standby instance in NOMOUNT mode using the parameter file copied from the primary, then use RMAN to restore and mount it as a standby.
Standby Database – SQL*Plus
STARTUP NOMOUNT;
Standby Database – RMAN
RMAN TARGET /
-- Restore the control file first
RESTORE CONTROLFILE FROM '/path/to/backup/controlfile_auto';
-- Restore the data files
RESTORE DATABASE;
-- Mount as standby
ALTER DATABASE MOUNT STANDBY DATABASE;
By mounting as a STANDBY DATABASE, you are telling Oracle this instance will receive and apply redo logs rather than generate its own transactions.
With the standby mounted, you now need to tell the primary where to send its redo data. This is done by setting Log Transport Services parameters on the primary database.
The key parameter is LOG_ARCHIVE_DEST_2, which defines the standby destination and transmission mode.
Primary Database – SQL*Plus
-- Register primary and standby in the Data Guard configuration
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db_unique_name,standby_db_unique_name)';
-- Define the remote archive destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db_unique_name';
-- Enable the destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ASYNC (asynchronous) transport is used here to avoid impacting primary database performance during network delays. The VALID_FOR attribute limits this destination to the primary role, so it won’t apply if this database ever switches to standby.
The last step is to start Redo Apply on the standby. This launches a background process that continuously receives redo data from the primary and applies it to the standby’s data files.
Standby Database – SQL*Plus
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;
USING CURRENT LOGFILE enables Real-Time Apply — the standby applies changes as they arrive in the standby redo logs, without waiting for a full archive log to be shipped. DISCONNECT runs the process in the background, freeing up your SQL*Plus session.
v$managed_standby on the standby and check for the APPLYING_LOG status.Oracle Data Guard gives you a solid foundation for Oracle HA — but managing it still requires ongoing manual oversight: monitoring replication status, handling failover scripts, and ensuring your standby stays in sync.
For organizations that need a broader, more automated approach to high availability across their entire infrastructure, i2Availability offers a complementary layer of protection.
For teams running Oracle in demanding environments: finance, healthcare, or government: i2Availability can extend your HA strategy beyond the database layer to cover your full application stack.
Most Data Guard errors come down to small oversights in network settings or initialization parameters. Here are the most common issues and how to fix them.
ORA-12154 or ORA-12514 in the alert log usually means a typo in tnsnames.ora, a stopped listener, or port 1521 being blocked. Run tnsping standby to test connectivity, and make sure the Remote Login Password File (orapwd) matches on both servers.
If redo logs are arriving but the standby is falling behind, the apply process may not be running or there may be a gap in the log sequence. Query V$MANAGED_STANDBY to check the apply status, and use V$ARCHIVE_GAP to identify and manually register any missing sequences.
Without ARCHIVELOG mode, there is no redo data to ship. Without FORCE LOGGING, NOLOGGING operations won’t be captured, leaving the standby with unrecoverable data blocks. Verify both settings before starting your configuration:
SELECT log_mode, force_logging FROM v$database;
If the directory structure differs between servers, DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT should be set in the standby’s SPFILE. Without them, the restore will fail if the primary and standby use different file paths.
Q1: What is the difference between switchover and failover in Oracle Data Guard?
A switchover is a planned role transition — both the primary and standby remain available throughout the process, and no data is lost. A failover is triggered when the primary database fails unexpectedly. Depending on the protection mode, a small amount of data loss may occur during failover.
Q2: Which type of standby database should I use for high availability?
For most production HA deployments, a Physical Standby is the right choice. It maintains a block-for-block copy of the primary and offers the fastest failover time with the lowest overhead. Logical Standby is better suited for reporting or rolling upgrades.
Q3: How many standby databases can I configure?
From Oracle 11g R2 onwards, a single Data Guard configuration supports up to 30 standby databases.
Q4: Does Data Guard impact the performance of the primary database?
It can, depending on the transport mode. ASYNC transport has minimal impact on the primary since it does not wait for standby acknowledgment. SYNC transport offers stronger data protection but can add latency if the network round-trip between primary and standby is high.
Q5: How do I verify that Data Guard is working correctly after configuration?
Query v$managed_standby on the standby to confirm the apply process is running, and check v$archive_gap for any missing log sequences. On the primary, v$dataguard_status shows the current transport and apply status.
Q6: What happens if the standby falls behind due to a network outage?
Data Guard automatically detects the gap and re-syncs the standby using archived redo logs once connectivity is restored. If archived logs are no longer available, you can use an RMAN incremental backup to bring the standby back in sync.
Configuring Oracle Data Guard is a fundamental task for administrator with protecting mission-critical data. By following this step-by-step guide, you ensure that your primary database is correctly prepared, your network is optimized for log transport, and your standby database is ready to take over at a moment’s notice.
Remember that a Data Guard setup is only as good as its last test. Always validate your configuration by performing a test switchover in a non-production environment. This ensures that when a real disaster strikes, your team has the expertise and the infrastructure ready to maintain business continuity with confidence.
