3 Creating a Physical Standby Database

3.1.1 Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

3.1.2 Create a Password File

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. See Oracle Database Administrator's Guide.

3.1.3 Configure a Standby Redo Log

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.

Perform the following steps to configure the standby redo log.

Step 1 Ensure log file sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.

Step 2 Determine the appropriate number of standby redo log file groups.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.

Step 3 Verify related database parameters and settings.

Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.

See Oracle Database SQL Reference and your operating system specific Oracle documentation for the default and legal values of the MAXLOGFILES and MAXLOGMEMBERS clauses.

Step 4 Create standby redo log file groups.

To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database. Example 3-1 and Example 3-2 show how to create a new standby redo log file group using the ALTER DATABASE statement with variations of the ADD STANDBY LOGFILE GROUP clause.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
  2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
  2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Step 5 Verify the standby redo log file groups were created.

To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created. For example:

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP#     THREAD#    SEQUENCE#  ARC STATUS     
---------- ---------- ---------- --- ----------
         3          1         16 NO  ACTIVE    
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED

3.1.4 Set Primary Database Initialization Parameters

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

Example 3-3 shows the primary role initialization parameters that you maintain on the primary database. This example represents a Data Guard configuration with a primary database located in Chicago and one physical standby database located in Boston. The parameters shown in Example 3-3 are valid for the Chicago database when it is running in either the primary or the standby database role. The configuration examples use the names shown in the following table:

DB_NAME=chicago
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=/arch1/chicago/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' 
STANDBY_FILE_MANAGEMENT=AUTO

3.1.5 Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

See Oracle Database Administrator's Guide for information about archiving.

3.2.1 Create a Backup Copy of the Primary Database Datafiles

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).

See Oracle High Availability Architecture and Best Practices for backup recommendations and Oracle Database Backup and Recovery Advanced User's Guide to perform an RMAN backup operation.

3.2.2 Create a Control File for the Standby Database

If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:

SQL> STARTUP MOUNT;

Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
SQL> ALTER DATABASE OPEN;

3.2.3 Prepare an Initialization Parameter File for the Standby Database

Perform the following steps to create a standby initialization parameter file.

Step 1 Copy the primary database parameter file to the standby database.

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:

SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Later, in Section 3.2.5, you will convert this file back to a server parameter file after it is modified to contain the parameter values appropriate for use with the physical standby database.

Step 2 Set initialization parameters on the physical standby database.

Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.

Example 3-5 shows the portion of the standby initialization parameter file where values were modified for the physical standby database. Parameter values that are different from Example 3-3 and Example 3-4 are shown in bold typeface. The parameters shown in Example 3-5 are valid for the Boston database when it is running in either the primary or the standby database role.

.
.
.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT=
 '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/boston/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=chicago LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston
.
.
.

3.2.4 Copy Files from the Primary System to the Standby System

Use an operating system copy utility to copy the following binary files from the primary system to the standby system:

• Backup datafiles created in Section 3.2.1

• Standby control file created in Section 3.2.2

• Initialization parameter file created in Section 3.2.3

3.2.5 Set Up the Environment to Support the Standby Database

Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.

Step 1 Create a Windows-based service.

If the standby system is running on a Windows-based system, use the ORADIM utility to create a Windows Service and password file. For example:

WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual

See Oracle Database Platform Guide for Microsoft Windows (32-Bit) for more information about using the ORADIM utility.

Step 2 Create a password file.

On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.

Step 3 Configure listeners for the primary and standby databases.

On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.

To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems:

% lsnrctl stop
% lsnrctl start

See Oracle Database Net Services Administrator's Guide.

Step 4 Create Oracle Net service names.

On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by redo transport services.

The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and service that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used.

See the Oracle Database Net Services Administrator's Guide and the Oracle Database Administrator's Guide.

Step 5 Create a server parameter file for the standby database.

On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Step 2. For example:

SQL> CREATE SPFILE FROM PFILE='initboston.ora';

3.2.6 Start the Physical Standby Database

Perform the following steps to start the physical standby database and Redo Apply.

Step 1 Start the physical standby database.

On the standby database, issue the following SQL statement to start and mount the database:

SQL> STARTUP MOUNT;

Step 2 Start Redo Apply.

On the standby database, issue the following command to start Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session. See Section 6.3, "Applying Redo Data to Physical Standby Databases" for more information.

Step 3 Test archival operations to the physical standby database.

In this example, the transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example:

SQL> ALTER SYSTEM SWITCH LOGFILE;

3.2.7 Verify the Physical Standby Database Is Performing Properly

Once you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database.

To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.

Step 1 Identify the existing archived redo log files.

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

3 rows selected.

Step 2 Force a log switch to archive the current online redo log file.

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.

On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
        11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
4 rows selected.

The archived redo log files are now available to be applied to the physical standby database.

Step 4 Verify new archived redo log files were applied.

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
  2  ORDER BY SEQUENCE#;

SEQUENCE# APP
--------- ---
        8 YES
        9 YES
       10 YES
       11 YES

4 rows selected.

See Section 5.9.1, "Monitoring Log File Archival Information" and Section 8.5.4, "Monitoring Log Apply Services on Physical Standby Databases" to verify redo transport services and log apply services are working correctly.