Physical Standby Database Creation Using RMAN Active Duplication

Environment

OS: RHEL5 - 64 Bit

Database Version : 11.1.0.7

Primary Database Unique Name : PROD

Physical Standby Database Unique Name : STBY

This can be done in 3 phases

Phase 1 : Configure Primary Database Environment

Phase 2 : Configure Physical Standby Database Environment

Phase 3 : Start Managed Recovery and Verify

Phase 1 : Configure Primary Database Environment

Step 1 : Enable Force Logging

sqlplus / as sysdba

SQL> alter database force logging;

Step 2 : Create Password File

cd $ORACLE_HOME/dbs

orapwd file=orapw$ORACLE_SID password=sys force=y

Step 3 : Configure Primary Database To receive redo data ( Create Standby log files on primary database )

Points to be noted while creating standby logs at primary site

- When database is running in primary role standby logs are ignored.

- When primary database is converted to standby role then role transition can be made quick because standby logs are already present

- The number of standby logs to be created at primary should be at-least one more than the number of online redo logs at standby (Again, online redo logs are ignored at standby site, but they are created and kept ready when standby assumes primary role)

- If Standby redo logs are created at primary site then while creating standby database using rman active duplication (lets say), rman automatically creates standby logs at standby database also, which enables you to start real time apply immediately after the rman duplication is completed.

sqlplus / as sysdba

SQL> alter database add standby logfile ('/u01/CHARAN/oracle/11gR1/PROD/oradata/slog1.log') size 4m;

SQL> alter database add standby logfile ('/u01/CHARAN/oracle/11gR1/PROD/oradata/slog2.log') size 4m;

SQL> alter database add standby logfile ('/u01/CHARAN/oracle/11gR1/PROD/oradata/slog3.log') size 4m;

Step 4 : Configure Parameter File at Primary

#Primary database Primary Role initialization parameters

DB_NAME=PROD

DB_UNIQUE_NAME=PROD

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STBY)'

CONTROL_FILES='/u01/CHARAN/oracle/11gR1/PROD/oradata/control1.ctl'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/CHARAN/oracle/11gR1/PROD/oradata/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'

LOG_ARCHIVE_DEST_2='SERVICE=TNS_STBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

#Primary database Standby role initialization parameters

FAL_SERVER=TNS_STBY

DB_FILE_NAME_CONVERT='STBY','PROD'

LOG_FILE_NAME_CONVERT='STBY','PROD'

STANDBY_FILE_MANAGEMENT=AUTO

Step 5 : Enable archived ( If database is not already in archive log mode )

sqlplus / as sysdba

SQL> shut immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

Step 6 : Configure Oracle Network

cd $ORACLE_HOME/network/admin

vi listener.ora

PROD =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba01.charan.com)(PORT = 1521))

)

)

SID_LIST_PROD =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME= /u01/CHARAN/oracle/11gR1/PROD/orahome)

(SID_NAME = PROD)

)

)

Start the listener : lsnrctl start PROD

vi tnsnames.ora

TNS_STBY=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=dba01.charan.com)(PORT=1541))

(CONNECT_DATA=

(SERVICE_NAME=STBY)

(INSTANCE_NAME=STBY)

)

)

TNS_PROD=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=dba01.charan.com)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=PROD)

(INSTANCE_NAME=PROD)

)

)

As of now, check only TNS_PROD : tnsping TNS_PROD

Note : TNS entries for both databases are required at primary only if rman command is issued at primary (rman has to be connected through Oracle Net Service names instead of OS Authentication for duplication since oracle transfers backup files only through network and authentication is made through password files)

Phase 2 : Configure Physical Standby Database Environment

Step 1 : Copy the Oracle Home from Primary to Standby Site

In my environment both primary and standby are present in the same server, I copied primary oracle home to a different directory for standby oracle home (though both primary and standby can use the same oracle home while they are residing on the same server)

cd /u01/CHARAN/oracle/11gR1/PROD/

nohup cp -r orahome /u01/CHARAN/oracle/11gR1/STBY &

Step 2 : Create an environment file for standby

vi db_STBY.env

export ORACLE_SID=STBY

export ORACLE_HOME=/u01/CHARAN/oracle/11gR1/STBY/orahome

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Step 3 : Create Password File at Standby Site

Create a password file with same password as primary sys password or copy the password file from primary and rename it

cd $ORACLE_HOME/dbs

mv orapwPROD.ora orapwSTBY.ora

Step 4 : Configure Standby database initialization parameters

Note: db_name parameter should be same for both primary and standby

DB_NAME=PROD

DB_UNIQUE_NAME=STBY

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STBY)'

CONTROL_FILES='/u01/CHARAN/oracle/11gR1/STBY/oradata/control1.ctl'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/CHARAN/oracle/11gR1/STBY/oradata/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY'

LOG_ARCHIVE_DEST_2='SERVICE=TNS_PROD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=TNS_PROD

DB_FILE_NAME_CONVERT='PROD','STBY'

LOG_FILE_NAME_CONVERT='PROD','STBY'

STANDBY_FILE_MANAGEMENT=AUTO

Note: My directory structure for datafiles and redo logfiles at standby (/u01/CHARAN/oracle/11gR1/STBY/oradata) and primary (/u01/CHARAN/oracle/11gR1/PROD/oradata) are only differed by PROD and STBY strings that is why the FILE_NAME_CONVERT parameters are set with only PROD and STBY strings, if directory structure vary a lot then full paths has to be specified for FILE_NAME_CONVERT parameters .

Step 5 : Configure Oracle Network

cd $ORACLE_HOME/network/admin

vi listener.ora

STBY =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba01.charan.com)(PORT = 1541))

)

)

SID_LIST_STBY =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME= /u01/CHARAN/oracle/11gR1/STBY/orahome)

(SID_NAME = STBY)

)

)

Start the listener : lsnrctl start STBY

vi tnsnames.ora

TNS_STBY=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=dba01.charan.com)(PORT=1541))

(CONNECT_DATA=

(SERVICE_NAME=STBY)

(INSTANCE_NAME=STBY)

)

)

TNS_PROD=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=dba01.charan.com)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=PROD)

(INSTANCE_NAME=PROD)

)

)

tnsping TNS_PROD

tnsping TNS_STBY

Note : TNS entries for both databases are required at standby only if rman command is issued at standby (rman has to be connected through Oracle Net Service names instead of OS Authentication for duplication since oracle transfers backup files only through network and authentication is made through password files)

Login to primary database and tnsping TNS_STBY, as listener is up, this should be resolvable now.

Step 6 : Create spfile from pfile

Since pfile is fully configured at standby site, we will create an spfile and start the instance, by doing so, we can prevent rman creating an auxiliary spfile for standby database and also we need not specify the spfile clause and all relevant parameters in the rman duplicate command.

sqlplus / as sysdba

SQL> create spfile from pfile;

Phase 3 : Start Managed Recovery and Verify

Step 1 : Start the standby database in nomount state

sqlplus / as sysdba

SQL> startup nomount;

Step 2 : Start duplicate for dataguard (

rman target sys/sys@TNS_PROD auxiliary sys/sys@TNS_STBY

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER;

Note:

1. The above command was run from standby database

2. If the standby database is being created on a different server and if the directory structure is same then include nofilenamecheck clause in the duplicate command otherwise RMAN will throw below error in 11.2.0

RMAN-05001: Auxiliary Filename Conflicts with a File Used by the Target Database

In this case the command to be used is

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

3. After duplication you might see below warnings

RMAN-05535: WARNING: All redo log files were not defined properly

These warnings can be ignored and are due to setting of parameter standby_file_management to AUTO

Important Note :

1. The above duplicate command is case insensitive.

2. The dorecover clause recovers the standby database and keeps it ready to be placed in managed recovery mode.

3. After the duplicate command the standby database is placed in mount state

4. RMAN will not place the standby database in managed recovery mode, we have to manually place in recovery mode after successful completion of duplicate command.

Step 3 : Place the standby database in managed recovery mode

Check if standby files are created before using real time apply

select member from v$logfile where type='STANDBY';

Note: An mrp process will be started when standby database is kept in recovery mode

before recovery mode they wont me any mrp process ps -ef|grep mrp

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

ps -ef|grep mpr (mrp0 would be started now)

Important Note :

1. The above duplicate command is case insensitive.

2. The using current logfile clause applies the redo from standby logs as it is being filled.

3. The disconnect from session clause runs the recovery in background returning the SQL> prompt.

That's it!!!

Regards

Charan