Loading...

Setting Up Instances for Oracle Standby Databases

Set up a standby instance for the Oracle database as a copy of the primary (production) database. You use a standby database for full system recovery (disaster recovery) and data corruption protection.

Before You Begin

  1. Create an Oracle online subclient.
  2. Perform an online full backup of the primary database ( Advanced Backup Options dialog box, Oracle Options tab, Backup Control File for Creating Standby Database option).
  3. Install the Oracle Agent on the destination host.

Procedure

The following parameters are examples and used in the steps. You can change the values to match your configuration:

Description

Production Parameter

Standby Parameter

Server Name

prodserver

standbyserver

Database Name

proddb

proddb

Database Unique Name

proddb

standbydb

TNS Host String

proddb

standbydb

  1. On the primary server, add the following entries to the init<SID>.ora initialization parameter file or PFI

    *.db_unique_name='proddb_prim'
    *.fal_client='proddb_prim'
    *.fal_server='proddb_standby'
    *.log_archive_config='DG_CONFIG=(proddb_prim,proddb_standby)'
    *.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/proddb_prim VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb_prim'
    *.log_archive_dest_2='SERVICE=proddb_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb_standby'
    *.log_archive_dest_state_1='enable'
    *.log_archive_dest_state_2='enable'

    The following is an example of a complete primary database init<SID>.ora file.

  2. On the standby server, add the following entries to the init<SID>.ora initialization parameter file or PFILE.

    *.db_unique_name='proddb_standby'
    *.fal_client='proddb_standby'
    *.fal_server=''proddb_prim'
    *.log_archive_config='DG_CONFIG=(proddb_prim,proddb_standby)'
    *.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/proddb_standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb_standby'
    *.log_archive_dest_2='SERVICE=proddb_prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb_standby'
    *.log_archive_dest_state_1='enable'
    *.log_archive_dest_state_2='enable'

    The following is an example of a complete standby database init<SID>.ora file.

  3. Add all the temp datafiles from the primary database location to the standby database location. Set the standby role initialization parameter, DB_FILE_NAME_CONVERT.

    DB_FILE_NAME_CONVERT=
    '<primary_database_temp_old _location>',
    '<standby_database_temp_new_location>'

  4. Add the following entry to the init.ora initialization parameter file of both hosts.

    *.LOG_FILE_NAME_CONVERT='<primary_database_logfile_old _location>', '<standby_database_logfile_new_location>'

  5. Optional: To use the TNS connect string instead of "/" to connect the auxiliary instance. Create a password file.

    UNIX

    On the command line, go the  ORACLE_HOME/dbs directory, and then type the following command:

    orapwd file=orapw<SID> password= <password for sys user>
    entries=<Number of user accounts to permit in the file>

    Example

    The auxiliary database name is dupdb.

    orapwd file=orapwdupdb password= <password for sys user> entries=100

    Windows

    On the command line, go the ORACLE_HOME\database directory, and then type the following command:

    orapwd file=PWD<SID> password= <password for sys user>
    entries=<Number of user accounts to permit in the file>

    Example

    The auxiliary database name is testdb1.

    orapwd file= PWDtestdb1 password= <password for sys user > entries=100

  6. For Windows configurations, create a new Windows service on the auxiliary database host by using the oradim command.

    In the example, the PFILE is "C:\app\admin\product\11.2.0\dbhome_1\database\INIThostdb.ORA".

    C:> oradim -NEW -SID hostdb -STARTMODE manual -PFILE "C:\app\admin\product\11.2.0\dbhome_1\database\INIThostdb.ORA"

  7. Add the following TNS entries on both the production and standby hosts.

    On the Production Host

    proddb_prim =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver)(PORT = 1521))
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SID = proddb)
           (UR = A) ) )

    On the Standby Host

    prod_standby =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver)(PORT = 1521))
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SID = proddb)
           (UR = A) ) )

  8. On the standby database instance host command line, type the command to restart the listener.

    $lsnrctl reload

  9. On the standby database instance host command line, type the command to put the auxiliary database instance in NOMOUNT mode.

    sql> startup nomount;

Last modified: 2/28/2018 12:28:45 AM