Oracle 11g Active Data Guard

Steps to configure Oracle 11g Active Data Guard: 

====================================================
Server details 

Primary Server:
hostname: vmnsedb01
Db_name : nsewebdv
DB unique name: nsewebdv
DB Service name: nsewebdv
sys password: N5EwEbdV
++++++++++++++++++++++++
Standby Server
hostname: vmnsedb02
Db_name : nsewebdv
DB unique name :nsewebsb
DB Service name :nsewebsb
sys password: N5EwEbdV
====================================================

In Primary Database 

STEP:1# Enable force logging:
sql> select force_logging from v$database;   #Output: No
sql> alter database force logging;
sql> select force_logging from v$database;   #Output: Yes

STEP:2# Enable archiving:
sql> shut immediate;
sql> startup mount;
sql> alter database archivelog;
sql> alter database open;
sql> archive log list  #Output: Enable

STEP:3# Create a password file for primary and standby. Password should be same for both primary and standby.
orapwd file=$ORACLE_HOME/dbs/orapwnsewebdv password=nsewebdv #For primary
orapwd file=$ORACLE_HOME/dbs/orapwnsewebsb password=nsewebdv  #For standby

STEP:4#. Configure standby redo log file ON PRIMARY DATABASE with the same size of redo log file.
alter database add standby logfile group 4 '+DATA' size 512M;
alter database add standby logfile group 5 '+DATA' size 512M;
alter database add standby logfile group 6 '+DATA' size 512M;
alter database add standby logfile group 7 '+DATA' size 512M;

STEP:5# Add following parameter in pfile for primary database. 

sql> create pfile='/tmp/pfile_nsewebdv.ora' from spfile;

vi /tmp/pfile_nsewebdv.ora

*.db_name='nsewebdv'
*.db_unique_name='nsewebdv'
*.log_archive_config='DG_CONFIG=(nsewebdv,nsewebsb)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsewebdv'
*.log_archive_dest_2='SERVICE=nsewebsb LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=nsewebsb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=nsewebsb
*.db_file_name_convert='nsewebsb','nsewebdv'
*.log_file_name_convert='nsewebsb','nsewebdv'
*.standby_file_management=auto

STEP:6# Add following parameter in pfile for standby database. 

cp /tmp/pfile_nsewebdv.ora /tmp/pfile_nsewebsb.ora

vi /tmp/pfile_nsewebsb.ora

*.db_name='nsewebdv'
*.db_unique_name='nsewebsb'
*.log_archive_config='DG_CONFIG=(nsewebsb,nsewebdv)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsewebsb'
*.log_archive_dest_2='SERVICE=nsewebdv LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=nsewebdv'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=nsewebdv
*.db_file_name_convert='nsewebdv','nsewebsb'
*.log_file_name_convert='nsewebdv','nsewebsb'
*.standby_file_management=auto

STEP:7# Add following entries to $ORACLE_HOME/network/admin/tnsnames.ora file on both servers

vi $ORACLE_HOME/network/admin/tnsnames.ora

NSEWEBDV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmnsedb01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nsewebdv)
    )
  )
NSEWEBSB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmnsedb02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nsewebsb)
    )
  )
NOTE: Check services are configured properly or not by using tnsping command on both nodes.
tnsping nsewebdv   # Output: ok
tnsping nsewebsb   # Output: ok

STEP:8# Add database entry in listener file and reload the listener

PRIMARY DATABASE: 

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = nsewebdv)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
     (SID_NAME = nsewebdv)
    )
   )

#> lsrnctl reload

STANDBY DATABASE:

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = nsewebsb)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
     (SID_NAME = nsewebsb)
    )
   )

#> lsrnctl reload   

STEP:9#
(a) Copy password file and pfile to standby database
scp /tmp/pfile_nsewebsb.ora oracle@vmnsedb02:/tmp/
scp $ORACLE_HOME/dbs/orapwnsewebsb oracle@vmnsedb02:$ORACLE_HOME/dbs/

(b) Create directory in standby as per standby pfile.
(c) Add standby database entry in standby oratab file.
vi /etc/oratab
nsewebsb:/u01/app/oracle/product/11.2.0.3/db_1:N

STEP:10# Start standby database in nomount stage.

Run below command to set ORACLE_SID & ORACLE_HOME
# . oraenv
[enter oracle sid:]  nsewebsb 

#sqlplus / as sysdba
sql> startup nomount pfile='/tmp/pfile_nsewebsb.ora';
sql> create spfile from pfile='/tmp/pfile_nsewebsb.ora';
sql> shut immediate;
sql> startup nomount;  

STEP:11# Start rman in primary database and run duplicat database command:

rman target sys/nsewebdv@nsewebdv auxiliary sys/nsewebdv@nsewebsb
rman> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
duplicate target database for standby from active database;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
NOTE: Wait for completion of restoration. 

STEP:12# Start Physical Standby Database :
sql> alter database recover managed standby database disconnect from session;

STEP:13# Check Standby Database : 

# In STAND database :
sql> select sequence#, first_time, next_time, applied from v$archived_log order by 1;
# In PRIM database :
sql>alter system switch logfile;
# In STAND database :
sql>select sequence#, first_time, next_time, applied from v$archived_log order by 1;

STEP:14# To Real Time Apply On Physical Standby Database
sql> alter database recover managed standby database cancel;
sql> alter database open;
sql> alter database recover managed standby database using current logfile disconnect from session;
sql> select open_mode from v$database; (Output : read only with apply)

Note: After commit on primary, it will directly apply on standby database.