Oracle RAC series: test the RAC Database Cluster Function

Oracle RAC is a cluster database that enables seamless load balancing and failover. How can we know that the RAC database has implemented these functions? We will test the functions below.

I. Server Load balancer Testing
The server Load balancer of the RAC database is the Server Load balancer for database connections. When a new session is connected to the RAC database, requests are distributed to any node in the cluster using the specified allocation algorithm, this is the function completed by the RAC database. Server Load balancer is divided into two types in RAC: Server Load balancer based on client connection and Server Load balancer Based on server connection.

1. RAC Client Server Load balancer
The server Load balancer instance connected to the client is easy to configure and has nothing to do with the instance load and listener of the RAC database. Therefore, you do not need to configure any Server Load balancer instance on the cluster node, as long as the tnsnames. add the Server Load balancer policy configuration in the ora file. Here we will introduce the Linux client as an example.

1) modify the/etc/hosts file
Edit the/etc/hosts file and add the IP address information related to the RAC database. For example:
192.168.12.231 node-rac1
192.168.12.20.node-rac2
192.168.12.230 node-vip1
192.168.12.240 node-vip2

2) view the service_names of the RAC Database
[Oracle @ node-rac1 ~] $ Sqlplus "/as sysdba"
SQL * Plus: Release 11.1.0.6.0-Production on Sun Sep 12 22:05:53 2010
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
NAME TYPE VALUE
-------------------------------------------------
Service_names string racdb
It should be noted that when configuring RAC Server Load balancer, the client is connected to the service name of the RAC database, not the Instance name, that is, the SERVICE_NAME must be set to "SERVICE_NAME = racdb"

3) modify the configuration file tnsnames. ora of the Oracle client.
RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip2) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip1) (PORT = 1521 ))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
The configuration file is described as follows:
Required LOAD_BALANCE = yes, indicating to enable connection load balancing. "LOAD_BALANCE = no" by default. Therefore, to configure Server Load balancer, you must add "LOAD_BALANCE = yes ". After Server Load balancer is enabled, SQLNet randomly selects any listener in the ADDRESS_LIST list and distributes requests to the listener. In this way, Server Load balancer is completed. If "LOAD_BALANCE = no", SQLNet selects the listener according to the order in the ADDRESS_LIST list. As long as the listener is normal, it will always be used.
Required SERVICE_NAME = racdb. This "racdb" is the service name of the RAC database, not the Instance name.

4) test Server Load balancer on the client
Start a sqlplus connection on the client and perform the following operations:
[Oracle @ client ~] $ Sqlplus system/xxxxxx @ racdb
SQL * Plus: Release 11.1.0.7.0-Production on Sun Sep 12 21:24:55 2010
Copyright (c) 1982,200 8, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
SQL> show parameter instance_name
NAME TYPE VALUE
-------------------------------------------------------
Instance_name string racdb1
Start the second sqlplus connection and perform the following operations:
[Oracle @ client ~] $ Sqlplus system/xxxxxx @ racdb
SQL * Plus: Release 11.1.0.7.0-Production on Sun Sep 12 21:31:53 2010
Copyright (c) 1982,200 8, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
SQL> show parameter instance_name

NAME TYPE VALUE
----------------------------------------------------
Instance_name string racdb2
In this way, multiple sqlplus connections are opened one after another. We can see that each connection to the instance changes between racdb1 and racdb2, thus implementing Load Balancing for the RAC database connection.

2. Server Load balancer
The Client Server Load balancer solves the load problem of connecting to the database. However, because the connection is initiated by the client, it does not know the busy status and connection information of each node in the RAC database cluster, therefore, nodes with a large load will still add new connections, which may cause the RAC node to fail to achieve load balancing. Fortunately, from Oracle 10 Gb, Server Load balancer can be based on the load and connections of each node in RAC, allocate new requests to nodes with low load and few connections in the cluster. This fundamentally achieves database load balancing, in addition, Server Load balancer connected to the client can be used together with Server Load balancer without affecting each other.
The load of each cluster node is regularly updated by the PMON process. The PMON process writes the load information and connections of each node in the cluster to service_register every three seconds. When the load of the node changes, it immediately notifies the listener, finally, the listener determines the node to which the new client connection is allocated. In this way, the RAC database implements real load balancing.
Server Load balancer configuration is also very simple. You only need to add a configuration to listen to each node in the tnsnames. ora file of each node, and then set remote_listener in the initialization parameter.
1) Modify tnsnames. ora on the server
You only need to add the following content:
LISTENERS_RACDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip2) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip1) (PORT = 1521 ))
)
 
2) modify the remote_listener parameter.
View the remote_listener parameter of the RAC database:
SQL> show parameter remote_listener
NAME TYPE VALUE
-------------------------------------
Remote_listener string LISTENERS_RACDB
As you can see, remote_listener has been set to "LISTENERS_RACDB.
If the value of remote_listener is null, you can run the following command to modify the remote_listener parameter for each instance:
SQL> alter system set remote_listener = 'listeners _ racdb' sid = 'node-rac1 ';
SQL> alter system set remote_listener = 'listeners _ racdb' sid = 'node-rac2 ';
In this way, the Server Load balancer configuration is complete.

2. transparent application failure switchover Test
Transparent Application failed to switch Transparent Application Failover, TAF), which is a function of the client. TAF has two meanings: failure switching means that the client connects to an instance. If the connection fails, the client can connect to another instance; A transparent application means that the client application can automatically reconnect to another database instance after the connection fails. This process is invisible to the application.
To use the TAF function, you only need to modify the settings in the tnsnames. ora file of the client. In combination with the Client Server Load balancer function described earlier, a client with the Server Load balancer and TAF functions is set as follows:
RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip2) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip1) (PORT = 1521 ))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(FAILOVER_MODE =
(TYPE = SELECT)
(MODE = BASIC)
(RETRY = 3)
(DEALY = 5)
)
)
)
The meanings of several parameters are as follows:
Specify TYPE, which is used to specify the FAILOVER_MODE TYPE. There are three optional types: SESSION, SELECT, and NONE.
Reset SESSION indicates that when a connected SESSION instance fails, the system can automatically switch the SESSION to another available instance, and the application does not need to initiate a connection request again, however, the SQL statement being executed when the instance fails must be re-executed.
Explain SELECT: If the connected instance fails, the SELECT Operation will continue with the cursor and the previous snapshot. Other operations must be re-executed.
Deny NONE. This is the default value of the client, indicating that the SQL take-over function is disabled.
Connection MODE, which indicates the connection MODE. There are two types: BASIC and PRECONNECT.
Primary BASIC indicates that only one node is connected when an initial connection is established, and the standby node is connected only when a node fails.
 PRECONNECT indicates that the master node and slave node are connected when an initial connection is established.
Failed RETRY: indicates the number of times the failed switchover function tries to connect to the slave node after the current node fails.
Interval DELAY: the number of seconds that a user attempts to wait.

After setting the client listener, restart the client service and perform the following operations:
[Oracle @ client ~] $ Sqlplus system/xxxxxx @ racdb
SQL * Plus: Release 11.1.0.7.0-Production on Sun Sep 12 23:23:15 2010
Copyright (c) 1982,200 8, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
SQL> COLUMN instance_name FORMAT a10
SQL> COLUMN host_name FORMAT a10
SQL> COLUMN failover_method FORMAT a15
SQL> COLUMN failed_over FORMAT a10
SQL> SELECT instance_name, host_name, NULL AS failover_type, NULL AS failover_method, NULL AS failed_over FROM v $ instance UNION SELECT NULL, NULL, failover_type, failover_method, failed_over FROM v $ session WHERE username = 'system ';

INSTANCE _ NAME HOST_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------------------------------------------------------------------
Racdb2 node-rac2 select basic no.
At this point, continue to enable this connection, and then execute the following statement on any node of the RAC database:
[Oracle @ node-rac2 ~] $ Srvctl stop instance-d racdb-I racdb2
Close the racdb2 instance of the node-rac2 node and continue executing the same statement as the previous SQL command, the result is as follows:
INSTANCE _ NAME HOST_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------------------------------------------------------------------
Racdb1 node-rac1 SELECT BASIC YES
From the output, we can see that the above SQL session has been switched to the node-rac1 instance racdb1, which is to achieve the fault automatic switch function.
So far, the functional test of the RAC database has been completed.
 

This article from the "Technical Achievement dream" blog, please be sure to keep this source http://ixdba.blog.51cto.com/2895551/988650