Oracle 11g R2 Dataguard configuration step by step

Date: March 17, 2016 Author: Shripal Singh (DBA)

Note:- Oracle Database RDBMS software installed with one database on the PRIMARY server and on STANDBY server installed only RDBMS software without any Database. Following below details:

Primary:

IP Address: 192.168.1.10
DB_NAME=db11g
DB_UNIQUE_NAME=db11g

Standby:

IP Address: 192.168.1.20
DB_NAME=db11g
DB_UNIQUE_NAME=std

Required parameters:

DB_NAME                                             – Must be same on primary and on all standby
DB_UNIQUE_NAME                             – Must be different on primary and all standby
LOG_ARCHIVE_CONFIG             – This parameter includes db_unique_name which are the part of Dataguard configuration
LOG_ARCHIVE_DEST_n                     – Define local and remote archive log file location
LOG_ARCHIVE_DEST_STATE_n – Define state of archiving (ENABLE or DIFER)
REMOTE_LOGIN_PASSWORDFILE    – Must be in EXCLUSIVE mode
FAL_SERVER                                        – Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT                 – Required when directory structure is different datafile
LOG_FILE_NAME_CONVERT               – Required when directory structure is different logfile
STANDBY_FILE_MANAGEMENT          – Keep auto to create file automatically on standby

Perform following steps on primary database:

Note:- Make sure primary database is runing in archivelog mode

Check your database mode using following command

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

If your database is not runing in archivelog mode use following command to change to archive mode.

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

Now your Database is runing in archivelog mode.

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

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

If not then following below command

SQL> ALTER DATABASE FORCE LOGGING;

Now verify DB_NAME and DB_UNIQUE_NAME of primary database

SQL> show parameter db_name SQL> show parameter db_unique_name

Now Make DB_UNIQUE_NAME to be part of dataguard. (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)

Then create service using “netmgr” command.

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

Now start listener

$lsnrctl start

Set archivelog destinations

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;**

Set remote login password to exclusive

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

Set fail server and file name convert parameter in case if directory structure is different in primary and standby database.

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;

Now tack the backup primary database using RMAN

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

Now create standby controlfile and pfile

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

Now edit your pfile

$vi /u01/initstd.ora

Note:- YOUR PFILE PARAMETER LOCK 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'

Save this file then create some directories on standby machine

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

After creating appropriate directory on physical standby and copy backupset, archivelog, pfile, standby controlfile and password file to 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 archivelog 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 remote login 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

On physical standby server

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

make tns service using below 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 listner

$lsnrctl start

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**

Restore backup on standby machine

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

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

Sql> exit

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

Note:- After finishing restore database we need to create standby redo log file on standby server, and it should be one extra either then 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 have needed to add four redo log files because we have three online redo log file.

Now check your log members and you can confirm using this command

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

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

So now going on PRIMARY server and add redolog 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;

Now CHECK….

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

Now start redo apply process on standby

Note: before applying redolog files, open alert logfile on different terminal

On standby machine

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

Run below command and check current redo sequence number

On primary server

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

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

SQL> alter system switch logfile;

Then check what your current sequence number on PRIMARY machine is

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

Then going on STANDBY machine and check redo are going on standby machine or not.

STANDBY:-

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

Now going on PRIMARY machine and run 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 Dataguard configuration is completed:

Steps to configure read only STANDBY

On STANDBY machine

Now I’m going to convert physical standby database into read only standby database.

In this case what happen your database will be in read only mode. Let me show you how to convert physical standby server into read only mode.

So what you do actually

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

After running these all commands your database will be open in read only mode.

Check…

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 PRIMARY machine and run switch log file 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 if you’re standby database in read only mode then redo are not applying.

If you want to bring back to physical standby following below steps

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

After that check redo applying or not

So this is your physical standby database in read only mode,

But in oracle 11g have new feature ACTIVE DATAGUARD.

How to configure ACTIVE DATAGUARD

In ACTIVE DATAGUARD feature we can open standby database in read only mode and also can apply log files.

Steps almost same like 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#;

Enjoy