High Availability of RAC connections

Connection Load Balancing Introduction :


Following are load balancing and fail over methods:



1) Client side connect time loadbalancing

it is set by using load_balance=true in tnsnames.ora

It makes the connection random else it always goes to the first protocol address

2) Client side connect time fail over

it is set by using failover=true in tnsnames.ora

It makes the connection to another listener if the first listener fails. Else it always tries the first listener only

3) Server Side connect time load balancing

Configured by configuring remote_listeners in spfile and also setting the load_balance to true and failover to true.

pmon contacts all the listeners in the cluster and updates the current load calculated by load balancing advisory.Then whenever

any requests comes the listener determines and redirects the service to the least utilized node.



FAN (fast application notification) :

This is intended for end to end lights out recovery and load balancing for database connections.

When the status of the components changes it posts a FAN event which the interested subscribers can recieve and act accordingly.

The fan event has a specific format which posts event for load balancing also.

It is very reliable and needs to be installed on all nodes.



Server side callouts:

Location : $CRS_HOME/racg/usrco needs to be configured with appropriate executable script.This executable is fired whenever a FAN event occurs.

Depending upon the FAN event generated you may configure for sending email,alerts etc ..



Oracle Notification Service :

Location :$CRS_HOME/opmn/conf/ons.config

Attributes :local port (used by ons to talk to local client) , Remote port(used by ons to talk to remote client) , nodes(list of all nodes for ons to communicate)

Key is to include the mid-tier nodes in the list of nodes to ensure the server is aware of the mid-tier servers.

You may use the racgons commands in any node to update the OCR with the ons configuration.



Fast connection failover

This is used by JDBC application in order to detect and recover the failed connections and also for load balancing.It also establishes the new connections once any


instance becomes available.

You can use Oracle Advanced queuing to publish FAN event to the subscribers like odp.net and oci. Also the view dba_outstanding_alerts gets populated with the message

being identified and published.



COnnection load balancing implementation:

Following 3 metrics are used to calculate the best nodes to connect by listener.

1)session count by instance - Used when load balancing goal is long

2)Run queue length of the queue - None

3)Goodness of the service - used when load balancing goal is short.



Connection load balancing goal - short,long,none



LBA is activated when Service goal is set to either service time or through put.

MMNL calculates the service good ness every 5 seconds and forwards to MMON

MMON computes the advisory and posts Load balancing advisory to the AQ.

IMON sends the same to ONS

EMON sends the same to OCI ,AQ subscribers.



Please use sys$service_metrics_tab to monitor the LBA fan events



TAF connection (Transparent application fail over ) :

This is to automatically reconnect to a service for the OCI applications if the initial connection fails .There are two types of fail over

1)Basic :The app recieves the error first time and then from next time it transparently connects to the service to the service.

2)Pre-connect : Similar to the above but the connection is always maintained with both the nodes .



Note any select statements can be resumed over to the other node but any active transaction will be rollbacked.



Implementation :

I.TAF with no notification for BASIC

Create a service with adding -p BASIC keyword. Then include the following parameters in the tnsnames.ora

a.method = basic and type=session :Only user sessions are authenticated and not the cursor

b.method=basic and type=select : both user sessions and the open cursors needs to be authenticated.So already fetced rows are discarded.



II.TAF with no notofication for PRE-CONNECT

Create service with -p PRECONNECT.Then use backup= and method=preconnect.



II.TAF with notification

Modify the service attribute with the fail over method (eg basic) and fail over type (session) and also ensure the hq_aq_notifications to be true.



TAF verification.

Please see the failover_method and failover_type from v$session



Please note whenever you use FCF do not use TAF (as it wont work).

Oracle Installation Tit bits

1) How to Install Oracle client 10.2.0.3.0 in windows 2003

The installation for 10.2.0.3.0 client first involves installing 10.2.0.1.0 downloading from oracle.com .Then next step contains downloading database patch set p5337014_10203_WINNT.zip and then proceeding .Here you need to select the previously installed client home or it might install the entire database .

2) Downloading the 11gr2

To download the 11gr2 software please follow the edelivery.oracle.com rather than oracle.com .This saes time and ensures the downloags get completed successfully.

ORA-1652 trace Analysis

Many times we dont have the sql traced whenever we hit the ORA-1652 and we send a mail to apps team no sql found and again application team come back again to us . To avoid we can do the following :




1)Set the trace to track ORA-1652 error in the memory :



ALTER SYSTEM SET EVENTS '1652 trace name ERRORSTACK level 3';

It doesnt show in the show parameter but somewhere stored in the database memory

2) To ensure the event is present even after the bounce do the following :



alter system set event=’1652 TRACE NAME ERRORSTACK LEVEL 3' scope=SPFILE;



We do this in two step as there is no magic pill to set trace in a single statement .

In any case if we need to revert back the changes ,please do the below :



1)To unset the trace event in the memory :



ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;



2)To unset the trace event from spfile also :



ALTER SYSTEM SET EVENT=' ' SCOPE=spfile;


Thanks to Amin Jaffer for the commands.

Inspirations for Daily Life

These are the inspirations I find in daily life. I like to share it with everyone and some might help you ..! ..

1) amazing thing is to observe oneself. Just give yourself 100% attention on what you do in the daily life .You can also practice this being an observer of you performing any activity. This gives an amazing peace and sweetness inside. It tells concentrate 100% of your thought on what you perform.

2)Sometimes I also like the healing meditation by Kelly Howell. That relaxes as well rejuvenates you.

3)There is an excellent yoga nidra audio by Tim Rowe ..It is really good to feel your body and relax when nothing else works to relax you.