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.

 

Method	Initialization Parameter	Host	Example
1	LOG_ARCHIVE_DEST_n where: n is an integer from 1 to 10	Local or remote	LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'
2	LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST          (a second location for the local archivelog destination)	Local only	LOG_ARCHIVE_DEST = '/disk1/arc' LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc'

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

SQL> startup;     (connected as sys)

Now we can connect and read the data in the standby database. However no DML or DDL are allowed.

9. Start the MRP (Managed Recovery Process)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Now the redo logs are applied. However no connections to the standby database are allowed. This command bring the database in mount state automatically (if is not already).

Now alter database OPEN READ ONLY; is not allowed. 

To open the database in READ ONLY (in READ WRITE is not possible if the database is a standby DB) we have to stop the MRP process: 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

10. Adding a temp file to the TEMP tablespace

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\DATA\TEMP01.TMP'
SIZE 200M AUTOEXTEND ON MAXSIZE 400M;