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 PFILE.

    *.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.ora file.
    proddb.__db_cache_size=1375731712
    proddb.__java_pool_size=16777216
    proddb.__large_pool_size=33554432
    proddb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    proddb.__pga_aggregate_target=620756992
    proddb.__sga_target=1845493760
    proddb.__shared_io_pool_size=0
    proddb.__shared_pool_size=402653184
    proddb.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.4.0'
    *.control_files='/u01/app/oracle/oradata/sproddb/control01.ctl','/u01/app/oracle/oradata/sproddb/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='proddb'
    *.db_unique_name='proddb_standby'
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP)(SERVICE=proddbXDB)'
    *.fal_client='proddb_standby'
    *.fal_server='proddb_standby'
    *.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_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'
    *.log_archive_format='%t_%s_%r.dbf'
    *.open_cursors=300
    *.pga_aggregate_target=612368384
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=1837105152
    *.undo_tablespace='UNDOTBS1'
  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.ora file.
    standbydb.__db_cache_size=1375731712
     standbydb.__java_pool_size=16777216
     standbydb.__large_pool_size=33554432
     standbydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
     standbydb.__pga_aggregate_target=620756992
     standbydb.__sga_target=1845493760
     standbydb.__shared_io_pool_size=0
     standbydb.__shared_pool_size=402653184
     standbydb.__streams_pool_size=0
     *.audit_file_dest='/u01/app/oracle/admin/standbydb/adump'
     *.audit_trail='db'
     *.compatible='11.2.0.4.0'
     *.control_files='/u01/app/oracle/oradata/standbydb/control01.ctl','/u01/app/oracle/oradata/standbydb/control02.ctl'
     *.db_block_size=8192
     *.db_domain=''
     *.db_name='proddb'
     *.db_unique_name='proddb_standby'
     *.diagnostic_dest='/u01/app/oracle'
     *.dispatchers='(PROTOCOL=TCP)(SERVICE=proddbXDB)'
     *.fal_client='proddb_standby'
     *.fal_server='proddb_standby'
     *.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_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'
     *.log_archive_format='%t_%s_%r.dbf'
     *.open_cursors=300
     *.pga_aggregate_target=612368384
     *.processes=150
     *.remote_login_passwordfile='EXCLUSIVE'
     *.sga_target=1837105152
     *.undo_tablespace='UNDOTBS1'
  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 to the auxiliary instance. Create a password file.

    UNIX

    On the command line, go to 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 to 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;

Loading...