| Configuration of Load Balancing and Transparent Application Failover [ID 226880.1] | |||||
|
| |||||
| 修改时间 14-JUL-2011 类型 BULLETIN 状态 PUBLISHED | |||||
In this Document
Purpose
Scope and Application
Configuration of Load Balancing and Transparent Application Failover
Applies to:
Oracle Net Services - Version: 9.0.1.0.0 to 9.2.0.8.0 - Release: 9.0.1 to 9.2Oracle Net Services - Version: 9.0.1.0.0 to 9.2.0.8.0 [Release: 9.0.1 to 9.2]
Information in this document applies to any platform.
Purpose
Configuration of Load Balancing and Transparent Application FailoverScope and Application
This note was created to provide a guideline to the configuration of:
- Client side connect time load balance only
- Server side listener connection load balance with client side connect time load balance
- Server side listener connection load balance only
- 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 Oracle Real Application Clusters and Oracle 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 Oracle Real
Application Clusters environment, connection load balancing also has the capability to balance the number of active connections among multiple instances.
Configuration of Load Balancing and Transparent Application Failover
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.
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:
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.
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
(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 to hprac-25 tnsnames.ora file
(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
(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.
4.There are few different ways to set up client side connect time load balance.
Here is another 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. The ADDRESS_LIST functionality was designed to force a client to try each address in the order of appearance. Using an ADDRESS_LIST clause with LOAD_BALANCE might yield unpredictable results.
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.
Loop.sh
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.
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'
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'
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'
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'
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.
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
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
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
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
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
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.
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'
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'
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'
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'
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.
(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.
> 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’
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’
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’
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’
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)
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).
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:
(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:
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
Listener services output for each nodes listener will confirm the values.
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 2134254Connection load balancing does not work for DEDICATED connections. The workaround is to add dispatchers="(pro=ipc)(dis=0)" in init.ora file.
Transparent Application Failover (TAF) testing
Please familiar yourself with Note 97926.1Failover 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.
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
'shutdown abort' or reboot server.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac4
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)
/
COUNT(*)
----------
60221
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
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)
/
COUNT(*)
----------
60221
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
---------- -------------- ------------------------- ------------------------------ ---------------------
14 20 SELECT BASIC YES
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac2
Troubleshooting:
If there still is a problem, you should send the screen print out together with the following into to the support:
1. RDA output for all nodes Note:314422.1 Remote Diagnostic Agent (RDA) 4.2 - Overview and User Guide
2. Tnsnames.ora, sqlnet.ora files from the client side
3. Sqlnet.log files from both client and server, listener.log file from the server
4. Run testing with Oracle Net Client tracing enabled. In sqlnet.ora file on the client, add :
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT =
TRACE_DIRECTORY_CLIENT =
TRACE_TIMESTAMP_CLIENT = ON
When you finish the testing, please make sure that you comment out the parameters.
产品
| |||
相关内容
返回页首来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-709535/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/38267/viewspace-709535/
996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
