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;