Steps for implementing Physical Standby Database

In this post Iam going to give step by step procedure for creating the Physical Standby Database.

Production Database: PRDB
Standby Database: LCDR
Production server:x.x.x.41
Standby Server: x.x.x.47
Database Version:10.2.0.4.0

Step1: Enable Force Logging in Primary Database

SQL> Alter Database Force Logging:

Step2: Backup the primary database and copy the backup to the Standby server. The directory structure of the backup must be same as that of the primary server.

For backup script click here

Step3: Update the oratab file in the standby server.

$ cd /etc
$ vi oratab

+ASM:/home/oracle/OraHome1:N
LCDR:/home/oracle/OraHome1:N

$

Step4: Create a password file for LCDR.
Copy the password file from production and rename it.

Step5: Create necessary directories (bdump,cdump etc). Since this is a physical standby the directory structure of the standby database must be identical to that of primary database. If you are using ASM the diskgroup name should be identical to primary ASM.

Step 6: Update tnsnames.ora and listener.ora

Step 7: Test the connectivity from both server.

$ tnsping PRDB
$ tnsping LCDR

Step 8: Create init.ora file for standby database.
Copy the init.ora file from the target database to aux server.
Modify/Add the following parameters,

Audit_file_dest
Background_dump_dest
Core_dump_dest
User_dump_dest
Control_files
Db_name
Db_unique_name
dispatchers
Fal_client
Fal_server
Instance_name
Log_archive_config
Log_archive_dest_1
Service_names
Db_file_name_convert
Log_file_name_convert

Modify/Add the following parameters in the primary database (PRDB),
SQL> alter system set log_archive_dest_2='SERVICE=LCDR valid_for=(online_logfiles, primary_role) db_unique_name=LCDR' scope=both;

SQL> alter system set log_archive_config='dg_config=(LCDR,PRDB)' scope=both;

SQL> alter system set standby_file_management='AUTO' scope=both;

Click here for a sample init.ora file

Step 9: Startup standby database in nomount stage.

From standby server
$ export ORACLE_SID=LCDR
$ sqlplus / as sysdba
connected to an idle instance
SQL> create spfile from pfile;
File created
SQL> Startup nomount;

Step 10: Run the RMAN duplicate command to create standby database.
For duplicate script click here
Step 11: Create the standby redolog (SRL) groups in standby database.

Number of SRL is one more than the no. of online redolog groups in primary database.
For eg. if the no.of online redolog groups in primary database is 3, you have to create four SRL.

SQL> Alter database add standby logfile group 4 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 5 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 6 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 7 '+ASMFLASH' size 512M;

Step 12: Start the Managed Recovery Process on the standby.

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

Step 13: Verify log shipment.

On the primary

SQL> Alter system set log_archive_dest_state_2= 'ENABLE' scope=both;
SQL> Alter system Switch logfile;

Check whether the logfiles are shipped to the standby server.

Thanks