How to Configure DataGuard in Oracle
Introduction to DataGuard
Oracle DataGuard is an advanced feature in Oracle Database that provides comprehensive data protection and disaster recovery solutions for mission-critical databases. It enables high availability, data protection, and database resilience by creating and maintaining standby databases that can take over production load in case of primary database failure. This guide provides step-by-step instructions on configuring Oracle DataGuard using both manual setup and DataGuard Broker.
Prerequisites for DataGuard Configuration
- Oracle Enterprise Edition installed on both primary and standby servers.
- Both primary and standby databases must have identical OS and hardware specifications.
- Network connectivity between primary and standby databases with secured SSH configurations.
- Configure the primary database in ARCHIVELOG mode and enable forced logging.
- Ensure both primary and standby databases have the same Oracle database version and patch level.
- Listener and TNS configurations should be properly set up for both primary and standby databases.
Configuring the Primary Database
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "cdb1" on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
-------------------- ----------- ------------------------------
db_name string cdb1
SQL> show parameter db_unique_name
NAME TYPE VALUE
--------------------- ----------- ------------------------------
db_unique_name string cdb1
SQL>
The DB_NAME parameter for the standby database should match that of the primary database, but it needs a distinct DB_UNIQUE_NAME.
In this example, we’ll set the standby database’s DB_UNIQUE_NAME to "cdb1_stby".
To configure the primary database for DataGuard, set the necessary initialization parameters. These configurations facilitate data transfer between primary and standby databases.
-- Set DataGuard configuration parameters
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(cdb1,cdb1_stby)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb1' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=cdb1_stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb1_stby' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='cdb1_stby' SCOPE=BOTH;
ALTER SYSTEM SET fal_client='cdb1' SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
Once the parameters are set, restart the primary database for changes to take effect.
Configuring Archve logging
Validate that the primary database is in archivelog mode.
select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
--If it is noarchivelog mode, switch is to archivelog mode.
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
Configuring logging
Enabled forced logging by issuing the following command.
-- Enable forced logging
ALTER DATABASE FORCE LOGGING;
Creating Standby Redo Logs on the Primary Database
To prepare for smooth role transitions, such as switchovers, it’s essential to create standby redo logs on the primary database. These logs should match or exceed the size of the largest online redo logs, with an additional group created per thread. This redundancy helps ensure data is captured even during role changes. The following example demonstrates how to set up standby redo logs on both primary and standby servers:
For setups using Oracle Managed Files (OMF):
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;
For setups not using Oracle Managed Files (OMF):
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('/u01/oradata/cdb1/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/u01/oradata/cdb1/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/cdb1/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/cdb1/standby_redo04.log') SIZE 50M;
Enabling Flashback Database on Primary
If you intend to use the flashback database feature, it is best to enable it now on the primary database, as this will allow it to be activated on the standby database as well. Flashback database can be extremely useful for recovery scenarios and testing. Enable it with the following command:
ALTER DATABASE FLASHBACK ON;
Service Configuration for DataGuard
Both the primary and standby databases need entries in the tnsnames.ora file, located at $ORACLE_HOME/network/admin/tnsnames.ora on each server.
You can add these entries manually or use the Network Configuration Assistant (netca). The example below shows the entries required for this setup.
Note that the SID (not the SERVICE_NAME) is specified here to ensure that the DataGuard Broker can still connect to the
databases if they are down, as service names may not be active in such cases.
Primary Database Entry
cdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb1)
)
)
Standby Database Entry
cdb1_stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb1)
)
)
Listener Configuration on Primary Server
The listener.ora file on the primary server should have the following configuration, located at $ORACLE_HOME/network/admin/listener.ora. We define the listener details here to allow connectivity even when the database is not running.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb1_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = cdb1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/db_1/network/admin")
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Listener Configuration on Standby Server
The listener.ora file on the standby server requires a similar setup. Because the broker may need to connect while the database is down, we explicitly define the entry for the standby database rather than relying on auto-registration.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb1_stby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = cdb1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/db_1/network/admin")
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Restarting the Listener
After making changes to the listener.ora file, restart the listener on both the primary and standby servers to apply the updates:
lsnrctl stop
lsnrctl start
Setting Up the Standby Database
Prepare for Database Duplication
Start by creating a parameter file for the standby database. Save this file as /tmp/initcdb1_stby.ora with the following content to specify the database name:
*.db_name='cdb1'
Next, establish the required directories on the standby server to match the structure of the primary:
mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed
mkdir -p /u01/app/oracle/oradata/cdb1/pdb1
mkdir -p /u01/app/oracle/fast_recovery_area/cdb1
mkdir -p /u01/app/oracle/admin/cdb1/adump
Configure Password File
Create a password file on the standby server, ensuring the SYS password matches that of the primary database. Run the following command:
$ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwcdb1 password=Password1 entries=10
Duplicating the Database with RMAN
To begin the database duplication, start the standby server's instance using the temporary parameter file:
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initcdb1_stby.ora';
Connect to RMAN, specifying the full connect strings for both the TARGET (primary) and AUXILIARY (standby) databases, avoiding OS authentication.
$ rman TARGET sys/Password1@cdb1 AUXILIARY sys/Password1@cdb1_stby
Then, execute the following DUPLICATE command to create the standby database:
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='cdb1_stby' COMMENT 'Standby Database'
nofilenamecheck;
Modifying File Locations and Parameters During Duplication
If any file paths need to be converted or additional parameters adjusted, use the SET clause within the DUPLICATE command:
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='cdb1_stby' COMMENT 'Standby Database'
set db_file_name_convert='/old/path1/','/new/path1/','/old/path2/','/new/path2/'
set log_file_name_convert='/old/path1/','/new/path1/','/old/path2/','/new/path2/'
set job_queue_processes='0'
nofilenamecheck;
Explanation of Clauses
- FOR STANDBY: Indicates that the DUPLICATE operation is for creating a standby database, avoiding a DBID change.
- FROM ACTIVE DATABASE: Creates the standby directly from primary database files, bypassing additional backup steps.
- DORECOVER: Ensures the DUPLICATE includes a recovery operation, bringing the standby up to date.
- SPFILE: Enables adjustments to parameter values in the spfile copied from the primary server.
- NOFILENAMECHECK: Skips validation for destination file paths, useful when paths differ.
With these steps completed, you’re ready to configure the DataGuard broker to manage your setup efficiently.
Using DataGuard Broker
DataGuard Broker provides an easier way to manage DataGuard configurations and monitor roles. To enable the Broker:
-- Enable Broker on both primary and standby databases
ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH;
After enabling the broker, use the DataGuard command-line interface (DGMGRL) to create the configuration.
$ dgmgrl sys/Password1@cdb1
DGMGRL> CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS 'cdb1' CONNECT IDENTIFIER IS 'cdb1';
DGMGRL> ADD DATABASE 'cdb1_stby' AS CONNECT IDENTIFIER IS 'cdb1_stby' MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;
Validating DataGuard Configuration
Verify that the configuration is complete and that both primary and standby databases are in sync.
DGMGRL> SHOW CONFIGURATION;
Configuration - DGConfig
Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 2 seconds ago)
This command shows the current status of the DataGuard configuration and helps identify any issues that need addressing.
DGMGRL> show database cdb1;
Database - cdb1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cdb1
Database Status:
SUCCESS
DGMGRL> show database cdb1_stby;
Database - cdb1_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: OFF
Instance(s):
cdb1
Database Status:
SUCCESS
DGMGRL>
Managing Standby Recovery
Stopping and Starting Recovery Mode
You can control the managed recovery process on the standby database by using the following SQL*Plus commands:
-- To stop managed recovery.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- To start managed recovery.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Performing a Database Switchover
A database can operate in two primary roles (either as primary or standby), and it's possible to switch between these roles without any data loss or resetting redo logs. This is known as a switchover. Here’s how to perform a switchover, starting from the primary database (cdb1) and switching to the standby (cdb1_stby):
$ dgmgrl sys/Password1@cdb1
DGMGRL> switchover to cdb1_stby;
-- The command will connect to "cdb1" on database "cdb1_stby"
-- and perform the switchover, making "cdb1_stby" the new primary.
Once completed, connect to the new primary (cdb1_stby) and switch back to make cdb1 the primary again:
$ dgmgrl sys/Password1@cdb1_stby
DGMGRL> switchover to cdb1;
-- This command re-establishes cdb1 as the primary and cdb1_stby as standby.
Database Failover
If the primary database becomes unavailable, the standby database can take over as the new primary. Connect to the standby database (cdb1_stby) and issue a failover:
$ dgmgrl sys/Password1@cdb1_stby
DGMGRL> failover to cdb1_stby;
-- The standby database "cdb1_stby" is now activated as the primary.
Since the standby is now the primary, a backup is recommended. The original primary (cdb1) can be reconfigured as a standby if Flashback Database was enabled, using the following command:
DGMGRL> reinstate database cdb1;
If Flashback Database is not available, you will need to recreate cdb1 as a standby manually.
Manual Reconfiguration of the Primary as Standby
- Cleanup Old Instance: Shutdown the old primary and remove previous data.
sqlplus / as sysdba
shutdown immediate;
exit;
rm -Rf /u01/app/oracle/oradata/cdb1/*
rm -Rf /u01/app/oracle/fast_recovery_area/cdb1
mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed
mkdir -p /u01/app/oracle/oradata/cdb1/pdb1
- Restart Instance with Temporary Parameter File:
export ORACLE_SID=cdb1
sqlplus / as sysdba
startup nomount pfile='/tmp/initcdb1_stby.ora';
exit;
- Initiate Database Duplication with RMAN: Connect to RMAN for database duplication.
$ rman target sys/Password1@cdb1_stby auxiliary sys/Password1@cdb1
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='cdb1' COMMENT 'Configured as standby'
nofilenamecheck;
- Enable the New Standby Database: Connect to DGMGRL on the primary and enable the standby configuration.
$ dgmgrl sys/Password1@cdb1_stby
DGMGRL> enable database cdb1;
With these steps completed, the original primary database (cdb1) is re-established as a standby, and your setup is fully functional again.
Conclusion
Configuring Oracle DataGuard is essential for organizations requiring high availability and data resiliency. With this setup, the database can quickly recover from any primary failures by promoting a standby database to act as the primary, ensuring minimal downtime and data loss. This guide provides a comprehensive setup, from initial configuration to testing, offering a strong foundation for leveraging Oracle DataGuard in critical environments.