DATA GUARD SETUP STEP BY STEP

just follow the below steps top setup dataguard:

Environment              Server       Database Name

Production Primary  172.16.118.37 ORACLE
Production Standby  172.16.118.38 ORACLE

2.2 Setup the Primary Site (172.16.118.37)

Assumptions: The db_unique_name parameter has to be set to ORACLE_A in the initORACLE.ora.

Step Procedure

1.Logon to the dm3cvs server has the oradpi user

Confirm that the ORACLE_SID and ORACLE_BASE environment variables are set properly

ORACLE_BASE= /d01/app/oracle
ORACLE_SID= ORACLE

$ env | grep ORA

2.Start a sqlplus session connected as sysdba

$ sqlplus / as sysdba
SQL> show parameter db_unique_name

Complete the following step only if: db_unique_name is not set to ORACLE_A

• Create pfile from spfile

SQL> create pfile='/d01/app/oracle/admin/ORACLE/pfile/initORACLE.ora' from
spfile='/d01/app/oracle/product/10.2.0/dbs/spfileORACLE.ora';

• Modify the db_unique_name  parameter in the initORACLE.ora

• Shut down the database

  SQL> shutdown immediate

• Startup using newly created and modified pfile

  SQL> startup pfile=’/d01/app/oracle/admin/ORACLE/pfile/initORACLE.ora';

• Create spfile from pfile

SQL> create spfile from pfile='/d01/app/oracle/admin/ORACLE/pfile/initORACLE.ora';

• Confirm Modification

SQL> show parameter db_unique_name

• Shutdown and Startup (to use spfile so following commands will not fail)

SQL> shutdown
SQL> startup

3 Apply Data Guard configuration

SQL> alter database force logging;

SQL> alter system set log_archive_config='DG_CONFIG=(ORACLE_A,ORACLE_B)' scope=both;

For the following command, ensure that the log_archive_dest location folder is created
   SQL> HOST mkdir -p /backup/raddpi/arch/ORACLE/BK_STB

SQL> alter system set log_archive_dest_1='LOCATION=/backup/raddpi/arch/ORACLE/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACLE_A' scope=both;

SQL> alter system set log_archive_dest_2='SERVICE=ORACLE_B LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACLE_B'
scope=both;

SQL> alter system set log_archive_dest_state_1='ENABLE' scope=both;

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

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

SQL> alter system set standby_file_management=auto scope=both;

SQL> alter system set log_archive_max_processes=3 scope=spfile;

SQL> alter system set log_archive_min_succeed_dest=1 scope=both;

SQL> alter system set fal_server=ORACLE_B scope=both;

SQL> alter system set fal_client=ORACLE_A scope=both;

4.Update pfile with spfile (will be used later for the standby database)

SQL> create pfile='/d01/app/oracle/admin/ORACLE/pfile/initORACLE.ora' from

spfile='/d01/app/oracle/product/10.2.0/dbs/spfileORACLE.ora';

5.Backup the Primary database using this rman script. (See appendix). 

Verify if the database is in archive log mode with the following SQL:

SQL> select name, log_mode from v$database;

If the log_mode = “NOARCHIVELOG” then execute the following SQL.

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> select name, log_mode from v$database;

Create rman directory (if it doesn’t exist)

   $ mkdir -p /d01/app/oracle/admin/ORACLE/rman

Copy the backup_db_for_standby.rcv rman script from the appendix to the rman folder

Execute the RMAN procedure.

$ rman target / cmdfile=backup_db_for_standby.rcv

Backup files will be located in the /backup/raddpi/arch/ORACLE/BK_STB directory.

6.Add the following entries to the  tnsnames.ora file located in

(/d01/app/oracle/product/10.2.0/network/admin) on the dm3cvs server

ORACLE_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.37)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = ORACLE)
  )
 )

ORACLE_B =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.38)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = ORACLE)
  )
 )

6 Make sure the following entries in the listener.ora file located in

(/d01/app/oracle/product/10.2.0/network/admin) are the same as shown below. If not then add
these entries to the listener.ora file on the dm3cvs server

LISTENER_ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.37)(PORT = 1521)
 )

SID_LIST_LISTENER_ORACLE =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /d01/app/oracle/product/10.2.0)
      (SID_NAME = ORACLE)
  )
 )

7 Check the status of the Listener

$ lsnrctl status LISTENER_ORACLE

8 Depending of the message you get you might need to Stop and or Start the Listener

$ lsnrctl stop LISTENER_ORACLE

$ lsnrctl start LISTENER_ORACLE

9 Add standby redo log

$ sqlplus / as sysdba

SQL> alter database add standby logfile

group 5 ('/u02/oradata/ORACLE/standby_redo01a.log',
'/u03/oradata/ORACLE/standby_redo01b.log') SIZE 200M;

SQL> alter database add standby logfile

group 6 ('/u02/oradata/ORACLE/standby_redo02a.log'
,'/u03/oradata/ORACLE/standby_redo02b.log') SIZE 200M;

SQL> alter database add standby logfile

group 7 ('/u02/oradata/ORACLE/standby_redo03a.log'
,'/u03/oradata/ORACLE/standby_redo03b.log') SIZE 200M;

SQL> alter database add standby logfile

group 8 ('/u02/oradata/ORACLE/standby_redo04a.log'
,'/u03/oradata/ORACLE/standby_redo04b.log') SIZE 200M;

Notes: The standby logfile sizes must be identical on the primary and standby databases and the
number of standby redo logfile groups should be at least the same as the redo logfile groups on the
primary database.

10 Bounce the database

   SQL> shutdown immediate

   SQL> startup

   SQL> exit

2.3 Set up the Standby Site (172.16.118.38)

Step Procedure Login to the dcocu2 server as the oradpi user

Confirm that the ORACLE_SID and ORACLE_BASE environment variables are set properly

ORACLE_BASE= /d01/app/oracle
ORACLE_SID= ORACLE

$ env | grep ORA

1 Obtain the backup sets from the Primary site. 

$ mkdir -p /backup/raddpi/arch/ORACLE/BK_STB

$ cd /backup/raddpi/arch/ORACLE/BK_STB
$ sftp oradpi@172.16.118.37
    Password: ************
sftp> cd /backup/raddpi/arch/ORACLE/BK_STB
sftp> ls
sftp> get <filename1>
sftp> get <filename2>
……
sftp> get <filenamex>
sftp> exit

Notes:  Get all the files starting with inc0 and cf. You can also use the mget command with a
combination of asterisk (ex: mget inc0*.*)

Obtain the archived logs from the Primary site.

 cd /backup/raddpi/arch/ORACLE
 sftp oradpi@172.16.118.37
 Password: ************
 sftp> cd /backup/raddpi/arch/ORACLE
 sftp> ls
 sftp> get <filename1>
 sftp> get <filename2>
       ……
 sftp> get <filenamex>
 sftp> exit

Notes: Need to get the recent archived logs, these generated after finishing backup with
RMAN. Most of the archive logs are already included in the RMAN backup, but if the database
is still open with activities, some archive logs following the last one included in the RMAN
backup could be necessary to complete recovery. You can also use the mget command with
a combination of asterisk (ex: mget *.arc)

2 Obtain the pfile from the Primary (dm3cvs) site.

From the dcuco2 server execute the following commands

$ cd /d01/app/oracle/admin/ORACLE/pfile
$ sftp oradpi@172.16.118.37
   Password: *************
   sftp> cd /d01/app/oracle/admin/ORACLE/pfile
   sftp> get initORACLE.ora
   sftp> exit

3 From the file obtained at the previous step modify the following entries to initORACLE.ora :

*.db_domain='***'
*.db_name='ORACLE'
*.db_unique_name='ORACLE_B'
*.fal_client='ORACLE_B'
*.fal_server='ORACLE_A'
*.log_archive_config='DG_CONFIG=(ORACLE_A,ORACLE_B)'
*.log_archive_dest_1='LOCATION=/backup/raddpi/arch/ORACLE/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACLE_B'
*.log_archive_dest_2='SERVICE=ORACLE_A LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACLE_A'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
   *.log_archive_format='%t_%s_%r.arc'
   *.log_archive_max_processes=3
   *.log_archive_min_succeed_dest=1
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

4  Add the following entries to the  tnsnames.ora file located in 

(/d01/app/oracle/product/10.2.0/network/admin)

ORACLE_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.37)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = ORACLE)
  )
 )

ORACLE_B =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.38)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = ORACLE)
  )
 )

5.  Make sure the following entries in the listener.ora file located in

(/d01/app/oracle/product/10.2.0/network/admin) is as the same as shown below. 
If not then add these entries to the listener.ora file

LISTENER_ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.118.38)(PORT = 1521))
 )
SID_LIST_LISTENER_ORACLE =

  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /d01/app/oracle/product/10.2.0)
      (SID_NAME = ORACLE)
  )
 )

6.Start the Listener

$ lsnrctl start LISTENER_ORACLE

7. Create password file (enter the sys password in the password parameter)

$ /d01/app/oracle/product/10.2.0/bin/orapwd
File=/d01/app/oracle/product/10.2.0/dbs/orapwORACLE password=<password>

8. Create spfile

SQL> sqlplus / as sysdba

SQL> create spfile='/d01/app/oracle/product/10.2.0/dbs/spfileORACLE.ora' from

pfile='/d01/app/oracle/admin/ORACLE/pfile/initORACLE.ora';

9. Restore database

   SQL> startup nomount

   SQL> alter system set disk_asynch_io=false scope=spfile;

   SQL> exit

Create rman directory (if it doesn’t exist)

   $ mkdir -p /d01/app/oracle/admin/ORACLE/rman

Copy the restore_db_for_standby.rcv script from the appendix to the rman folder

Execute the RMAN procedure.

$ cd /d01/app/oracle/admin/ORACLE/rman

$ nohup rman target sys/<password>@ORACLE_A auxiliary sys/<password>@ORACLE_B

log=restore_db_for_standby.log
      cmdfile=restore_db_for_standby.rcv &

10. Add standby redo log

 sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database add standby logfile group 5 ('/u02/oradata/ORACLE/standby_redo01a.log'

         ,'/u03/oradata/ORACLE/standby_redo01b.log') SIZE 200M;

SQL> alter database add standby logfile group 6 ('/u02/oradata/ORACLE/standby_redo02a.log'

         ,'/u03/oradata/ORACLE/standby_redo02b.log') SIZE 200M;

SQL> alter database add standby logfile group 7 ('/u02/oradata/ORACLE/standby_redo03a.log'

         ,'/u03/oradata/ORACLE/standby_redo03b.log') SIZE 200M;

SQL> alter database add standby logfile group 8 ('/u02/oradata/ORACLE/standby_redo04a.log'

         ,'/u03/oradata/ORACLE/standby_redo04b.log') SIZE 200M;

11.Add TEMP tablespace

   SQL> alter database open read only;

   SQL> alter tablespace "TEMP" add tempfile

'/u03/oradata/ORACLE/temp01.dbf' SIZE 2048M;

   SQL> shutdown immediate

Applying Redo Data to Standby Database

Step Procedure

1 Standby database: 172.16.118.38

$ sqlplus  / as sysdba

SQL> startup mount;

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

2 Primary database:172.16.118.37

   SQL> alter system archive log current;

Appendixes

Appendix A : backup_db_for_standby.rcv

RMAN script to be used at Primary server:

run {                                                                
allocate channel d1 type disk;                              
allocate channel d2 type disk;                              
allocate channel d3 type disk;                              
allocate channel d4 type disk;                              

setlimit channel d1 kbytes 2097150;

setlimit channel d2 kbytes 2097150;
setlimit channel d3 kbytes 2097150;
setlimit channel d4 kbytes 2097150;

backup                                              
   incremental level 0                              
   database                                        
   tag inc0_db_open                                
   filesperset=5                                    
   format '/backup/raddpi/arch/ORACLE/BK_STB/inc0_df_%U.dat'
   include current controlfile for standby;                    

sql "alter system switch logfile";                      

sql "alter system archive log current";                  
backup                                                  
   format '/backup/raddpi/arch/ORACLE/BK_STB/cf_standby.%T.%u'  
   current controlfile for standby;                      
sql "alter system switch logfile";                      
sql "alter system archive log current";                  
backup                                                  
   format '/backup/raddpi/arch/ORACLE/BK_STB/cf_backup.%T.%u'  
   current controlfile;                      
}                                  

Appendix B : restore_db_for_standby.rcv

RMAN restore script to be used at Standby server:

run {                                                                
allocate auxiliary channel d1 type disk;                              
allocate auxiliary channel d2 type disk;                              
allocate auxiliary channel d3 type disk;                              
allocate auxiliary channel d4 type disk;                              
duplicate target database for standby nofilenamecheck dorecover;                                                      

}