Oracle-L: RE: RAC - setting up failover and load

From: Ron Yount <ronwy_at_swbell.net>
Date: Tue, 26 Aug 2003 17:49:27 -0800
Message-ID: <F001.005CD588.20030826174927@fatcity.com>

I see some points that may be causing failover issues in the sample you supplied below:

Let's start by covering the assumptions I have made about your environment : 1) Assumption: You are using a client connection that can leverage TAF (such as sqlplus or jdbc oci "thick" driver) thin clients cannot use TAF 2) This tnsnames entry comes from the tnsnames.ora on you client/application host.
3) You have two instances: vldbn1, vldbn2 for a single database ? name unknown
4) You wish to operate with vldbn1 as the primary database, and vldbn2 as the secondary

Now let's apply those assumptions to your entry.

When the application connects, it does so with a Net Service Name of "VLDB"
Your failover=on parameter will allow an attempt to connect using two different listeners on port 1521 using the stated IP address. Oracle will attempt both entries before giving you a failed "connection" error.
Your load_balance=yes parameter instructs oracle to randomly try one of your two connect descriptors (as opposed to a top down use of the list)
You will ONLY connect to an instance with a service_name of "vldbn2" this may be the start of your problems, unless both instances are registering with their respective listeners with the same service_name (vldbn2). You may want to consider a generic "shared by both instances", service_name of "vldb". This is done by setting the service_names parameter for each instance to the same value "vldbn2" The service_name used for registration with the listener can be verified with lsnrctl services on each node. You should find one service with two "instances" registered. e.g. Service "vldb" has two handlers, "vldbn1" and "vldbn2"
This is where your initial "connect" information stops, and TAF configuration begins, therefore, the settings above are about establishing a new connection, the settings below are about how TAF is going to handle a failure of your connection.
backup=secondary, when/if your connection is lost due to an instance failure on vldbn1, use a separate alias "SECONDARY" to get connected again.
type=select - If you are running a select statement, it will be replayed on the new connection and records you have not yet received will be given to application
retries=5, delay=5 (Consider increasing these, because you are only allowing 25 seconds for instance recovery on the surviving instance and the ability to accept new connection requests). It is not uncommon for this to take longer depending upon the nature of the primary instance failure..
method=basic, after failure, establish a new connection, e.g. Do not preconnect prior to failure
your "SECONDARY" Net Service Name entry looks fine, but note that in your original connection, you specified "service_name" of vldbn2, and you are specifying the same service name in this entry... No where do I see that you are allowing connections to an instance with a service name of vldbn1 (You probably intended to do this in the "VLDB" or primary entry.

HTH,
Don't hesitate to ask additional questions.

If you use the following init/spfile parameters, the following entry should work after both instances are restarted to reflect the changes: Instance 1: "instance_name = vldbn1" and "service_names = vldb" and "db_name = "vldb"
Instance 2: "instance_name = vldbn2" and "service_names = vldb" and "db_name = "vldb"

VLDB =
(DESCRIPTION =
(FAILOVER=ON)
(LOAD_BALANCE = no) - Direct first attempt to vldbn1 host if it is available (Ordered attempts)

(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.25)(PORT = 1521)) --
Always try me first, if not available, use next entry.

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.26)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VLDB)

       (FAILOVER_MODE=
         (BACKUP=SECONDAY)
          (TYPE=select)
          (METHOD=basic)
          (RETRIES=5)
          (DELAY=5)
       )

)
)

SECONDARY =
(DESCRIPTION =
(FAILOVER=ON)
(LOAD_BALANCE=NO) - Try to direct entries to host with instance vldbn2
if available

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.26)(PORT = 1521)) -
Always try me first, if not available, try next entry.

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.25)(PORT = 1521))

     (CONNECT_DATA =
        (SERVICE_NAME = VLDB)

)
)

HTH,
Don't hesitate to ask additional questions. -Ron-

-----Original Message-----
laura pena
Sent: Tuesday, August 26, 2003 4:49 PM
To: Multiple recipients of list ORACLE-L

So I have RAC setup ( Solaris 2.8 , Veritas DBE/AC 3.5 MP1 and HDS disks if you are interested) and I am attempting to setup my failover and loadbalancing from a client.

I brought down VLDBN1
and Thought I should fail over to VLDB2 ... but I did not.

Here is what I have in my tnsnames.ora:

VLDB =
(DESCRIPTION =

    (FAILOVER=ON)
    (LOAD_BALANCE = yes)
    (ADDRESS_LIST =


      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.25)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.26)(PORT = 1521))

)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VLDBN2)

       (FAILOVER_MODE=
         (BACKUP=SECONDAY)
          (TYPE=select)
          (METHOD=basic)
          (RETRIES=5)
          (DELAY=5)
       )

)
)
SECONDARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.250.26)(PORT = 1521))

     (CONNECT_DATA =
        (SERVICE_NAME = VLDBN2)

)
)

Any ideas?

Do you Yahoo!?
Yahoo! <http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com> SiteBuilder - Free, easy-to-use web site design software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Yount
  INET: ronwy_at_swbell.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Aug 26 2003 - 20:49:27 CDT

RE: RAC - setting up failover and load_balance