Tuesday, April 28, 2009

Standby Site for Apps 11i

Lets continue from last two post on Dataguard which are at


Today I am going to cover Dataguard which is used for business continuity for Oracle Applications 11i including fusion middleware (Using AS Guard). There are two types of standby databases Physical Standby Database & Logical Standby database. Since oracle applications currently support only physical standby database , I am going to cover only physical standby databases.

Overview of Dataguard & Dataguard Broker
Dataguard consist of one primary database & one or more standby database where primary & standby databases are connected by sqlnet (How to configure this check in coming posts). These primary & standby database can be monitored by command line (sql) as well from GUI (Graphical User Interface) Tool called as Dataguard broker.

Important Services in Dataguard configuration
Two very important services in dataguard configuration are Redo Transport Service & Log Apply Services. In order to keep standby database in synch with primary database ; redo transport service transfer(Pull/Push) redo logs from primary to standby database & Log apply service applies that redo logs to standby database.

Switchover & Failover
These are basically two kind of roles in dataguard Failover & Switchover; In Switchover Primary database becomes standby database & one of (If there are more than one standby) standby database becomes primary database for ex. during server maintenance. Failover is used in scenarion in which your primary database is not available and you want to convert role of one of standby database to primary database for ex. during primary database failure.

Data Protection Mode
Standby database in dataguard configuration can be configured in any of three protection mode which can be changed later as well depending on your requirement . These protection modes are Maximum Protection Mode , Maximum Availability Mode & Maximum Performance Mode.

Maximum Protection Mode
In maximum protection mode as name suggest no data loss will occur if primary database fails. This is achieved by oracle by ensuring that redo data must be written to both local online redo log location & standby redo log location before transaction commits. This standby redo log will ensure full data protection. For full protection mode atleast one standby database should have standby redo log and LGWR, SYNC, and AFFIRM attribute should be used with LOG_ARCHIVE_DEST_n parameter. (I am going to discuss about these parameters in next post)
Here your primary database will shutdown if it is unable to write redo records to atleast one standby location.

Maximum Availability Mode
This is similar to Maximum protection mode except that it will not shutdown primary database if primary database is not able to write standby redo logs to atleast one standby location but in that case Maximum Availability will act in Maximum Performance Mode until fault is corrected and all gaps in redo logs are resolved. Once all gaps are resolved primary database will automatically goes into Maximum Availability Mode.

Maximum Performance Mode
This is default mode in which redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data. You can use LGWR and ASYNC attribute or ARCH attribute in LOG_ARCHIVE_DEST_n

You can check your database mode as

SQL> select PROTECTION_LEVEL from v$database;
Today I am going to cover step by step configuration for Oracle Dataguard & may be in next or future posts I will discuss on common mistakes which you can do while configure dataguard / standby database.

Below steps are based on assumptions that Primary Database is on Machine1.domain.com & Standby database is on Machine2.domain.com . Database Instance Name is PROD and database listener is listening on port 1525. Mount point on primary & standby database are same (If they are not you need to set parameter db_file_convert)

I am assuming using LGWR, ASYNC option with log shipping service which means ,
LGWR (Log Writer process will be used to write to standby site instead of ARC archiver process)
ASYNC (Redo logs to standby is asynchronous to primary site)

You may have to change options with log shipping service (LOG_ARCHIVE_DEST_n) depending on data protection mode you wish to choose. (I am using Maximum Performance Mode - Default Mode)

Enable Archive log
For standby database configuration your primary database should be running in achieve log mode. In order to convert your primary database into archive log mode follow these steps


SQL> alter system set LOG_ARCHIVE_DEST_1 ='LOCATION=/u01/oracle/data/arch' scope=spfile; (I am assuming that you are using spfile here , if you are using pfile skip scope=spfile)

SQL> alter system set LOG_ARCHIVE_DEST_2 ='SERVICE=PROD_remote1.domain.com LGWR ASYNC REOPEN=60' scope=spfile; (We will create above TNS Alias in next step)
SQL> alter system log_archive_dest_2='DEFER' (Defer this until you have standby system Up)

SQL> shutdown immediate
SQL> startup

You should see archive logs generated after this on standby site

Enable Force Logging
You should enable Force logging in primary database else if some transaction which doesn't generate redo log can corrupt your standby database. (Careful in OLTP transactions or long running requests in Apps)

Set Initialization Parameter
SQL> alter system set fal_client = Â’PROD.domainname.comÂ’ scope=both;
SQL> alter system set fal_server = Â’PROD_remote1.domainnameÂ’ scope=both;
(Here FAL, Fetch Archive Log is used to fetch archives in case gap in archives arises because of any reason)

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both; (This is used to automatically generate file in standby site like if you add a datafile, this will create one in standby site as well)

Create Standby Control file & copy datafile including oracle_home
Create standby control file as
SQL> alter DATABASE CREATE STANDBY CONTROLFILE AS '/u01/oracle/home/standby.ctl'

You can installed New Database on standby site or Use ORACLE_HOME software from primary site on standby site.
Copy datafiles, redologs, initializations file, tnsnames.ora, listener.ora & control file from primary site to standby site.

Today lets conclude dataguard/standby configuration & look at Metalink note on Dataguard configuration in Apps 11i with database 9i & 10g.

After creating standby control file and moving datafiles, redo log, control file,tnsnames.ora to standby site , start standby database as mentioned below using standby control file.

SQL> startup mount

In order to put standby database in managed recovery you can use


To Cancel recovery you can use

All these sql's are available in Standby Configuration guide so your can refer guide mentioned in my first post on standby database.

Failover is used if you loose your primary database or wish to break relationship between primary & standby database & convert standby database in to primary database.
Switchover is used to change role of primary to standby & vice versa.

For configuring Dataguard as Disaster Recovery solution in Oracle Applications 11i use Metalink Note # 216212.1 Business Continuity for Oracle Applications Release 11i, Database Releases 9i and 10g

Next level of Business Continuity is Application Server Guard (Currently for Standalone Application Server only) which includes middle tier recovery including database. AS Guard (Application Server Guard) also called as DSA is used for seamless Integration of Disaster Recovery solution for both Middle Tier & Database Tier. I am going to cover how to configure AS Guard in future.

Stay tuned to learn how to configure Single Sign On (SSO) access to HTMLDB / Apex Applications ......

No comments:

Post a Comment