Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand connection management in Oracle RAC environment". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Connection Management in Oracle RAC Environment

The so-called "connection management" is mainly reflected in Load Balancing and Failover. Load Balancing and Failover under Oracle RAC 11gR2 are divided into four types: Connect Time Load Balancing, Runtime Connection Load Balancing, Connect Time Connection Failover and Runtime Connection Failover according to whether pre-existing connections (such as connections in connection pooling) are used. Those with the prefix "connection" refer to the cases where connections already exist, such as connection pooling.

First, let's introduce Connect Time Connection Failover.

Connect Time Connection Failover refers to the Failover when an existing connection is not obtained from the connection pool, but is connected directly to the Oracle database. Before Oracle RAC 11gR2, Connect Time Connection Failover was very easy to implement, as long as multiple vip and FAILOVER=ON were specified in the relevant tnsnames.ora. As follows:

(DESCRIPTION= (FAILOVER=ON) (ADDRESS_LIST= (LOAD_BALANCE=OFF) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC1-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC2-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC3-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC4-vip) (PORT=1521) (CONNECT_DATA= (SERVICE_NAME=RAC10g)

Here, the client process will first try to connect to RAC1-vip, and if it cannot be connected, it will try RAC2-vip, and then it will continue to try until all the vip addresses that appear in ADDRESS_LIST have been tried sequentially. This kind of Failover when the client connects to the Oracle database is suitable not only for the RAC environment, but also for the Data Guard environment. As follows:

DESCRIPTION= (FAILOVER=ON) (ADDRESS_LIST= (LOAD_BALANCE=OFF) (ADDRESS= (PROTOCOL=TCP) (HOST=primary-ip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=standby-ip) (PORT=1521) (CONNECT_DATA= (SERVICE_NAME=service10g)

Oracle RAC 11gR2 introduces SCAN (Single Client Access Name), and the client connects to the entire RAC environment through SCAN by default, as shown in the architecture diagram of SCAN:

As shown in the figure above, if you use DNS or GNS (Grid Naming Service), you can have up to 3 SCAN VIP and 3 SCAN Listener;. If you choose to use the hosts file instead of using DNS or GNS, you will only have 1 SCAN VIP and 1 SCAN Listener.

It is assumed that the configuration in tnsnames.ora is as follows:

(DESCRIPTION = (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP) (HOST = MySCAN) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC11g)

Strictly speaking, the above SCAN VIP makes sense only if the RAC environment has more than one FAILOVER=ON-it means that when the client connects to the SCAN VIP, if one of the SCAN VIP is not connected, it will immediately try the other SCAN VIP.

When the hosts file is used to specify SCAN VIP, that is, when there is only one SCAN VIP in the entire RAC environment, Failover actually exists, but in this case the Failover will be slower. This is because when the node where the SCAN VIP is located goes down, the SCAN VIP and the relevant SCAN Listener will Failover to other nodes as a whole, but the Failover takes time, and the client needs to wait for the Failover process to finish before reconnecting to the RAC.

Second, let's introduce Runtime Connection Failover.

Runtime Connection Failover refers to the Failover where the connection already exists. This existing connection may be a connection in use in the connection pool, or it may be a connection to the Oracle database through an OCI client, such as sqlplus, without going through the connection pool.

This kind of Runtime Connection Failover refers to the problem of how to Failover if an abnormal situation occurs on the Oracle database (such as Service down, Instance crash, Session broken), which leads to the interruption of the existing connection when the connection already exists.

There are two ways to implement Runtime Connection Failover, namely TAF (Transparent Application Failover) and FCF (Fast Connection Failover).

First of all, let's introduce TAF. TAF has the following knowledge points that we should pay attention to:

1. It can be defined in the connection string in the tnsnames.ora on the client or in the service on the server, but the settings on the service will replace the settings in the tnsnames.ora on the override client:

The client can set the TAF as follows:

(DESCRIPTION = (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP) (HOST = MySCAN) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Email) (FAILOVER_MODE= (TYPE=select) (METHOD=basic) (RETRIES=180) (DELAY=5)

The Server side can set TAF as follows:

Srvctl modify service-d RAC11g-s Email-Q TRUE-P BASIC-e SELECT-z 180-w 5-j LONG the meaning of each parameter can be found in the following notes:

Usage: srvctl modify service-d-s [- c {UNIFORM | SINGLETON}] [- P {BASIC | PRECONNECT | NONE}] [- l [PRIMARY] [, PHYSICAL_STANDBY] [, LOGICAL_STANDBY] [ SNAPSHOT_STANDBY] [- y {AUTOMATIC | MANUAL}] [- Q {true | false}] [- x {true | false}] [- j {SHORT | LONG}] [- B {NONE | SERVICE_TIME | THROUGHPUT}] [- e {NONE | SESSION | SELECT}] [- m {NONE | BASIC}] [- z] [- w]-d Unique name for the database-s Service name-c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)-P {NONE | BASIC | PRECONNECT} TAF policy specification-l Role of the service (primary Physical_standby, logical_standby, snapshot_standby)-y Management policy for the service (AUTOMATIC or MANUAL)-e Failover type (NONE, SESSION, or SELECT)-m Failover method (NONE or BASIC)-w Failover delay-z Failover retries-j Connection Load Balancing Goal (SHORT or LONG). Default is LONG. -B Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)-x Distributed Transaction Processing (TRUE or FALSE)-q AQ HA notifications (TRUE or FALSE)-h Print usage

2. When the TYPE of TAF is set to select, simple select operation (excluding select … For update) can achieve "breakpoint continuation", that is, simple select operations can be carried out from the interrupted place after using TAF to implement Failover.

3. TAF cannot resume the DML operation "breakpoint", that is, if a transaction uses TAF to implement Failover, the transaction cannot continue to execute from the interrupted place and needs to be executed from scratch again.

4. TAF is only valid for clients and connection pooling that use OCI connections. OCI connections can be encapsulated on OCI connections. For example, JDBC-OCI driver supports TAF, but JDBC thin driver does not support TAF (because JDBC thin driver is not based on OCI).

Next, before introducing FCF (Fast Connection Failover), we must first introduce FAN (Fast Application Notification).

FAN is an active message notification mechanism in Oracle RAC. When the service down/up,instance down/up appears in the RAC and the node load changes, the Oracle database can publish the information through FAN events, and the clients subscribing to these FAN events can make corresponding actions to respond to these FAN events as soon as they receive these FAN events.

There are two types of FAN events, the first is FAN HA events, and the second is LBA events, where LBA refers to Load Balancing Advisory.

FAN HA events is triggered when service down/up and instance down/up appear in RAC. An example of FAN HA events is as follows:

Event 1: FAN event type: instance Properties: version=1.0 service=PROD database=PROD instance=PROD1 host=node1 status=downEvent 2: FAN event type: service_member Properties: version=1.0 service=ERP database=PROD instance=PROD1 host=node1 status=downEvent 3: FAN event type: service_member Properties: version=1.0 service=ERP database=PROD instance=PROD3 host=node3 status=up

An example of LBA events,LBA events generated after the load change of a node in RAC is as follows:

Event 4: FAN-event type: service_metrics Properties: version=2.0 service=ERP database=PROD instance=PROD1 percent=70 service_quality=GOOD instance=PROD2 percent=30 service_quality=GOOD Event 5: FAN-event type: service_metrics Properties: version=2.0 service=CRM database=PROD instance=PROD2 percent=30 service_quality=GOOD instance=PROD3 percent=70 service_quality=GOOD

The above FAN events may be spread through a variety of channels, including ONS (Oracle Notification Service), AQ (Advanced Queue), PMON and so on. Here are two pictures of the architecture and transmission of FAN events, which visually illustrate the transmission path of FAN events:

Clients that subscribe to FAN HA events include: JDBC Implicit Connection Cache, OCI, ODP.NET Connection Pools, Listener, Server Side Callouts, etc.

Clients that subscribe to LBA events include: JDBC Implicit Connection Cache, ODP.NET Connection Pools, Listener,OCI Session Pools, etc.

After introducing FAN, you can now begin to introduce FCF:FCF, which means Fast Connection Failover, which is actually implemented by the client by subscribing to FAN HA events. Here are two examples of clients implementing FCF by subscribing to FAN HA events:

Example 1: JDBC Fast Connection Failover (FCF)

The JDBC connection here refers to the JDBC thin connection. Because JDBC thin connections are not based on OCI, Runtime Connection Failover in this case cannot use TAF, only FCF. And you need to do the following before you can use FCF properly:

1. Open the implicit connection cache

2. Open the FastConnectionFailoverEnabled

3. It is best to subscribe to remote ONS directly (in previous versions of Oracle 10gR2, you cannot directly subscribe to remote ONS. You can only transfer FAN events by installing ONS locally)

4. It is best to set TCP timeout in the Java program (later on how to adjust TCP timeout in the Oracle database)

The demo code is as follows:

OracleDataSource ods = new OracleDataSource ()... Ods.setUser ("Scott") ods.setPassword ("tiger") ods.setConnectionCachingEnabled (true); ods.setFastConnectionFailoverEnabled (true); ods.setConnectionCacheName ("MyCache") ods.setConnectionCacheProperties (cp); ods.setONSConfiguration ("nodes=racnode1:6201,racnode2.:6201"); ods.setURL ("jdbc:oracle:thin:@sales1-scan:1521/oltp"); / / TCP connect timeoutProperties prop = new Properties (); prop.setProperty ("MinLimit", MIN_CONN); prop.setProperty ("MaxLimit", MAX_CONN) Prop.setProperty ("InitialLimit", INIT_CONN); prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR, "1000"); / / this means that TCP timeout is set to 1000 milliseconds, that is, 1 second ods.setConnectionCacheProperties (prop)

Example 2: ODP.NET Fast Connection Failover (FCF)

For ODP.NET, you can usually use FCF after doing the following:

1. Open the AQ Notification of the corresponding service:

Srvctl modify service-d RAC11g-s Email-Q TRUE

2. Set the aq_tm_processes value to 1

3. Give the specified user the permission to de-queue:

Exec dbms_aqadm.grant_queue_privilege ('DEQUEUE','SYS.SYS$SERVICE_METRICS',)

4. Set HA events=true in the .NET connection string

The demo code is as follows:

/ / C # using System; using Oracle.DataAccess.Client; class ConnectionPoolingSample {static void Main () {OracleConnection con = new OracleConnection (); / / Open a connection using ConnectionString attributes / / related to connection pooling. Con.ConnectionString = "User Id=scott;Password=tiger;Data Source=crm;" + "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" + "HA events=true", "Incr Pool Size=5; Decr Pool Si=2"; con.Open (); Console.WriteLine ("Connection pool successfully created"); / / Close and Dispose OracleConnection object con.Close (); con.Dispose (); Console.WriteLine ("Connection is placed back into the pool.");}}

There is a big difference between FCF and TAF is that even if it is a simple select operation, FCF can not do the "breakpoint continuation" like TAF. For connection pooling with configured FCF, when it receives a FAN HA events containing instance/service downtime, the connections associated with this instance/service that were originally in the cache connection pool will be marked as invalid and these connections will be cleared, and the transaction using these connections will be aborted and rolled back immediately. When the application catches the error message generated by the aborted transaction, it either returns the relevant error directly to the end user, or re-obtains a valid connection from the connection pool and re-executes the aborted transaction.

With FCF enabled, if the connection error is returned to the end user, how do you determine the source of the error message (that is, is it the error returned by FCF)? It's very simple, just use isFatalConnectionError (SQLException e) to judge, the demo code is as follows:

Try {conn = getConnection (); / / do the relevant work after obtaining the connection here} catch (SQLException e) {handleSQLException (e)}. Void handleSQLException (SQLException e) {if (OracleConnectionCacheManager.isFatalConnectionError (e)) ConnRetry = true; / / here means to catch the error returned by FCF. Third, introduce Connect Time Load Balancing.

Connect Time Load Balancing refers to the Load Balance when you connect to an Oracle database directly instead of getting an existing connection from the connection pool. Connect Time Load Balancing is subdivided into two types, namely, Connect Time Load Balancing on the client side and Connect Time Load Balancing on the Server side.

Prior to Oracle RAC 11gR2, client-side Connect Time Load Balancing was very easy to implement, as long as multiple vip and LOAD_BALANCE=ON were specified in the relevant tnsnames.ora. As follows:

(DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC1-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC2-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC3-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC4-vip) (PORT=1521) (CONNECT_DATA= (SERVICE_NAME=RAC10g)

In this way, when connecting, the client will randomly choose one of the above four VIP addresses to connect to the Oracle database to achieve the purpose of Load Balance.

As mentioned earlier, SCAN (Single Client Access Name) is introduced into Oracle RAC 11gR2, and the client connects the entire RAC environment through SCAN by default. If DNS or GNS (Grid Naming Service) is used, then there can be up to 3 SCAN VIP and 3 SCAN Listener;. If you choose to use a hosts file instead of using DNS or GNS, there will only be 1 SCAN VIP and 1 SCAN Listener.

Let's assume that you configure it like this in tnsnames.ora:

(DESCRIPTION = (LOAD_BALANCE=ON) (ADDRESS = (PROTOCOL = TCP) (HOST = MySCAN) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC11g)

Strictly speaking, the above SCAN VIP makes sense only when there is more than one SCAN VIP in the RAC environment-it means that when the client connects to the Oracle 11gR2 RAC, it randomly selects one of the three SCAN VIP to connect, so the client Connect Time Load Balancing of Oracle 11gR2 RAC is actually for SCAN VIP, rather than the VIP that is directly aimed at RAC nodes like Oracle RAC 10gR2 / 9iR2 (there is no VIP in Oracle RAC 9iR2) At this point Connect Time Load Balancing is for public ip).

When the hosts file is used to specify the SCAN VIP, the client Connect Time Load Balancing does not actually exist because there is now only one SCAN VIP for the entire RAC environment.

Now let's introduce Connect Time Load Balancing on the Server side. The Connect Time Load Balancing on the Server side is relatively more complex, which is described below for different versions of the Oracle database.

The first thing to note is that the Server side Connect Time Load Balancing of both Oracle RAC 9iR2/10gR2 and Oracle RAC 11gR2 is implemented through the combined use of local_listener and remote_listener.

Let's first introduce the Server Connect Time Load Balancing under Oracle RAC 9iR2:

The assumption here is that it is a 4-node connection string in Oracle RAC 9iR2 divintnsnames.ora as follows:

(DESCRIPTION= (FAILOVER=ON) (ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC1-ip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC2-ip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC3-ip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC4-ip) (PORT=1521) (CONNECT_DATA= (SERVICE_NAME=RAC9i)

Then add the following settings to the tnsnames.ora of each node:

LISTENER_RAC1 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-ip) (PORT = 1521)) LISTENER_RAC2 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-ip) (PORT = 1521) LISTENER_RAC3 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC3-ip) (PORT = 1521) LISTENER_RAC4 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC4-ip) (PORT = 1521) LISTENERS_RAC = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-ip) (PORT = 1521) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-ip) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC3-ip) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC4-ip) (PORT = 1521)

Then you only need to add the following settings to the initialization parameter file to implement Connect Time Load Balancing on Server:

RAC1.local_listener=LISTENER_RAC1RAC2.local_listener=LISTENER_RAC2RAC3.local_listener=LISTENER_RAC3RAC4.local_listener=LISTENER_RAC4*.remote_listener=LISTENERS_RAC

After making the above settings, the listener of the above four nodes actually knows not only the load of this node, but also the load of the rest of the nodes. So when you first pass through the Connect Time Load Balancing of the first layer of the client, for example, here you are randomly connected to the listener (that is, LISTENER_RAC2) on the second node, and when the LISTENER_RAC2 finds that its load is high, it is possible to transfer your connection request (redirect) to the listener of the other nodes with lower load-this is the so-called layer 2 Load Balancing, that is, the Connect Time Load Balancing on the server side.

The Connect Time Load Balancing on the server side of the Oracle RAC 9iR2 is based on the load (CPU runqueue-based load) of the CPU of each node or the number of session connected by each node. We can control which judgment basis is adopted by the Oracle RAC 9iR2 database through the parameter prefer_least_loaded_node_ in the listener.ora of the corresponding node. The default value of prefer_least_loaded_node_ is on, which means that Listener will redirect the connection to a node with a lower CPU load, that is, in this case, the load is judged by the CPU load of each node. If it is set to off, it means that Listener will consider the number of connected session of each node when transferring (redirect) connections and will try its best to ensure a balanced number of connected session of each node, that is, in this case, the load is judged by the number of connected session of each node.

Next, let's take a look at the Server Connect Time Load Balancing under Oracle RAC 10gR2. Connect Time Load Balancing on Server in Oracle RAC 10gR2 is also implemented through the joint use of local_listener and remote_listener, but VIP is introduced into Oracle RAC 10gR2, so local_listener and remote_listener must listen to VIP instead of public ip as Oracle RAC 9iR2 does.

Let's assume that it is a 4-node connection string in Oracle RAC 10gR2 < tnsnames.ora as follows (note that the connection address here is already VIP):

(DESCRIPTION= (FAILOVER=ON) (ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC1-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC2-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC3-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=RAC4-vip) (PORT=1521) (CONNECT_DATA= (SERVICE_NAME=RAC10g)

Then add the following settings to the tnsnames.ora of each node (note that VIP is already listening here):

LISTENER_RAC1 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-vip) (PORT = 1521)) LISTENER_RAC2 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-vip) (PORT = 1521) LISTENER_RAC3 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC3-vip) (PORT = 1521) LISTENER_RAC4 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC4-vip) (PORT = 1521) LISTENERS_RAC = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-vip) (PORT = 1521) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-vip) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC3-vip) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = RAC4-vip) (PORT = 1521)

Then you only need to add the following settings to the initialization parameter file to implement Connect Time Load Balancing on Server:

RAC1.local_listener=LISTENER_RAC1RAC2.local_listener=LISTENER_RAC2RAC3.local_listener=LISTENER_RAC3RAC4.local_listener=LISTENER_RAC4*.remote_listener=LISTENERS_RAC

After making the above settings, like Oracle RAC 9iR2, you can also implement Connect Time Load Balancing on the Server side. It's just that the basis for judging the load has changed here. (note: if the client cannot resolve a hostname such as RAC1-vip, it is likely to report an ORA-12545 error when connecting. The VIP address should be used instead of the hostname in the TNS configuration corresponding to local_listener. The hostname is written here only for demonstration purposes.)

Oracle 10g introduces Service, so in Oracle 10g, the basis for determining load is tied to Service. The basis for judging the load of the Server Connect Time Load Balancing in Oracle RAC 10gR2 is determined by the combination of the relevant service parameters CLB_GOAL and GOAL.

The load in Oracle RAC 10gR2 can be viewed through v$servicemetric:

SQL > desc v$servicemetric Name Type Nullable Default Comments-BEGIN_TIME DATE Y END_TIME DATE Y INTSIZE_CSEC NUMBER Y GROUP_ID NUMBER Y SERVICE_NAME_HASH NUMBER Y SERVICE_NAME VARCHAR2 (64) Y CTMHASH NUMBER Y ELAPSEDPERCALL NUMBER Y CPUPERCALL NUMBER Y DBTIMEPERCALL NUMBER Y CALLSPERSEC NUMBER Y DBTIMEPERSEC NUMBER Y GOODNESS NUMBER Y DELTA NUMBER Y FLAGS NUMBER Y

Each service corresponds to two records in v$servicemetric, with one record sampled every 5 seconds and the other sampled every 60 seconds.

The load of each service is measured here, mainly through the three columns GOODNESS, DELTA, and FLAGS. Here is their respective meaning:

GOODNESS indicates the likelihood that this node will become the target node of the Server-side Connect Time Load Balancing, and the higher this value, the lower the probability. That is, the higher the GOODNESS of the service on a node, the heavier the load on the node, the less likely it is that the node will become a target node for the Connect Time Load Balancing on the Server side.

DELTA represents the estimate of the increase in load when a node adds an additional session.

FLAGS is a marker bit, and its values mean the following:

0-all good 1-blocked 2-crossed threshold 4-goodness unknown (usually when no sessions connected)

The CLB_GOAL corresponding to each service actually represents Client Load Balance Goal, whose value is either LONG or SHORT, and the default value is LONG.

The difference between LONG and SHORT is that LONG is the default value for CLB_GOAL and is usually used for connections that need to be maintained for a long time, such as third-party connection pooling or SQL*Form applications, while SHORT is usually used for applications with short connection duration, and if you use a connection pool that supports subscription LBA (Load Balancing Advisory), you should set the value of CLB_GOAL to SHORT.

If the CLB_GOAL of a service is set to LONG, it means that the load of the node where the service is located is measured by the number of session connected to the node, and the setting of another parameter GOAL corresponding to CLB_GOAL will no longer take effect.

If you set the CLB_GOAL of a service to SHORT, it means that the load of this service is measured by LBA, and when judging the load according to LBA, it can be subdivided into SERVICE_TIME or THROUGHPUT according to the setting value of the corresponding service's GOAL. That is, the GOAL corresponding to each service actually represents LBA GOAL, and its value is either THROUGHPUT or SERVICE_TIME, or the default value for NONE,GOAL is NONE. That is, when you set CLB_GOAL to SHORT, in this case, the basis for the Server Connect Time Load Balancing to judge the load is determined by the setting of GOAL.

The differences between the three values of GOAL, THROUGHPUT, SERVICE_TIME, and NONE are:

THROUGHPUT: indicates that the load is judged by throughput (THROUGHPUT), which is usually used in systems where concurrent transaction has similar completion times and similar completion rates, such as online trading systems.

SERVICE_TIME: indicates that the load is judged on the basis of response time (response time), which is usually used in concurrent transaction systems with different completion times and different completion rates, such as online shopping systems, where different people complete an online shopping, and the products purchased and the time spent may vary greatly.

NONE: indicates that LBA is not enabled.

If we combine the CLB_GOAL and GOAL of service, as well as v$servicemetric, we can sum up the basis for judging the load of the Connect Time Load Balancing on the Server side of Oracle RAC 10gR2:

1. The Connect Time Load Balancing on the Server side of Oracle RAC 10gR2 by default determines the load based on the number of session connected to each node, that is, when CLB_GOAL is the default LONG, the GOODNESS=number of connected sessions,DELTA=1 of v$servicemetric corresponds to service. Note that LBA is not enabled at this time.

2. In Oracle RAC 10gR2, if the CLB_GOAL of service is set to SHORT and GOAL is set to THROUGHPUT or SERVICE_TIME, it means that the Connect Time Load Balancing on the Server side judges the node load on the basis of LBA. At this point, if GOAL is set to THROUGHPUT, the GOODNESS value of the corresponding service of v$servicemetric is calculated based on CPUPERCALL and DBTIMEPERCALL; if GOAL is set to SERVICE_TIME, the GOODNESS value of the corresponding service of v$servicemetric is calculated based on CALLSPERSEC and DBTIMEPERSEC.

Next, take a look at the Server Connect Time Load Balancing under Oracle RAC 11gR2:

The Server Connect Time Load Balancing under Oracle RAC 11gR2 is similar to the Server Connect Time Load Balancing under Oracle RAC 10gR2, except that because SCAN is introduced into Oracle RAC 11gR2, remote_listener should be set to SCAN:port in Oracle RAC 11gR2 environment.

Suppose it is a 4-node Oracle RAC 11gR2 environment, and the connection string in tnsnames.ora is as follows:

(DESCRIPTION = (FAILOVER=ON) (LOAD_BALANCE=ON) (ADDRESS = (PROTOCOL = TCP) (HOST = MySCAN) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC11g)

Then add the following settings to the tnsnames.ora of each node (note that what is listening here is the VIP of each node):

LISTENER_RAC1 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-vip) (PORT = 1521)) LISTENER_RAC2 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-vip) (PORT = 1521) LISTENER_RAC3 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC3-vip) (PORT = 1521) LISTENER_RAC4 = (ADDRESS = (PROTOCOL = TCP) (HOST = RAC4-vip) (PORT = 1521))

Then you only need to add the following settings to the initialization parameter file to implement Connect Time Load Balancing on Server:

RAC1.local_listener=LISTENER_RAC1RAC2.local_listener=LISTENER_RAC2RAC3.local_listener=LISTENER_RAC3RAC4.local_listener=LISTENER_RAC4*.remote_listener= MySCAN:1521

When the above settings are made, the Connect Time Load Balancing on the Server side of Oracle 11gR2 RAC will be ready. At this point, all SCAN Listener actually know the load of all RAC nodes. When you first go through the Connect Time Load Balancing of the first layer of the client, for example, after randomly connecting to the SCAN Listener corresponding to the second SCAN VIP, the SCAN Listener will select a RAC node with a lower actual load, and then transfer the connection request (redirect) to the Local Listener of the low-load RAC node-- this is the Connect Time Load Balancing on the server side of the Oracle RAC 11gR2.

In fact, local_listener and remote_listener support complex concatenation string writing. Therefore, you can set local_listener and remote_listener directly in the initialization parameters, rather than in the tnsnames.ora under $ORACLE_HOME/network/admin.

Let's look at an example of a two-node Oracle 11gR2 RAC. The hosts file is used in this environment, and the content of the hosts file is as follows:

10.1.15.64 P550-05-LA 10.1.15.84 P550-05-LA-vip 9.2.1.64 P550-05-LA-priv 10.1.15.65 P550-05-LB 10.1.15.85 P550-05-LB-vip 9.2.1.65 P550-05-LB-priv10.1.15.86 nbsdev-scan

As you can see from the above, the vip of node 1 is 10.1.15.84, the vip of node 2 is 10.1.15.85, and the SCAN vip of the entire RAC environment is 10.1.15.86.

Log in to node 1 first and take a look at the settings of local_listener and remote_listener on node 1: SQL > show parameter instance_name

NAME TYPE VALUE-- instance_name string NBSDEV1

SQL > show parameter local_listener

NAME TYPE VALUE---local_listener string ( DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=10.1.15.84) (PORT=1522)

SQL > show parameter remote_listener

NAME TYPE VALUE-- remote_listener string nbsdev-scan:1522

Log in to node 2 again and take a look at the settings of local_listener and remote_listener on node 2: SQL > show parameter instance_name

NAME TYPE VALUE-- instance_name string NBSDEV2

SQL > show parameter local_listener

NAME TYPE VALUE---local_listener string ( DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=10.1.15.85) (PORT=1522)

SQL > show parameter remote_listener

NAME TYPE VALUE-- remote_listener string nbsdev-scan:1522

The Connect Time Load Balancing on the server side of the above environment has actually been configured, but we can see from the following that we have not configured the relevant local_listener and remote_listener in the tnsnames.ora under $ORACLE_HOME/network/admin:

Ora11g:/nbsdu01/app/oracle/product/11.2/network/admin > cat tnsnames.ora

# tnsnames.ora Network Configuration File: / nbsdu01/app/oracle/product/11.2/network/admin/tnsnames.ora# Generated by Oracle configuration tools.NBSDEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = nbsdev-scan) (PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NBSDEV)) 4. Finally introduce Runtime Connection Load Balancing

Runtime Connection Load Balancing refers to the Connection Load Balancing when an existing connection is obtained from a connection pool.

In the past, whether it was Oracle RAC 9iR2/10gR2 or Oracle RAC 11gR2, in the case of a connection pool, a simple Connect Time Load Balancing on the server side does not guarantee that when an application needs to obtain an existing connection from the connection pool, the connection will point to the node with a lower load. Because the connection obtained by the application from the connection pool is probably the connection formed when the connection pool is initialized, it only reflects the load of each node at that time point of connection pool initialization, and with the passage of time, the load of each node may have changed greatly, so in this case, the connection of the connection pool is probably not the real Load Balance.

Actually, FAN is designed to solve the above problems. By subscribing to FAN HA events, a connection pool that supports FAN events ensures that when an application needs to obtain an existing connection from the connection pool, the connection must be a valid connection and will not point to nodes where the service goes down or the instance crashes (as mentioned earlier, this is achieved through FCF: when the connection pool that supports FAN events receives a FAN HA events containing instance/service failures Connections associated with this cache that were originally in the connection pool will immediately be marked as invalid and these connections will be cleared) On the other hand, the connection pool that can support FAN events can know the actual load of each RAC node in near real time by subscribing to LBA events, so when the application needs to get an existing connection from the connection pool, the connection pool can provide users with a real low-load RAC node, thus realizing the real Runtime Connection Load Balancing.

Now let's look at two examples of implementing Runtime Connection Load Balancing by subscribing to LBA events.

Example 3: JDBC Runtime Connection Load Balancing

The JDBC connection here refers to the JDBC thin connection. To implement JDBC Runtime Connection Load Balancing, you only need to do the following two steps:

1. First of all, set up the JDBC FCF as in "example 1: JDBC Fast Connection Failover (FCF)".

2. Enable LBA events:

Srvctl modify service-d RAC11g-s Email-B SERVICE_TIME-j SHORT

Here, first set CLB_GOAL to SHORT, and then set GOAL to SERVICE_TIME, both of which are indispensable. The detailed meanings of the values of CLB_GOAL and GOAL have been explained in detail in Connect Time Load Balancing, so I won't repeat them here.

Example 4: ODP.NET Runtime Connection Load Balancing

The enabling of Runtime Connection Load Balancing for ODP.NET is similar to the steps in "example 2: ODP.NET Fast Connection Failover (FCF)". You only need to do the following 4 steps. Note that steps 1 and 4 are different from the corresponding steps in "example 2: ODP.NET Fast Connection Failover (FCF)":

1. Open the AQ Notification of the corresponding service, and set both CLB_GOAL and GOAL:

Srvctl modify service-d RAC11g-s Email-Q TRUE-B SERVICE_TIME-j SHORT

2. Set the aq_tm_processes value to 1

3. Give the specified user the permission to de-queue:

Exec dbms_aqadm.grant_queue_privilege ('DEQUEUE','SYS.SYS$SERVICE_METRICS',)

4. Set Load Balancing=true in the .NET connection string, as shown below:

Con.ConnectionString = "User Id=user_name;Password=password;Data Source=odpapp;" + "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" + "Load Balancing=true;Incr Pool Size=5;Decr Pool Size=2"

So far, we have described the connection management in RAC environment in detail.

At the end of this article, let's explain how to set up TCP timeout in the Oracle database.

1. Oracle database 11.2.0.1 on 32-bit Windows the default operating system TNS connects to timeout for about 20 seconds:

16:27:26 SQL > conn scott/tiger@cuihua112; ERROR:ORA-12170: TNS: connection timeout 16:27:49 SQL >

As you can see here, the interval between the start of the connection and the connection timeout is 23 seconds. Excluding the time taken to enter the above connection string "conn scott/tiger@cuihua112", we can know that the default operating system TNS of Oracle database 11.2.0.1 on 32-bit Windows connects to timeout in about 20 seconds.

2. Modify the sqlnet.ora file on client, and change the timeout time of TNS connection to 5 seconds (this is achieved by setting SQLNET.OUTBOUND_CONNECT_TIMEOUT):

# sqlnet.ora Network Configuration File: C:\ app\ cuihua\ product\ 11.2.0\ dbhome_1\ network\ admin\ sqlnet.ora# Generated by Oracle configuration tools.# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.OUTBOUND_CONNECT_TIMEOUT = 5NAMES.DIRECTORY path = (TNSNAMES, EZCONNECT)

As can be seen from the following results, the interval between the start of the connection and the connection timeout is 7 seconds. Excluding the time spent entering the above connection string "conn scott/tiger@cuihua112", we can see that the above 5-second timeout setting does take effect.

16:28:34 SQL > conn scott/tiger@cuihua112;ERROR:ORA-12170: TNS: connection timeout 16:28:41 SQL >

3. Comment out the above SQLNET.OUTBOUND_CONNECT_TIMEOUT = 5, and set the TNS connection timeout time to 15 seconds in the connection string of cuihua112 in tnsnames.ora:

CUIHUA112 = (DESCRIPTION = (CONNECT_TIMEOUT=5) (RETRY_COUNT=2) (ADDRESS = (PROTOCOL = TCP) (HOST = 172.20.190.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cuihua112)

As can be seen from the following results, the interval between the start of the connection and the connection timeout is 17 seconds. Excluding the time taken to enter the above connection string "conn scott/tiger@cuihua112", we can see that the above 15-second timeout setting does take effect.

16:31:08 SQL > conn scott/tiger@cuihua112;ERROR:ORA-12170: TNS: connection timeout 16:31:25 SQL >

4. Enable the connection string of SQLNET.OUTBOUND_CONNECT_TIMEOUT = 5 and the above cuihua112 at the same time. You can see from the result that the setting in tnsnames.ora replaces the setting of TNS connection timeout in sqlnet.ora. That is, when enabled at the same time, the current TNS connection timeout setting is still 15 seconds.

16:33:12 SQL > conn scott/tiger@cuihua112;ERROR:ORA-12170: TNS: connection timeout 16:33:29 SQL >

Here you can see that the interval between the start of the connection and the connection timeout is 17 seconds. Excluding the time taken to enter the above connection string "conn scott/tiger@cuihua112", you can see that the 15-second timeout setting is in effect.

5. Set the TNS connection timeout time to 40 seconds in the cuihua112 connection string of tnsnames.ora, which has exceeded the default timeout value of TNS connection. As you can see from the following tests, the Oracle database will follow the setting in tnsnames.ora (of course, the prerequisite here is that the CONNECT_TIMEOUT setting of a single connection does not exceed the default timeout value of the operating system TNS connection. If so, the CONNECT_TIMEOUT setting is invalid, but the RETRY_COUNT setting is still valid).

CUIHUA112 = (DESCRIPTION = (CONNECT_TIMEOUT=10) (RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP) (HOST = 172.20.190.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cuihua112)

Here, CONNECT_TIMEOUT is set to 10 seconds and RETRY_COUNT is set to 3, which actually sets the TNS connection timeout time to 40 seconds.

16:52:52 SQL > conn scott/tiger@cuihua112;ERROR:ORA-12170: TNS: connection timeout 16:53:33 SQL >

Here you can see that the interval between the start of the connection and the connection timeout is 41 seconds. Excluding the time taken to enter the above connection string "conn scott/tiger@cuihua112", you can see that the 40-second timeout setting is in effect.

This is the end of the content of "how to understand connection Management in Oracle RAC Environment". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.