Subject:	Configuration of Load Balancing and Transparent Application Failover
	:	Note:226880.1	Type:	BULLETIN
	Last Revision Date:	27-JUL-2005	Status:	PUBLISHED


Configuration of Load Balancing and Transparent Application Failover


This note was created to provide a guideline to the configuration of:

1) client side connect time load balance only
2) server side listener connection load balance with client side connect
   time load balance
3) server side listener connection load balance only
3) transparent application failover


Before you configure the Net Services features, here is a definition of the
these features that can be implemented either singly or in combination with
each other.

 Client Side Connect-Time Failover
The connect-time failover enables clients to connect to another listener if
the initial connection to the first listener fails. The number of listener 
protocol addresses determines how many listeners are tried. Without 
connect-time failover, Oracle Net attempts a connection with only one listener.  
The default is on.

 Transparent Application Failover
The Transparent Application Failover (TAF) feature is a runtime failover for
high-availability environments, such as Oracle9i Real Application Clusters and
Oracle9i Real Application Clusters Guard. TAF fails over and reestablishes 
application-to-service connections. It enables client applications to 
automatically reconnect to the database if the connection fails and, 
optionally, resume a SELECT statement that was in progress. The reconnection 
happens automatically from within the Oracle Call Interface (OCI) library.

 Client Side Connect Time Load Balancing
The client load balancing feature enables clients to randomize connection 
requests among the listeners. Oracle Net progresses through the list of 
protocol addresses in a random sequence, balancing the load on the various 
listeners. Without client load balancing, Oracle Net progresses through the 
list of protocol addresses sequentially until one succeeds.  This normally is 
referred to connect-time load balance.  

 Server Side Listener Connection Load Balancing
The listener connection load balancing feature improves connection performance
by balancing the number of active connections among multiple dispatchers and 
instances. In a single-instance environment, the listener selects the least 
loaded dispatcher to handle the incoming client requests. In an Oracle9i Real 
Application Clusters environment, connection load balancing also has the 
capability to balance the number of active connections among multiple instances.

Due to dynamic service registration, a listener is always aware of all 
instances and dispatchers regardless of their locations. Depending on the load
information, a listener decides which instance and, if shared server is 
configured, which dispatcher to send the incoming client request to. In a 
shared server configuration, a listener selects a dispatcher in the following 
order:
1. Least-loaded node
2. Least-loaded instance
3. Least-loaded dispatcher for that instance

In a dedicated server configuration, a listener selects an instance in the 
following order:
1. Least loaded node
2. Least loaded instance

If a database service has multiple instances on multiple nodes, the listener 
chooses the least loaded instance on the least loaded node. If shared server 
is configured, then the least loaded dispatcher of the selected instance is 
chosen.


Included is server side node1's init.ora, listener.ora, and tnsnames.ora files 
as well as client side tnanames.ora file.

This is a four nodes cluster setup.  The configuration is as follows.  If you 
replace your hostname, service_name, sid_name, instance_name and $ORACLE_HOME
in the example files, you should be able to configure a successful setup.  

       hostname  service name  sid name  instance_name  ORACLE_HOME
       ========  ============  ========  =============  =====================
node1   hprac-22      rac         rac1         rac1     /oracle/9iship/rac901
node2   hprac-23      rac         rac2         rac2     /oracle/9iship/rac901
node3   hprac-24      rac         rac3         rac3     /oracle/9iship/rac901
node4   hprac-25      rac         rac4         rac4     /oracle/9iship/rac901


All 4 nodes init.ora file have the following parameters:
remote_listener='LISTENERS_RAC'
rac1.local_listener="LISTENER_rac1”
rac2.local_listener="LISTENER_rac2"
rac3.local_listener="LISTENER_rac3"
rac4.local_listener="LISTENER_rac4"
# dispatchers="(pro=ipc)(dis=0)"
db_name='rac'
rac1.instance_name='rac1'
rac2.instance_name='rac2'
rac3.instance_name='rac3'
rac4.instance_name='rac4'

Since service_names is not specified in the init.ora file, it defaults to 
db_name.db_domain.  Each node should list it's own host name and instance_name.  
With the above setup, after you start the instance, when checking the sql 
session from node1, you will find the following info.  


SQL> show parameter db_name
NAME                        TYPE      VALUE
--------------------------- --------- -----------------------------------------
db_name                     string    rac

SQL> show parameter db_domain
NAME                        TYPE      VALUE
--------------------------- --------- -----------------------------------------
db_domain                   string

SQL> show parameter service_names
NAME                        TYPE      VALUE
--------------------------- --------- -----------------------------------------
service_names               string    rac

SQL> show parameter instance_name
NAME                        TYPE      VALUE
--------------------------- --------- -----------------------------------------
instance_name               string    rac1

SQL> show parameter listener
NAME                    TYPE      VALUE
---------------------- -----     ----------------------------------------------
local_listener         string    LISTENER_rac1
mts_listener_address   string
mts_multiple_listeners boolean   FALSE
remote_listener        string    LISTENERS_RAC


hprac-22 listener.ora file
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/9iship/rac901)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = /oracle/9iship/rac901)
      (SID_NAME = rac1)
    )
  )


hprac-22 ~ hprac-25 and client side tnsnames.ora file

LISTENERS_RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    )
  )

RAC1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac1)
    )
  )

RAC2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac2)
    )
  )

RAC3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac3)
    )
  )

RAC4 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac4)
    )
  )

RAC =
  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
    )
  )

failover =
  (DESCRIPTION =
    (enable=broken)
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
      (failover_mode=(type=select)(method=basic))
    )
  )

# For hprac-22 only
LISTENER_rac1 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
  )

# For hprac-23 only
LISTENER_rac2 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
  )

# For hprac-24 only
LISTENER_rac3 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
  )

# For hprac-25 only
LISTENER_rac4 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
  )


Remarks:
1) LISTENERS_RAC, LISTENER_rac1, LISTENER_rac2, LISTENER_rac3, LISTENER_rac4, 
is the net_service_name (connect descriptor) for remote_listener and 
local_listener.  On the client side, you do not need these net_service_name.
2) failover is the net_service_name for transparent application failover (TAF)
testing.
3) RAC is the net_service_name for client side load balance, if you do not 
need to configure TAF.

There are few different ways to set up client side connect time load balance.  
Here is another alternative:

4) RAC_alternative =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
    )
  )
Note:  The (load_balance=yes) instructs Net to progress through the list of 
listener addresses in a random sequence, balancing the load on the various 
listeners.  When set to OFF, instructs Net to try the addresses sequentially 
until one succeeds.  This parameter must be correctly coded in your net 
service name (connect descriptor).  By default, this parameter is set to ON 
for DESCRIPTION_LISTs.  Load balancing can be specified for an ADDRESS_LIST or 
associated with a set of ADDRESSes or set DESCRIPTIONs. If you use ADDRESS_LIST,
(load_balance=yes) should be within the (ADDRESS_LIST=) portion.  If you do 
not use ADDRESS_LIST, (load_balance=yes) should be within the (description=)
portion.  We recommend not to use this (ADDRESS_LIST=) clause.

5) (failover=on) is default for ADDRESS_LISTs, DESCRIPTION_LISTs, and a set of
DESCRIPTIONs., therefore, you do not have to specify.  This is for 
connect-time-failover, please do not confuse it with transparent application 
failover (TAF).  
6) (failover_mode=): The FAILOVER_MODE parameter must be included in the 
CONNECT_DATA portion of a net_service_name.  
7) There is no (backup=failover) in (failover_mode=), this implies 
(failover_mode=(type=select)(method=basic)(backup=failover)), which means 
whenever failover occurs, the connected session will failover to the 
net_service_name failover again.  A backup should be specified when using 
PRECONNECT to pre-establish connections.  For details of TAF, please refer to 
Oracle official documentations.


Methodology for the testing
1. You should always start with a simple setup and then move towards 
more complicated one.
2. After you start the listener and instance, verify the output from 
'lsnrctl services' first.  'lsnrctl status' alone does not provide you with 
sufficient info whether or not you have the correct setup.  If ‘lsnrctl 
services’ output is incorrect.  STOP!  Correct the listener.ora or init.ora 
before you continue.  
3. Test the connections from the server for the client-side-load-balancing, 
verify it with the verify.sql provided here.  You can comment out the sql 
you do not need.
4. Test the connections from the server for the server side listerener 
connection load balance, verify it.
5. Test the TAF connections from the server, verify the failover_type and
failover_mothod before shutting down the instance or rebooting the server. 


===============================================================================
Before you start the testing, you can create a loop.sh file with the following
lines repeated as many times as you like.  In my testing, I repeat 512 times 
connection.  Please replace username, password and net_service_name accordingly.

loop.sh
nohup sqlplus su/su@failover @verify.sql &
   sleep 1
nohup sqlplus su/su@failover @verify.sql &
   sleep 1
nohup sqlplus su/su@failover @verify.sql &
   sleep 1
nohup sqlplus su/su@failover @verify.sql &
   sleep 1


verify.sql (to verify the connection)
REM  set pagesize 1000
REM  the following query is for TAF connection verification
col sid format 999
col serial# format 9999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over
  from v$session where username = 'SU';

REM  the following query is for load balancing verification
select instance_name from v$instance;
exit

REM you can also combine two queries:
col inst_id format 999
col sid format 999
col serial# format 9999999
col failover_type format a13
col failover_method format a15
col failed_over format a11

select inst_id, sid, serial#, failover_type, failover_method, failed_over
  from gv$session where username = 'SU';

REM  a simple select to see the distribution of users when testing connection 
REM  load balancing

select inst_id, count(*) from gv$session group by inst_id;

To run the test, simply type './loop.sh', the output will go to nohup.out.

===============================================================================

Client Side Connect Time Load Balancing



Testing
For client side connect time load balancing testing only, without server side 
listener connection load balancing, please remove the remote_listener in the 
init.ora file and restart all instances.  There will be no remote instances 
registered to the listener.  You will only find the local server for each 
instance.  

hprac-22 'lsnrctl service' 
Service "rac" has 1 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-23 'lsnrctl service' 
Service "rac" has 1 instance(s).
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "rac2" has 1 instance(s).
  Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-24 'lsnrctl service' 
Service "rac" has 1 instance(s).
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "rac3" has 1 instance(s).
  Instance "rac3", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-25 'lsnrctl service' 
Service "rac" has 1 instance(s).
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "rac4" has 1 instance(s).
  Instance "rac4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


Simply run the './loop.sh' script, and check the output periodically.

> grep rac1 nohup.out | wc -l
28
> grep rac2 nohup.out | wc -l
28
> grep rac3 nohup.out | wc -l
22
> grep rac4 nohup.out | wc -l
17


> grep rac1 nohup.out | wc -l
87
> grep rac2 nohup.out | wc -l
72
> grep rac3 nohup.out | wc -l
73
> grep rac4 nohup.out | wc -l
63


> grep rac1 nohup.out | wc -l
147
> grep rac2 nohup.out | wc -l
126
> grep rac3 nohup.out | wc -l
133
> grep rac4 nohup.out | wc -l
106


After the testing completes, in this particular test, there were 147 
connections go to rac1, 126 connections go to rac2, 133 connections go to 
rac3 and 106 connections go to rac4.  This gives you a rough idea about 
the distribution of client side randomly selection among all 4 nodes.  

==============================================================================

Server Side Listener Connection Load Balancing Testing (combined with client 
side connect time load balancing)

Server side listener connection load balancing is where the listener routes 
the connections to the least-loaded instance.  Please add back remote_listener 
parameter in the init.ora file and restart all 4 instances.  Remember to 
verify the output of ‘lsnrctl services’.  The node1 'lsnrctl services' output 
looks like (I have eliminiated "MODOSE" and "PLSExtProc" service output to 
make it easier to read):

hprac-22 $ lsnrctl services

LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 06-JUN-2002 19:44:17

Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
      "D002" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-22)(PORT=62235))
         (PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
      "D001" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-22)(PORT=62234))
         (PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
      "D002" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-23)(PORT=51212))
         (PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
      "D001" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-23)(PORT=51211))
         (PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
      "D002" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-24)(PORT=52409))
         (PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
      "D001" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-24)(PORT=52408))
         (PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
      "D002" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-25)(PORT=53755))
         (PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
      "D001" established:0 refused:0 current:0 max:2026 state:ready
         DISPATCHER 
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-25)(PORT=53754))
         (PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
$

Note: Comparing this to the 'lsnrctl status' output below, you will find the
above provides much more information.  Please note that for an instance on its
own node, i.e. rac1 on node1 (hprac-22), you should have both local server and
remote server.  For all other remote instances, i.e. rac2, rac3 and rac4 on 
node1 (hprac-22), you will only find the remote server.  Under the remote 
server, please make sure the address listed is correct, no null hostname.
(ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
                        ^^^^^^^^^^^^^

hprac-22 $ lsnrctl status

LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 06-JUN-2002 19:45:14

Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for HPUX: Version 9.0.1.3.0 - Production
Start Date                15-MAY-2002 15:33:41
Uptime                    22 days 4 hr. 11 min. 33 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/9iship/rac901/network/admin/listener.ora
Listener Log File         /oracle/9iship/rac901/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hprac-22)(PORT=1521)))
Services Summary...
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 4 handler(s) for this service...
  Instance "rac2", status READY, has 3 handler(s) for this service...
  Instance "rac3", status READY, has 3 handler(s) for this service...
  Instance "rac4", status READY, has 3 handler(s) for this service...
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


Example of 'lsnrctl services' output from hprac-23, hprac-24, hprac-25.  
To make it easier to read, I have eliminated the aurora dispatchers info.  


hprac-23 $ lsnrctl services

LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 06-JUN-2002 19:44:15
Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
   Instance "rac2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADc-25)(PORT=1521))
Service "rac2" has 1 instance(s).
  Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
$

hprac-24 $ lsnrctl services

LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 06-JUN-2002 19:44:14
Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
   Instance "rac3", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac3" has 1 instance(s).
  Instance "rac3", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
$


hprac-25  $ lsnrctl services

LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 06-JUN-2002 19:43:20
Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac4" has 1 instance(s).
  Instance "rac4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


===============================================================================
Simply run the './loop.sh' script again.  You can also periodically check the 
results.

After the testing completes, in this particular test, there were 182 
connections go to rac1, 109 connections go to rac2, 153 connections go to 
rac3 and 68 connections go to rac4.  This output combines both client side 
load balancing (randomly selected) and the server side listener connection 
load balancing.
  
> grep rac1 nohup.out | wc -l
182
> grep rac2 nohup.out | wc -l
109
> grep rac3 nohup.out | wc -l
153
> grep rac4 nohup.out | wc -l
68

You can safely assume that node1 had the least CPU load, therefore, most 
connections went to rac1.  Node4 had the heaviest CPU load, therefore, least 
connections went to rac4.  

After the testing, you can check 'lsnrctl services' from all 4 nodes again:
hprac-22 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:182 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
   Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
   Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:23 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-23 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:58 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:109 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
   Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:50 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
   Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:22 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac2" has 1 instance(s).
  Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-24 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:65 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
   Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:39 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:153 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:23 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac3" has 1 instance(s).
  Instance "rac3", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully



hprac-25 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:55 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:32 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:48 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:68 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac4" has 1 instance(s).
  Instance "rac4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

===============================================================================

Server Side Listener Connection Load Balancing Only Testing
If you prefer, you can eliminate the client side connect time load balance by 
removing or commenting out (LOAD_BALANCE = yes) and check the distribution of 
the server side listener connection load balance only.

RAC_no_client_side_load_balance =
  (DESCRIPTION =
#      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-22)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-23)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hprac-25)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
    )
  )

In this case, all connections will go to hprac-22 listener and hprac-22 
listener will route to the other instances based on the last updated load 
information updated by PMON.  

The result is 
> grep rac1 nohup.out | wc -l
189
> grep rac2 nohup.out | wc -l
106
> grep rac3 nohup.out | wc -l
105
> grep rac4 nohup.out | wc -l
112

After the testing completes, in this particular test, there were 189 
connections go to rac1, 106 connections go to rac2, 105 connections go to 
rac3 and 112 connections go to rac4.  This gives you a rough idea about 
the distribution of server side listener connection load balancing among 
all 4 nodes.  You can compare it with the previous two tests.  


hprac-22 ‘lsnrctl services’
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:189 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:106 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:105 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:112 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-23 ‘lsnrctl services’
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
      "DEDICATED" established:106 refused:0 state:ready
         LOCAL SERVER
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac2" has 1 instance(s).
  Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-24 ‘lsnrctl services’
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
      "DEDICATED" established:105 refused:0 state:ready
         LOCAL SERVER
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
Service "rac3" has 1 instance(s).
  Instance "rac3", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


hprac-25 ‘lsnrctl services’
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-22)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-23)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-24)(PORT=1521))
  Instance "rac4", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))
      "DEDICATED" established:112 refused:0 state:ready
         LOCAL SERVER
Service "rac4" has 1 instance(s).
  Instance "rac4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

You will also notice that from hprac-22 ‘lsnrctl service’ output, it has all 4
instances established connection count info.  However, the other 3 nodes only 
have it’s own instance established connection count info.  It’s due to no 
client side load balancing (load_balance=on), all connections go to hprac-22 
listener (first one in the address_list) and hprac-22 listener routes to the 
other instances.  

===============================================================================

Common misconception

If you want to have remote instances registered with the listener, even if 
your listener is using port 1521, you still need to have local_listener in 
your init.ora file.  Otherwise, with remote_listener="LISTENERS_RAC" alone, 
you will not get the remote instances registered with the listener and no 
server side listener connection load balancing.  This is due to , 
fixed at 10i.  Let’s remove all local_listener parameters from the init.ora 
file and restart all 4 nodes, to see what will happen.  Please note the (HOST=)
under REMOTE SERVER.  

If you are not using the default port 1521, it’s required that you have 
local_listener in the init.ora file.  If your hostname output is your 
interconnect ip address as opposed to the public ethernet ip address, PMON 
process will register the service and instance with the hostname’s listener.  
In this case, you should also specify the local_listener parameter to 
instruct the PMON to register the service and instance with the public 
ethernet ip address listener. 

hprac-22 'lsnrctl service'
 (Similar output will be found for hprac-23, hprac-24, hprac-25)
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
                                ^^^^^^^
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
                                ^^^^^^^
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
                                ^^^^^^^
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
                                ^^^^^^^
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


Since the remote instances registered with NULL hostname, only 1 out of 4 
connections (4 nodes configuration) will go thru (via local server) and the 
others will fail with ORA-12502.  The following test was using the 
net_service_name RAC with (load_balance = yes).  

> grep rac1 nohup.out | wc -l
30
> grep rac2 nohup.out | wc -l
33
> grep rac3 nohup.out | wc -l
32
> grep rac4 nohup.out | wc -l
33
> grep 12502 sqlnet.log | wc -l
384

In this particular testing, 384 output 512 connections failed. Among the 
successful connections, 30 connections go to rac1, 33 connections go to rac2, 
32 connections go to rac3, and 33 connections go to rac4.  On the client 
side sqlnet.log file you will find the following error messages:

***********************************************************************
Fatal NI connect error 12502, connecting to:
 (DESCRIPTION=(enable=broken)(ADDRESS=(PROTOCOL=TCP)(HOST=hprac-25)(PORT=1521))(
CONNECT_DATA=(service_name=rac)(failover_mode=(type=select)(method=basic))(CID=(
PROGRAM=)(HOST=opcbhp1)(USER=usupport))))

  VERSION INFORMATION:
        TNS for HPUX: Version 9.0.1.3.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 9.0.1.3.0 - Production
  Time: 06-JUN-2002 19:27:25
  Tracing not turned on.
  Tns error struct:
    nr err code: 0
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

The ora-12502 error is due to every connection going to node1 listener and 
it would try to route 3 out of 4 connections (4 nodes configuration) to the 
remote server with (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)).  It does not 
know where, or which host to connect to.  

In this particular testing, for the failing 384 connections, 89 went to 
hprac-22 listener, 98 went to hprac-23 listener, 97 went to hprac-24 listener 
and 100 went to hprac-25 listener. Here was the statistics: 

> grep HOST=hprac-22 sqlnet.log | wc -l
89
> grep HOST=hprac-23 sqlnet.log | wc -l
98
> grep HOST=hprac-24 sqlnet.log | wc -l
97
>  grep HOST=hprac-25 sqlnet.log | wc -l
100

hprac-22 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:30 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:31 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

hprac-23 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:32 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
Service "rac2" has 1 instance(s).
  Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

hprac-24 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:32 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac3", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:32 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac4", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:32 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
Service "rac3" has 1 instance(s).
  Instance "rac3", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s)d:0 refusecommand co5 'lsnrctl service'
Service "rac" has 4 instance(s).
  Instance "rac1", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:34 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac2", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
  Instance "rac4", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:33 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
Service "rac4" has 1 instance(s).
  Instance "rac4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


Caution:  If you are not running 9.0.1.3, for dedicated connection, server 
side load balancing will route most connections to one node, this is due to 
  The workaround is to add dispatchers="(pro=ipc)(dis=0)" in 
init.ora file.  

===============================================================================

Transparent Application Failover (TAF) testing

Please familiar yourself with WebIV  - Failover Issues and 
Limitations [Connect-time failover and TAF] by Richard Powell for detailed 
explanation about TAF.  The following only demonstrate the transparent 
application failover testing.  

> sqlplus su/su@failover

SQL*Plus: Release 9.0.1.3.0 - Production on Thu Jun 6 19:44:57 2002
(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.3.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.0.1.3.0 - Production

SQL> col sid format 999
SQL> col serial# format 9999999
SQL> col failover_type format a13
SQL> col failover_method format a15
SQL> col failed_over format a11
SQL> select sid, serial#, failover_type, failover_method, failed_over
     from v$session where username = 'SU';

       SID    SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---------- ---------- ------------- --------------- -----------
        13          8 SELECT        BASIC           NO

If you see NONE under failover_type and failover_method, STOP!!  You need
to fix your tnsnames.ora file, it does not make sense to continue to test 
'shutdown abort' or reboot server.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac4

*** Due to the load balancing, we need to find out which instance it 
currently connects to.  Now, we can continue our TAF testing.  

SQL> select count(*) from
(select * from dba_source
union
select *  from dba_source
union
select *   from dba_source
union
select *    from dba_source
union
select *     from dba_source)
/

*** At this point, I ‘shutdown abort’ rac4 from another window.  The query
did not stop and the result should be back.  

COUNT(*)
----------
     60221

*** The query results is back without any error.  

SQL> col sid format 999
SQL> col serial# format 9999999
SQL> col failover_type format a13
SQL> col failover_method format a15
SQL> col failed_over format a11
SQL> select sid, serial#, failover_type, failover_method, failed_over
     from v$session where username = 'SU';

       SID    SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---------- ---------- ------------- --------------- -----------
        13          5 SELECT        BASIC           YES

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac1

*** Note here, it failed over to rac1 with different serial# and 
failed_over flag was set.
 
Restarted rac4, make this database back to 4 instances database.

SQL> select count(*) from
(select * from dba_source
union
select *  from dba_source
union
select *   from dba_source
union
select *    from dba_source
union
select *     from dba_source)
/

*** At this point, I ‘shutdown abort’ rac1 from another window.  The query 
did not stop and the result should be back.  


  COUNT(*)
----------
     60221
The query results is back without any error.  

SQL>          
    SID    SERIAL#    FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---------- ---------- ------------- --------------- -----------
        14         20 SELECT        BASIC           YES

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac2

*** Note here, it failed over to rac2 with different sid, serial# and 
failed_over flag was set.


Troubleshooting:

If there still is a problem, you should send the screen print out together 
with the following into to the support:

1. listener.ora, tnsnames.ora, sqlnet.ora files from all server nodes
2. 'lsnrctl services' output from all server nodes, please note: 
'lsnrctl status' output is not sufficient. 
3. init.ora, common.ora, and spfile converted init.ora if any, from all 
server nodes
4. in sql session, spool out the following info:
show parameter db_name
show parameter db_domain
show parameter service_name
show parameter instance_name
show parameter listener

5. ‘hostname’ output and /etc/hosts file or nslookup `hostname` output.
6. tnsnames.ora, sqlnet.ora files from the client side
7. sqlnet.log files from both client and server, listener.log file from the 
server
8. In sqlnet.ora files on both client and server, please set up the tracing 
parameters as follows and send us the sqlnet client/server trace files from 
both client and server sides.
trace_level_client = 16
trace_unique_client = on
# please provide a valid directory with write permission for the following 
parameter
trace_directory_client = /oracle/9iship/rac901/network/trace 
trace_level_server = 16
# please provide a valid directory with write permission for the following 
parameter
trace_directory_server = /oracle/9iship/rac901/network/trace  
trace_timestamp_client = on
trace_timestamp_server = on

9.  When you finish the testing, please make sure that you comment out the 
following two lines.  
# trace_level_client = 16
# trace_level

Configuration of Load Balancing and Transparent Application Failover

相關文章