Standby Database

Primary Database Setup
Standby Database Setup
Standby Recovery
Managed Standby Recovery
Read Only Database
Standby Database Backup

Primary Database Setup

Shutdown the database using: SHUTDOWN IMMEDIATE
Backup all database files.
Add an entry for the standby server into the tnsnames.ora file:

STBY=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = STBY.world)
    )
  )

Assuming your database in already in ARCHIVELOG mode the first four of the following init.ora paramters will already be set. Add the last two entires:

log_archive_start = true
log_archive_dest_1 = "location=c:\Oracle\oradata\SID\archive\ mandatory reopen=5"
log_archive_dest_state_1 = enable
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

log_archive_dest_2 = "service=STBY optional reopen=5"
log_archive_dest_state_2 = enable

Startup the database using: STARTUP PFILE=C:\Oracle\Admin\SID\PFile\init.ora
Create standby database controlfile using: ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f';

Standby Database Setup

Copy the production backup files to the standby server.
Copy the standby controlfile to the standby server.
Alter the control_files and archive parameters of the init.ora as follows:

#use the standby service name
service_names = STBY.world

#reference the standby controlfile
control_files = ("c:\Oracle\oradata\SID\stbycf.f")

#switch archiving and reference archive directory
log_archive_start = false
log_archive_dest = c:\Oracle\oradata\SID\archive\
standby_archive_dest = c:\Oracle\oradata\SID\archive\
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

Add the following entries into the listener.ora file:

(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ntfm451)(PORT = 1512))
)

STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)

The file should resemble the following:

# LISTENER.ORA Network Configuration File: C:\Oracle\Ora817\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
      )
    )
    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 2481))
    )
  )

STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\Oracle\Ora817)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = C:\Oracle\Ora817)
      (SID_NAME = PGOAL1)
    )
  )

Reload the listener file using lsnrctl reload from the command prompt.
Add the following entry into the tnsnames.ora file:

STBY=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = STBY.world)
    )
  )

At this point the recovery process can start.

Standby Recovery

Regular recovery requires archive logs to be copied manually between the server and user initiation of the recovery process.

Copy all archive logs from the primary to the standby server.
From sqlplus do the following:

SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\SID\PFile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER STANDBY DATABASE;

This process must be repeated every time archive logs are manually transfered.

Managed Standby Recovery

During managed recovery the transfer of archivelogs is controlled by the servers without user intervention.

Copy all archive logs from the primary to the standby server. This is the only time you should need to do this.
From sqlplus do the following:

SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\SID\PFile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;

This window will then hang indefinitely while it continues to look for archive logs to apply. To stop the recovery open another sqlplus session and type:

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;

Read Only Database

The standby database can be opened in read only mode at any point. While open in this mode new archive logs are not applied to the database.

SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;

The database can subsequently be switched back to recovery mode as follows:

SQL> CONNECT sys/password AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\SID\PFile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;

Backup Standby Database

Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simple copy the appropriate database files.

For more information see:

Oracle8i Standby Database Concepts and Administration

Hope this helps. Regards Tim...

Back to the Top.