When running a RAC cluster, some operational practices can reduce overall administration costs and improve manageability. The design of a RAC cluster is critical for the effective implementation of these practices. While this chapter discusses numerous RAC design patterns, implementation of these design practices will lead to better operational results.

Workload Management

Workload management is an essential tool that simplifies the management of RAC clusters. Skillful use of workload management techniques can make the administration of a production cluster much easier. It can also speed up the detection of failures and accelerate the failover process. Services, VIP listeners, and SCAN listeners play important roles in workload management.

Workload management is centered on two basic principles:

1. Application affinity: Optimal placement of application resource usage to improve the performance of an application.
2. Workload distribution: Optimal distribution of resource usage among available nodes to maximize the use of cluster nodes.

Application affinityis a technique to keep intensive access to database objects localized to improve application performance. Latency to access a buffer in the local buffer cache is on the order of microseconds (if not nanoseconds with recent faster CPUs), whereas latency to access a buffer resident in a remote SGA is on the order of milliseconds, typically, 1–3ms.¹  Disk access latency is roughly 3 to 5ms for single block reads; that is nearly the same as that for remote buffer cache access latency, whereas local buffer cache access is orders of magnitude faster. So, if an application or application components (such as a group of batch programs) access some objects aggressively, connect those application components to an instance so that object access is mostly localized, improving application performance.

A complementary (though not necessarily competing) technique to application affinity is workload distribution. For example, if there are four nodes in a cluster, then you would want to distribute workload evenly among all four nodes so that a node is not overloaded. Service is a key tool to achieving workload distribution evenly among all nodes of a cluster.

In practice, application affinity and workload distribution coexist. Some application components use application affinity to improve performance, and a few application components use workload distribution.

In addition to administrator-managed databases, you can also use policy-managed databases for workload management. Oracle 11gR2 introduces policy-managed databases and Oracle version 12c enhances policy-managed databases for dynamic policy management. Policy-managed databases are discussed later in this chapter.

Services

A service is a logical abstraction designed to group application components for optimal workload management. By implementing a service-based connection design, the application workload can be affinitized, distributed, or both. A database connection string specifies a service as a connection property, and the cluster processes distribute new connections to an instance depending on the workload specification of the service. The performance metrics of a service are also used by the listener processes to redirect the connection. Further, database processes calculate, maintain, and propagate service metrics to facilitate workload distribution and failover techniques.

A typical database connection using a service is done according to the following steps:

1. Application process sends a connection request specifying a service_name to the SCAN (Single Client Access Name) listener.
2. SCAN listener identifies the instance that can provide the best quality of service.
3. SCAN listener redirects the connection to the VIP (virtual IP address) listener listening locally for that instance.
4. VIP listener creates a database connection process, and the application continues processing the data by communicating to the connection process.

   By default, every database has some default services created, as shown in the following query output. Default services are marked as preferred in all instances; therefore, default services are not useful for workload management. For example, a service SOLRAC matching the database name (db_name initialization parameter) is created, and that service is preferred in all instances of the database. So, workload distribution among the instances of the database is not effective using the SOLRAC service since connecting through that service can connect to any instance.

   SQL> select name, goal from v$active_services
    
   NAME                           GOAL
   ------------------------------ ------------
   SOLRAC                         NONE
   SYS$BACKGROUND                 NONE
   SYS$USERS                      NONE

   You need to create a new service to implement workload management properly. Only a service created by you can be used effectively for workload management. There are many ways to create a service, such as using the srvctl command, the dbms_service package call, or Oracle Enterprise Manager (OEM). In a single-instance database, you can use the dbms_service package to create and manipulate a service. In RAC, you should not use the dbms_service package call. Instead, it is better to use the srvctl command to create a service. The srvctl command creates a cluster resource in Clusterware, in addition to creating a service in the database. This cluster resource for a service is monitored for availability, and the resource is failed over in cases of instance failure.

   The following command adds a new service: po to solrac database. This command also specifies that this service should be preferred in solrac1 and solrac3 instances, and the service should fail over to solrac2 and solrac4 instances if a preferred instance fails. Flag –r specifies preferred instances, and flag –a specifies availableinstances.

   $ srvctl add service -d solrac -s po -r solrac1,solrac3 
        -a solrac2,solrac4 -m BASIC -j short -B service_time

   In Oracle Database version 12c, the syntax to add a service is different and the options are more verbal. While the command options in 11.2 will also work in version 12c, the abbreviated command options are deprecated in version 12c. Earlier version 11.2 commands to add a service can be rewritten as follows using version 12c syntax.

   $ srvctl add service -db solrac -service po
      -preferred solrac1,solrac3 
      -available solrac2,solrac4  -failovermethod BASIC 
      -clbgoal short -rlbgoal service_time

   Adding a service using the srvctl command adds a resource in Clusterware, too. Whereas in 11.2 versions, the user has to start the service manually after adding a service, in version 12c, the service is automatically started. This cluster resource is a dependent of database and VIP resources. So, as soon as the database and VIP resources are online, the service resource will be altered online.

   $ crsctl stat res ora.solrac.po.svc -p |more
   NAME=ora.solrac.po.svc
   TYPE=ora.service.type
   ...
   SERVICE_NAME=PO
   START_DEPENDENCIES=hard(ora.solrac.db,type:ora.cluster_vip_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora.solrac.db)

   Starting the database resource will recursively start dependent Clusterware resources. As the services are defined as dependent resources, starting the Clusterware resource of a service enables the service in the database by implicitly calling the dbms_service package.

   You could explicitly start a service using the srvctl command, as well. Query the view v$active_services to see that a new service has been created and started.

   $ srvctl start service –d solrac –s po
   SQL> select name, creation_date from v$active_services
        where name='PO';
   NAME                                      CREATION_DATE
   ----------------------------------------- ---------------
   PO                                        17-DEC-12

   Service Metrics

   The MMON (Memory Monitor) database background process calculates the service-level metrics using service statistics, and these metrics are visible at v$activeservice dynamic performance view. Metrics are calculated at intervals of 5 seconds and 1 minute. In the following output, the first line shows the metrics calculated at 1-minute intervals, and the second line shows the metrics calculated at 5-second intervals.

   SQL> select inst_id, begin_time, end_time from gv$servicemetric
        where service_name='PO' order by 1, 2;
    
   INST BEGIN_TIME                 END_TIME
   ---- -------------------------- --------------------------
      1 14-JAN-2013 16:05:57       14-JAN-2013 16:06:57
      1 14-JAN-2013 16:07:06       14-JAN-2013 16:07:11
   ...

   Two sets of metrics are calculated by the MMON background process, one indicating service_time and the other indicating throughput. Depending on the service configuration, goodness of a service is calculated and PMON (Process Monitor) or LREG (Listener Registration - 12c) background processes communicate the service metrics to the SCAN and VIP listeners.

   Figure 3-1 shows the MMON propagation details. It gives an overview of service metrics calculation and propagation.

   

   Figure 3-1. Service metrics calculation and propagation

5. MMON process populates service metrics in AWR (Automatic Workload Repository) base tables.²
6. Service metrics are propagated to the ONS (Oracle Notification Service) daemon, and ONS daemons propagate the service metrics to the client-side driver and client-side ONS daemon.
7. In addition, service metrics are enqueued in the sys$service_metrics queue. A client program can register as a subscriber to the queue, receive queue updates, and take necessary action based on the queue alerts.

Also, MMON propagation is a key component of the FAN (Fast Application Notification) feature discussed later in this chapter.

A set of columns, namely, elapsedpercall, cpupercall, and dbtimepercall, indicate the response time quality of the service. The following data from v$servicemetric shows that response time metrics are better in instance 1 than in instance 2, as dbtimepercall is lower in instance 1 than in instance 2. Using these statistics, the SCAN listener redirects the connection to an instance that can provide better quality of service.

SQL> select inst_id,service_name, intsize_csec, elapsedpercall,
     cpupercall,dbtimepercall
     from gv$servicemetric where service_name='PO'
     order by inst_id, 2,3;
 
INST SERVICE    INTSIZE_CSEC ELAPSEDPERCALL CPUPERCALL DBTIMEPERCALL
---- ---------- ------------ -------------- ---------- -------------
   1 PO                  511     45824.8171 19861.5611    45824.8171
   1 PO                 5979     32517.4693 13470.9456    32517.4693
   2 PO                  512     89777.4994 41022.0318    89777.4994
   2 PO                 5938      50052.119 23055.3317     50052.119
...

Columns callspersec and dbtimepersec indicate the throughput metrics of a service.

SQL> select inst_id,service_name, intsize_csec, callspersec,
     dbtimepersec
     from gv$servicemetric where service_name='PO'
     order by inst_id, 2,3;
 
INST SERVICE    INTSIZE_CSEC CALLSPERSEC DBTIMEPERSEC
---- ---------- ------------ ----------- ------------
   1 PO                  512  1489.64844   4099.97602
   1 PO                 6003  1200.56638     4068.117
...

Note that these metrics are indicative of an average of performance statistics of all the workloads connecting to that service in an instance. As averages can be misleading, it is important to understand that in a few cases listeners can redirect the connections to instances that may not be optimal for that service.

Load Balancing Goals

Services can be designed with specific goals, to maximize either response time or throughput. Service metrics are factored to identify best-instance matching with the service goals. Two attributes of a service definition specify the service goal; namely, GOAL and CLB_GOAL.

• Attribute GOAL can be set to a value of either LONG or SHORT. The value of LONG implies that the connections are long-lived, such as connections originating from a connection pool. When the attribute GOAL is set to LONG, connections are evenly distributed among available instances, meaning that the listener tries to achieve an equal number of connections in all instances where services are available. An attribute value of SHORT triggers runtime load balancing based upon performance metrics.
• If the attribute GOAL is set to SHORT, then only the CLB_GOAL attribute is applicable. If the attribute CLB_GOAL is set to SERVICE_TIME, then connections are redirected to the instance that can provide better response time for that service. If CLB_GOAL is set to THROUGHPUT, then connections are redirected to the instance that can provide better throughput for that service.

If the attribute CLB_GOAL is set to response_time, then service metrics with names ending with percall are considered to measure goodness of an instance for a service. If the attribute CLB_GOAL is set to THROUGHPUT, then metrics ending with persec are considered to measure goodness of an instance for a service. Table 4-1 summarizes these service attributes and offers a short recommendation.

The MMON process calculates goodness and delta columns using service metrics. A lower value for the goodness column indicates quality of service in that instance (in retrospect, it should have been called a badness indicator). The delta column indicates how the value of the goodness column will change if a new connection is added to that service. In this example output, PO service is defined with the GOAL attribute set to the LONG value, and so the goodness column simply indicates the number of connections to that service in that instance. Since instances 2 and 3 have lower values for the goodness column, a new connection to PO service will be redirected to instance 2 or 3.

SQL> select inst_id, service_name, intsize_csec,goodness,delta
     from gv$servicemetric
     where service_name='PO' order by inst_id, 2,3;
 
INST SERVICE    INTSIZE_CSEC   GOODNESS      DELTA
---- ---------- ------------ ---------- ----------
   1 PO                  514       1883          1
   1 PO                 5994       1883          1
   2 PO                  528       1820          1
   2 PO                 5972       1820          1

In versions 11.2 and earlier, the PMON process propagates the service metrics to the listeners registered in local_listener and remote_listener initialization parameters. As the remote_listener specifies the address of SCAN listeners and the local_listener parameter specifies the address of the VIP listener, the PMON process propagates the service metrics to both SCAN and VIP listeners. You can trace listener registration using the following command.

alter system set events='immediate trace name listener_registration level 15';

The PMON trace file shows how the quality of service is propagated to listener processes. Here is an example (output of PMON process sending the service quality to the listener process).

kmmgdnu: po
goodness=100, delta=10,
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-

In version 12c, listener registration is performed by a new mandatory background process named LREG. At database start, the LREG process polls the listeners specified in the local_listener and remote_listener parameters to identify whether the listeners are running. If the listeners are available, then the services are registered to the listeners by the LREG process. The earlier command to trace listener registration in version 11.2 is applicable to version 12c also, but the listener registration details are written to an LREG trace file instead of a PMON trace file.

Unix process pid: 3196, image: [email protected](LREG)
...
Services:
...
  2 -po.example.com
       flg=0x4, upd=0x6
       goodnes=0, delta=1
...
Listen Endpoints:
0 -(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6rac1.example.com)(PORT=5500))
       (Presentation=HTTP)(Session=RAW))
       flg=0x80000000, nse=0, lsnr=, lflg=0x13
       pre=HTTP, sta=0

If a service is not registered properly to the listeners or if the service quality decisions taken by the listeners are incorrect, then you can trace the service registration to check if the listeners received correct service metrics.

Version 11.2 introduces the Valid Node Checking for Registration feature. SCAN listeners will accept registration from any IP address in the same subnet as the SCAN IP address. The VIP listener will accept the registration only from the local node. You can use invited_nodes to specify the permitted nodes for listener registration.

Runtime Failover

At runtime, a connection can be configured to fail over quickly to an available instance by configuring the failover type service attribute. A service supports failover types of NONE, SESSION, or SELECT. Failover type NONE does not provide any failover. If the failover type is set to SESSION, then after a node failure, sessions will fail over to surviving instances and the application must restart all work, including in-transit work. If the failover type is SELECT, then cursor fetch details are remembered by the client-side JDBC/OCI driver, the SELECT statement is restarted, and FETCH continues from the last row fetched before the failure.

In version 11.2, while the SELECT failover type supports a transparent restart of queries, it doesn’t restart any DML operation. The application must restart DML statements, and failures in DML statements must be handled by the application.

Version 12c introduces TRANSACTION, a new failover type, and this failovertype attribute value is specific to a new feature called Application Continuity. Application Continuity also captures DML changes and replays the DML activity using the Oracle client-side replay driver. The Application Continuity feature is discussed later in this chapter.

In version 12c, command options are specified more verbally. For example, in 11.2, option -e specifies the failover type, and in version 12c, service attribute failovertype specifies failover type.

Service in Second Network

A service can be configured to operate in a subnet. For example, to specify a service to be available in only the second network, you would specify option –k 2 while creating a service. In version 12c, clause -netnum 2 can be used to specify that the service will operate only in the second network. Please refer to Chapter 9 to learn more about the second network in a RAC cluster.

Guidelines for Services

The following guidelines are useful while creating services to improve cluster management.

• Create a separate service for each major application component. Service is an excellent method to implement application affinity. For example, if PO application accesses numerous PO-related tables and if one instance is sufficient to service the PO application, then keep PO service contained in one instance. You can keep the PO service connecting to an instance by creating the service with just a preferred instance. Application affinity improves performance by reducing global cache latency.
• Design the service in such a way that both preferred and available instances are configured. While designing services, consider the scenario of each node shutting down and construct the service placement based upon those scenarios.
• Use policy-managed databases instead of administrator-managed databases if there are numerous nodes (12+) in your cluster.
• Use of SCAN listeners and services reduces administration overhead. Especially for sites upgrading from versions 11.1 and earlier, it is important to alter the connection string to use SCAN listeners and SCAN IP addresses. It is very hard to change the connection string post-upgrade.
• Specify optimal clbgoal and rlbgoal parameters matching the application workload. Service metrics are calculated as an average for all the workloads connecting to that service. So, if your application has different workloads and if it is possible to segregate the application components depending upon the workload characteristics, then use disjointed, unique services for those application components. This segregation of application workload to different services improves the accuracy of service metrics and improves the accuracy of load balancing decisions. This segregation also improves the ability to identify application components consuming resources quickly.

There are only a few minor pitfalls in creating a new service. Production clusters with 100+ services operate without any major side effects. While we are not recommending that you create hundreds of unnecessary services, you should create as many services as you need to split the applications into manageable, disjointed workloads. In version 11.2, if there are numerous services (100+) and numerous listeners, then there is a possibility that the PMON process might spend more time on service registration to listeners due to the sheer number of services and listeners. But, in version 12c, this possibility is eliminated as the LREG parameter performs service registration and PMON is freed from listener registration.

SCAN Listener in Second Network (12c)

From version 11.2 and later, it is possible to create a second network, and VIP listeners can be created in the second network, too. However, SCAN listeners can listen only in the default first network in version 11.2. Version 12c eliminates that restriction, and it is possible to have SCAN listeners listening on a second network also. By specifying the listener_networks parameter, SCAN listeners will redirect the connection to VIP listeners within the same network subnet.

Let us walk through an example of SCAN listener in the second network in version 12c. The following set of commands adds a network with netnumber 2 in a subnet of 192.168.8.0 (note that first network has 10.7.11.0 subnet). The next three commands add VIP addresses with –netnumber 2, specifying that these VIP listeners are associated with the second network. The fourth command adds a listener to listen in port 1521 in these node VIP addresses.

$ srvctl add network -netnum 2 -subnet 192.168.8.0/255.255.255.0
$ srvctl add vip -noderac1.example.com-address  rac1vip.example2.com/255.255.255.0 -netnum 2
$ srvctl add vip -noderac2.example.com-address  rac2vip.example2.com/255.255.255.0 -netnum 2
$ srvctl add vip -noderac3.example.com-address  rac3vip.example2.com/255.255.255.0 -netnum 2
$ srvctl add listener -netnum 2 -endpoints TCP:1522

The following commands add a SCAN name in network 2 with a SCAN name of ebs.example2.com. The second of the following two commands adds a SCAN listener³ listening in port 20000, and Grid Infrastructure identifies SCAN IP addresses by querying DNS for SCAN name ebs.example2.com.

$ srvctl add scan -scanname ebs.example2.com -netnum 2
$ srvctl add scan_listener -listener LISTSCAN -endpoints TCP:20000

Database initialization parameter listener_networks must be modified so that the PMON or LREG process can register the services to listeners in both networks. The LISTENER_NETWORKS parameter specifies the network configuration in two parts, the first part specifying the configuration of the first network, named network1, and the second part specifying the configuration of the second network, named network2. In this example, listener_net1 and listener_net2 are TNS aliases connecting to VIP listener in local nodes; the remote_listener parameter refers to the SCAN listeners running in their respective networks specified using EZConnect syntax.

alter system set LISTENER_NETWORKS='((NAME=network1)(LOCAL_LISTENER=listener_net1)(REMOTE_LISTENER=ebs.example.com:10000))','((NAME=network2)(LOCAL_LISTENER=listener_net2) (REMOTE_LISTENER=ebs.example2.com:20000))';

Consider the following connection string from a user connecting to the second network (possibly a connection originating from example2.com domain): SCAN listeners will redirect the connection to a VIP listener in the second network only. Essentially, connection from the second network subnet will remain in the second network subnet and the network packets may not traverse from one subnet to another subnet. This network demarcation is important, as a firewall might prevent network packets from crossing over to another domain.

PO =
  (DESCRIPTION=
     (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebs.example2.com)(PORT=20000)))
      (CONNECT_DATA=(SERVICE_NAME=PO)
     )
  )

Support of SCAN listeners in the second network is an important feature in an enterprise database cluster with multiple network segments and domains, especially after major business events such as a merger of two companies.

Guidelines for SCAN Listeners

Optimally configured SCAN IP addresses and SCAN listeners are essential production components from 11.2 and later. The SCAN feature greatly reduces administration overheads, as you can publish the TNS connection string once and that connection string is not affected by RAC cluster topology changes. As SCAN listeners are lightweight forwarders, even if you have numerous nodes in a cluster, just three SCAN listeners are able to service an onslaught of connection requests. In fact, in a busy, connection-intensive production cluster, VIP listeners are bottlenecks, while SCAN listeners are able to withstand spurious connection requests easily.

Multiple DNS servers with failover and load balancing represent an extra layer of protection to avoid a single point of failure. This load balancing between DNS servers is usually a responsibility of network administrators. If you configure GNS servers, Clusterware manages GNS daemons and VIPs. If a server running GNS daemon or VIP fails, then the Clusterware will automatically fail over the GNS daemon and VIP to a surviving instance.

In a single segment network cluster, the local_listener parameter should refer to the VIP listener running on the local node, and the remote_listener parameter should refer to the SCAN listeners using EZConnect syntax specifying the DNS alias of the SCAN IP address and port number. Do not specify remote VIP listeners from another node in the remote_listener parameter, as that can cause cross-registration and accidental connection forwarding to the other node.

Global Database Services (12c)

Oracle Database version 12c introduces the concept of Global Database Services (GDS). Services discussed so far in this chapter are those that distribute the workload between instances of a database. In a truly globalized environment, however, databases are replicated using tools such as GoldenGate, Streams, etc. Global services provide a mechanism to fail over the connections globally between the databases.

It is easier to explain the concept of GDS with a connection string example. Consider that there are two databases kept in sync using a bidirectional replication technology. The database servicing the California region is physically located near that region, and likewise the database servicing the New York region is physically located there. Thus, clients in the California region will benefit by connecting to the California database during normal operating conditions, and in the case of failure of the database in California, the connections should fail over to the New York database, and vice versa. Until version 11.2, this was achieved using a connect time failover connect string.

As of version 12c, the connect string for the California region specifies two address_list sections. These two address_lists have the FAILOVER attribute set to ON but load_balance set to OFF. This connection string will try the connection to the first address_list in the connect string; that is, the application will connect to the California database. If that database does not respond before TCP timeout, then the connection will fail over to the New York database.

(DESCRIPTION=
  (FAILOVER=on)
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=ca1)(port=1522))
    (ADDRESS=(host=ca2)(port=1522))
    (ADDRESS=(host=ca3)(port=1522)))
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=ny1)(port=1522))
    (ADDRESS=(host=ny2)(port=1522))
    (ADDRESS=(host=ny3)(port=1522)))
  (CONNECT_DATA=
    (SERVICE_NAME=PO)
    (REGION=ca))
 )

In addition to the failover specification among address_list sections, each address_list section also specifies multiple addresses with LOAD_BALANCE set to ON. Essentially, a new connection from the California region will try the first address_list as load_balance is set to OFF by default for description list. Then, an address from the chosen address_list will be used to send packets to GDS listeners as load_balance is set to ON for that address_list.

Note that traditionally, a VIP listener or a SCAN listener is specified in the ADDRESS section. But, in the case of GDS connect string, these are not traditional listeners, but GDS listeners. This feature was introduced in version 12c. GDS listeners can be visualized as global SCAN listeners. Oracle recommends three GDS listeners per region, very similar to the three SCAN listeners in a RAC cluster. Also, services are managed by srvctl command and global services are managed by the gsdctl command interface.

GDS listeners connect to the database and register as subscribers of the servicemetric queue. Any topology changes and Load Balancing Advisories (LBAs) are propagated to GDS listeners, and the GDS listeners use the LBAs to forward connections to a database that can provide better quality of service.

GDS are useful with the Active Data Guard setup also. With the Active Data Guard feature, it is possible to use a data guard instance for read-only activity. In many cases, the need arises for a global failover; that is, if the Active Data Guard database is not available, then failover will be to the primary database and vice versa. The GDS feature will be handy in implementing this requirement. Refer to the GDS Concept and Administration Guide for more details.

TAF

TAF fails over the connection to a surviving instance after encountering an instance failure. TAF can operate in either SESSION or SELECT failover mode.

• In a SESSION failover mode, if a connection is severed then the connection will fail over to a surviving instance. Essentially, the connection is reattempted automatically. Only the connection is restarted, and the application must detect failures and restart failed workload.
• In a SELECT failover mode, the client-side driver remembers the cursor fetch details, and if the current connection is severed, then the connection will fail over to surviving node. The SELECT statement is re-executed, rows are fetched from the cursor and scrolled to the point of failure in the cursor fetch operation, and rows are returned from the point of failure.

The following connect string is a simple example of TAF using a SCAN listener. With this connection string, the application connects to a SCAN listener listening in port 12099. Notice that the failover_mode parameter specifies the type as SESSION, indicating session failover mode. If the connection is severed, then the client will retry the connection to the SCAN listener again. The SCAN listener will redirect the connection to the VIP listener of a surviving node.

PO_TAF =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)
     (HOST = rac-scan.example.com)(PORT = 12099))
          (CONNECT_DATA =
            (SERVICE_NAME = PO)
            (FAILOVER_MODE= (TYPE=SESSION) (METHOD=basic))
     )
    )

Dynamic-view v$session can be queried to verify if TAF is configured properly or not. The preceding connect string shows that session level failover is enabled for the current connection. Killing the connection resulted in respawning of a new connection, confirming that TAF is working as designed.

SQL> select sid, FAILOVER_TYPE, FAILOVER_METHOD, failed_over
     from v$session where
     sid =(select sid from v$mystat where rownum =1);
  
       SID FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ---------- ---
      8227 SESSION       BASIC      YES

While it is possible to set up TAF with a client connection string, TAF can be implemented more elegantly using service attributes. The advantage of implementing TAF using service attributes is that configuration changes can be performed on the database server side without altering the client-side connect string. The following srvctl command shows that SESSION failover type is added to po service. Option –z specifies failover retry attempts, and the –w option specifies delay between failover attempts. In this example, connection will be attempted five times with 60-second delays between successive attempts.

$  srvctl add service -d orcl12 -s po -r oel6vm1 -a oel6vm2 
             -m BASIC -e SESSION -z 5 -w 60

In version 12c, adding a service requires a different syntax, and options are specified more verbally. The earlier command is written in version 12c as follows.

$ srvctl add service -db orcl12 -service po 
      -preferred oel6vm1 -available oel6vm2 
      -tafpolicy BASIC -failovermethod SESSION 
      -failoverretry 5 -failoverdelay 60

It is a better practice to keep the connection string as simple as possible and modify configuration of services to implement TAF. This strategy simplifies connection string management in an enterprise database setup.

Further, it is best to implement TAF while creating services so that you can improve availability.

Fast Connection Failover

Fast Connection Failover (FCF) is a proactive failover method to avoid downtime when there is an instance failure. Proactive connection management improves application availability and user experience, as you can reduce runtime errors. Here are a few examples where FCF can be used to avoid errors:

• After a database instance failure, all connections in a connection pool must be recycled by the connection manager. Usually, stale connections are identified only when the connection is reused by the application⁴, but the failure is detected only after an error is encountered by the application. This late detection of error condition can lead to application errors and other undesirable user experiences.
• Connection pool connections that were busy in the database executing queries might need to wait for TCP timeouts. This delay can lead to slowness and errors in the application.
• After the recovery of failed instances, the application might not reconnect to the recovered node. This issue can lead to unnecessary overload of the surviving nodes.

Instance failures can be propagated earlier to the application, and errors can be minimized by requesting the application to recycle its connections after node or service failures. Essentially, service changes, node failures, node up/down events, etc., are communicated immediately to the application, and this propagation is known as FAN. After receiving a FAN event, the application proactively recycles connections or takes corrective actions depending upon the nature of the failure.

FCF is a technique that captures FAN events and reacts to the received FAN events. FAN events can be captured and connections recycled utilizing a few technologies:

1. Your application can implement connection pool technology that handles the FAN events, such as Oracle WebLogic Active GridLink data source, Oracle JDBC Universal Connection Pool, ODP.Net connection pool, OCI session pools, etc.
2. Your application can use programmatic interfaces to capture FAN events and react accordingly.
3. Or, you can use database side callouts to handle FAN events.

WebLogic Active GridLink

WebLogic Active GridLink for RAC is a data source available in Oracle WebLogic version 11g. This data source is integrated with RAC to handle FAN events and LBA without the help of any code changes. To make use of Active GridLink for FCF, you should configure an Active GridLink data source to connect to the SCAN listener and the ONS daemon, in a WebLogic configuration screen.

The ONS daemon propagates RAC topology changes to the registered clients. Any topology change is propagated to the clients listening to the SCAN IP address and 6200 ports. GridLink data source registers to the ONS daemon to receive notifications. If there are any topology changes as broadcast by the ONS daemon, such as instance restart, then GridLink data source captures the events and recycles the connections in the connection pool. Further, if there is an Instance UP event due to instance restart, then that event is captured by GridLink data source, and the connections are reapportioned among available nodes. This connection management avoids overloading of too few nodes after node recovery. Also, Active GridLink receives LBAs from ONS daemons, and GridLink data source uses connections from the least-loaded instances, implementing the runtime load balancing algorithm.

Essentially, Active GridLink is an important tool to implement FCF, runtime load balancing, and transaction affinity. If your application uses WebLogic as a web server, you should implement Active GridLink-based data sources to maximize the failover capabilities of RAC.

Transaction Guard (12c)

A key problem with failure detection and failure recovery is that the response from the database to the application is transient. So, the response to a COMMIT statement can be lost in the case of instance failures. For example, consider a scenario in which an application modified some rows and sent a commit to the database, but the database instance failed and the application did not receive any response from the database. Hence, the application does not know about the transaction status as the response from the database was lost. Two unique scenarios are possible:

1. Database committed the changes, but success response was not received by the application.
2. Database failed even before the successful commit operation, and so the application must replay the changes.

In either scenario, the application must know the transaction status to avoid data corruption. A new feature in version 12c is the Transaction Guard, which provides a mechanism to keep track of the transaction state even after database failures.

The Transaction Guard feature introduces the concept of logical transaction identifier. A logical transaction_id is unique for a specific task from a connection, and the logical transaction_id is mapped to a database transaction_id. The outcome of the logical transaction_id is tracked in the database. If there is a failure in the database, then application connection can query the transaction status using logical transaction_id to identify if a transaction is committed or not.

To implement this feature, you need to create a service with two attributes, namely, commit_outcome and retention. The commit_outcome attribute determines whether the logical transaction outcome will be tracked or not. Attribute retention specifies the number of seconds a transaction outcome will be stored in the database (defaults to a value of 86,400 seconds = 1 day). The following command shows an example of po service creation with commit_outcome set to TRUE, enabling Transaction Guard.

$ srvctl add service -db orcl12 -service po -preferred oel6vm1 -available oel6vm2 
  -commit_outcome TRUE –retention 86400

The Transaction Guard feature requires code changes, and your application code must be changed to react at the onset of failure. Application code must perform the following actions at the time of failure:

1. Receive FAN events from ONS daemon for failures.
2. Get the last logical transaction_id by calling getLTXID from a failed session.
3. Retrieve the status of the logical transaction_id by calling GET_LXID_OUTCOME, passing the logical transaction_id retrieved in step 2.
4. Handle the transaction depending upon the transaction state.

Application Continuity (12c)

Application Continuity is a new feature implemented using the Transaction Guard feature.  Oracle introduces a new client-side JDBC driver class to implement Application Continuity. Universal Connection Pool and WebLogic Active GridLink support Application Continuity by default. If your application uses a third-party connection pool mechanism, then you need to modify code to handle Application Continuity.

The application issues database calls through the JDBC driver. The JDBC replay driver remembers each call in a request and submits the calls to the database. In the case of failures, JDBC replay driver uses the Transaction Guard feature to identify the global transaction state. If the transaction is not committed, then the JDBC replay driver replays the captured calls. After executing all database calls, replay driver issues a commit and terminates replay mode.

To support Application Continuity, service must be created by setting a few service attributes, namely, failovertype, failoverretry, failoverdelay, replay_init_time, etc. The following command shows an example of service creation. Notice that failovertype is set to TRANSACTION; this is an additional failovertype value introduced by version 12c.

$ srvctl add service -db orcl12 -service po -preferred oel6vm1 -available oel6vm2 
   -commit_outcome TRUE –retention 86400 –failovertype TRANSACTION 
   -failoverretry 10 –failoverdelay 5 –replay_int_time 1200

Not all application workloads can be safely replayed. For example, if the application uses autonomous transactions, then replaying the calls can lead to duplicate execution of autonomous transactions. Applications must be carefully designed to support Application Continuity.

Process Priority

In RAC, a few background processes must have higher CPU scheduling priority. For example, an LMS background process should run with higher CPU priority, as the LMS process is the workhorse of global cache transfer, and CPU starvation of an LMS process can increase global cache latency. Further, if an LMS process suffers from CPU starvation, network packets can be dropped, leading to gc blocks lost waits in other nodes. Underscore initialization parameter _high_priority_processes controls the processes that will execute at an elevated priority. By default, this parameter is set to elevate the priority of VKTM (Virtual Keeper of Time) and LMS background processes. Generally, you do not need to modify this parameter.

_high_priority_processes= 'LMS*|VKTM'

For example, in the Solaris platform, the LMS process will execute with RT priority. Of course, elevating a process priority requires higher-level permissions, and this is achieved using oradism binary in Unix platforms and oradism service in Windows platform. Notice in the following that oradism executable is owned by root userid with setuid flag enabled. OS group prod has execute permission on this oradism binary. Essentially, with setuid permission of the root user, the oradism executable is utilized to elevate the priority of background processes.

$ ls -lt oradism
-rwsr-x---   1 root     prod     1320256 Sep 11  2010 oradism

Setuid permissions on oradism executable are needed to alter the priority of background processes. When you execute root.sh during GI software installation, this setuid permission is set on oradism executable, but during software cloning, DBAs typically do not run root.sh, and so the oradism executable does not have correct permissions. Hence, the priority of LMS process is not elevated, leading to global cache latency issues during CPU starvation. Executing root.sh script and verifying that the oradism executable has correct permission after the completion of software cloning is recommended.

Also, GRID processes, such as cssd.bin, cssdagent, and cssdmonitor, etc., should run with elevated priority. Again, oradism binary in Grid Infrastructure software must have proper permissions. Permissions on an ordism file in GI were not a big problem until version 11.2, as cloning of GI was not widely used. However, in version 12c, cloning of GI software is allowed and binary permissions require careful attention if GI software is cloned.

While elevated priority for background processes protects the background processes from CPU starvation, it does not mean that CPU starvation will not cause node stability issues. For example, the LMS process must wait for the LGWR background process to do a log flush sync before sending the blocks (for certain types of global cache transfer). If there is CPU starvation in the server, then LGWR might not get enough CPU; LGWR will suffer from CPU starvation, and that can lead to longer waits by the LMS process. Remote instances will suffer from prolonged global cache transfers. So, CPU starvation in one node can increase the global cache latency in other nodes, inducing slowness in the application; in a few scenarios, this CPU starvation can also lead to node reboots. It is a recommended practice to keep CPU usage below 75% to avoid CPU starvation and reduce node stability issues.

Memory Starvation

Memory starvation in a cluster node can cause stability issues in a RAC cluster. Elevated process priority protects the background process from CPU starvation but does not prevent memory starvation issues. Memory starvation of an LMS process can lead to higher latency for global cache wait events in other nodes. Further, memory starvation of Grid Infrastructure daemons can lead to node reboots. It is a recommended practice to design cluster nodes with sufficient memory and avoid memory starvation.

Virtual memory swappiness is an important attribute to consider in a Linux kernel. This kernel parameter affects swapping behavior. As Linux kernel version 2.6.18 improves swapping daemons throughput, you should upgrade the kernel to at least version 2.6.18. If you cannot upgrade the kernel to at least 2.6.18, then the vm.swappiness kernel parameter is an important parameter to consider. If the parameter is set to a lower value, then pages will be kept in memory as long as possible. If the vm.swappiness parameter is set to a higher value, then the pages will be swapped more aggressively. If you are using a version prior to 2.6.18 kernels, you should reduce the vm.swappiness kernel parameter to a lower value such as 30 (default is 60).

We also recommend that you implement HugePages in the Linux platform. At the time of this writing, Oracle has made HugePages as one of the important ways to improve the stability of a RAC cluster. With HugePages, two important benefits can be achieved:

• SGA is locked in memory. Linux kernel daemons have fewer pages to scan during memory starvation. Locking SGA also protects from paging.
• Further, default page size of memory mapping is 4KB in the Linux platform. Pointers to these pages must be maintained in page table entries, and so page table entries can easily grow to a few gigabytes of memory. With the HugePages feature, page size is increased to 2MB and the page table size is reduced to a smaller size.

HugePages and Automatic memory management (AMM) do not work together. We recommend you use HugePages instead of AMM to improve RAC stability.

SGA size

If the data blocks are already residing in local SGA, then access to those buffers is in the range of microseconds. Thus, it is important to keep SGA size bigger in a database with OLTP workload. It is a best practice to increase the SGA size when you convert the database to RAC.

If the application workload is mostly Decision Support System (DSS) or Data Warehouse type, then keep PGA bigger and buffer cache smaller, as PX server processes read blocks directly into the PGA (Program Global Area). The parameter pga_aggregate_target is not a hard limit and the sum of PGA allocated by the database processes can far exceed the pga_aggregate_target parameter. This unintended excessive memory usage can induce memory starvation in the server and lead to performance issues. Release 12c introduces pga_aggregate_limit parameter and the total PGA used by the database cannot exceed the pga_aggregate_limit value. If the database have allocated pga_aggregate_limit amount of PGA, then the process with largest PGA allocation is stopped until the PGA size falls below the pga_aggregate_limit value. Therefore, in 12c, you can avoid excessive memory swapping issues by setting pga_aggregate_limit to an optimal value.

It is advisable to keep the SGA size as big as possible without inducing memory starvation, as discussed earlier. Database nodes with ample physical memory are essential while converting a database to a RAC database. More memory can also reduce unnecessary CPU usage, which can be beneficial from a licensing point of view.

Filesystem Caching

For Oracle database software and Grid Infrastructure software, the file system must have caching enabled. Without file system caching, every access to binary file pages (software binary pages are accessed frequently from an executing UNIX process) will lead to a disk access. This excessive disk reads induces latency in the executable, leading to instance reboot or even node reboot.