Creating a Physical Standby

 < Day Day Up > 

In this section we will perform our first Data Guard HA Workshop, which outlines the procedure to create a physical standby. If we strip it down to the bare essentials, we see exactly how easy it is: create a backup of the primary, create a standby controlfile, transfer the files to the standby host, and mount the standby. Although it is really that simple, we will go into great detail so that we can make the right choices up front and thus save ourselves heartache later.

HA Workshop: Creating a Physical Standby

start example

Workshop Notes

The following names will be used when building our configuration:


Database Type


TNS Alias






Physical Standby



Properly configuring the primary database prior to creating the standby will ensure easier maintance and will smooth the road to later role transitions. While some steps are not mandatory, we will explain the benefits to illustrate how those choices can result in a more sound disaster recovery solution. Also, as several steps involve bouncing the primary, you should attempt to consolidate those steps to reduce the number of bounces.

Step 1.  Enable archiving.

As Data Guard is dependant on redo to maintain the standby, we must assure that the primary database is in archivelog mode. To place the primary into archivelog, perform the following steps:

shutdown immediate; startup mount; alter database archivelog; alter database open;

The LOG_ARCHIVE_START parameter is no longer necessary in Oracle Database 10g. Automatic archiving is enabled by default when the database is placed into archivelog mode.

Step 2.  Create a password file.

Due to new log transport security and authentication features, it is mandatory that every database in a Data Guard configuration utilize a password file. In addition, the password for the sys user must be identical on every system for log transport services to function. If the primary database does not currently have a password file, create one with the following steps:

$cd $ORACLE_HOME/dbs $orapwd file=orapwOrlando password=Not4U

Once the password file is created, you must set the following parameter in the spfile while the database is in the nomount state:

alter system set remote_login_passwordfile=exclusive scope=spfile;

Step 3.  Enable force logging (optional).

Any nologging operations performed on the primary are not fully logged within the redo stream. As Data Guard depends on the redo stream to maintain the standby, this can cause a lot of additional work for the DBA. In addition, it is difficult to know when nologging operations are occurring. One solution is to place the primary database into force logging mode. In this mode, nologging operations are permitted to run, but the changes are placed into the redo stream anyway. This assures that a sound disaster recovery solution is maintained.

To place the primary database in forced logging mode, enter the following as sys:

alter database force logging;

Step 4.  Create standby redo logs (optional).

Certain protection modes, such as maximum protection and maximum availability, mandate that standby redo logs be present. Standby redo logs are highly recommended regardless of your protection mode, as generally more data can be recovered during a failover than without them. A best practice is to create the standby redo logs on both the primary and the standby so as to make role transitions smoother.

By creating standby redo logs at this stage, we can assure that they exist on both the primary and newly created standby. When creating the standby redo log groups, we should have one more standby redo log file group than the number of online redo log file groups on the primary database. In addition, the size of the standby redo logs must match exactly with the size of online redo logs.

The following syntax is used to create the standby redo logs:

alter database add standby logfile thread 1 ('/database/10gDR/srl1a.dbf') SIZE 500M;

Step 5.  Configure the primary initialization parameters.

When configuring the initialization parameters on the primary database, it is important to consider future role transitions. We must configure the parameters to control log transport services and log apply services so that the database will seamlessly operate in either role with no parameter modification. While the database is mounted on a primary controlfile, the standby parameters are not read and are not put into effect, so they will not affect the operation of the database while in the primary role.

The parameters shown here are to be placed into the primary init.ora:

## Primary Role Parameters ## DB_UNIQUE_NAME=Orlando SERVICE_NAMES=Orlando LOG_ARCHIVE_CONFIG='DG_CONFIG=(Orlando,Nashville)' LOG_ARCHIVE_DEST_1=  'LOCATION=/database/10gDR/Orlando/arch/    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=Orlando' LOG_ARCHIVE_DEST_2=  'SERVICE=Nashville_hasun1    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=Nashville' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=DEFER ## Standby Role Parameters ## DB_FILE_NAME_CONVERT=  ('/database/10gDR/Orlando/','/database/10gDR/Nashville/') LOG_FILE_NAME_CONVERT=  ('/database/10gDR/Orlando/','/database/10gDR/Nashville/')  STANDBY_FILE_MANAGEMENT=AUTO FAL_SERVER=Nashville_hasun1 FAL_CLIENT=Orlando_hasun1 


We initially defer LOG_ARCHIVE_DEST_2 until the standby has been created and brought to the mount state. Also note that the DB_FILE_NAME_CONVERT and the LOG_FILE_NAME_CONVERT parameters are only needed if the paths are not identical between the primary and standby host.

Step 6.  Create a backup of the primary database.

A physical standby can be created using either a hot or cold backup as long as all of the necessary archivelogs are available to bring the database to a consistent state. See Chapter 8 for information on using RMAN to back up the primary database.

Step 7.  Create a standby controlfile.

With the primary database in either a mount or open state, create a standby controlfile with the following syntax:

alter database create standby controlfile as '/database/10gDR/backup/control_Nashville.ctl';

You can also use RMAN to create a standby controlfile:

rman> backup current controlfile for standby;

Step 8.  Create an initialization parameter file for the standby.

If your primary database is using an spfile, you will need to create a pfile for use by the standby. Enter the following command on the primary database:

create pfile= '/database/10gDR/backup/initNashville.ora' from spfile;

Once you have a pfile created for your standby, you will need to adjust several parameters. Below are parameters that needed to be modified in our configuration:

control_files=("/database/10gDR/data/control_Nashville.ctl") DB_UNIQUE_NAME=Nashville SERVICE_NAMES=Nashville LOG_ARCHIVE_CONFIG='DG_CONFIG=(Orlando,Nashville)' LOG_ARCHIVE_DEST_1=  'LOCATION=/database/10gDR/Orlando/arch/    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=Nashville' LOG_ARCHIVE_DEST_2=  'SERVICE=Orlando_hasun1    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=Orlando' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE ## Standby Role Parameters ## DB_FILE_NAME_CONVERT=  ('/database/10gDR/Nashville/','/database/10gDR/Orlando/') LOG_FILE_NAME_CONVERT=  ('/database/10gDR/Nashville/','/database/10gDR/Orlando/')  STANDBY_FILE_MANAGEMENT=AUTO FAL_SERVER=Orlando_hasun1 FAL_CLIENT=Nashville_hasun1

Please note that other parameters, such as dump destinations, may need to be modified depending on your environment.

Step 9.  Transfer files to the standby host.

Using an operating system utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host. If you are using RMAN, you can use the ‘duplicate... for standby' operation to create a physical standby database with RMAN backups. See Chapter 8 for more information on RMAN and Data Guard.

Step 10.  Prepare the standby host.

If you are on the Windows operating system, we must create a service for the standby database on the standby host. To create the service, perform the following command:

oradim -NEW -SID Nashville -INTPWD Not4U -STARTMODE manual

If you are running in a Unix environment, you should create the appropriate environment variables.

Step 11.  Create the standby password file.

Due to new log transport security and authentication features, we must create a password file for the standby database, and its password for the sys user must be the same as specified for the primary database. To create the standby password file, enter the following commands:

$cd $ORACLE_HOME/dbs $orapwd file=orapwNashville password=Not4U 

Step 12.  Configure Oracle Net components.

Data Guard relies on Oracle Net as the transport mechanism to get changes made on the primary to the standby. We must configure Oracle Net listeners and Oracle Net aliases on both the primary and standby host in order to support Data Guards communication needs. Specifically we should configure the following:

  • A listener running on the primary host

  • A listener running on the standby host

  • An Oracle Net alias on the primary that points to the standby listener

  • An Oracle Net alias on the standby that points to the primary listener

Step 13.  Create an spfile for the standby instance.

If so desired, convert the initialization parameter file copied from the primary into an spfile by entering the following command on the standby instance:

create spfile from pfile;

The above command assumes that you placed the initialization file copied from the primary into the $ORACLE_HOME/dbs directory.

Step 14.  Start the standby database.

Finally, we are ready to start the standby database. To do so, enter the following command SQL*Plus prompt:

startup mount

Those that are used to mounting a standby prior to Oracle Database 10g will be asking 'Don't I have to use the MOUNT STANDBY command?' Starting in Oracle Database 10g, when we mount a database we perform a check to determine if the controlfile is a primary or standby controlfile. If the controlfile is found to be a standby controlfile, the database is mounted as a standby automatically.

Step 15.  Begin shipping redo to the standby database.

If you remember, earlier we deferred LOG_ARCHIVE_DEST_2 on the primary database until we had the standby mounted. Now it is time to enable that destination and begin shipping redo to the standby. On the primary database, enter the following command:

alter system set log_archive_dest_state_2=enable scope=both; 

Next, perform a log switch on the primary and verify that the transmission of that log was successful:

alter system switch logfile; select status,error from v$archive_dest where dest_id=2;

If the transmission was successful, the status of the destination should be valid. If the status is invalid, investigate the error listed in the error column to correct any issues.

In the 'Managing a Physical Standby' section, we will cover starting managed recovery in depth. However, if you just can't help yourself, the following statement will instruct the standby to begin applying changes from archived redo logs:

alter database recover managed standby database disconnect;
end example

 < Day Day Up > 

Oracle Database 10g. High Availablity with RAC Flashback & Data Guard
Oracle Database 10g. High Availablity with RAC Flashback & Data Guard
ISBN: 71752080
Year: 2003
Pages: 134 © 2008-2017.
If you may any questions please contact us: