Step by step DataGuard Physical Standby Setup

Oracle Version: 11.2.0.3
OS Version: Oracle Enterprise Linux 6.3
Primary DB Host: oel1.localdomain, 192.168.1.201/24
Standby DB Host: oel2.localdomain, 192.168.1.202/24

Perform following task on Primary Server(oel1.localdomain)

Execute following SQL statement to verify database is on archivelog mode or not. 

SQL> SELECT log_mode FROM v$database;

If database is not on archivelog mode the alter database to archivelog mode

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

Execute following command to enable force logging.

SQL> alter database force logging;

In case of primary server both db_name and db_unique_name should be same. In our case it is orcl for both.

SQL> show parameter db_name

SQL> show parameter db_unique_name

Modify archive related oracle database parameter.

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCL_STDBY)'; 

SQL> alter system set log_archive_dest_2='SERVICE=orcl_stdby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STDBY';

SQL> alter system set log_archive_dest_state_2=DEFER;

Change additional oracle parameters related to archive.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

SQL> alter system set log_archive_max_processes=10;

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

SQL> alter system set fal_server=ORCL_STDBY;

SQL> alter system set standby_file_management=auto;

Add tns entry of standby server on  file $ORACLE_HOME/network/admin/tnsnames.ora

orcl_stdby =

    (DESCRIPTION = 

(ADDRESS_LIST = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

)

(CONNECT_DATA =

    (SERVICE_NAME = orcl)

)

    )

Now create pfile from existing spfile and modify.

SQL> create pfile='/home/oracle/pfile' from spfile;

Modify following lines on pfile.

*.db_unique_name='orcl_stdby'

*.fal_server='orcl'

*.fal_client='orcl_stdby'

*.log_archive_dest_2='SERVICE=orcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

Now ship pfile, password file , tnsnames.ora to standby server.

[oracle@oel1 ~]$ scp pfile oracle@192.168.1.202:

[oracle@oel1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/dbs/

[oracle@oel1 dbs]$ scp orapworcl oracle@192.168.1.202:

[oracle@oel1 dbs]$ cd ../network/admin/

[oracle@oel1 admin]$ scp tnsnames.ora oracle@192.168.1.202:

Check the number of redo log files and create standby redolog files at least one extra group than the number of redo logs. 

SQL> select group#, f.member, l.bytes from v$log l join v$logfile f using(group#);

In our case There are 3 number of logfiles so we need to create at least 4 standby redo logfiles.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo01.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo02.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo03.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo04.log') size 52428800;

SQL> select * from v$logfile;

Perform following task on Standby Server(oel2.localdomain)

Create necessary directories on the standby server.

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/oradata/orcl

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/admin/orcl/adump

[oracle@oel2 admin]$ 

Configure listener with static SID and start listener.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@oel2 admin]$ lsnrctl start

Copy password file, tnsnames file and pfile to required location.
Startup database in nomount mode with pfile shipped earlier.

Now from primary database use rman to connect target and auxiliary database.

[oracle@oel1 trace]$ rman target sys/password@orcl auxiliary sys/password@orcl_stdby nocatalog

Execute Duplicate command from RMAN prompt

If standby redolog error occured as below then re-create standby redolog files manually.

Start redo apply on standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now enable log archive destination on both Primary and Standby server.

SQL> alter system set log_archive_dest_state_2=enable;