RAC Specific Wait Event

A) Block-Oriented Waits
-----------------------
1. gc current block 2-way
2. gc current block 3-way
3. gc cr block 2-way
4. gc cr block 3-way

B) Message-Oriented Wais
-------------------------
1. gc current grant 2-way
2. gc current grant 3-way
3. gc cr grant 2-way
4. gc cr grant 3-way (Normally this wait event is not possible, but "_cr_grant_local_role" --> turn 3-way CR grants off, make it automatic, or turn it on)

C) Contention-Oriented Waits
----------------------------
1. gc current block busy (cluster cache contention)
2. gc cr block busy
3. gc current buffer busy (local cache contention)

D) Load-Oriented Waits
----------------------
1. gc current block congested
2. gc cr block congested
3. gc current grant congested
4. gc cr grant congested

Now let's come to the discription part
---------------------------------------
A) Block-Oriented Waits
========================
1. gc current block 2-way (write/write with 2 nodes)
-----------------------------------------------------
Definition
-----------

- requesting instance request any data block for dml(current) from Master.
- If master is the holder of that data block and also has already modified that block.
- Then master will retain PI block for itself.
- Master will also flush respective redo log to log file before sending CURRENT block to requesting instance.
- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 2-WAY"

Reason
---------
As you know before sending the current data block to the requesting instance master instance first flush respective redo log of the log file then it will prepare PI block and then  send CURRENT block to the requesting instance over the interconnect.

- This wait event appears in "TOP-5" timed events section of AWR Report.
- Analyze the contention using AWR REPORT.
- In AWR REPORT, analyze "Current Block Recieved" and identify the top contentious objects.
- You are getting contention at segment level because of bad database design, DB object layout and Space Management.
- LGWR is not efficient to write in redo log file and thats why requesting instance is waiting in "gc current block 2-way".
- Interconnect is having N/W latency.
- Application Segregation is also a reason.

Troubleshooting
----------------
 - Best Solution is to apply application Segregation means try to locate all select query on one node and all DML on another node.
- Tune LGWR
- Tune Interconnect

2. gc current block 3-way (write/write with 3 nodes)
----------------------------------------------------

Definition
----------

- Requesting instance request any data block in CURRENT MODE for dml(current) from Master.

- If master is not holder of that data block and that data block is globally available on another instance.

_ Master will send a message to the current holding instance to relinquish ownership (Downgrade lock).

- The holding instance retain the PI of that data block and then serve to the requesting instance.

- Holding instance will also flush respective redo log to log file before sending CURRENT block to requesting instance.

- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 3-WAY"


Reason
---------

As you know before sending the current data block to the requesting instance master instance first flush respective redo log of the log file then it will prepare PI block and then CR block to send to the requesting instance over the interconnect.

- This wait event appears in "TOP-5" timed events section of AWR Report.

- Analyze the contention using AWR REPORT.

- In AWR REPORT, analyze "Current Block Recieved" and identify the top contentious objects.

- You are getting contention at segment level because of bad database design, DB object layout and Space Management.

- LGWR is not efficient to write in redo log file and thats why requesting instance is waiting in "gc current block 3-way".

- Interconnect is having N/W latency.

- Application Segregation is also a reason.


Troubleshooting
----------------

- Best Solution is to apply application Segregation means try to locate all select query on one node and all DML on another node.

- Tune LGWR

- Tune Interconnect

3. gc cr block 2-way (read/read or write/read with 2 nodes)
-----------------------------------------------------------
Definition
-----------
case-1: WRITE/READ
-------------------

- Requesting instance request any CR data block for select from Master.

- If master is the holder of that data block and also has already modified that block.

- Then master will prepare CR copy of that data block (using undo).

- Finally Master instance serve CR block to the requesting instance.

- Meanwhile requesting instace will wait in "gc cr block 2-way"

Case-2: READ/READ
------------------
- Requesting instance request any CR data block for select from Master.

- If master is the holder of that data block and has not already modified that block.

- Master instance serve CR block to the requesting instance immediatly.

- Meanwhile requesting instace will wait in "gc cr block 2-way"

Reason
---------
- In both the cases you will encounter this wait event in "TOP-5" section of AWR Report.

- Plan of action would be similer like "gc current block 2-way"

4. gc cr block 3-way (read/read or write/read with 3 nodes)
-----------------------------------------------------------
This wait event is exactly same as "gc cr block 2-way", only the difference is that here 3 or more than 3 instances are involved.

#################################################################################

B) Message-Oriented Waits
==========================
1. gc current grant 2-way
--------------------------
Definition
----------
- An instance request any data block in CURRENT MODE for dml(current) from Master.

- If the data block is not cached on any instance even on master too, then master instance will send a message to the requesting instance granting the EXCLUSIVE lock.

- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 2-WAY"

- Requesting instace will read data block from disk and do the physical I/O.

- This wait event does not indicate any contention.

Reason
---------
- This wait event appears in "TOP-5" timed events section of AWR Report.

- This wait event represent that requesting instance is spending a significant amount of time in obtaining the locks.

- Interconnect is having N/W latency (rare).

Troubleshooting
----------------
 - Best Solution is to tune your SQL Application so that it request less amout of data blocks.

- Because if requesting instance ask for more data blocks then master has to locking and holding instance related information in GRD before granting EXCLUSIVE LOCK, which will be high.

- Tune Interconnect (very rare).

2. gc cr grant 2-way
---------------------

Definition
----------
- An instance request any data block in CR MODE for select from Master.

- If the data block is not cached on any instance even on master too, then master instance will send a message to the requesting instance granting the SHARED lock.

- Meanwhile requesting instance will wait in "GC CR BLOCK 2-WAY"

- Requesting instace will read data block from disk and do the physical I/O.

- This wait event does not indicate any contention.


3. gc current grant 3-way
--------------------------
Definition
-----------
- An instacne request for any data block in current mode.

- Mater is not the holder of that data block, then master forward a message to provide the data block to the requesting instance.

- Current holding instance don't have the block because of aging out mechanism.

- Current holding instance grant exclusive lock to the requesting instance for physical IO.

- Meanwhile requesting instance will wait in "gc current grant 3-way".

- there is a hidden parameter to control this "_cr_grant_local_role"

C) Contention-Oriented Waits
=============================
1. gc current block busy
-------------------------
Definition
-----------
- An instance request for any data block in current mode, it send a request to the master.

- If master is the holder of that data block and also has already modified that block.

- Then master will retain PI block for itself.

- Master will also flush respective redo log to log file before sending CURRENT block to requesting instance.

- Now the block transfer delayed on requsting instance.

- Meanwhile requesting instance will wait in "gc current block busy"

Reason
-------

- The block was being used by a session on another instance.

- The block transfer was delayed because the holding instance could not write the corresponding redo record to the online redo log file.

Troubleshooting
----------------
 - Tune LGWR
- Appropriate Application Partitioning
- Tune N/w

2. gc current buffer busy
--------------------------
This wait event appears mostly in SINGLE INSTANCE when more than one sessions are trying to access same data block.
Troubleshooting
---------------
 - Application tuning


3. gc cr block busy
--------------------

- This event is the same as a "gc current block busy"


################################################################################################


D) Load-Oriented Waits (LMS)
=============================

1. gc current block congested
------------------------------

Definition
-----------

- Whenever any instance (HOST-03) request for any data block in any mode, this request will be served by MASTER NODE (HOST-01) of that data block.

- LMS process running on master node will provide data block to the requesting instance LMS process.

- Now LMS process running on both the nodes are highly loaded so there would be wait event ""gc current block congested


Reason
-------

- Highly loaded LMS process or CPU.



Troubleshooting
----------------

- Increase numner of LMS Processes by setting "GCS_SERVER_PROCESS" in 9i RAC onward.

- Optionally you can also set "_LM_LSM" till OPS.

- Tune OS

- Add CPUs



2. gc cr block congested
------------------------------

- This event is the same as a "gc current block congested".



3. gc current grant congested
------------------------------

Definition
-----------

- Whenever any instance (HOST-03) request for any data block in any mode, this request will be served by MASTER NODE of that data block.

- Meanwhile requesting instance (HOST-03) is waiting for approval from master instance to perform physical IO to read data block from DISK.

- This wait event is "gc current grant congested"

-

ORACLE RAC EVOLUTION HISTORY

Hello friend, this is my first article for RAC and in this article I am just going to discuss few basic evolutionary information about Oracle RAC. 

Very soon I'll write few other articles about RAC...

1. oracle cluster database was introduces with oracle 6 for DIGITAL VAX cluster product.

2. Oracle was the first product that support clustering at database level.

3. In the same version oracle create LOCK MANAGER for VAX/VMS  cluster but that was not very scalable and DB requires locking at block level.



4. Oracle 6.2 gave birth to OPS (OPS was used till 8i from 9i onward oracle start using RAC).

5. Oracle 6.2 was the first database that run the parallel server.

6. That time many UNIX vendor started cluster but they all were using Oracle DLM.



7. Oracle 7 OPS used vender-supplied clusterware.

8. OPS was avalaible for all vendor but main drawback was complexity with configuration.



9. Oracle 8 introduce GENERIC LOCK MANAGER

10. Then oracle intergrate LOCK MANAGER with oracle code with additional layer called OSD (OPERATING SYSTEM DEPENDENT).


WHAT IS OSD (Oracle Disk Manager)

Oracle provides cluster software (or OSD – operating system dependent layer) to the windows cluster for the use of RAC. This is different from the Microsoft Cluster Service software that Microsoft provides for windows clustering. The Microsoft MSC deals with the failover type of clustering.

Vendors provide the OSD clusterware for UNIX operating systems, and Oracle provides the OSD clusterware for Windows NT and Windows 2000 operating systems. The OSD has the following subcomponents:

a. The Cluster Manager
b. The Node Monitor
c. The Interconnect

The Cluster Manager
-------------------

The cluster manager (CM) oversees internode messaging that travels over the interconnect to coordinate internode operations. The cluster manager also provides a global view of the cluster and the nodes and instances that are members of it. The cluster manager also controls cluster membership.

The Node Monitor
-----------------

The cluster manager includes a subset of functionality known as a node monitor. The node monitor polls the status of each resource in the cluster including the nodes, the interconnect hardware and software, and the shared disks. In the Oracle-supplied Cluster Manager for Windows, the node monitor also polls the Oracle instances.

The cluster manager informs clients and the Oracle server when the status of cluster resources change. This is because Real Application Clusters manages cluster membership by reconfiguring the cluster database when a joining instance registers with the cluster manager or when an existing instance disconnects from it.

The node monitor also serves the cluster manager by:

- Providing node management interface modules
- Discovering and tracking the membership states of the nodes by providing a common view of node membership across the cluster
- Detecting and diagnosing changes in the states of active nodes and communicating information about those change events

The Interconnect
----------------

The interprocess communication (IPC) software, or interconnect, is another key OSD component. The IPC controls messaging among the nodes. Real Application Clusters also uses the IPC to transfer data blocks between instances.

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


11. Very soon Oracle's LOCK MANAGER intregated with oracle Kernel and became known as IDLM(Intregated Distributed Lock Manager).

12. Oracle 9i RAC start using IDLM but still It was using external clusterware.

13. With 9i oracle introduce its own clusterware but only for WINDOWS & LINUX.



14. From 10g onward its provide its own clusterware for all other OS.

15. In 11g its became more powerful with SERVERPOOL and with additional API to manage third party applications.



16. So finally with 9i oracle launch RAC but few constraints was there that was hampering the adoption of RAC and those constraints were-

a. The unavailability of general-purpose clusterware
b. The cluster-wide filesystem

17. With 9i, oracle solve both the issues that was hampering the adoption of RAC with the help of:

a. Oracle 9i RAC provide ORACLE CLUSTER MANAGER (OraCM) for LINUX & WINDOWS.
b. Oracle also developed cluster filesystem called as ORACLE CLUSTER FILESYSTEM (OCFS).

18. Now, stll few restriction was there with OCFS - OCFS has inherent limitations and was not very scalable large no. of cluster nodes and also OCFS was only ported for LINUX & WINDOWS.

19. So to solve the above limitation Oracle introduced another product in Oracle 9i Release 2 called ORACLE DISK MANAGER (ODM) for Solaris OS. 

Now the question is how ODM is good???

ODM was capable of handling file management operations and also ODM replaced FILE DISCRIPTER with FILE IDENTIFIER and start caching this FILE DISCRIPTER in SGA to reduce the kernel overheads for file lookups and file I/O operations.


20. With the success and wisdom gained by ODM, oracle was ready to launch fully functional and cluster-aware file system called ASM with oracle 10g.

Thanks,

DNS CONFIGURATION

Again this section just DNS CONFIGURATION which Actually help you for 11g Release2 RAC configuration. Which I'll write very soon.

Step-1: Configure YUM first

Follow my previous article for YUM CONFIGURATION http://ashishdba.blogspot.in/2012/09/yum-configuration.html .

Step-2: 

# yum install caching-nameserver*
# yum install bind*


Step-3: Copy named.conf file to the folder: /var/named/chroot/etc
   
(Note this named.conf file doesn't exist in your server, you'll find a file "named.caching-nameserver.conf" in the same location. Just copy the same file and rename with named.conf and do few changes, here is the sample file contents. 

Remember in this file do the following changes 

(a) in first section [ listen-on port 53 { 192.9.201.59; }; ]

(b) In the last just add following entry:


zone "example.com" IN {
        type master;
        file "forward.zone";
allow-transfer { 192.9.201.180; };

};


zone "201.9.192.in-addr.arpa" IN {
        type master;
        file "reverse.zone";

};

zone "0.0.10.in-addr.arpa" IN {
        type master;
        file "reverse1.zone";
};


----------------------------- named.conf ------------------------------------------
/
// named.caching-nameserver.conf
//
// Provided by Red Hat caching-nameserver package to configure the
// ISC BIND named(8) DNS server as a caching only nameserver 
// (as a localhost DNS resolver only). 
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
// DO NOT EDIT THIS FILE - use system-config-bind or an editor
// to create named.conf - edits to this file will be lost on 
// caching-nameserver package upgrade.
//

options {
listen-on port 53 { 192.9.201.59; };
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";

// Those options should be used carefully because they disable port
// randomization
// query-source    port 53;
// query-source-v6 port 53;

allow-query     { any; };
allow-query-cache { any; };
};
//logging {
  //      channel default_debug {
    //            file "data/named.run";
      //          severity dynamic;
       // };
//};
//view localhost_resolver {
// match-clients   { localhost; };
// match-destinations { localhost; };
// recursion yes;
// include "/etc/named.rfc1912.zones";
//};
//named.rfc1912.zones:

// Provided by Red Hat caching-nameserver package 
//
// ISC BIND named zone configuration for zones recommended by
// RFC 1912 section 4.1 : localhost TLDs and address zones
// 
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
zone "." IN {
type hint;
file "named.ca";
};

zone "localdomain" IN {
type master;
file "localdomain.zone";
allow-update { none; };
};

zone "localhost" IN {
type master;
file "localhost.zone";
allow-update { none; };
};

zone "0.0.127.in-addr.arpa" IN {
type master;
file "named.local";
allow-update { none; };
};

zone "0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN {
        type master;
file "named.ip6.local";
allow-update { none; };
};

zone "255.in-addr.arpa" IN {
type master;
file "named.broadcast";
allow-update { none; };
};

zone "0.in-addr.arpa" IN {
type master;
file "named.zero";
allow-update { none; };
};





zone "example.com" IN {
        type master;
        file "forward.zone";
allow-transfer { 192.9.201.180; };

};


zone "201.9.192.in-addr.arpa" IN {
        type master;
        file "reverse.zone";

};

zone "0.0.10.in-addr.arpa" IN {
        type master;
        file "reverse1.zone";
};

-----------------------------------------End of File --------------------------------------

Step-5: This command will change the ownership on named.conf file.

# chgrp named named.conf

Step-6: Then copy forward.zone, reverse.zone and reverse1.zone files to "/var/named/chroot/var/named" location

Step-7: This command will change the ownership on below files.

# chgrp named forward.zone
# chgrp named reverse.zone
# chgrp named reverse1.zone

Step-8: Now restart the named service so that we can use DNS Server. And the second command will start named service automatically whenever we'll restart our machine.

# service named restart
# chkconfig named on

Step-9: Now using dig command you can check whether you are getting reply from your DNS Server or not.

# dig server1.example.com

Thanks,

YUM Configuration

In this section I am just showing you how to configure YUM SERVER. Actually very soon I'll write a section for 11g Release2 RAC configuration, there this YUM Configuration will help you.

Step-1: cd /media/RHEL_5.4\ x86_64\ DVD/Server/

Step-2:  [root Server]# rpm -ivh vsftpd-2.0.5-16.el5.x86_64.rpm  (it will create a directory named as /ftp/pub which will be accessiable to  all in the n/w)

Step-3: copy Server folder from Linux DVD to /ftp/pub folder

Step-4: [root Server]# rpm -ivh createrepo-0.4.11-3.el5.noarch.rpm 


Step-5:  [root]# rm -rf /var/ftp/pub/Server/repodata/   (to remove old repository)

Step-6: [root ~]# createrepo -vg /media/RHEL_5.4\ x86_64\ DVD/Server/repodata/comps-rhel5-server-core.xml /var/ftp/pub/Server/


Step-7: Create Repository File

  # vim /etc/yum.repos.d/yum.repo

        [server]
name=server
baseurl=file:///var/ftp/pub/Server
gpgcheck=0
enabled=1

Step-8: # service vsftpd restart
Step-9: # yum list all

Thanks,

LOGICAL STANDBY CREATION STEPS

Step-1:

Using Grid Control create a new physical standby database on any machine but better on new. Then follow the following stpes to convert it into Logical standby.


Note: CREATE SERVICE dg03 ON THE THIRD NODE AND REGISTER THE SERVICE dg03 WITH LISTENER.


Step-2: On logical standby

SQL_Logical> alter database recover managed standby database cancel;


Step-3: Do nothing with pfile (It is updated automatically by Grid Control)

But just analyze the pfile and see the new entries added by Grid Control.


Step-4: On primary database, Ensures supplemental logging is enabled properly and builds the LogMiner dictionary by the this command

SQL_Primary> exec dbms_logstdby.build(); 


Note:
------
Use this procedure on the primary database to record relevant metadata (LogMiner dictionary) information in the redo log, which will subsequently be used by SQL Apply. This procedure will enable database-wide primary- and unique-key supplemental logging, if necessary.



Step-5: On logical standby fire this command to convert physical Standby db to Logical
---------------------------------------------------------------------------------------

SQL_Logical> alter database recover to logical standby dg04;


Step-6: on logical Standby Database
------------------------------------

SQL_Logical> shut immemdiate

SQL_Logical> startup mounts

SQL_Logical> alter database open resetlogs


Step-7: on Logical standby


SQL_Logical> alter database start logical standby apply immediate; ----> (it will start LSP)


Note: To stop LSP Process on logical standby 


SQL_Logical> alter database stop logical standby apply;


Step-8: Update Broker Configuration for new Logical Standby



Broker setup on dg01



1. (on dg01)DGMGRL> disable configuration

2. (on dg01) DGMGRL> remove configuration

3. DGMGRL> add database dg02 as connect identifier is 'dg02' maintained as physical;
Database "dg02" added

4. DGMGRL> add database dg04 as connect identifier is 'dg04' maintained as logical;
Database "dg04" added




5. DGMGRL> enable configuration;
Configuration
Name:                dgconfig1
Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    dg01 - Primary database
    dg02 - Physical standby database
    dg04 - Logical standby database

Current status for "dgconfig1":

SUCCESS

PROTECTION MODE

dgmgrl> edit configuration set protection mode as maxperformance;

dgmgrl> show configuration verbose

dgmgrl> edit database dg02 set property 'logxptmode'='sync';




Change to MaxProtection Mode


Step-1: Set logxptmode=sync for both databases.


DGMGRL> edit database dg01 set property logxptmode=sync;


DGMGRL> edit configuration set protection mode as maxprotection;

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.


DGMGRL>  edit database dg02 set property logxptmode=sync;


DGMGRL> edit configuration set protection mode as maxprotection;

Operation requires shutdown of instance "dg01" on database "dg01"
Shutting down instance "dg01"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg01" on database "dg01"
Starting instance "dg01"...
ORACLE instance started.
Database mounted.


DGMGRL> show configuration verbose;

Configuration
  Name:                dgconfig1
  Enabled:             YES
  Protection Mode:     MaxProtection
  Fast-Start Failover: DISABLED
  Databases:
    dg01 - Primary database
    dg02 - Physical standby database

Current status for "dgconfig1":
SUCCESS


NOTE:
Now just try to change the logxptmode=async, it will through an error because it will voilate MXPROTECTION rules.
EX:

10g Data Guard Broker Setup

In this second post i just want to show you how we to configure Data Guard Broker, which is most useful and powerful tool for Data Guard. So here we have the steps:


Step-1: Cretae a service dg01_DGMGRL on DG01 and dg02_DGMGRL on DG02 database using Netmgr. This service is required for Data Guard Broker.


Step-2: Change the broker related parameter on both the primary and standby database, i.e DG01 & DG02.

DG01_SQL> alter system set dg_broker_start=true; (on both databases)

DG02_SQL> alter system set dg_broker_start=true;


Step-3: Start DGMGRL prompt

$ dgmgrl
DGMGRL> connect sys@dg01
Password: *****
Connected.


Step-4: Create configuartion file for primary and standby databases and start Borker

DGMGRL> create configuration dgconfig1 as primary database is 'dg01' connect identifier is 'dg01' ;


DGMGRL> add database dg02 as connect identifier is 'dg02' maintained as physical ;


DGMGRL> enable configuration

DGMGRL> show database dg01

DGMGRL> show database verbose dg01



To Remove Configuation of Broker


DGMGRL> disable database dg02;
Disabled.

DGMGRL> disable configuration;
Disabled.

DGMGRL> remove configuration;

In the third I'll show you how to play with protection mode.

10g Data Guard Configuration on RHEL/OEL- 5

In this blog we'll see how to configure 10g Data Guard. In this, we'll do the configuration in two Parts.

1) Data Guard Configuration

2) Configure Data Guard processes.

Part-1: Data Guard Configuration

Configuration on primary site:-

Step 1) Configure the database in archive log mode and enable the force logging.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE OPEN;

Step 2) Configure standby redo log files to enable fast failover and real time apply. (Assume we have three redo log group)

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/dg01/sredo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/dg01/sredo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/dg01/sredo06.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/dg01/sredo07.log’ size 50m;

Note: - Number of standby redo log group must be more than number of redo log group and size of the standby logfile must be same as the size of redo log file.

Steps 3) CREATE PFILE FROM SPFILE.

Now configure following parameters for primary database:-

LOG_ARCGIVE_CONFIG='DG_CONFIG=(dg01,dg02)'

DB_UNIQUE_NAME=dg01

SERVICE_NAMES=dg01

DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_ARCHIVE_DEST_1=’location=/u01/app/oracle/oradata/dg01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG01’

LOG_ARCHIVE_DEST_2=’service=dg02 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG02’

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg01

FAL_SERVER=dg02

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 4) Take the entire database backup on pre configured staging directory.

mkdir /home/oracle/rman (Staging directory for rman backup)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ DATABASE;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ CURRENT CONTROLFILE FOR STANDBY;

RMAN> SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”;

Step 5) Copy the following items from machine1 to machine2.

PASSWORD FILE

PARAMETER FILE

RMAN BACKUP TAKEN ABOVE

Note: - RMAN backup must be copy at the same directory on machine2 i.e. /home/oracle/rman.

Step 6) Configure net service for dg01 (primary database) as well as for dg02 (standby database).

DG02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg02)

    )

  )

DG01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dg01)

    )

  )

------------------------ ON STANDBY DATABSE --------------------------

Step-1: Edit Pfile from Standby Database:

dg02.__db_cache_size=440401920

dg02.__java_pool_size=4194304

dg02.__large_pool_size=4194304

dg02.__shared_pool_size=163577856

dg02.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/dg02/adump'

*.background_dump_dest='/u01/app/oracle/admin/dg02/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/dg02/control01.ctl'

*.core_dump_dest='/u01/app/oracle/admin/dg02/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='dg01'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg02XDB)'

*.job_queue_processes=10

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=204472320

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=613416960

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/dg02/udump'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg01,dg02)'

DB_UNIQUE_NAME=dg02

SERVICE_NAMES=dg02

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dg01','/u01/app/oracle/oradata/dg02'

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dg01','/u01/app/oracle/oradata/dg02'

LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/dg02/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG02'

LOG_ARCHIVE_DEST_2='service=dg01 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG01'

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg02

FAL_SERVER=dg01

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 2: Create the required directory for standby database

# Mkdir -p  /u01/app/oracle/admin/dg02/adump

# Mkdir -p  /u01/app/oracle/admin/dg02/bdump

# Mkdir -p  /u01/app/oracle/admin/dg02/cdump

# Mkdir -p  /u01/app/oracle/admin/dg02/udump

# Mkdir -p  /u01/app/oracle/admin/dg02/create

# Mkdir -p  /u01/app/oracle/admin/dg02/pfile

# Mkdir -p  /u01/app/oracle/admin/dg02/scripts

Step-3: Configure net service for dg01 (primary) as well as dg02 (standby) database.

DG02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg02)

    )

  )

DG01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dg01)

    )

  )

Step-4: Start dg02 at nomount stage

$ export ORACLE_SID=dg02

SQL> CREATE SPFILE FROM PFILE;

SQL> STARTUP NOMOUNT

SQL> EXIT

Step-5: Now using RMAN duplicate command create standby database

RMAN TARGET SYS/ORACLE@DG01

RMAN> CONNECT AUXILIARY SYS/ORACLE@DG02

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;

Step-6: Add standby log on standby database

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/dg01/sredo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/dg01/sredo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/dg01/sredo06.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/dg01/sredo07.log’ size 50m;

Step-7: Start Flashback on standby database to avoid any problem

SQL> alter database flashback on;

So, in this way we can configure 10g Data Guard.

Part-2: Configure Data Guard Process

NOTE:

Now, After configuration you'll have to start few process so that your Data Guard could work properly. Here are the following steps that you'll gave to follow:

Step-1: Start MRP Process on Standby Node

SQL> alter database recover managed standby database disconnect from session;

SQL>  select PROGRAM from v$process where PROGRAM like '%MRP%';

PROGRAM

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

oracle@node2.oracle.com (MRP0)

Step-2: Start Real Time Apply (From standby Redo Log)

SQL> select dest_name,recovery_mode from v$archive_dest_status;

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect from session; (Before this command fire alter system switch logfile from DG01)

SQL> select dest_name,recovery_mode from v$archive_dest_status;

SQL> select sequence#, applied from v$archived_log;

This is the first part of Data Guard in coming parts i'll give you more explanations and demos for Data Guard.
Thanks,