Creating a Physical Standby Database

 Preparing the Primary Database for Standby Database Creation

Before you create a standby database you must first ensure that the primary

database is properly configured.

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 may take a considerable amount of time to complete, because it

waits for all unlogged direct write I/O operations to finish.

Enable Archiving and Define a Local Archiving Destination

Ensure that the primary database is in ARCHIVELOG mode, that automatic

archiving is enabled, and that you have defined a local archiving destination.

Set the local archive destination using the following SQL statement:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll

2> MANDATORY’ SCOPE=BOTH;

Creating a Physical Standby Database

Identify the Primary Database Datafiles

On the primary database, query the V$DATAFILE view to list the files that will be

used to create the physical standby database, as follows:

SQL> SELECT NAME FROM V$DATAFILE;

NAME

----------------------------------------------------------------------------

/disk1/oracle/oradata/payroll/system01.dbf

/disk1/oracle/oradata/payroll/undotbs01.dbf

/disk1/oracle/oradata/payroll/cwmlite01.dbf

.

.

.

Make a Copy of the Primary Database

On the primary database, perform the following steps to make a closed backup

copy of the primary database.

Step 1 Shut down the primary database.

Issue the following SQL*Plus statement to shut down the primary database:

SQL> SHUTDOWN IMMEDIATE;

Step 2 Copy the datafiles to a temporary location.

Copy the datafiles that you identified in v$datafile to a temporary location using

an operating system utility copy command. The following example uses the UNIX

cp command:

cp /disk1/oracle/oradata/payroll/system01.dbf

/disk1/oracle/oradata/payroll/standby/system01.dbf

Copying the datafiles to a temporary location will reduce the amount of time that

the primary database must remain shut down.

Step 3 Restart the primary database.

Issue the following SQL*Plus statement to restart the primary database:

SQL> STARTUP;

Create a Control File for the Standby Database

On the primary database, create the control file for the standby database, as shown

in the following example:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

2> '/disk1/oracle/oradata/payroll/standby/payroll2.ctl';

Note: You cannot use a single control file for both the primary and

standby databases.

The filename for the newly created standby control file must be different from the

filename of the current control file of the primary database. The control file must

also be created after the last time stamp for the backup datafiles.

Prepare the Initialization Parameter File to be Copied to the Standby Database

Create a traditional text initialization parameter file from the server parameter file

used by the primary database; a traditional text initialization parameter file can be

copied to the standby location and modified. For example:

SQL> CREATE PFILE=’/disk1/oracle/dbs/initpayroll2.ora’ FROM SPFILE;

Later, 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.

Copy Files from the Primary System to the Standby System

On the primary system, use an operating system copy utility to copy the following

binary files from the primary system to the standby system:

 Backup datafiles 

 Standby control file 

 Initialization parameter 

Set Initialization Parameters on a 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.

Modifying Initialization Parameters for a Physical Standby Database

.

.

.

db_name=PAYROLL

compatible=9.2.0.1.0

control_files=’/disk1/oracle/oradata/payroll/standby/payroll2.ctl’

log_archive_start=TRUE

standby_archive_dest=’/disk1/oracle/oradata/payroll/standby’

db_file_name_convert=(’/disk1/oracle/oradata/payroll/’,

’/disk1/oracle/oradata/payroll/standby/’)

log_file_name_convert=(’/disk1/oracle/oradata/payroll/’,

’/disk1/oracle/oradata/payroll/standby/’)

log_archive_format=log%d_%t_%s.arc

log_archive_dest_1=(’LOCATION=/disk1/oracle/oradata/payroll/standby/’)

standby_file_management=AUTO

remote_archive_enable=TRUE

instance_name=PAYROLL2

# The following parameter is required only if the primary and standby databases

# are located on the same system.

lock_name_space=PAYROLL2

.

.

.

Caution: Review the initialization parameter file for additional

parameters that may need to be modified. For example, you may

need to modify the dump destination parameters (background_

dump_dest, core_dump_dest, user_dump_dest) if the

directory location on the standby database is different from those

specified on the primary database. In addition, you may have to

create some directories on the standby system if they do not

already exist.

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. If you plan to manage the configuration using

the Data Guard broker, you must configure the listener to use the TCP/IP protocol

and statically register service information for each database using the SID for the

database instance.

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

Enable Dead Connection Detection on the Standby System

Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter

to 2 in the SQLNET.ORA parameter file on the standby system. For example:

SQLNET.EXPIRE_TIME=2

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

log transport services.

The Oracle Net service name must resolve to a connect descriptor that uses the

same protocol, host address, port, and SID 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.

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. For example:

SQL> CREATE SPFILE FROM PFILE=’initpayroll2.ora’;

Start the Physical Standby Database

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

database in standby mode:

SQL> STARTUP NOMOUNT;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Initiate Log Apply Services

On the standby database, start log apply services as shown in the following

example:

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

The example includes the DISCONNECT FROM SESSION option so that log apply

services run in a background session.

Enable Archiving to the Physical Standby Database

This section describes the minimum amount of work you must do on the primary

database to set up and enable archiving to the physical standby database.

Set initialization parameters to define archiving

To configure archive logging from the primary database to the standby site the

LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be

defined.

The following example sets the initialization parameters needed to enable archive

logging to the standby site:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=payroll2’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

Start remote archiving

Archiving of redo logs to the remote standby location does not occur until after a

log switch. A log switch occurs, by default, when an online redo log becomes full.

To force the current redo logs to be archived immediately, use the SQL ALTER

SYSTEM statement on the primary database. For example:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Verifying the Physical Standby Database

Once you create the physical standby database and set up log transport services,

you may want verify that database modifications are being successfully shipped

from the primary database to the standby database.

To see the new archived redo logs that were received on the standby database, you

should first identify the existing archived redo logs on the standby database,

archive a few logs 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 logs.

On the standby database, query the V$ARCHIVED_LOG view to identify existing

archived redo logs. 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.

Archiving the current log

On the primary database, archive the current log using the following SQL

Statement:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify that the new archived redo log was received

On the standby database, query the V$ARCHIVED_LOG view to verify the redo log

was received:

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 logs are now available for log apply services to apply redo data to the standby

database.

Verify that the new archived redo log was applied

On the standby database, query the V$ARCHIVED_LOG view to verify the archived

redo log was 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.