Oracle 11g R2 Dataguard configuration step by step

Oracle 11g R2 Dataguard configuration step by step, before starting DG configuration you must read the below note.

Data Guard configuration steps in oracle 11gr2

Subscribe on YouTube

Oracle Database RDBMS software installed with one database on the PRIMARY server and on STANDBY server installed only RDBMS software without any Database.

Required parameters

You must read the below articles:

Oracle 19c Data Guard Configuration step by step

How to Convert Physical Standby To Snapshot Standby?

Resolve huge archive gap between primary and standby

Step by Step to configure Oracle 12c Data Guard Physical Standby

Oracle 11g R2 Dataguard Configuration with video step by step

Perform the following steps on the primary database

Step 1 - Check log mode

Make sure the primary database is running in archive log mode. Check your database mode using the following command:

SQL> select log_mode from v$database;
              OR
SQL> archive log list

Step 2 - Change log mode

If your database is not running in archive log mode use the following command to change to the archive mode of your database:

SQL> SHU IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Now your database is running in archive log mode.

Step 3 - Enable force logging

Note: – Make sure the database is in force logging mode.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

If not then run the below command to enable force logging.

SQL> ALTER DATABASE FORCE LOGGING;

Step 4 - Check DB name

Now verify DB_NAME and DB_UNIQUE_NAME of the primary database

SQL> show parameter db_name
SQL> show parameter db_unique_name

Step 5 - Change log_archive_config value

Now Make DB_UNIQUE_NAME to be part of the data guard. (std service we will create soon)

SQL> alter system set log_archive_config=’DG_CONFIG=(db11g,std)’; 

(db11g primary service name & std standby service name)

Step 6 - Create TNS services and start listener

Now it's time to create a TNS service using “netmgr”.

$netmgr –> service add for std (+) –> net service name std –> hostname standby machine ip –> service name std -> save

Start production listener:

$lsnrctl start

Step 7 - Set log_archive_dest_2 values

Set archive dest 2 location on production.

SQL> alter system set log_archive_dest_2=’service=std
     Valid_for=(online_logfiles, primary_role) db_unique_name=std’;

SQL>alter system set log_archive_dest_state_2=enable;

Step 8 - Change remote_login_passwordfile parameter

You must set the remote login password to in exclusive mode.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> show parameter remote_login

Step 9 - FAL server setting

Set fal server and file name convert parameter in case if the directory structure is different in primary and standby databases.

SQL> ALTER SYSTEM SET FAL_SERVER=std;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’std’,’db11g’ scope=spfile;
SQL> ALTER SYSTEM SET LOG_FILES_NAME_CONVERT=’std’,’db11g’ scope=spfile;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step 10 - Take RMAN full backup

Now tack the backup of the primary database using RMAN

$rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Step 11 - Standby Controlfile and pfile

Create standby control file and pfile

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/stdcontrol.ctl’;
SQL> CREATE PFILE=’/u01/initstd.ora’ from spfile;

Step 12 - Edit pfile for standby

Edit backup pfile and make some changes for standby, after making changes the standby pfile look like this:

std.__db_cache_size=318767104
std.__java_pool_size=4194304
std.__large_pool_size=4194304
std.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
std.__pga_aggregate_target=335544320
std.__sga_target=503316480
std.__shared_io_pool_size=0
std.__shared_pool_size=159383552
std.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/fast_recovery_area/std/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='db11g','std'
*.db_name='db11g'
*.db_unique_name='std'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db11gXDB)'
*.fal_server='DB11G'
*.log_archive_config='DG_CONFIG=(db11g,std)'
*.log_archive_dest_2='SERVICE=db11g VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='db11g','std'
*.memory_target=836763648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step 13 - Create required directories on standby

Create required directories on the standby side.

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

Step 14 - Copy backup files on standby

After creating an appropriate directory on physical standby and copying the backupset, archive log, pfile, standby control file, and password file to the physical standby database.

#scp /u01/stdcontrol.ctl oracle@192.168.1.20:/u01/app/oracle/oradata/std/control01.ctl
#scp /u01/stdcontrol.ctl oracle@192.168.1.20:/u01/app/oracle/fast_recovery_area/std /control02.ctl

Transfer archive log and backups

#scp –r /u01/app/oracle/fast_recovery_area/DB11G oracle@192.168.1.20:/u01/app/oracle/fast_recovery_area/

Copy Parameter file

#scp /u01/initstd.ora oracle@192.168.1.20:/u01/initstd.ora

Transfer password file

#scp /u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwdb11g oracle@192.168.1.20:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwstd

Action On physical standby server

Set ORACLE_HOME

$export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1

Step 1 - Add TNS entry on Standby and start listerner

make TNS service using below the command

$netmgr
 =>service naming
           + add new
              Net service name (db11g)
                      Host name (server ip)
                             Service name (db11g)
 Test your connection then finish
Add one more service for standby server
        + add new
           Net service name (std)
                 Host name (standby ip)
                         Service name (std)
                                    Then finish

Then save click on file => save network configuration

Start listener:

$lsnrctl start

Step 2 - Update /etc/oratab

Now update /etc/oratab file on standby machine

$vi /etc/oratab  (add below line in end of file) 

Std:/u01/app/oracle/product/11.2.0.4/db_1:N

Step 3 - Create spfile from pfile

Create spfile from pfile:

$export ORACLE_SID=std
$sqlplus / as sysdba
Sql> create spfile from pfile=’/u01/initstd.ora’;

Step 4 - Restore RMAN backup on standby

Restore backup on standby machine:

Now exit from SQL prompt and login with RMAN then restore backup

$rman target=/
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> exit

Step 5 - Create standby logfiles

Note:- After finishing the restore database we need to create a standby redo log files on the standby server, and it should be one extra either than an online redo log file.

$sqlplus / as sysdab
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo01.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo02.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo03.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo04.log’) size 50m;

Note:- we need to add four redo log files because we have three online redo log files, always add one extra standby log file.

Check your log members and you can confirm using the below command.

SQL> select member from v$logfile  where type=’STANDBY’;
SQL> select member from v$logfile;

Step 6 - Create standby logfiles also on standby

Note:- Now we need to create the same online redo log files on the PRIMARY machine also in case you planning to switch the role so if your primary become STANDBY then you need to have STANDBY redo log files.

On PRIMARY server and add redo log files.

SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo01.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo02.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo03.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo04.log’) size 50m;

Check standby redo details:

SQL> select member from v$logfile  where type=’STANDBY’;

Step 7 - Start MRP process

Now start the redo apply process on standby

Note: before applying redo log files, open the alert log file on a different terminal for monitoring standby database activity.

On-standby machine:

QL> alter database recover managed standby database disconnect from session;

Run the below command and check the current redo sequence number on the primary side:

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

Now switch the log file using the below command and check it’s applying on the standby server or not.

SQL> alter system switch logfile;

Then check your current sequence number on the PRIMARY machine:

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

Then switch on the STANDBY machine and check the redo is coming on the standby machine or not.

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Now go on the PRIMARY machine and run the switch logfile command one more time.

SQL> alter system switch logfile;

Now check DB mode and protection mode run below command on both machines:

SQL> desc v$database
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

Now your Oracle 11g R2 Dataguard configuration is completed.

Step 8 - Open physical standby in Read only mode

Steps to configure read-only STANDBY - Action On STANDBY machine

Now I’m going to convert the physical standby database into a read-only standby database. In this case, what happens to your database will be in read-only mode. Let me show you how to convert the physical standby server into read-only mode.

SQL>Shu immediate
SQL>startup mount;
SQL>alter database open read only;

After running the above commands your database will be open in read-only mode.

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
SQL> select * from scott.emp;   (now you able to read your database)

Now login on the PRIMARY machine and run the switch logfile command.

SQL>alter system switch logfile;

On STANDBY check redo applying or not

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Note:-You can see redo files but it’s not applied, So it is simple if you’re a standby database in read-only mode then archives are not applying.

Let's bring back to physical standby using the below steps:

SQL> shu immediate
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session;

Now the archive is applied on the standby side.

Steps to configure Active Data Guard

How to convert physical standby database into active data guard step by step.

Oracle 11g has a new feature called ACTIVE DATAGUARD.

In the ACTIVE DATAGUARD feature, we can open the standby database in read-only mode and also can apply log files. Steps are almost the same as a read-only standby database.

SQL>shu immediate
SQL>startup mount;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;

Now you can check open mode:

 SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

And check redo apply

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Step to Convert physical standby into snapshot

Switchover activity