Creating a Physical Standby Database

Oracle Database - Enterprise Edition - Version 11.2.0.4.0

Preparing the Primary Database for Standby Database Creation

Enable an Appropriate Logging Mode

• FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner. This can slow down the load process because the loaded data must be copied into the redo logs. FORCE LOGGING mode is enabled using the following command:

  SQL> ALTER DATABASE FORCE LOGGING;

• STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment. It is enabled with the following command:

  SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;

• STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment. It is enabled with the following command:

  SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

Note:When you enable STANDBY NOLOGGING FOR DATA AVAILABILITY or STANDBY NOLOGGING FOR LOAD PERFORMANCE on the primary database, any standbys that are using multi-instance redo apply functionality will stop applying redo with the error ORA-10892. You must first restart redo apply and allow the affected standbys to progress past the NOLOGGING operation period and then enable multi-instance redo apply.

Configure Redo Transport Authentication

Configure the Primary Database to Receive Redo Data

It is a best practice to configure the primary database to receive redo if this is the first time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;

Set Primary Database Initialization Parameters

The configuration examples use the names shown in the following table:

Parameters	Primary	Physical standby
DB_NAME	orcl	orcl
DB_UNIQUE_NAME	chicago	boston
Oracle Net Service Name	chicago	boston

The parameters shown in this example are valid for the Chicago database when it is running in either the primary or the standby database role.

DB_NAME=orcl
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

The following shows the additional standby role initialization parameters on the primary database. These parameters take effect when the primary database is transitioned to the standby role.

FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO