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

Create an Oracle online subclient.
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).
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

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'

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'

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>'
```

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>'

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
```
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"
```

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) ) )

On the standby database instance host command line, type the command to restart the listener.
```
$lsnrctl reload
```
On the standby database instance host command line, type the command to put the auxiliary database instance in NOMOUNT mode.
```
sql> startup nomount;
```