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

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Fri, 29 Aug 2003 12:49:30 -0800
Message-ID: <F001.005CE06C.20030829124930@fatcity.com>

Ron and all;
Sorry took me so long to replay ... I got the flu :( Anyways thanks so much for the reply. I went back and read more about TAF, I still am expecting to finish my selet call count on a 32 million row table even if I kill an instance with srvctl command. Here is where I am at now. Thanks for any input.

 --my single instance is called VLDB, 
 --I am using sqlplus to try and failover at the session level. 
 --All other assumption you stated are correct.
 --I can fail over the session itself (example listed)

SQL> select machine,failover_type,failover_method,failed_over,count(*) 2 from v$session
3 group by machine,failover_type,failover_method,failed_over 4
SQL> /

MACHINE                                                          FAILOVER_TYPE
---------------------------------------------------------------- -------------

FAILOVER_M FAI COUNT(*)
---------- --- ----------

VLDBN2                                                           SELECT
BASIC      NO           1

My error when I issue a svrctl stop instance -d VLDB instance -i VLDBN2 SQL> select count(*) From voicelog.cdr_table; select count(*) From voicelog.cdr_table

ERROR at line 1:
ORA-01089: immediate shutdown in progress - no operations are permitted SQL> select machine,failover_type,failover_method,failed_over,count(*) 2 from v$session
3 group by machine,failover_type,failover_method,failed_over;

MACHINE                                                          FAILOVER_TYPE
---------------------------------------------------------------- -------------

FAILOVER_M FAI COUNT(*)
---------- --- ----------

VLDBN2                                                           NONE
NONE       NO          14
VLDBN2                                                           SELECT
BASIC      YES          1

Here is my new tnsnames.ora file. BTW, I was using a different client, now I just go onto the VLDBN2 box and set up the tnsnames as follow: LISTENERS_VLDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN1)(PORT = 1521))     (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN2)(PORT = 1521))   )
VLDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
(LOAD_BALANCE =ON)
(FAILOVER=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN2)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VLDB)
(FAILOVER_MODE=

         (TYPE=SELECT)
         (METHOD=BASIC)
         (BACKUP=SECONDARY)
         (RETRIES=10)
         (DELAY=10)
      )

    )
  )
SECONDARY =
  (DESCRIPTION =

     (load_balance=NO)
     (failover=ON)
     (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN1)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN1)(PORT = 1521))
        (CONNECT_DATA =
        (SERVICE_NAME = VLDB)
           (FAILOVER_MODE=
              (TYPE=SELECT)
              (METHOD=BASIC)
              (RETRIES=10)
              (DELAY=10)
           )
        )

  )
VLDBN2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN2)(PORT = 1521))     (CONNECT_DATA =
(SERVICE_NAME = VLDB)
(INSTANCE_NAME = VLDBN2)

    )
  )
VLDBN1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN1)(PORT = 1521))     (CONNECT_DATA =
(SERVICE_NAME = VLDB)
(INSTANCE_NAME = VLDBN1)

    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)

    )
  )
LISTENER_VLDBN2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN2)(PORT = 1521)) LISTENER_VLDBN1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = VLDBN1)(PORT = 1521)) Ron Yount <ronwy_at_swbell.net> wrote:
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-----
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! SiteBuilder - Free, easy-to-use web site design software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: laura pena
  INET: lizzpenaorclgrp_at_yahoo.com

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 Fri Aug 29 2003 - 15:49:30 CDT

RE: RAC - setting up failover and load_balance