This chapter contains the following sections:
You scale the number of AGL data sources as the number of services increases in the data base, independent of the number of nodes in the Oracle RAC cluster(s). Examples of High Availability support for multiple clusters include Data Guard, GoldenGate, and Global Database Service.
Active GridLink and Multi Data Source are designed to work with Oracle RAC clusters. Oracle does not recommend using generic data sources with Oracle RAC clusters. See Comparing AGL and Multi Data Sources.
Figure 6-1 Active GridLink Data Source Connectivity
An AGL data source includes the features of generic data sources plus the following support for Oracle RAC:
An AGL data source uses Fast Connection Failover and responds to Oracle RAC events using Oracle Notification Service (ONS). This ensures that the connection pool in the AGL data source contains valid connections (including reserved connections) without the need to poll and test connections. It also ensures that connections are created on new nodes as they become available.
Figure 6-2 Fast Connection Failover
An AGL data source uses Fast Connection Failover to:
Provide rapid failure detection.
Abort and remove invalid connections from the connection pool.
Adapt to changes in topology, such as adding or removing a node.
Distribute runtime work requests to all active Oracle RAC instances, including those rejoining a cluster.
AGL data sources do not support the deprecated
FastConnectionFailoverEnabled connection property. An attempt to create an XA connection with this property enabled results in a
java.sql.SQLException: Can not use getXAConnection() when connection caching is enabled exception because the driver implementation of Fast Connection Failover for this property does not support XA connections.
AGL data sources provide load balancing. AGL data sources use runtime connection load balancing (RCLB) to distribute connections to Oracle RAC instances based on Oracle FAN events issued by the database. This simplifies data source configuration and improves performance as the database drives load balancing of connections through the AGL data source, independent of the database topology.
Runtime Connection Load Balancing allows WebLogic Server to:
Adjust the distribution of work based on back end node capacities such as CPU, availability, and response time.
React to changes in Oracle RAC topology.
Manage pooled connections for high performance and scalability.
Figure 6-3 Runtime Connection Load Balancing
If FAN is not enabled, AGL data sources use a round-robin load balancing algorithm to allocate connections to Oracle RAC nodes.
Connections may be shut down periodically on AGL data sources. If the connections allocated to various RAC instances do not correspond to the Runtime Load Balancing percentages in the FAN load-balancing advisories, connections to overweight instances are destroyed and new connections opened. This process occurs every 30 seconds by default.
You can tune this behavior using the
weblogic.jdbc.gravitationShrinkFrequencySeconds system property which specifies the amount of time, in seconds, the system waits before rebalancing connections. A value of 0 disables the rebalancing process.
WebLogic Server GridLink affinity policies are designed to improve application performance by maximizing RAC cluster utilization. See:
Web applications have better performance when repeated operations against the same set of records are processed by the same RAC instance. Business applications such as online shopping and online banking are typical examples of this pattern.
An AGL data source uses the Session Affinity policy to ensure all the data base operations for a web session, including transactions, are directed to the same Oracle RAC instance of a RAC cluster.
The context is stored in the HTTP session. It is up to the application how windows (within a browser or across browsers) are mapped to HTTP sessions.
If an AGL data source with a session affinity policy is accessed outside the context of a web session, the affinity policy changes to the XA affinity policy. See XA Affinity Policy.
Figure 6-4 Session Affinity
An AGL data source monitors RAC load balancing advisories (LBAs) using the AffEnabled attribute to determine if RAC affinity is enabled for a RAC cluster. The first connection request is load balanced using Runtime Connection Load-Balancing (RCLB) and is assigned an Affinity context. All subsequent connection requests are routed to the same Oracle RAC instance using the Affinity context of the first connection until the session ends or the transaction completes. Affinity is based on the database name, service name, and instance name. Although the Session Affinity policy for an AGL data source is always enabled by default, a Web session is active for Session Affinity if:
Oracle RAC is enabled, active, and the service has enabled RCLB. RCLB is enabled for a service if the service
CLB_GOAL) is set to either
The database determines there is sufficient performance improvement in the cluster wait time and the Affinity flag in the payload in the information from ONS is set to
If the database determines it is not advantageous to implement session affinity, such as a high database availability condition, the database load balancing algorithm reverts to its default work allocation policy and the Affinity flag in the payload is set to
XA Affinity for global transactions ensures all the data base operations for a global transaction performed on an Oracle RAC cluster are directed to the same Oracle RAC instance. There are limitations to consider:
XA transaction can't span instances.
Strict affinity is enforced for connections within an XA transaction. If a connection cannot be created on the correct instance, an exception is thrown.
Figure 6-5 XA Affinity
There are two options to load balance connections across nodes:
Use a single Oracle Single Client Access Name (SCAN) address
Use multiple non-SCAN addresses with LOAD_BALANCE=on
Using a SCAN address is recommended over using multiple non-SCAN addresses. However, a SCAN address can only be used if your database is configured to use it. Contact your network administrator for appropriately configured SCAN URLs for your environment.
When using Oracle RAC 11.2 and higher, consider the following:
If the Oracle RAC listener is set to
SCAN, the AGL data source configuration can only use a SCAN address.
If the Oracle RAC listener is set to
List of Node VIPs, the AGL data source configuration can only use a list of VIP addresses.
If the Oracle RAC listener is set to
Mix of SCAN and List of Node VIPs, the AGL data source configuration can use both SCAN and VIP addresses.
Overview of Automatic Workload Management with Dynamic Database Services in Real Application Clusters Administration and Deployment Guide.
Oracle Single Client Access Name (SCAN) White Paper at http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
This feature allows you to configure secure communication with the ONS listener using Oracle Wallet. See Secure ONS Client Communication.
This topic describes the support for active data guard.
Active GridLink also works with Oracle Active Data Guard. Oracle Clusterware must be installed and active on the primary and standby sites for both single instance (using Oracle Restart) and Oracle RAC databases. Oracle Data Guard broker coordinates with Oracle Clusterware to properly fail over role-based services to a new primary database after a Data Guard failover has occurred. Cluster Ready Services (CRS) posts FAN events when the role change occurs.
Use the WebLogic Server Administration Console or WLST to create an AGL data source in a WebLogic domain.
Create JDBC GridLink data sources in the Oracle WebLogic Server Administration Console Online Help.
The sample WLST script
EXAMPLES_HOME represents the directory in which the WebLogic Server code examples are configured. This example creates a generic data source. See WLST Online Sample Scripts in Understanding the WebLogic Scripting Tool.
The following sections provide an overview of the basics steps used in the data source configuration wizard to create a data source using the WebLogic Server Administration Console:
JDBC Data Source Properties include options that determine the identity of the data source and the way the data is handled on a database connection.
JDBC data source names are used to identify the data source within the WebLogic domain. For system resource data sources, names must be unique among all other JDBC system resources, including data sources. To avoid naming conflicts, data source names should also be unique among other configuration object names, such as servers, applications, clusters, and JMS queues, topics, and servers. For JDBC application modules scoped to an application, data source names must be unique among JDBC data sources that are similarly scoped.
Select the scope for the data source from the list of available scopes. You can set the scope to Global (at the domain level), or to any existing Resource Group or Resource Group Template.
You can configure a data source so that it binds to the JNDI tree with a single or multiple names. You can use a multi-JNDI-named data source in place of legacy configurations that included multiple data sources that pointed to a single JDBC connection pool. See Developing JNDI Applications for Oracle WebLogic Server.
When you configure a JDBC data source using the WebLogic Server Administration Console, WebLogic Server automatically selects specific transaction options based on the type of JDBC driver:
For the XA driver, the system automatically selects the Two-Phase Commit protocol for global transaction processing.
For the non-XA or replay driver, local transactions are supported by definition, and WebLogic Server offers the following options
Supports Global Transactions: (selected by default) Select this option if you want to use connections from the data source in global transactions, even though you have not selected an XA driver. See Enabling Support for Global Transactions with a Non-XA JDBC Driver.
When you select Supports Global Transactions, you must also select the protocol for WebLogic Server to use for the transaction branch when processing a global transaction:
Logging Last Resource: With this option, the transaction branch in which the connection is used is processed as the last resource in the transaction and is processed as a local transaction. Commit records for two-phase commit (2PC) transactions are inserted in a table on the resource itself, and the result determines the success or failure of the prepare phase of the global transaction. This option offers some performance benefits and greater data safety than Emulate Two-Phase Commit, but it has some limitations. See Understanding the Logging Last Resource Transaction Option.
Emulate Two-Phase Commit: With this option, the transaction branch in which the connection is used always returns success for the prepare phase of the transaction. It offers performance benefits, but also has risks to data in some failure conditions. Select this option only if your application can tolerate heuristic conditions. See Understanding the Emulate Two-Phase Commit Transaction Option.
One-Phase Commit: (selected by default) With this option, a connection from the data source can be the only participant in the global transaction and the transaction is completed using a one-phase commit optimization. If more than one resource participates in the transaction, an exception is thrown when the transaction manager calls
XAResource.prepare on the 1PC resource.
For more information on configuring transaction support for a data source, see JDBC Data Source Transaction Options.
Connection Properties are used to configure the connection between the data source and the DBMS. Typical attributes are the service name, database name, host name, port number, user name, and password.
Using service names:
When a Database Domain is used, service names must be suffixed with the domain name. For example, if the database name is
db.country.myCorp.com, the service name
myservice would need to be entered as
The console allows you to enter connection properties in one of the following ways:
On the GridLink data source connection Properties Options page, select Enter individual listener information and click Next. Enter the connection properties. For example:
Enter myService in
Enter left:1234, center:1234, right:1234 in the
Host and Port:. Separate the host and port of each listener with colon.
Enter myDataBase in
Database User Name.
Enter myPassword1 in
If required, set Protocol to
The console automatically generates the complete JDBC URL. For example:
jdbc:oracle:thin:@( DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=left)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=center)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=right)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=myService)))
On the GridLink data source connection Properties Options page, select Enter complete JDBC URL and click Next. Enter the connection properties. For example:
In Complete JDBC URL, enter the JDBC URL. For example:
jdbc:oracle:thin:@( DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=left)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=center)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=right)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=myService)))
You can also use a SCAN address. For example:
Enter myDataBase in
Database User Name.
Enter myPassword1 in
If required, set Protocol to
This topic describes the supported AGL data surce url formats.
AGL data sources only support long format JDBC URLs. The supported long format pattern is:
Easy Connect (short) format URLs are not supported for AGL data sources. The following is an example of a Easy Connect URL pattern that is not supported for use with AGL data sources:
Recommendations for AGL Data Source URLs
The following section provides general recommendations when creating AGL data source URLs.
Use a single
DESCRIPTION. Avoid a
DESCRIPTION_LIST to avoid connection delays.
ADDRESS_LIST for each RAC cluster or DataGuard database
RETRY_COUNT, RETRY_DELAY, CONNECT_TIMEOUT at the
DESCRIPTION level so that all
ADDRESS_LIST entries use the same value.
RETRY_DELAY specifies the delay, in seconds, between the connection retries. This attribute is new in the Oracle 126.96.36.199 release.
RETRY_COUNT is used to specify the number of times an
ADDRESS list is traversed before the connection attempt is terminated. The default value is 0. When using
SCAN listeners with
RETRY_COUNT to a value of 2 means that if you had 3
SCAN IP addresses, each would be traversed three times each, resulting in a total of nine connect attempts (3 * 3)
LOAD_BALANCE=on for each address list to balance the
The service name should be a configured application service, not a PDB or administration service.
CONNECT_TIMEOUT is used to specify the overall time used to complete the Oracle Net connect. Set
CONNECT_TIMEOUT=90 or higher to prevent logon storms. For JDBC driver 188.8.131.52 and earlier,
CONNECT_TIMEOUT is also used for the TCP/IP connection timeout for each address in the URL. When considering TCP/IP connections, a shorter
CONNECT_TIMEOUT is preferred though secondary to overall timeout requirements.
Do not set the
oracle.net.CONNECT_TIMEOUT driver property on the data source because it is overridden by the URL property.
Test Database Connection allows you to test a database connection before the data source configuration is finalized using a table name or SQL statement. If necessary, you can test additional configuration information using the
System Properties attributes.
ONS client configuration allows the data source to subscribe to and process Oracle FAN events. When configuring the ONS node list, Oracle recommends not specifying a value and allowing auto-ONS to perform the ONS configuration. In some cases, however, it is necessary to explicitly configure the ONS configuration, for example if you need to specify an Oracle Wallet and password, or if you want to explictly specify the ONS topology.
You can also configure an ONS client using WLST. For an example, see Configuring an ONS Client Using WLST
To configure an ONS client from the Summary of Data Sources page in the Administration Console, see Configure ONS client parameters in Oracle WebLogic Server Administration Console Online Help.
In general, if a WebLogic Server datasource setting of initial capacity is set to 0, WebLogic Server makes no DBMS connections at startup. For AGL datasources with Auto-ONS, WebLogic Server needs to connect to the DBMS once at startup to get the ONS information.
To ensure that the data source is configured to subscribe to and process Oracle Fast Application Notification (FAN) events, select
This topic describes configuration of ONS host and port.
There are two methods that you can use to configure the OnsNodeList value: a single node list or a property node list. You can use one or the other, but not both. If the WebLogic Server OnsNodeList contains an equals sign (=), it is assumed to be a property node list.
For both types of node lists you can use a Single Client Access Name (SCAN) address instead of a host name, and to access FAN notifications. For more information about SCAN addresses, see Scan Addresses.
To configure the OnsNodeList value using a:
Single node list—Specify a comma separated list of ONS daemon listen addresses and ports for receiving ONS-based FAN events. For example,
rac1:6200,rac2:6200. You can enter a single node list in the ONS host and port field in the Administration Console when creating an AGL Data Source.
Property node list—Specify a string composed of multiple records, with each record consisting of a key=value pair and terminated by a new line ('\n') character. For example, nodes.
1=rac1:6200,rac2:6200. You cannot enter a property node list in the ONS host and port field when creating a data source. Instead, you should leave this field blank. After you finish creating the data source, you can enter the property node list on the Configuration: ONS tab on the settings page for the data source.
You can specify the following keys in a property node list:
nodes.id—A list of nodes representing a unique topology of remote ONS servers. id specifies a unique identifier for the node list. Duplicate entries are ignored. The list of nodes configured in any list must not include any nodes configured in any other list for the same client or duplicate notifications will be sent and delivered. The list format is a comma separated list of ONS daemon listen addresses and ports pairs separated by colon.
maxconnections.id—Specifies the maximum number of concurrent connections maintained with the ONS servers. id specifies the node list to which this parameter applies. The default is
true, the list is active and connections are automatically established to the configured number of ONS servers. If false, the list is inactive and is only be used as a fail over list in the event that no connections for an active list can be established. An inactive list can only serve as a fail over for one active list at a time, and once a single connection is re-established on the active list, the fail-over list reverts to being inactive. Note that only notifications published by the client after a list has failed over are sent to the fail over list.
id specifies the node list to which this parameter applies. The default is
remotetimeout —The timeout period, in milliseconds, for a connection to each remote server. If the remote server has not responded within this timeout period, the connection is closed. The default is 30 seconds
walletpasswordare supported in the string, WebLogic Server has separate configuration elements for these values,
To use an Oracle Wallet file with WebLogic Server, you must:
Update your AGL data source configuration to include the directory of the Oracle wallet file in which the SSL certificates are stored and optionally, the ONS Wallet password. See Secure ONS Listener using Oracle Wallet in Oracle WebLogic Server Administration Console Online Help.
For more information on Oracle Wallet, see the Creating and Managing Oracle Wallet.
Test ONS client configuration allows you to test a connection to the ONS listener before the data source configuration is finalized.
To use the Socket Direct Protocol (SDP), your database network must be configured to use Infiniband. SDP does not support SCAN addresses.
See Configuring SDP Support for InfiniBand Connections in the Oracle Database Net Services Administrator's Guide.
To configure load balancing across SDP connections, you must edit the
TNSNAMES.ORA file on all nodes and add an SDP end-point to the
TNSNAMES.ORA file is only read at instance startup or when using an
ALTER SYSTEM SET LISTENER_NETWORKS="
" command. After updating the
TNSNAMES.ORA file, restart all instances or run the
ALTER SYSTEM SET LISTENER_NETWORKS command on all networks.
LISTENER_IBLOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sclcgdb02ibvip.country.myCorp.com)(PORT=1522)) (ADDRESS = (PROTOCOL = SDP)(HOST = sclcgdb02-bvip.country.myCorp.com)(PORT=1522)) ) )
You should then distribute connections on the
LISTERNER_IB network using the following URL:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=SDP) (HOST=sclcgdb01-bvip.country.myCorp.com)(PORT=1522))(ADDRESS=(PROTOCOL=SDP) (HOST=sclcgdb02-ibvip.country.myCorp.com)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=elservice)))
Certain Oracle JDBC extensions may durably alter a connection's behavior in a way that future users of the pooled connection will inherit. WebLogic Server attempts to protect connections against some types of these calls when possible.
The following sections include information about connection pool options for a JDBC data source.
You can see more information and set these and other related options through the:
WebLogic JDBC data sources support the
javax.sql.ConnectionPoolDataSource interface implemented by JDBC drivers. You can enable driver-level features by adding the property and its value to the
Properties attribute in a JDBC data source. Driver-level properties in the
Properties attribute are set on the driver's
Do not use
ConnectionCacheName as Driver-level properties in the
Properties attribute in a JDBC data source.
WebLogic JDBC data sources support setting driver properties using the value of system properties. The value of each property is derived at runtime from the named system property. You can configure connection-based system properties using the WebLogic Server Administration Console by editing the
System Properties attribute of your data source configuration.
Do not specify
oracle.jdbc.FastConnectionFailover as a Java system property when starting the WebLogic Server.
When WebLogic Server creates database connections in a data source, the server can automatically run SQL code to initialize the database connection. To enable this feature, enter
SQL followed by a space and the SQL code you want to run in the Init SQL attribute on the JDBC Data Source: Configuration: Connection Pool page in the WebLogic Server Administration Console. Alternatively, you can specify simply a table name without
SQL and the statement
SELECT COUNT(*) FROM tablename is used. If you leave this attribute blank (the default), WebLogic Server does not run any code to initialize database connections.
WebLogic Server runs this code whenever it creates a database connection for the data source, which includes at server startup, when expanding the connection pool, and when refreshing a connection.
You can use this feature to set DBMS-specific operational settings that are connection-specific or to ensure that a connection has memory or permissions to perform required actions.
Start the code with
SQL followed by a space. An Oracle DBMS example:
SQL alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
or an Informix DBMS:
SQL SET LOCK MODE TO WAIT
The SQL statement is executed using
JDBC Statement.execute(). Options that you can set using InitSQL vary by DBMS. See the documentation for your database vendor for supported statements. If you want to execute multiple statements, you may want to create a stored procedure and execute it. The syntax is vendor specific. For example, to execute an Oracle stored procedure:
SQL CALL MYPROCEDURE()
WebLogic Server provides several attributes that provide improved data source performance when using Oracle drivers.
Use WLST to configure an ONS client.
The following fragment provides an example for setting the Oracle parameters of an Active Gridlink data source.
cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCOracleParams/' + dsName) cmo.setFanEnabled(true) cmo.setOnsNodeList('nodes.1=rac1:6200,rac2:6200\nmaxconnections.1=3\n')
For more information about configuring an ONS client, see ONS Client Communication.
By properly configuring the connection pool attributes in JDBC data sources in your WebLogic Server domain, you can improve application and system performance.
Learn about monitoring and debugging Active Gridlink data sources.
You can view run-time statistics for an AGL data source via the WebLogic Server Administration Console or through the associated runtime MBeans.
JDBCOracleDataSourceRuntimeMBean provides methods for getting the current state of the data source instance. The
JDBCOracleDataSourceRuntimeMBean provides methods for getting the current state of the data source and for getting statistics about the data source, such as the average number of active connections, the current number of active connections, and the highest number of active connections. This MBean also has a child
JDBCOracleDataSourceInstanceRuntimeMBean for each node that is active in the AGL datasource. See JDBCOracleDataSourceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.
JDBCOracleDataSourceInstanceRuntimeMBean provides methods for getting the current state of the data source instance. There an instance for each ONS listener that is active. In a configuration that uses
auto-ONS where the administrator doesn't configure the ONS string, this is the only way to discover which ONS listeners are available. See JDBCOracleDataSourceInstanceRuntimeMBean in the MBean Reference for Oracle WebLogic Server.
ONSDaemonRuntimeMBean provides methods for monitoring the ONS client configuration that is associated with an AGL data source.
The following is a WLST script for testing an ONS connection. In this example, the Active GridLink data source is named glds and it is targeted to myserver:
connect(<wluser>, <wlpassword>, 't3://localhost:7001') serverRuntime() cd('JDBCServiceRuntime') cd('myserver') cd('JDBCDataSourceRuntimeMBeans') cd('glds') cd('ONSClientRuntime') cd('glds') cd('ONSDaemonRuntimes') cd('glds_0') cmo.ping()
See ONSDaemonRuntimeMBean in the MBean Reference for Oracle WebLogic Server.
You can activate WebLogic Server's debugging features to track down the specific problem within the application
The following are registered debugging scopes for JDBC:
DebugJDBCRAC—prints information about AGL data source lifecycle, UCP callback, and connection information.
DebugJDBCONS—traces ONS client information, including the LBA event body. One trace is available for each ONS listener that is active. In a configuration that uses auto-ONS where the administrator doesn't configure the ONS string, this is the only way to see what ONS listeners are available.
DebugJDBCReplay—traces application continuity replay information.
DebugJDBCUCP—traces low level RAC information from the UCP driver.
You can enable UCP JDK logging by following the instructions in Setting Up Logging in UCP in Universal Connection Pool for JDBC Developer's Guide.
Set the appropriate AGL data source debugging properties on the command line. For example,
-Dweblogic.debug.DebugJDBCRAC=true -Dweblogic.debug.DebugJDBCONS=true -Dweblogic.debug.DebugJDBCUCP=true -Dweblogic.debug.DebugJDBCREPLAY=true
Setting these values is static and can only be used at server startup.
See java.util.logging in Java Platform Standard Edition API Specification.
Note:This is not a standard recommendation from Oracle.
Oracle recommends that you enable
TestConnectionsOnReserve. You might need to turn off FAN if a configured firewall doesn't allow this protocol to flow.
The following table indicates the availability of AGL data source features when FAN Enabled set to
Table 6-1 GridLink Features when FAN Enabled is False
|Active GridLink Feature||Available when FAN Enabled is False?|
Single data source configuration for access to RAC cluster
Runtime MBeans for individual RAC cluster instances
Connection load balancing using Runtime Load Balancing (RLB)
Fast Application Notification (FAN)
Fast Connection Failover (FCF)
Gravitation (rebalancing connections)
ONS Client Support, including password and encrypted wallet configurations
In WebLogic Server 12.1.2 and higher, the
ActiveGridlink attribute is used to explicitly declare a data source configuration as an AGL datasource. It is automatically enabled by the WebLogic Server Administration Console when creating a GridLink data source. If you create data source configurations using WLST, you must remember to set
To maintain backward compatibility with releases prior to WebLogic Server 12.1.2, a data source configuration is always an AGL data source configuration if
FanEnabled=true or the
OnsNodeList is non-null. In this case, the ActiveGridlink value is ignored.
Legacy data source configurations are not updated during the upgrade process. If you need to update a legacy AGL data source to access RAC clusters without enabling Fast Application Notification (FAN), edit or use WLST to set
ActiveGridlink=true in the configuration.
Learn about the best practices for using AGL data sources by understanding the catch and handle exceptions and how connections are created when using an AGL data source.
Applications need to catch and handle all exceptions. Applications using AGL data sources should expect exceptions, such as an
IO socket read error, when performing JDBC operations on borrowed connections. Best practice is to check the connection validity and reconnect if necessary. Connection exceptions can occur if the driver detects an outage earlier than FAN event arrival or as a result of the cleanup of a connection. For unplanned down events, a connection pool aborts all borrowed connections that are affected by the outage.
This section summarizes the change in connections in AGL, assuming FAN and ONS are enabled:
Connections are added to the pool initially based on the configured initial capacity. That uses connect time load balancing based on the listener. For that to work correctly, you must either specify
LOAD_BALANCE=ON for multiple non-scan addresses or use SCAN.
Connections are added to the pool on demand based on runtime load balancing. However, this is overridden by XA affinity or Web session affinity, in which case connections are added on the instance providing affinity to the last request in the transaction or Web session.
When a planned down event occurs, unused connections for that instance are released immediately and connections in use are released when returned to the pool.
When an unplanned down event occurs, all connections for that instance are destroyed immediately.
When an up event occurs, connections are proactively created on the new instance.
When gravitation shrinking occurs, one unused connection is destroyed on a heavily loaded instance (per period).
When normal shrinking occurs, half of the unused connections down to minimum capacity are destroyed without respect to load (per period).
There are several benefits to using AGL data sources over multi data sources when using Oracle RAC clusters.
The benefits include:
Requires one data source with a single URL. Multi data sources require a configuration with n generic data sources and a multi data source.
Eliminates a polling mechanism that can fail if one of the generic data sources is performing slowly.
Eliminates the need to manually add or delete a node to/from the cluster.
Provides a fast internal notification (out-of-band) when nodes are available so that connections are load-balanced to the new nodes using Oracle Notification Service (ONS).
Provides a fast internal notification when a node goes down so that connections are steered away from the node using ONS.
Provides load balancing advisories (LBA) so that new connections are created on the node with the least load, and the LBA information is also used for gravitation to move idle connections around based on load.
Provides affinity based on your XA transaction or your web session which may significantly improve performance.
Leverages all the advantages of HA configurations like DataGuard. For more information, see Oracle WebLogic Server and Highly Available Oracle Databases: Oracle Integrated Maximum Availability Solutions on the Oracle Technology network at http://www.oracle.com/technetwork/middleware/weblogic/learnmore/index.html.
Many multi Data Source data source users are migrating to AGL data sources. Migrating from multi data sources to AGL data sources is a simple manual process.
No changes should be required to your applications. A standard application looks up the MDS in JNDI and uses it to get connections. By giving the AGL the same JNDI name as the MDS, the process is exactly the same in the application to use a data source name from JNDI.
The only changes necessary should be to your configuration. An AGL data source is composed of information from the MDS and the member generic data sources combined into a single AGL descriptor. The only additional information that is needed is the configuration of Oracle Notification Service (ONS) on the RAC cluster. In many cases, the ONS information consists of the same host names as used in the MDS and the only additional information is the port number, and which can be simplified by the use of a SCAN address.
A MDS descriptor does not contain much information. The key components are:
The JNDI name. It must become the name of your new AGL data source to keep things transparent to the application. If you want to run the MDS in parallel with the AGL data source, then you must give the AGL data source a new JNDI name but you must also update the application to use that new JNDI name.
A list of the member generic data sources which provide any remaining information that you need to configure the AGL data source.
Each of the member generic data sources has its own URL. As described in Using Multi Data Sources with Oracle RAC,, it has the following pattern:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS= (PROTOCOL=TCP)(HOST=host1-vip)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=dbservice)(INSTANCE_NAME=inst1)))
Each member should have its own host and port pair. The members probably have the same service and often have the same port on different hosts. The URL for the AGL data source is a combination of the host and port pairs. For example:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=host1-vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2-vip)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=dbservice))
It is preferable to use an Oracle Single Client Access Name (SCAN) address instead of multiple host or Virtual IP (VIP) addresses. SCAN addresses are simpler and makes changes to the nodes in the cluster transparent. For more information on SCAN addresses, see the Oracle Real Application Clusters Administration and Deployment Guide. For example:
Ignore the Algorithm Type.
The following section provides the basic steps needed to migrate a MDS to a AGL data source:
Delete the MDS and the generic data sources from the configuration using the WebLogic Server Administration Console.
Add a single AGL data source using the WebLogic Server Administration Console.
Give it the same JNDI name as the MDS.
Select an XA or non-XA driver based on your what generic data sources used.
Enter the complete URL as described in Configuration Changes to Migrate a Multi Data Source.
Set the user and password, it should be the same as what you had on the MDS members.
On the Test GridLink Datasource Connection page, click Test All Listeners and verify the new URL.
Enter the information for the ONS connections. Specify one or more host
:port pairs. For example,
scanaddress:6200. If possible, use a single SCAN address and port. Make sure that FAN Enabled is checked.
Test the ONS connections.
Deploy the data source.
Edit the AGL data source and configure additional parameters.
There are many data source parameters that can't be configured while creating a new data source. In most cases, you should be able to use the parameter setting used in the MDS. If there are conflicts, you will need to resolve them and select the appropriate settings for your environment.
For more information on creating AGL data sources using the WebLogic Server Administration Console, see Configure JDBC GridLink data sources in Oracle WebLogic Server Administration Console Online Help.
Learn several ways to handle database downtime with AGL data sources in an Oracle RAC database environment.
Ensure that the AGL data source is configured as follows:
Fast Application Notification (FAN) is enabled. FAN provides rapid notification about state changes for database services, instances, the databases themselves, and the nodes that form the cluster. It allows for draining of work during planned maintenance with no errors returned to applications.
Is using auto-ONS, or an explicitly defined ONS configuration. See ONS Client Configuration.
Is using a dynamic database service. Do not connect using the administrative service or PDB service. They are for intended for administration purposes only and are not supported for FAN.
Test connections is enabled. Depending on the outage, applications may receive stale connections when connections are borrowed before a down event is processed. This can occur, for example, on a clean instance down when sockets are closed coincident with incoming connection requests. To prevent the application from receiving any errors, connection checks should be enabled at the connection pool. This requires setting
test-connections-on-reserve to true and setting the
test-table (the recommended value for Oracle is SQL ISVALID).
SCAN usage is optimized. For database drivers 184.108.40.206 and later, set the URL setting
LOAD_BALANCE=TRUE for the
ADDRESSLIST as an optimization to force re-ordering of the SCAN IP addresses that are returned from DNS for a SCAN address.
For database drivers before 220.127.116.11, use the connection property
See SCAN Addresses.
Transparent scheduled maintenance—Ensures that the scheduled maintenance process at the database servers is transparent to applications.
Session Draining—When an instance is brought down for maintenance at the database server, session draining ensures that all work using instances at that node completes and that idle sessions are removed. Sessions are drained without impacting in-flight work.
For maintenance purposes (such as software and hardware upgrades, repairs, changes, migrations within and across systems), the services used are shutdown gracefully one or several at a time without disrupting the operations and availability of the WebLogic Server applications. Upon a FAN DOWN event, AGL drains sessions away from the instance(s) targeted for maintenance. It is necessary to stop non-singleton services running on the target database instance (assuming that they are still available on the remaining running instances) or relocate singleton services from the target instance to another instance. Once the services have drained, the instance is stopped with no application errors
The following steps provide a high level overview of the planned maintenance process:
Table 6-2 Steps Performed on Database Server for AGL Planned Maintenance
|Step #||Database Server Steps||Command||Mid-Tier Reaction|
Stop the non-singleton service without -force or relocate the singleton service.
Omitting the –server option operates on all services on the instance.
The FAN Planned Down (reason=USER) event for the service informs the connection pool that a service is no longer available for use and connections should be drained. Idle connections on the stopped service are released immediately. In-use connections are released when returned (logically closed) by the application. New connections are reserved on other instance(s) and databases offering the services. This FAN action invokes draining the sessions from the instance without disrupting the application.
Disable the stopped service to ensure it is not automatically started again. Disabling the service is optional. This step is recommended for maintenance actions where the service must not restart automatically until the action has completed.
No new connections are associated with the stopped/disabled service at the mid-tier.
Allow sessions to drain.
The amount of time depends on the application. There may be long-running queries. Batch programs may not be written to periodically return connections and get new ones. It is recommended that batch be drained in advance of the maintenance.
Check for long-running sessions. Terminate these sessions using a transactional disconnect. Wait for the sessions to drain. You can run the query again to check if any sessions remain.
The connection on the mid-tier will get an error. If using application continuity, it is possible to hide the error from the application by automatically replaying the operations on a new connection on another instance. Otherwise, the application gets a SQLException.
Repeat steps 1 through 4.
Repeat for all services targeted for planned maintenance
Stop the database instance using the immediate option.
No impact on the mid-tier until the database and service are re-started.
Optionally, disable the instance so that it will not automatically start again during maintenance.
This step is for maintenance operations where the services cannot resume during the maintenance.
Perform the scheduled maintenance work (patches, repairs, and changes).
Enable and start the instance.
Enable and start the service back. Check that the service is up and running.
The FAN UP event for the service informs the connection pool that a new instance is available for use, allowing sessions to be created on this instance at the next request submission. Automatic rebalancing of sessions starts.
shows the distribution of connections for a service across two Oracle RAC instances before and after Planned Downtime. Notice that the connection workload moves from fifty-fifty across both instances to hundred-zero. In other words, RAC_INST_1 can be taken down for maintenance without any impact on the business operation.
Figure 6-6 Distribution of Connections Across Two Oracle RAC Instances
There are several differences when an unplanned outage occurs:
A component at the database server may fail making all services unavailable on the instances running at that node. There is no stop or disable on the services because they have failed.
The FAN unplanned DOWN event (
reason=FAILURE) is delivered to the mid-tier.
All sessions are closed immediately, preventing the application from hanging on TCP/IP timeouts. Existing connections on other instances remain usable, and new connections are opened to these instances as needed.
There is no graceful draining of connections. For those applications using services that are configured to use Application Continuity, active sessions are restored on a surviving instance and recovered by replaying the operations, masking the outage from applications. If not protected by Application Continuity, any sessions in active communication with the instance receive a SQLException.
During planned database maintenance, gradually close the database connections instead of closing all of the connections immediately. This strategy prevents uneven performance by the application.
When planned database maintenance occurs, a planned down service event is processed by the WebLogic Server JDBC data source. By default, all unreserved connections in the pool are closed immediately and borrowed connections are closed when they are returned to the pool. This shutdown process can cause uneven application performance because:
New connections need to be created on the alternative instances.
A logon storm can occur on the other instances.
This feature is supported for an active GridLink data source running with Oracle RAC.
Setting the Drain Timeout Period
The connection property
weblogic.jdbc.drainTimeout is recognized to define the draining period in seconds. The value must be a non-negative integer. For example, the following is a sample from a WLST script that creates a datasource.
jdbcSR = create(dsname, 'JDBCSystemResource') jdbcResource = jdbcSR.getJDBCResource() driverParams = jdbcResource.getJDBCDriverParams() driverProperties = driverParams.getProperties() drainprop = driverProperties.createProperty('weblogic.jdbc.drainTimeout') drainprop.setValue('60')
When running with the Oracle database 12.2 driver and the Oracle database 12.2 server, the drain timeout can be configured on the database server side by setting
-drain_timeout on the database service. For example, a replayable service can be created by using:
srvctl add service -db ORCL -service otrade -clbgoal SHORT -preferred orcl1,orcl2 -rlbgoal SERVICE_TIME -failoverretry 30 -failoverdelay 10 -failovertype TRANSACTION -commit_outcome TRUE -replay_init_time 1800 -retention 86400 -notification TRUE -drain_timeout 60
If both the connection property and the server-side drain timeout are set on an Oracle database 12.2 configuration, the server-side value takes precedence. This value is only used during a planned down event to stop some but not all of the instances on which a service is running. For example,
srvctl stop service -db ORCL -instance orcl2 -service otrade.us.oracle.com
If the drain period is not set or set to 0, then by default, there is no drain period and connections are closed immediately.
A small value accelerates the migration, but might cause applications to experience higher response times, as requests on the target node hit a cold buffer cache. A larger value migrates work more gently and gives the buffer cache on the target node more time to warm-up, which in consequence leads to reduced impact on the application, but a longer overall migration duration.
Gradual Draining Processing
Processing starts when a database service that is configured for an AGL datasource is stopped using
srvctl stop service -db dbname -instance instancename -service servicename
Note:Draining is not done if all services are shutdown (for example, when no instance name is specified).
If the drain timeout is not set or set to 0, there is no drain period. Unreserved connections are immediately closed and borrowed connections are closed when returned to the pool.
If the drain timeout is specified, it takes effect only if the service is available at another RAC instance. For active/active services draining is gradual. For active/passive services, version 12.2 of RAC relocates the service first, so gradual draining is also supported. This feature does not work with Oracle DataGuard, which has only one primary active service at a time.
If an alternative instance is available, the drain timeout period is started. The granularity and reducing the connections is done on a five-second interval. The total connection count is the count of the unreserved and the count of the reserved connections. The total count is divided by the value “(drain period/5)” to compute the number of connections to be released per interval (note that if the number is less than 1, then some intervals may not have any connections drained). After each five-second interval, harvestable connections are harvested and interval count connections are closed if they are unreserved or marked for closure on return to the pool. After the last interval, the instance is marked as down (with respect to monitor status).
If a datasource is suspended or shut down, draining is stopped on any instance that is currently draining. Unreserved connections are immediately closed and borrowed connections are closed when returned to the pool.
If a service is started again on an instance that is draining for that service, draining is stopped.
If a service is stopped on all instances by not specifying a instance name or the last instance is stopped, draining is stopped on all instances. For all instances, unreserved connections are immediately closed and borrowed connections are closed when returned to the pool.
When draining is happening on an instance, connection gravitation on the datasource (rebalancing connections based on the runtime load balancing information) is stopped until the draining completes.
When the service is stopped, the Load Balance Advisories (LBA) indicates that the percentage for the stopped service should be 0. This causes the preference for allocating existing connections to other instances first. If a connection does not exists on the other instances and a connection exists on the stopped service, it will pick that one instead of creating a connection. This applies to connections created using LBA or Session Affinity. XA affinity will try to create a new connection for the instance in the affinity context, and only use a different instance or branch if a new connection can't be created.
beadev2” just after 25:00. Note that it takes a while for the Load Balancing Advisories (LBA) to respond to the shut down at around
25:25and the percentage goes to
0for instance “beadev2”. WebLogic Server receives the shutdown event almost instantly and starts to take action. If gradual draining were not configured, the graph of Current Capacity would show the capacity dropping to
0(or the count of active connections) immediately when the event is received. Instead, you can see that the capacity gradually goes down every five- seconds for the sixty-second drain period and there is a corresponding increase in capacity on “beadev1”. Note that the total capacity stays constant through the entire period.
Note:These graphs were generated from an artificial work-load of requests that are getting a connection, doing a little work, and releasing the connection. In the real world, the results may not be so perfect.