Types of Workload Distribution (load Balancing) in Oracle RAC

Every now and then, you’ll stumble upon a trend that just doesn’t seem to go away. From time to time, these trends can be silly, like the ever-popular planking meme. Other times, they can be a little bit more serious, such as stage diving. As you might have guessed, a stage channel in Discord is a public chat room where you can discuss something that’s either related to a certain streamer or video game, or something entirely different.

what is a stage channel in discord?

Types of workload distribution

In RAC, multiple listeners on multiple nodes can be configured to handle client connection requests to the same database service. When you configure multiple listeners, you can use the following failover and load balancing features:

• Load balancing of client-side connections
• Connection failover at the client side
• Load balancing of connections on the server side

These functions can be performed separately or in combination. If you use connection pools, you can also take advantage of readily available connection load balancing at runtime to distribute client workload requests to the connection pool set up by the middle tier. The Oracle Universal Connection Pool (UCP) for Java and the Oracle Data Provider for .NET (ODP.NET) connection pool provide this capability.

Message: Effective with Oracle Database 11g Release 1 (11.1.0.7), Oracle released a new universal connection pool for JDBC. For this reason, Oracle discontinues the JDBC connection pool (i.e., the implicit connection cache) that was introduced in Oracle Database 10g Release 1.

Load balancing of client-side links

Client-side load balancing is defined in the client connection definition (e.g., in the tnsnames.ora file) by the LOAD_BALANCE=ON parameter. If you set this parameter to ON, Oracle Database randomly selects an address from the address list and connects to the listener for that node. This distributes client connections to all available SCAN listeners in the cluster.

ERP =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE=ON))
(ADDRESS=(PROTOCOL=TCP)(HOST=node1vip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=node2vip)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=ERP))

If you configure SCAN for connection requests, client-side load balancing is not relevant for clients that support SCAN access. When clients connect via SCAN, Oracle Net automatically distributes the load of client connection requests among the three IP addresses that you set for SCAN when you are not using EZConnect.

The SCAN listener redirects the connection request to the local listener of the instance that is least busy (if -clbgoal is set to SHORT) and provides the requested service. When the listener receives a connection request, it connects the user to an instance that it knows provides the requested service. To find out what services the listener supports, run the lsnrctl services command.

When clients connect via SCAN, Oracle Net automatically distributes the load of client connection requests to the three IP addresses that you set for SCAN when you are not using EZConnect.

Rapid application notification (FAN): Overview

FAN is a high availability notification mechanism that Oracle RAC uses to notify other processes of configuration and service level information that includes changes in service state, such as UP or DOWN events. Oracle client drivers and Oracle connection pools respond to FAN events and take immediate action. The FAN UP and DOWN events can be applied to instances, services and nodes.

Oracle’s connection pools, for example, use FAN to detect failures very quickly, rebalance connections after failures, and rebalance connections after recovery from failing components. Thus, when a service is started on an instance, the FAN event is immediately used to direct work to that resource. When a service fails on an instance or node, the FAN event is immediately used to stop applications for recovery.

By using FAN events, applications do not have to wait for TCP timeouts, waste time processing the last result on the client after a failure, and waste time operating on slow, hung, or dead nodes. When changes are made to the cluster configuration, the Oracle RAC high availability fabric immediately issues a FAN event when a state change occurs in the cluster. Instead of waiting for the application to correlate with the database in time and detect a problem, applications can receive FAN events and respond immediately. With FAN, ongoing transactions are terminated immediately and the client is notified of an instance error.

FAN also publishes load balancing notification events. Applications can take advantage of FAN load balancing advisory events to route work requests to the cluster instance that is currently providing the best quality of service.

Advanced notification applicable: Benefits

Traditionally, client or intermediate applications connected to the database relied on connection delays, out-of-band polling mechanisms, or other custom solutions to detect the failure of a system component. This approach has a huge impact on application availability as downtime increases and becomes more noticeable.

With FAN, critical events are delivered with high availability as soon as they are detected. The result is more efficient use of available IT resources and better integration with business applications, such as mid-level connection managers or IT management consoles, including trouble ticket recorders and mail/personal search servers.

FAN is essentially a distributed system that is activated at each participating node. This makes it very reliable and fault-tolerant, as the failure of one component is detected by another. Thus, the notification of the event can be detected and transmitted by each of the nodes involved. FAN events are tightly integrated with the Oracle JDBC Universal Connection Pool, ODP.NET Connection Pool, OCI Session Pool, Oracle WebLogic Server Active Gridlink for Oracle RAC, and OCI and ODP.NET clients. This includes applications that use Application Continuity or Transaction Guard. For example, Oracle JDBC applications that manage connection pools do not require custom code development. They automatically integrate with the ONS when implicit connection cache and fast connection failover are enabled.

Implementation of FAN activities

You can use FAN events in the following three ways:

1. Your application can use FAN without programmatic changes if you use the built-in Oracle client. Built-in clients for FAN events include Oracle JDBC Universal Connection Pool, ODP.NET Connection Pool, OCI Session Pool, Oracle WebLogic Server Active Gridlink for Oracle RAC, and OCI and ODP.NET clients. This includes applications that use Application Continuity or Transaction Guard. Embedded Oracle clients must be Oracle Database 10g version 2 or later to take advantage of high-availability FAN events. Unified customers can also benefit from FAN’s load balancing advisory events.
2. Applications can use FAN programmatically by using the JDBC and Oracle RAC FAN application programming interface (API) or by using OCI and ODP.NET callbacks to subscribe to FAN events and perform event processing actions when an event is received.
3. You can implement FAN with server-side calls at the database level. If you use one of the built-in clients from step 1 of the previous list, DOWN events minimize application downtime because the FAN client terminates sessions with the failed instance or node before it is used again. Incomplete transactions will be cancelled and the user of the application will be notified immediately. Application users requesting connections are only directed to available instances.

UP events create new connections when services and instances are started, allowing the application to quickly use additional hardware resources or extra power.

FAN and integrated Oracle clients

Fast Application Notification (FAN) provides end-to-end application recovery and load balancing based on actual transaction performance in the RAC environment. Applications use high availability (HA) FAN events to detect failures very quickly, rebalance link pools after failures, and redistribute links after recovery from failed components.

FAN events with load balancing advice help connection pools consistently deliver connections to available instances that provide the best service. FAN HA is integrated with :

• JDBC thin
• The OIC driver
• Universal JDBC connection pool (and old implicit connection cache)
• OCI Meeting Pools
• the ODP.NET connection pool, and
• Oracle WebLogic Server Active GridLink for Oracle RAC.

Through integration with FAN, embedded clients are better informed of the current status of the RAC cluster. This prevents client connections from waiting or trying to connect to instances or services that are no longer available. When instances are started, Oracle RAC uses FAN to inform the connection pool so that the connection pool can make connections to the newly started instance and use the additional resources provided by the instance.

With FAN integrated, Oracle client drivers can:

• Remove disconnected connections immediately when a service is declared DOWN in an instance, and immediately when nodes are declared DOWN.
• Alert clients immediately when the Oracle database detects a DO NOT-RESTART condition, rather than making the client wait while the service repeatedly attempts to restart.

Oracle connection pools integrated with FAN can :

• Balance connections between all Oracle RAC instances when the service starts; this is preferable to directing sessions defined for the connection pool to the first Oracle RAC instance supporting the service.
• Balance the demands of the ongoing workload with advisory load balancing events.

The use of client drivers or connection pools and FANs requires that Oracle Notification Service be properly configured to pass FAN events to clients. Also, for load balancing, configure load balancing of database connections on all instances that provide services used by the connection pool. Oracle recommends configuring client-side and server-side load balancing with Oracle Net Services. When you use DBCA to create a database, load balancing is configured by default on both the client and server sides.

Message: As shown in the slide, FAN events are published via ONS Service and Oracle Streams, with the latter still being used for backward compatibility with earlier versions of the Oracle database.

Event types supported by FAN

FAN returns events related to the list of managed cluster resources shown in the slide. The table contains a description of each source.

FAN Causes ofevents

The occurrence status for each managed resource is associated with the cause of the occurrence. The reason describes more exactly what caused the event. The table on the slide contains a list of possible causes with accompanying descriptions.

FAN event status

This table describes the event status for each of the managed cluster resources discussed earlier.

Event format FAN

In addition to the type, status, and cause, a FAN event has other useful data fields to more accurately describe the individual cluster resource whose status is being monitored and published:

• Version of the event payload
• The name of the primary or shadow service of the application. This name is excluded from NODE events.
• Name of RAC database which is also excluded from NODE events
• The name of the RAC instance that is excluded from the SERVICE, DATABASE, and NODE events.
• Name of the host computer in the cluster that is excluded from SERVICE and DATABASE events.
• The service card finality is excluded from all events except SERVICE STATUS=UP events.
• The date and time, on the server side, when the event was detected.

The general format of the FAN event is described on the slide, along with examples of possible FAN events. Note the differences in payloads for each type of FAN event.

VERSION=
[service=] [database=] [instance=] [host=] status=
reason=
[card=] timestamp=

SERVICE VERSION=1.0 service=ERP.example.com
database=ORCL status=up reason=user card=4 timestamp=16-jul-2013 13:21:11

NODE VERSION=1.0 host=host01
status=nodedown timestamp=16-jul-2013 11:42:05

Request for load balancing: FAN event

The FAN (Load Balancing Advisory) event is described on the slide. Essentially, it contains the calculated percentage of work requests to be sent to each agency. The flag indicates the behaviour of the service on the corresponding instance, with respect to the thresholds defined on this instance for this service. The easiest way to take advantage of these events is to use the connection load balancing feature while running an embedded Oracle client such as JDBC, Universal Connection Pool, ODP.NET Connection Pools, OCI Session Pools, or Oracle WebLogic Server Active GridLink for Oracle RAC. Other client applications can use FAN programmatically by using the Oracle RAC FAN API to subscribe to FAN events and perform event processing actions as they are received. Here’s an example:

Report type: database/event/servicemetrics/prod
VERSION=1.0 database=PROD service=myServ { instance=PROD2 percent=38 flag=GOOD
aff=TRUE}{stance=PROD3 percent=62 flag=GOOD aff=TRUE} } timestamp=2013-07-30 08:47:06

Message: Applications using UCP and Oracle Database 11g or later can benefit from the affinity feature. When the affinity flag is set in the Load Balancing Advisory event, UCP creates an affinity context for a web session so that when that session receives a connection from the pool, the pool always attempts to give that session a connection to the instance it connected to when it first received the session. The selection of the instance for the first connection is based on the current load balancing advisory information. The affinity indication occurs automatically if load balancing advice is enabled via the service target setting.

Execution of server-side calls

Any database event detected by RAC High Availability (HA) causes any script or executable implemented in the default Oracle Clusterware call folder to be executed. On UNIX, that is [Grid Home]/racg/usrco. If the Oracle Clusterware home directory is not network-wide, each new call must be distributed to each RAC node. The order in which these calls are made is not deterministic. However, RAC ensures that all calls are called asynchronously, once for each detected event. It is therefore recommended to combine calls that should be executed in a certain order.

You can set up as many call scripts or programs as your organization wants, as long as each call doesn’t result in costly actions that slow down the distribution of HA events. If many call programs must be written to perform different operations based on the received event, it may be more efficient to write one program that combines each call program.

Writing server-side calls includes the steps listed on the slide. To identify an event, your call must analyze the event payload sent to your call by the RAC HA framework. Once a sent event is detected, your call can filter it to avoid having to respond to every event notification. Your call should then implement an appropriate event handler based on the event itself and the recovery process required by your organization.

Message: For security reasons, ensure that the invocation directory and the invocations it contains have write permissions only for the system user who installed Oracle Clusterware.

Syntax analysis of server-side calls Example

# !/bin/sh
NOTIFY_EVENTTYPE=$1
for ARGS in $* ; do
PROPERTY=`echo $ARGS | $AWK -F= ‘{print $1}’`
VALUE=`echo $ARGS | $AWK -F= ‘{print $2}’`
case $PROPERTY in
VERSION|version) NOTIFY_VERSION=$VALUE ;
SERVICE|service) NOTIFY_SERVICE=$VALUE ;
DATABASE|database) NOTIFY_DATABASE=$VALUE ;
INSTANCE|instance) NOTIFY_INSTANCE=$VALUE;
HOST|host) NOTIFY_HOST=$VALUE;
STATUS|status) NOTIFY_STATUS=$VALUE; ;
REASON|reason) NOTIFY_GRUND=$VALUE; ;
CARD|card) NOTIFY_CARDINALITY=$VALUE; ;
TIMESTAMP|time stamp) NOTIFY_LOGDATE=$VALUE; ;
??:??:?: ??) NOTIFY_LOGTIME=$PROPERTY ; ;
esac
done

If you do not want your calls to be executed every time an event is signaled, you must first define the event parameters that will be automatically passed to your call when it is executed. The above example shows how these arguments are parsed using the Bourne shell script as an example.

The first argument passed to your call is the type of event detected. Then, depending on the type of event, we pass a set of PROPERTY=VALUE rules that precisely identify the event itself. The script in the slide identifies the event type and each pair of lines PROPERTY=VALUE. The data is then passed to a set of variables that can later be used in a filter call.

As mentioned in the previous slide, it may be better to have a call that parses the event payload and then executes a function or other program based on the information in the event, rather than having to filter the information at each call. This is only necessary if many calls are required.

Message: Make sure the execution rights for the call script are set correctly.

Server-side call filter: Example

if ( [ $NOTIFY_EVENTTYPE = SERVICE ] ||
[ $NOTIFY_EVENTTYPE = DATABASE ] ||
[ $NOTIFY_EVENTTYPE = NODE ] ) &&
( [ $NOTIFY_STATUS = not_restarting ] ||
[ $NOTIFY_STATUS = restart_failed ] ) &&
( [ $NOTIFY_DATABASE = HQPROD ] ||
[ $NOTIFY_SERVICE = ERP ] ))
then
/usr/local/bin/logTicket $NOTIFY_LOGDATE
$NOTIFY_LOGTIME
$NOTIFY_SERVICE
$NOTIFY_DBNAME
$NOTIFY_HOST
fi

The above example shows how FAN events can be filtered out of the call script. This example is based on the example in the chapter. Now that the event properties are set, this script executes the /usr/local/bin/logTicket problem logger only when the RAC HA framework publishes an event of type SERVICE, DATABASE, or NODE, with a status of not_restarting or restart_failed, and only for the HQPROD RAC production database or ERP service.

It is assumed that the logTicket program has already been created and accepts the above arguments. It is also assumed that a ticket is only logged for not_restarting or restart_failed events, as these are the events that have exceeded internally controlled time limits and require human intervention to fully resolve.

ONS server side

usesharedinstall=true
allowgroup=true
localport=6100 # string added by agent
remoteport=6200 # string added by agent
nodes=host01:6200 # string added by agent

The ONS configuration is managed by the configuration file [GRID_HOME]/opmn/conf/ons.config. This file is automatically created during installation. Starting with Oracle Database 11g Release 2 (11.2), this is automatically supported by the ONS CRS agent using the information stored in the OCR. There are three main parameters that are always configured for each NSO:

• The first is localport, the port that ONS uses to communicate with local clients.
• The second is remoteport, the port that the ONS uses to communicate with other ONS daemons.
• The third parameter is called the node. Specifies a list of other ONS daemons to communicate with. This list includes all RAC-ONS and all medium-level ONS daemons. Node values are specified as hostnames or IP addresses followed by their remote port. This information is stored in the Oracle Cluster Registry (OCR).

The above example assumes that ONS daemons are already running on each node in the cluster. This should be the default situation if the RAC is installed correctly.

Client-side ONS extended configuration

Oracle Database 11g Release 2 has introduced a new set of APIs for Oracle RAC Fast Application Notification (FAN) events. These APIs provide an alternative way to take advantage of the high availability (HA) features of the Oracle database when you are not using Universal Connection Pool or Oracle JDBC Connection Caching. These APIs are not part of the Oracle JDBC APIs. To use Oracle RAC Fast Application Notification, the us.jar file must be present in the CLASSPATH or the Oracle Notification Services (ONS) client must be installed and running on the client system. To use ONS on the client side, you must configure all RAC nodes in the ONS configuration file. An example of a configuration file might look like the one shown above.

After configuring ONS, start the ONS daemon with the command onsctl start. It is your responsibility to ensure that the ONS daemon is operational at all times. You can verify that the ONS daemon is active by running the onsctl ping command.

Message: In Oracle Database 10g Release 2 and later, you do not need to use ONS daemons at the intermediate level when using Oracle Universal Connection Pool. Use the OracleDataSource property or the setONSConfiguration API setter (configStr) to configure this setting. The input to this API is the contents of the us.config file, which is specified as a string. The us.jar file must be in the CLASSPATH of the client. No demon needs to be executed or controlled.

JDBC DCS connection quick change: Overview

Oracle Universal Connection Pool (UCP) provides tight integration with Oracle RAC database features such as Fast Connection Failover (FCF). In essence, FCF is a FAN client implemented via a connection pool. FCF quickly and automatically restores lost or damaged connections. This automatic connection checking occurs as a result of FAN events received from the local ONS daemon, or from the remote ONS if the local daemon is not in use, and is handled by a dedicated event processing thread. JDBC thin and JDBC OCI drivers are supported.

Thus, if UCP and FCF are enabled, your Java program automatically becomes an ONS participant without having to handle FAN events directly. When a service or node in the middle level of the ONS is rendered inoperative, the event handler automatically marks the corresponding connections as inoperative and terminates them. This prevents applications that request connections from the cache from receiving invalid or bad connections.

Each time a service update event is received at the middle ONS level, the event handler recycles some unused connections and restores them using the event’s service name. The number of reused connections is automatically determined by the connection cache. Since listeners perform connection load balancing, this automatically redistributes connections between preferred service instances without waiting for connection requests or connection attempts.

JDBC/ODP.NET Benefits of FCF

By enabling FCF, your existing Java applications that connect via Oracle JDBC and Application Services or your .NET applications that use the ODP.NET and Application Services connection pools receive the following benefits:

• All connections to the database are distributed to all RAC instances that support the new service name, instead of routing the first batch of sessions to the first RAC instance. This is based on the load balancing advisory algorithm you use (see next slide). Connection pools are rebalanced when service, instance, or node start events occur.
• The connection cache immediately begins posting connections to a particular RAC instance when a new service is started on that instance.
• The connection cache immediately disconnects obsolete connections to RAC instances when service on that instance is stopped, or when the node is down.
• Your application automatically becomes a FAN participant without having to manage FAN events directly. You only need to set indicators in the connection descriptors.
• Once the service status changes to not_restarting, an exception is immediately thrown to prevent unnecessary attempts to connect to the service.

Tax balancing consultations

Load balancing distributes the work across all available RAC database instances. Well-written applications use persistent connections that span the RAC instances that provide the services. Bonds are rarely formed and last for a long time. The work enters the system at a high frequency, establishes these connections, and persists for a relatively short period of time.

The role of the Load Balancing Advisory is to recommend that incoming work be routed to the RAC agencies that provide the optimal quality of service for that work. The LBA algorithm uses metrics that are sensitive to the current performance of system services.

The Load Balancing Council is used with major Oracle clients such as Listener, JDBC Universal Connection Pool, OCI Session Pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and ODP.NET Connection Pools. Third-party applications can also subscribe to load balancing advisory events via the JDBC API and Oracle RAC FAN or via OCI callbacks.

Using load balancing Tips for load balancing take into account differences in computer capacity, blocked sessions, failures that block processing, and competing services of varying importance. Using load balancing tips prevents work from being sent to overloaded, failed or non-functioning nodes.

JDBC/ODP.NET DCS runtime connection load balancing: Overview

Without the use of Load Balancing Advisory, work requests are assigned to RAC instances on a random basis, which is appropriate if each instance performs equally well. However, if one of the instances is busier than the other due to the workload involved in each connection assignment, the random model will not work optimally.

The connection load balancing feature at runtime ensures that connections are allocated based on load balancing information from RAC cluster instances. The connection cache allocates connections between clients based on a relative number that indicates the percentage of work requests each instance should handle.

In the above diagram, the feedback shows that INST1’s CRM service is so busy that it should handle only 10% of CRM work requests; INST2 is so lightly loaded that it should handle 60%; and INST3 is somewhere in the middle, with 30% of the requests. It should be noted that these percentages apply to each department and the decision is taken on a departmental basis. In this example, the service is CRM.

Note Runtime connection load balancing is a feature of Oracle connection pools.

Oracle Net Services provides the ability to distribute client connections between instances in a RAC configuration. In the case of server-side load balancing, the SCAN listener forwards the connection request to the best instance currently providing the service, based on the -clbgoal parameter for that service.

When you create a RAC database with DBCA, server-side load balancing is automatically configured and enabled. An example of a client-side load balancing connection definition is also created in the tnsnames.ora file on the server. FAN, Fast Connection Failover, and load balancing polling depend on the exact configuration of connection load balancing, including defining a connection load balancing target for the service. The target can be LONG or SHORT for load balancing the connection. These goals have the following characteristics:

1. SHORT TERM : Use the SHORT connection load balancing method for applications that use load balancing at runtime. If you are using connection pools that are integrated with Load Balancing Advisory, set the destination to SHORT.

srvctl modify service -db orcl -service oltpapp -clbgoal SHORT

2. LONG : Use the LONG method if no load balancing is required at runtime. This is typical of a batch operation. LONG is the default target for connection load balancing.

srvctl modify service -db orcl -service batchconn -clbgoal LONG

If you set the load balancing target for the connection to NONE at runtime, load balancing is disabled for the service. You can see the target parameters of a service in the data dictionary by querying the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views. You can also view load balancing settings for the service using Oracle Enterprise Manager.

LBA FANEvent monitoring

You can use the SQL query below to check the FAN events with load balancing notification for each of your services.

SQL> SELECT TO_CHAR(enq_time, ‘HH:MI:SS’) enq_time, user_data
2 FROM sys.sys$service_metrics_tab
3 ORDER BY 1 ;

ENQ_TIME USER_DATA
——– —————————————————–
…
04:19:46 SYS$RLBTYP(‘MYSERV’, ‘VERSION=1.0 database=orcl
service=MYSERV { {instance=orcl_2 percent=50
flag=UNKNOWN}{instance=orcl_1 percent=50 flag=UNKNOWN}
} timestamp=2013-07-19 11:07:32’)
04:20:16 SYS$RLBTYP(‘MYSERV’, ‘VERSION=1.0 database=orcl
service=MYSERV { {instance=orcl_2 percent=80
flag=UNKNOWN}{instance=orcl_1 percent=20 flag=UNKNOWN}
} timestamp=2013-07-19 11:08:11’)
SQL>

This source has been very much helpful in doing our research. Read more about oracle rac failover scenarios and let us know what you think.

Frequently Asked Questions

What is load balancing in Oracle RAC?

Every now and then, you’ll stumble upon a trend that just doesn’t seem to go away. From time to time, these trends can be silly, like the ever-popular planking meme. Other times, they can be a little bit more serious, such as stage diving. As you might have guessed, a stage channel in Discord is a public chat room where you can discuss something that’s either related to a certain streamer or video game, or something entirely different. what is a stage channel in discord?

How do you control what workloads are run in a RAC environment?

Every now and then, you’ll stumble upon a trend that just doesn’t seem to go away. From time to time, these trends can be silly, like the ever-popular planking meme. Other times, they can be a little bit more serious, such as stage diving. As you might have guessed, a stage channel in Discord is a public chat room where you can discuss something that’s either related to a certain streamer or video game, or something entirely different. what is a stage channel in discord?

What is OC4J in Oracle RAC?

Every now and then, you’ll stumble upon a trend that just doesn’t seem to go away. From time to time, these trends can be silly, like the ever-popular planking meme. Other times, they can be a little bit more serious, such as stage diving. As you might have guessed, a stage channel in Discord is a public chat room where you can discuss something that’s either related to a certain streamer or video game, or something entirely different. what is a stage channel in discord?

Related Tags:

how to check oracle rac load balancingload balancing advisory oracle racoracle rac failoveroracle rac failover scenariosoracle rac node failurehow to test failover in oracle rac,People also search for,Feedback,Privacy settings,How Search works,how to check oracle rac load balancing,load balancing advisory oracle rac,oracle rac failover,oracle rac failover scenarios,oracle rac node failure,how to test failover in oracle rac,the database service instance is the target of the connection requests from applications,oracle rac service preferred available