Scribd
Upload
4K views4 pages

How To Create A Physical Standby Database in ORACLE 9i

This document provides steps to create a physical standby database in Oracle 9i. It outlines 6 key steps: 1) Configure initialization parameters in the primary database to archive logs, 2) Add the standby database tns entry to the primary, 3) Configure standby-specific initialization parameters, 4) Configure tns and listener on the standby, 5) Switch logs and copy files from primary to standby while tablespaces are in backup mode, and 6) Start up the standby database and recover it using archived redo logs. It also provides notes on creating a standby on the same server and checking applied archive logs.

Uploaded by

Rahul Gupta
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
4K views4 pages

How To Create A Physical Standby Database in ORACLE 9i

This document provides steps to create a physical standby database in Oracle 9i. It outlines 6 key steps: 1) Configure initialization parameters in the primary database to archive logs, 2) Add the standby database tns entry to the primary, 3) Configure standby-specific initialization parameters, 4) Configure tns and listener on the standby, 5) Switch logs and copy files from primary to standby while tablespaces are in backup mode, and 6) Start up the standby database and recover it using archived redo logs. It also provides notes on creating a standby on the same server and checking applied archive logs.

Uploaded by

Rahul Gupta
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 4

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 (You can use this parameter for the automatic switching of archive log in primary. Its value differ from
0-7200 seconds)

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'
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 parameter “instance_name” in init.ora of Primary and Standby , giving suitable
value

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 ecengineer84@gmail.com

You might also like