Oracle RAC Functional Testing

Oracle RAC is a clustered database that allows for seamless failover of load balancing and failures. How do you know that the RAC database has implemented these features, and then perform a functional test on this?

One, load balancing test
The load balancing of a RAC database is the load balancing of a database connection, and when a new session is connected to the RAC database, the request is assigned to any node of the cluster through the specified allocation algorithm, which is the function of the RAC database. There are two kinds of load balancing in RAC: One is load balancing based on client connection, and one is server-side load balancing.

1. RAC Client Load Balancing
The load balancing of client connections is very simple and does not have anything to do with the instance payload and monitoring of the RAC database, so there is no need to make any settings on the cluster nodes as long as the load balancing policy configuration is added to the Tnsnames.ora file on the client machine. Here is an example of a Linux client.

(1) Modify the/etc/hosts file
Edit the/etc/hosts file to add the IP address information associated with the RAC database, for example:
192.168.12.231 NODE-RAC1
192.168.12.232 NODE-RAC2
192.168.12.230 Node-vip1
192.168.12.240 NODE-VIP2

(2) View service_names of the RAC Database
[[email protected] ~]$ sqlplus "/as sysdba"
Sql*plus:release 11.1.0.6.0-production on Sun Sep 22:05:53
Copyright (c) 1982, Oracle.  all rights reserved.connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-production
with the partitioning, Real A Pplication Clusters, OLAP, Data Mining
and Real application testing options
name        &n bsp;  type        value
------------------------------------     -------------
service_names    string        racdb
   It is important to note that when configuring RAC load Balancing, the client connects to the service name of the RAC database instead of the instance name, that is, service_name must be set to "service_name = racdb"

(3) Modify the configuration file of the Oracle client Tnsnames.ora
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 description of this configuration file is as follows:
? Load_balance = yes, which means that connection load balancing is enabled. By default, "Load_balance = no", so if you want to configure load balancing, you must add the setting "Load_balance = yes". When load balancing is enabled, sqlnet randomly selects any listener in the Address_list list and distributes the request to this listener to complete load balancing. If "load_balance = no", then Sqlnet will select the listener in the order of the Address_list list, as long as the listener is working properly.
? Service_Name = racdb, this "racdb" is the service name of the RAC database, not the instance name.

(4) Test load Balancing on the client
To open a Sqlplus connection on the client, do the following:
Sql*plus:release 11.1.0.7.0-production on Sun Sep 12 21:24:55 2010
Copyright (c) 1982, the Oracle. All rights reserved.
Connected to:
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
Continue to open the second sqlplus connection and do the following:
Sql*plus:release 11.1.0.7.0-production on Sun Sep 12 21:31:53 2010
Copyright (c) 1982, the Oracle. All rights reserved.
Connected to:
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 in succession, and you can see that each connection to the instance changes between RACDB1 and RACDB2, thus achieving load balancing of the RAC database connection.

2. Server-Side Load balancing
Client-side load balancing solves the load problem of connecting to the database, but because the connection is initiated by the client, it does not know the busy state and connection information of each node in the RAC DB cluster, so a larger load node will still add new connections, which may result in the inability of the RAC node to truly load-balance. Fortunately, starting with Oracle 10g, server-side load Balancing can allocate new requests to nodes with lower load and fewer connections in the cluster based on the load and connections of each node in the RAC, thus fundamentally achieving load balancing of the database. And the load balancing of the client connection and server-side load balancing can be used in conjunction with each other.
The load situation for each cluster node is updated periodically by the Pmon process. The Pmon process writes the load information and the number of connections per node in the cluster to service_register every 3 seconds, and when the load of the node changes, the listener is notified immediately, and finally the listener decides which node to assign the new client connection to, in this way, The RAC database achieves true load balancing.
Server-side load balancing configuration is also very simple, simply add a configuration for each node in the Tnsnames.ora file to listen to the connection to each node, and then set the Remote_listener in the initialization parameters.
(1) Modify the server-side Tnsnames.ora
Just add the following:
LISTENERS_RACDB =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip2) (PORT = 1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = node-vip1) (PORT = 1521))
)

(2) Modify the parameter Remote_listener
To view the parameter Remote_listener for a RAC database:
Sql> Show Parameter Remote_listener
NAME TYPE VALUE
---------------       -----------   -----------
Remote_listener string listeners_racdb
As you can see, the Remote_listener has been set to "listeners_racdb".
If the value of Remote_listener is empty, you can modify the Remote_listener parameter for each instance by using the following command:
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, server-side load balancing is configured to complete.

Second, transparent application failure switch test
Transparent application failed switchover (Transparent application Failover,taf), which is a function of the client. TAF contains two meanings: failover means that the client connects to an instance, and if the connection fails, it can be connected to another instance; a transparent application is a client application that can automatically reconnect to another DB instance after a failed connection, which is not visible to the application.
To use the TAF feature, simply modify the settings in the client's Tnsnames.ora file, in conjunction with the Client Load Balancing feature described earlier, a client setting that includes load balancing and TAF capabilities is 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)
)
)
)
Several of these parameters have the following meanings:
? Type, which specifies the types of Failover_mode, is available in 3 types, namely session, select, and none.
? Session, which 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 the connection request again, but the SQL that is executing when the instance fails needs to be re-executed.
? Select, which indicates that if the instance being connected fails, the select operation will continue with the cursor and the previous snapshot, and the other operations must be re-executed.
? NONE, this is the client default value, which means that the SQL takeover is disabled.
? mode, which represents the connection mode, is of two types, basic and Preconnect, respectively.
? Basic means that only one node is connected when the initial connection is established, and that the standby node is connected only if a node failure occurs.
? Preconnect means that you connect to the primary and standby nodes when you establish the initial connection.
? RETRY: Indicates the number of times the failover function attempted to connect an alternate node after the current node failed.
? DELAY: Represents the number of seconds to wait between two attempts.

After setting up client listening, restart the client service, and then do the following:
Sql*plus:release 11.1.0.7.0-production on Sun Sep 12 23:23:15 2010
Copyright (c) 1982, the Oracle. All rights reserved.
Connected to:
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$instan Ce 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, the connection is kept open, and then the following statement is executed on any node of the RAC database:
[[email protected] ~]$ srvctl stop instance-d racdb-i racdb2
After closing the RACDB2 instance of the NODE-RAC2 node, proceed to the same statement as the previous SQL command, with the following result:
Instance_ NAME host_name failover_type failover_method failed_over
-------------         ------------------    -------------       ---------------           -------------
RACDB1 node-rac1 SELECT BASIC YES
From the output can be seen, the above SQL session has been switched to the NODE-RAC1 instance racdb1, that is, the implementation of automatic failure switch function.
At this point, the functional tests on the RAC database have been validated.

Oracle RAC Functional Testing