Oracle®10g Data Guard Concepts and Administration

1. Creating Physical Standby Database

• Preparing the Primary Database for Standby Database Creation
• Step-by-Step Instructions for Creating a Physical Standby Database
• Post-Creation Steps

Preparing the Primary Database for Standby Database Creation

Before  creation of  a standby database we  must first ensure the primary database is properly configured.

Enable Forced Logging

Create Password File

Configure a Standby Redo Log

Set Primary Data Base initialization parameters

Enable Archiving

Enable Forced Login

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.

Create 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

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

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

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

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.

How to add a  Standby Redo Log File Group to a Specific Thread

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5

2> (‘/oracle/dbs/log1c.rdo’,'/oracle/dbs/log2c.rdo’) SIZE 500M;

How to add  a Standby Redo Log File Group to a Specific Group Number

You can also specify a number that identifies the group using the GROUP clause:

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.

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

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.

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

Primary Database: Standby Role Initialization Parameters

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

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;

Step-by-Step Instructions for Creating a Physical Standby Database

1. Create a Backup Copy of the Primary Database Datafile

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).

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. Prepare an Initialization Parameter File for 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;

Modifying Initialization Parameters for a Physical Standby Database
.
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

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 DataFile
Standby Control File
Initialization Parameter

5. Set Up the Environment to Support the Standby Database
   1. Create Window Based Service
   2. WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual
   3. Create a password file.
   4. On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.
   5. % lsnrctl stop
   6. % lsnrctl start
   7. Create Oracle Net service names.
   8. Create a server parameter file for the standby database.

   9. CREATE SPFILE FROM PFILE='initboston.ora';

   • Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.
6. Start the Physical Standby Database

SQL> STARTUP MOUNT;
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

Test archival operations to the physical standby database.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the Physical Standby Database Is Performing Properly

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

Force a log switch to archive the current online redo log file.
SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the new redo data was archived on the standby database.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Verify new archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG   2  ORDER BY SEQUENCE#;

Post-Creation Steps

At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database:

• Upgrade the data protection modeThe Data Guard configuration is initially set up in the maximum performance mode (the default).
• Enable Flashback DatabaseFlashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than traditional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both.