High availability and disaster recovery are critical for any business that relies on uninterrupted database access. Oracle Data Guard provides a robust solution by allowing you to maintain one or more standby databases that act as real-time replicas of your production database. In the event of system failures, planned maintenance, or site outages, a standby database can seamlessly take over, ensuring business continuity.
Here in this article, I am going to cover a step-by-step approach to configure Oracle 11g Data Guard Physical Standby. By following this guide, you will be able to simulate and implement a fully functional Data Guard environment for learning, testing, or production usage.
Ingredients for the Data Guard Environment
For this setup, I used two virtual machines (VMs) – one as the primary database server and the other as the disaster recovery (DR) standby server. Both machines are equipped with sufficient CPU and RAM resources to run Oracle Database.
- Primary Server Configuration:
- OS: CentOS 6.5
- Server name:
pr - IP:
192.168.17.131 - Oracle version: 11.2.0.1.0
- Oracle SID/Global name:
RTS - Oracle db_unique_name:
RTS
- Standby Server Configuration:
- OS: CentOS 6.5
- Server name:
dr - IP:
192.168.17.132 - Oracle version: 11.2.0.1.0
- Oracle SID/Global name:
RTS - Oracle db_unique_name:
RTSDR
Note:
- The Oracle version on both primary and standby servers must be identical.
- The
db_unique_namemust differ between primary and standby.- Ensure both servers can communicate via hostname and IP. Update
/etc/hostsif needed.
Primary Server Configurations
Step 1 – Enable Archive Log Mode
The primary database must run in Archive Log Mode.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Confirm with:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3Step 2 – Enable Force Logging
SQL> ALTER DATABASE FORCE LOGGING;
This ensures that all changes are written to redo logs.
Step 3 – Verify Database Parameters
Check and confirm db_name and db_unique_name:
SQL> show parameter db_name;
NAME TYPE VALUE
----------------
db_name string RTS
SQL> show parameter db_unique_name;
NAME TYPE VALUE
----------------
db_unique_name string RTS
Step 4 – Configure Log Archive Parameters
Enable Data Guard config:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';
show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------
log_archive_config string DG_CONFIG=(RTS,RTSDR)
Step 5 – Set Remote Archive Destination
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V ALID_FOR=(ONLINE_LOGFILES,PRIM ARY_ROLE) DB_UNIQUE_NAME=rtsdr
SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------
log_archive_dest_state_2 string ENABLE
Step 6 – Tune Archive Settings
Set archive format:
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------
log_archive_format string %t_%s_%r.arc
Increase processes:
SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
Set password file usage:
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
Step 7 – Configure FAL Parameters
SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
NAME TYPE VALUE
--------------------------
fal_server string RTSDR
SQL> alter system set fal_client=RTS;
SQL> show parameter fal_client
NAME TYPE VALUE
--------------------------
fal_client string RTS
SQL> alter system set standby_file_management=auto;
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------
standby_file_management string AUTO
Step 8 – Configure Listener
Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RTS)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = RTS)
) )
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
Step 9 – Configure TNS Names
Both servers need matching tnsnames.ora entries for RTS and RTSDR. Verify with tnsping.
RTS =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
) (CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A)
) )
RTSDR =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
) (CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A) )
)Ensure your above configuration by TNSPing utility on Primary and standby:
[oracle@pr admin]$ tnsping RTS
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
OK (10 msec)
[oracle@pr admin]$ tnsping RTSDR
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
OK (20 msec)
Step 10 – Backup primary database via RMAN backup utility:
[oracle@pr admin]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: RTS (DBID=1421312347)
RMAN> backup database plus archivelog;
Step 11 – Create Standby Control File
SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';
Step 12 – Create PFILE
SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;
Edit the PFILE for standby:
*.db_unique_name='RTSDR'
*.fal_server='RTS'
*.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'Step 13 – Copy Required Files to Standby
- Parameter file
- Control files
- Password file
- Listener file
- RMAN backup
Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl
Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Edit DR database listener file with host name. ( i.e. (HOST = dr))
And finally copy RMAN backup to DR @ flash recovery area.
[oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
[oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/Standby Server Configurations
Step 14 – Startup in Mount Mode
Set environment variables, then:
SQL> startup mount;
export ORACLE_SID=RTS
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
[oracle@DR ~]$ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 972898304 bytes
Fixed Size 2219272 bytes
Variable Size 566231800 bytes
Database Buffers 398458880 bytes
Redo Buffers 5988352 bytes
Database mounted.
Step 15 – Create SPFILE from PFILE
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';
Step 16 – Start Listener
lsnrctl start
Step 17 – Prepare Directories
Create admin and recovery directories if missing.
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_areaStep 18 – Restore and Recover Database
RMAN> restore database;
RMAN> recover database;
Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.
Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554
Step 20 – Start Apply Process
Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.
SQL> sqlplus / as sysdba
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;Step 21 – Start Apply Process
Start apply process @ DR.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;
In case of you want to cancel apply process, issue following command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
After graceful completion of above apply process, verify archive logs on Primary as well as DR.
Verification and Testing
Check sync with:
On Primary
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
On Stnadby
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 0
Current log sequence 10By above result, archive logs on both the databases are in sync now.
To test your data guard configuration, generate archive logs on primary site and verify it on DR site:
Switch logs on primary:
SQL> alter system switch logfile;
On Primary
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
On Standby
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 0
Current log sequence 21
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
Verify roles:
SQL> select name,open_mode,database_role from v$database;
On Primary:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- --------------------
RTS READ WRITE PRIMARYOn Standby:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- --------------------
RTS MOUNTED PHYSICAL STANDBY
Check applied logs:
On Primary
select thread#,max(sequence#) from gv$archived_log group by thread#;On Standby
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;If issues arise, run:
SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
DEST_NAME
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2 VALID
SQL> select message from v$dataguard_status;
In addition to manual setup, Oracle also provides a Broker utility to simplify administration. Learn how to create an Oracle Data Guard Broker configuration
Conclusion
By following these detailed steps, we have successfully configured Oracle 11g Data Guard with a Physical Standby database. This setup not only ensures data protection but also provides a robust disaster recovery solution. You can extend this configuration further by testing failover and switchover operations to fully utilize Oracle Data Guard’s potential.
With both servers in sync, you now have a strong foundation for high availability, data integrity, and business continuity. Whether you’re a DBA practicing in a lab or preparing for enterprise deployment, mastering Oracle Data Guard will add significant value to your database management skills.
Comments 3