Create Standby instance and Configure Data Guard on 10gR2

Step1: Configure Listener in Production Server and Standby Server.

TIPS: You should try to Create Listener (Standby) by using Net Configuration Assistant on Standby Server.

TIPS: assume Listener already configure with PROD name on Primary Node. If Listener not configured on Primery Node , You Should Create Listener by using Net Configuration Assistant on Primary Server.

Step2: Configure TNSNAMES.ORA in Production Server and Standby Server. Following TNSNAMES.ORA entry on Production Database and Standby Database

# Connection string for Primary Instance.

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PROD)

)

)

# Connecting string for Standby Instance

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PROD)

)

)

Step3: Put your production database in Archive Log mode if your database not running in Archive log mode.

Step4: add following entries in init.ora file in Production Server.

LOG_ARCHIVE_DEST_1='LOCATION=/oracle/database/archive MANDATORY REOPEN=30'

LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'

STANDBY_FILE_MANAGEMENT=AUTO

# db_file_name_convert: do not need; same directory structure

# log_file_name_convert: do not need; same directory structure

Step 5: shutdown immediate and Copy the Data files and redo log files to standby location.

Step 6: Restart the Production Database

SQL> startup;

Step10 : Create Control file for Standby Database Issue the following command on production database to create control file for the standby database.

SQL> Alter database create standby controlfile as '/oracle/controlfile_standby.ctl';

Database altered.

Step11: Create init.ora file for standby database.
Copy init.ora file from Production Server to Stand by Server in Database folder in oracle home directory and add following entries:

STANDBY_FILE_MANAGEMENT = AUTO

LOG_ARCHIVE_MIN_SUCCEED_DEST=1

STANDBY_ARCHIVE_DEST = '/standby/archive '

fal_server = FAL

fal_client = STANDBY

db_unique_name=standby

# db_file_name_convert: do not need; same directory structure

# log_file_name_convert: do not need; same directory structure

Step 12 Copy the standby control file to standby server and modify control_file parameter in standby init file.

Step: 13 Start Physical standby database
Start up the stand by database using following commands

SQL> conn sys/prod as sysdba

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

Step: 14 Initiate Log apply services The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

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

Database altered.

Now your Standby Instance has been created successfully.