Standby Database Creation Oracle 9ir1(9.0.1)

Standby Database Creation

Primary Database: 9ir1(9.0.1.0.1)

Standby Database: 9ir1(9.0.1.0.1)

Platform: windows XP 32bit

Standby & Primary Database Same System/Machine

Primary database name: ORA9I

Standby database name: ORA9ISTD

NOTE: Database Name should not be excced more than 8 character.

TNSENTRY Primary DB: ORA9I

TNSENTRY Standby DB: ora9istd

Location of datafile/controlfile/redofile

Primary site: $ORACLE_HOME/oradata/ORA9I

Standby site: c:\ora9istd

Perform following task at Primary database
1. Enable archivelog mode
2. take primary site database backup for standby
3. create standby controlfile
4. modify init.ora file at primary site for standby db
5. create init.ora file for standby site
6. startup standby database

1. How to enable archivelog mode

http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html

2. Backup

I am using HOT backup through USER MANAGED method

3. Create CONTROLFILE to standby database
SQL> alter database create standby controlfile as 'c:\ora9istd\control01.ctl';
Database altered.

4. COPY database backup and standby controlfile to STANDBY location

5. Modify init.ora file at primary site FOR PRIMARY DB.

standby_file_management=AUTO scope=spfile

STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9i\archive'

log_archive_dest_1='LOCATION=c:\ora9ihome\rdbms'

log_archive_dest_2='SERVICE=ora9istd'

log_archive_dest_state_1=ENABLE

log_archive_dest_state_2=ENABLE

remote_archive_enable=TRUE

NOTE: Restare Oracle server ( shutdown + startup) to take effect of static parameter setting.

6. Modify init.ora file at primary site FOR STANDBY DB.

*.lock_name_space=ORA9I

*.FAL_SERVER=ora9istd

*.FAL_CLIENT=ora9i

7. Create standby init.ora file
use OS copy command to copy init.ora(primary site) and paste at standby site and modify below parameter.

*.STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9istd\archive'
*.background_dump_dest='C:\ora9istd'
*.compatible='9.0.0'
*.control_files='C:\ora9istd\control01.ctl',

*.core_dump_dest='C:\ora9istd'
*.fal_client='ORA9ISTD'
*.fal_server='ORA9I'
*.instance_name='ora9iSTD'
*.lock_name_space='ORA9ISTD'
*.log_archive_dest_1='LOCATION=c:\ora9istd\archive'
*.log_archive_dest_state_1='ENABLE'
*.remote_archive_enable=TRUE
*.standby_archive_dest='LOCATION=c:\ora9istd\archive'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='C:\ora9istd'

8. Create standby services through ORADIM & create password file through ORAPWD and startup the standby database

C:\ora9ihome\BIN>oradim -NEW -SID ora9istd
C:\Ora9ihome\BIN>orapwd file=c:\ora9ihome\database\pwdora9istd.ora password=oracle entries=5

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup NOMOUNT pfile='c:\ora9istd\initora9istd.ora';

ORACLE instance started.
[output cut]

NOTE: STARTUP NOMOUNT is required.

SQL> alter database mount standby database;
Database altered.

10. RENAME DATAFILE OR REDOLOG FILE TO POING STANDBY LOCATION

SQL> alter database rename file

2 'C:\ORA9IHOME\ORADATA\ORA9I\INDX01.DBF',

3 'C:\ORA9IHOME\ORADATA\ORA9I\REDO01.LOG',

4 'C:\ORA9IHOME\ORADATA\ORA9I\REDO02.LOG',

5 'C:\ORA9IHOME\ORADATA\ORA9I\REDO03.LOG',

6 'C:\ORA9IHOME\ORADATA\ORA9I\SYSTEM01.DBF',

7 'C:\ORA9IHOME\ORADATA\ORA9I\TOOLS01.DBF',

8 'C:\ORA9IHOME\ORADATA\ORA9I\UNDOTBS01.DBF',

9 'C:\ORA9IHOME\ORADATA\ORA9I\USERS01.DBF'

10

to

11 'c:\ora9istd\INDX01.DBF',

12 'c:\ora9istd\REDO01.DBF',

13 'c:\ora9istd\REDO02.DBF',

14 'c:\ora9istd\REDO03.DBF',

15 'c:\ora9istd\SYSTEM01.DBF',

16 'c:\ora9istd\TOOLS01.DBF',

17 'c:\ora9istd\UNDOTBS01.DBF',

18 'c:\ora9istd\USERS01.DBF';
Database altered.


After that If required then create STANDBY redolog file then configure TNSNAME.ORA file for primary db will connect to standby database and standby database will connect to primary db.

Source: http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88808/stdbyconfig.htm#46558