Home  >  Oracle Database Administration (DBA)  >  Replication  >  Creating a Physical Standby Database (10g)

1. Enable Forced Logging (Primary Database)

SQL> ALTER DATABASE FORCE LOGGING;

2. Enable Archiving and Define a Local Archiving Destination (Primary database)

To learn how to put a database in archive log click here.

One LOG_ARCHIVE_DEST_n must be set to send the archive logs using Oracle Net to the StandBy database (ex: LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'). In this case standby1 must point to a standby database (standby1 = alias in tnsnames.ora). 

Set LOG_ARCHIVE_FORMAT (not mandatory):

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

t = thread

s = sequence

r = resetlog

3. Identify the Primary Database Datafiles

SQL> SELECT NAME FROM V$DATAFILE;

NOTE: The log file are created on the standby database when  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT will run for the first time. 

4. Create a Control File for the Standby Database (On the Primary database)

SQL> startup mount;        (the data files and the newly created standby control file must have the same SCN )

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/oracle/oradata/sb_controlfile.ctl';

SQL> shutdown;

NOTES:

1)  The sb_controlfile.ctl file is a binary file which is used to start the standby database;

2)  The control file says if the database is in primary or standby mode;

3)  If the database is in standby mode, the logs can be received (MRP - Managed Recovery Process apply the logs;

     this process could be started or not).

5. Copy data files to the destination database

Copy the data files, password file and sb_control.ctl file to the standby database server. sb_control.ctl file must be copied and renamed to have sb_control01.ctl ... sb_control02.ctl at the correct location.

NOTE: The log files and the temp files are not copied. The log file are created on the standby database when  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT will run for the first time. The TEMP tablespace will be created without a temp file. This file must be added one time the database will be open (in a physical standby mode will be OPEN READ ONLY always).

6. Modify init.ora (the pfile) on the standby database

If the control files, udump, bdump directories, etc are different on the standby database, the pfile must be modified.  

7. Startup the database in mount state (optional)

The database could be open in mount state to specify the correct location of the data files if the location on the standby database is different from the location on the primary database;

8. Start the database in READ ONLY mode

9. Start the MRP (Managed Recovery Process)

10. Add a temp file to the TEMP tablespace

More information about  this subject ( Creating a Physical Standby Database (10g) ) you can get from  www.in-oracle.com