| : |
Configuration of Load Balancing and Transparent Application Failover |
| |
문서 ID: |
공지:226880.1 |
유형: |
BULLETIN |
| |
마지막 갱신 날짜: |
15-NOV-2007 |
상태: |
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 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))
)
)
# 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))
)
Client side Tnsnames.ora file
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))
)
)
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 <machine: hprac-22, pid: 27840>
(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 <machine: hprac-22, pid: 27838>
(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 <machine: hprac-23, pid: 16911>
(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 <machine: hprac-23, pid: 16909>
(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 <machine: hprac-24, pid: 27815>
(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 <machine: hprac-24, pid: 27813>
(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 <machine: hprac-25, pid: 5081>
(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 <machine: hprac-25, pid: 5079>
(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 SER (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 Bug 2194549,
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 refusecommice "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
Bug 2134254. The workaround is to add dispatchers="(pro=ipc)(dis=0)" in
init.ora file.
===============================================================================
Transparent Application Failover (TAF) testing
Please familiar yourself with WebIV Note 97926.1 - 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
