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 database1. Enable archivelog mode2. take primary site database backup for standby3. create standby controlfile4. modify init.ora file at primary site for standby db5. create init.ora file for standby site6. startup standby database
1. How to enable archivelog mode
1- Shutdown immediate
2- Startup mount
3- Alter database archivelog
4- Alter database open
5- Take Backup of Database
2. Backup
I am using HOT backup through USER MANAGED method
3. Create CONTROLFILE to standby databaseSQL> 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 fileuse 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 ora9istdC:\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
