This article explains the setup of DataGuard in Oracle 11G R2 database. Switching to a DataGuard structure (primary – standby) increases a single instance database’s accessibility.
The setup of DataGuard consists of below major parts:
For our article, we will use an operational ERP database with a SID of “erp”, to which a standby database will be added. Both the primary and standby database will have the same DB_NAME, but it will have a different DB_UNIQUE_NAME. Below are the configurations:
Parameter | Primary Database | Standby Database |
DB_NAME / ORACLE_SID | erp | erp |
DB_UNIQUE_NAME | erpprm | erpsby |
IP Address | 192.168.137.100 | 192.168.137.105 |
Hostname | adb.sysdba.org | sdb.sysdba.org |
Configuration of the primary database
Step 1:
Configure the /home/oracle/.bash_profile file with the below parameter in server.
Primary DB
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ORACLE_HOSTNAME=adb.sysdba.org; export ORACLE_HOSTNAME ORACLE_UNIQNAME=erp_prm; export ORACLE_UNIQNAME ORACLE_BASE=/Oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/11G; export ORACLE_HOME ORACLE_SID=erp; export ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH; export PATH PATH=/usr/sbin:$PATH; export PATH |
Step 2:
Update the /etc/hosts files. Add the following two lines (on both servers)
1 2 3 |
192.168.137.100 adb.sysdba.org adb 192.168.137.105 sdb.sysdba.org sdb |
Step 3:
Placing the Primary DB in ArchiveLog mode
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
!mkdir /Oracle/ArcLog alter system set log_archive_dest_10='LOCATION=/Oracle/ArcLog' scope=spfile; alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfile; shutdown immediate; startup mount; alter database archivelog; alter database open; To check: select log_mode from v$database; select archiver from v$instance; archive log list; |
Step 4:
Activating “Force Logging” in the Primary DB.
1 |
alter database force logging; |
Step 5:
Copy the password file from the primary DB server to standby DB server.
1 |
scp /Oracle/11G/dbs/orapwderp sdb:/Oracle/11G/dbs/ |
Step 6:
1 2 3 |
alter system set service_names=’erpprm’ scope=both; alter system set DB_UNIQUE_NAME=’erpprm’ scope=spfile; |
1 2 3 4 5 6 7 |
show parameter service NAME TYPE VALUE --------------------- ----------- ----------- service_names string erpprm |
1 2 3 |
shutdown immediate; startup; |
Step 7:
1 2 3 |
alter system set fal_server='erpsby' scope=both; alter system set fal_client='erpprm' scope=both; |
To synchronize the changes in the Primary DB with the Standby DB:
1 2 3 |
alter system set standby_file_management=AUTO scope=both; alter system set LOG_ARCHIVE_DEST_10='LOCATION=/Oracle/ArcLog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erpprm'; |
If db_recovery_file_des was used:
1 |
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erpprm'; |
The previous line indicates that the RedoLogs will be moved to the Standby DB.
Step 8:
Add standby redo logfile in the primary DB.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
shutdown immediate; startup mount alter database add standby logfile group 4 size 100m; alter database add standby logfile group 5 size 100m; alter database add standby logfile group 6 size 100m; alter database add standby logfile group 7 size 100m; alter database set standby database to maximize availability; alter database open; |
Using RMAN for...
12 March 2019