How To Create A Physical Standby Database In Oracle 9i

How To create a Physical Standby database in ORACLE 9i By : RAHUL GUPTA Client Site : LG Electronics, Greater Noida Company : Path InfoTech Limited

Step 1 : Parameter that needed to be entered in the init<SID>.ora of the primary database log_archive_dest_1 = 'LOCATION=/ms11/arch/m3 MANDATORY' log_archive_dest_state_1 = 'enable' log_archive_start = true log_archive_dest_2 = 'SERVICE=STANDBY','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=60 register’ OR ( You can put the whole tns entry of STANDBY database) log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.180)(PORT=1530)))(CONNECT_DATA=(SID=m3)(SERVER=DEDIC ATED)))"','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=60 register ' standby_file_management=auto #*.archive_lag_target = 1800 0-7200 seconds)

(You can use this parameter for the automatic switching of archive log in primary.

log_archive_dest_state_2='enable' #log_archive_dest=/home/oracle/proddata/arch

(Comment this parameter on you primary database)

Step 2 : Enter the tns of standby database in the tnsnames.ora of primary STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.180)(PORT = 1530)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) )

Step 3 : Parameter that needed to be entered in the init<SID>.ora of the standby database fal_client = 'PROD_stdby' fal_server = 'PROD_prim' standby_file_management = AUTO control_files=/home/users/PROD/m3stndby.ctl standby_archive_dest = '/ms11/archm3/m3'

Its value differ from

log_archive_dest = /ms11/archm3/m3 log_archive_start = true db_file_name_convert = ('/u06/','/u06e/','/u07/','/u07e/') -> Use this if your directory structure of standby database is different from that of primary database log_file_name_convert = ('/u06/','/u06e/','/u07/','/u07e/')-> Use this if your directory structure of standby database is different from that of primary database

Step 4 : (a). Enter the following in tnsnames.ora of Standby PROD_prim = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.245)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) ) PROD_stdby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.180)(PORT = 1530)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) )

(b). Enter the following in listner.ora of Standby PROD_stdby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.180)(PORT = 1530)) ) SID_LIST_PROD_stdby = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD) (ORACLE_HOME = /ms10/oracle/9.2.0) (SID_NAME = PROD ) ) )

Step 5 : (a) . Switch one archive on the primary alter system switch logfile; (b). Now take the tablespace of primary in backup mode [ If you want to create the database using hot backup ] select ' alter tablespace '||tablespace_name||' begin backup ;' from dba_tablespaces where CONTENTS <> 'TEMPORARY';

alter tablespace esvc begin backup ; alter tablespace samora begin backup; . . . . . .. . . . . . .. . . . . . Run the commands given the above query . It’ll bring all the tablespace in backup mode . Now before copying the files to the standby database . Check whether all files are in mode or not .

select status,count(*) from v$backup group by status; STATUS COUNT(*) ----------------- ---------ACTIVE 29

(c) . Now copy all the files from production database to standby database (d) Now take the tablespace out of begin backup mode .. select ' alter tablespace '||tablespace_name||' end backup ;' from dba_tablespaces where CONTENTS <> 'TEMPORARY'; alter tablespace esvc end backup ; alter tablespace samora end backup;

. . .... ....… . . .... ....… Run the commands given by the above query . It’ll take all the tablespace out of backup mode . Now check , whether all the files are out of backup mode or not . select status,count(*) from v$backup group by status; STATUS COUNT(*) ----------------- ---------NOT ACTIVE

29

(d) Once all the files from primary are copied to standby . Then create the standby controlfile as given below : alter database create standby controlfile as '/home/users/PROD/m3stndby.ctl'; Then , copy this file to the location that you have mentioned in your Standby init.ora

Step 6: Now up the Standby database as follows (a ) .

Up the standby database in nomount state .. startup nomount pfile=’u01/file/initPROD.ora’

(b)

Mount the standby database alter database mount standby database ;

-----------------------------------------------------------------------------Note : Now , your standby database is in mount state . And for the standby to Be in automatic recovery mode . You need to restart your PRIMARY database . So That the changes that you’ve made in the initPROD.ora of primary will come into Efftect . -------------------------------------------------------------------------------

(c) C.1 # If you have restarted your primary database . Then take the standby database in managed recover mode alter database recover managed standby database disconnect from session ; C.2 # If you havn’t restarted your primary database . Then manually transfer the archive from primary to archive location of standby database . And run the command given below . recover standby database; Here , choose the “AUTO” option . It’ll apply all the archives .

===========================================================================

Note 1 : Creating standby database on the same server as that of primary In this case a parameter “LOCK_NAME_SPACE” need to be used . If the standby database resides on the same file system as the primary database, set LOCK_NAME_SPACE in the standby initialization parameter file to a distinct value such as the following: LOCK_NAME_SPACE = standby And put the value

parameter “instance_name” in init.ora of Primary and Standby , giving suitable

Note 2 : How to check the max archive log number that has been applied to the standby ..

select max(sequence#) from v$archived_log where applied=’YES’

####################################################################################################### @ Thanks for reading this doc. Any suggestion on this or any changes ( or more information that you want to add in this doc ) will be highly appreciated and welcomed . You can mail that to me on my id [email protected]