Detailed explanation of connection management in Oracle RAC environment

This article introduces the connection management in Oracle RAC Environment in detail, and describes what is connect time load Balancing, Runtime Connection load Balancing, connect timer, respectively. Connection Failover and Runtime Connection Failover, as well as the TAF, ONS, FCF, FAN, LBA and many other knowledge points involved. This article focuses on connection management in Oracle RAC 11GR2 environments, but also compares Oracle RAC 10GR2/9IR2 to reflect differences in connection management between them.

The so-called "connection management", mainly embodied in the Load balancing and Failover two aspects. The Load balancing and Failover under Oracle RAC 11gr2 are divided into connect time load balancing, Runtime Connection, depending on whether pre-existing connections (such as connections in the connection pool) are used. Load balancing, connect time Connection Failover and Runtime Connection Failover These 4 types, usually with the "Runtime" prefix, refers to the existing situation of the connection, For example, use a connection pool.

First, to introduce Connect time Connection Failover

Connect time Connection Failover refers to Failover that do not obtain an existing connection from a connection pool, but connect directly to an Oracle database. Before Oracle RAC 11gr2, the Connect time Connection Failover is very easy to implement, just specify multiple VIPs in the relevant Tnsnames.ora, and specify Failover=on as well. As shown below:

(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 attempt to connect RAC1-VIP, if not, will try to RAC2-VIP, and then will continue to try, until all the VIP addresses appear in the address_list in full order to finish. The Failover of this client when connecting to an Oracle database is not only applicable to the RAC environment, but also to the Data Guard environment. As shown below:

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 introduced SCAN (single client Access Name), and the client defaults to connect the entire RAC environment through SCAN, as follows is the SCAN schema diagram:

As shown in the figure above, if you use DNS or GNS (Grid naming Service), you can have up to 3 SCAN VIPs and 3 SCAN Listener; If you do not use DNS or GNS, but instead choose to use the Hosts file, only There will be 1 SCAN VIP and one SCAN Listener.

This is assumed to be configured in Tnsnames.ora:

(DESCRIPTION =

(Failover=on)

(address = (PROTOCOL = TCP) (HOST = Myscan) (PORT = 1521))

(Connect_data = (SERVER = dedicated) (service_name =rac11g))

Strictly speaking, only when the RAC environment has more than 1 SCAN VIP, the above failover=on is meaningful-it means that the client when connecting SCAN VIP, if one of the SCAN VIP is not connected, will immediately try another SCAN V Ip.

When using the Hosts file to specify the SCAN VIP, that is, in the entire RAC environment only 1 SCAN VIP, Failover actually exist, but in this case Failover speed will be slower. Because when the SCAN VIP node after the outage, SCAN VIP will and related SCAN Listener together Failover to other nodes, but this Failover takes time, and the client needs to wait for the Failover process is completed before the reconnect Rac.

Second, the next introduction Runtime Connection Failover

Runtime Connection Failover refers to the Failover in the case where the connection already exists. This existing connection may be a connection that is being used in a connection pool, or a connection that is not connected to an Oracle database through a connection pool, directly via a OCI client (such as Sqlplus).

This Runtime Connection Failover refers to the presence of the connection, if there is an unexpected situation in the Oracle database (such as Service downtime, Instance crash, session broken), and how to Failover of the problem.

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

First to introduce TAF. TAF has the following knowledge points that require our attention:

1. It can be defined in the connection string in the client side of the Tnsnames.ora, or it can be defined in the server-side service, except that the service-side setting replaces the setting in the (override) client Tnsnames.ora:

The client can set TAF like this:

(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 this way:

Srvctl Modify service-d rac11g-s email-q true-p basic-e select-z 180-w 5-j The meaning of a specific parameter can be seen in the following comments:

Usage:srvctl Modify service-d <db_unique_name>-S <service_name> [-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 <integer>] [-w <integer>]

-D <db_unique_name> Unique name for the database

-S <service> 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> Role of the service (primary, Physical_standby,

Logical_standby, Snapshot_standby)

-Y <policy> Management policy for the service (AUTOMATIC or MANUAL)

-E <failover type> Failover type (NONE, session, or SELECT)

-M <failover method> Failover method (NONE or BASIC)

W <integer> Failover Delay

-Z <integer> Failover retries