This blog describes the use of Oracle's Autonomous Transaction Processing (ATP) service with a WebLogic Server (WLS) datasource. There is documentation available from various sources that sort of covers this but this document will try to pull it all together in one place for WLS and try to cover solutions for difficulties seen by our customers. This document is focused on what is more specifically called ATP-S (as opposed to ATP-D, the RAC-based version - most of the information in this article is applicable).
See Oracle Cloud Autonomous Transaction Processing for more information on ATP.
The blog Creating an Autonomous Transaction Processing (ATP) Database has screen shots for creating a new ATP database from the Cloud console, during which you specify the passwords for the database ADMIN user and download the client credentials wallet. This blog assumes you have already completed that process and downloaded the wallet zip file. The creation blog assumes /tmp/demoatp is used the wallet directory and this blog uses the same. The use of these files will be described further below in relation to datasource configuration. The only information that you need is the alias name from the tnsnames.ora file. For WLS, you should use the alias name of the form "name_tp"; this service is configured correctly for WLS transaction processing.
This blog article has functional scripts for creating the datasource using either online WLST or REST and also has screen shots for creating the datasource via the administration console. Before creating the datasource, we need to check a few prerequisites.
WebLogic Server releases 184.108.40.206.0 and 220.127.116.11.0 shipped with the 19.3 driver and support JDK8 so they work with ATP out-of-the-box. It is recommended that you use one of these releases or later for the simplest platform to get started. The following sections detail the requirements for running with ATP.
WebLogic Server18.104.22.168.0 and later are certified with ATP. The following sections have some information about using earlier WLS versions but are not currently certified or supported.
WebLogic Server 12.1.3 and later support JDK 8. Look at the update number for the JDK by running `java -version` and checking to see if it is 1.8.0_169 or later. If you haven't been keeping up with quarterly JDK 8 CPU's (shame on you), you have the option of either catching up to at least update 169 or later (this is highly recommended), or you need to download the JCE Unlimited Strength Jurisdiction Policy Files 8. See the associated README file for installation notes. Without this, you will get a 'fatal alert: handshake_failure' when trying to connect to the database.
WebLogic Server 10.3.6 through 12.1.2 run on JDK7 (JDK 8 is not supported). If running on JDK7, you need to download and install the JCE Unlimited Strength Jurisdiction Policy Files 7.
WLS 22.214.171.124.0 shipped with the 126.96.36.199 Oracle driver. There are no special requirements for using this driver and the attached scripts should work with no changes. That makes configuration (and certification) simple. You can skip to the next section.
WLS versions 12.1.3 through 188.8.131.52.0 shipped with the 184.108.40.206 Oracle driver. WLS version 10.3.6 through 12.1.2 shipped with the 220.127.116.11 driver. The 18.104.22.168 driver works with the 18c and 19c database servers. It is possible to upgrade the 22.214.171.124 driver to to the 126.96.36.199 driver using information at this link (driver upgrades are only supported for WLS, not JRF or FA). The 188.8.131.52 and 184.108.40.206 drivers need a patch to wlserver_10.3/server/lib/ojdbc7.jar to support TLSv1.2. See this link to download the jar file or apply a patch for the bug 23176395. Refer to the MOS note 2122800.1 for more details.
When using the pre-12.2.x driver, the use of driver connection properties for SSL configuration, as shown in the attached scripts, is not supported (you don't need to remove them from the scripts - they will be ignored). You must instead use command-line system properties as documented at this link. For example, set -Doracle.net.tns_admin=<absolute path to client credentials> , -Doracle.net.wallet_location=file://< absolute path to client credentials>, and -Doracle.net.ssl_version=1.2 as JVM args.
For FMW/FA running on 11g, ojdbc7.jar lives at wlserver_10.3/server/lib/ojdbc7.jar and ojdbc7dms.jar lives at modules/oracle.jdbc_11.1.1/ojdbc7dms.jar. It is also necessary to copy an updated 18c/19c version of oraclepki.jar, osdt_core.jar, and osdt_cert.jar to the oracle_common/modules directory (for example, the 18.3 driver jar files can be downloaded from ttps://www.oracle.com/database/technologies/appdev/jdbc-ucp-183-downloads.html ). Also, add a new security provider oracle.secruity.pki.OraclePKIProvider to jdk1.7/jre/lib/security/java.security in position #3 (this allows for using SSO or PKCS12 wallets).
You can and may need to update to a later version of Oracle driver jar files (18c or later) if you want some newer features, such as HTTP proxy configuration. Note that this requires the environment to be running JDK8.
If the client is behind a firewall and your network configuration requires an HTTP proxy to connect to the internet, you have two options. You can convince your network administrator to open outbound connections to hosts in the oraclecloud.com domain using port 1522 without going through an HTTP proxy. The other option if running on JDK8 is to upgrade to the Oracle 18c or later JDBC Thin Client, which enables connections through HTTP proxies. See the blog Oracle 18.3 Database Support with WebLogic Server for instructions on how to get the jar files and update your CLASSPATH/PRE_CLASSPATH. In addition, you will need to update the _tp service entry in the tnsnames.ora file to change "address=" to "address= (https_proxy=proxyhostname)(https_proxy_port=80)". Failure to do this will cause connections to the database to hang or not find the host.
Now that you have the necessary credential files, JDK, and driver jar files, you are ready to create the datasource.
The online-WLST script is attached at this link to online-WLST script (rename it online.py). To run the script, assuming that the server is started with the correct JDK and driver jar files, just run
java weblogic.WLST online.py
The REST script is attached at this link to REST script (rename it rest.sh). To run the script, assuming that the server is started, just run the following from the domain home directory
Both scripts create the same datasource descriptor file and deploy the datasource to a server. Let's look at scripts to see how the datasource is configured. In each of these scripts, the variables that you need to set are at the top so you can update the script quickly and not touch the logic. WLST uses python variables and the REST script uses shell variables.
The alias name (serviceName variable) of the form of the form "name_tp" is taken from the tnsnames.ora file. The URL is generated by using an @alias format "jdbc:oracle:thin:@name_tp". For this to work, we also need to provide the directory where tnsnames.ora file is located (the "tns_admin" variable) using the "oracle.net.tns_admin" driver property. Note that the URL information in the tnsnames.ora uses the long format so that the protocol can be alternatively specified as TCPS.
The datasource name (variable "dsname") is also used to generate the JNDI name by prefixing it with "jndi." in the example. You can change it to match your application requirements.
The recommended test table name is "SQL ISVALID" for optimal testing and performance. You can set other connection pool parameters based on the standards for your organization.
ATP-S provides access to a single Pluggable DataBase (PDB) in a Container DataBase (CDB). (ATP-D provides an entire CDB and one or more PDB's.) Most of the operations on the PDB are similar to a normal Oracle database. For ATP-S, you have a user called ADMIN that does not have the SYSDBA role but does have some administrative permissions to do things like creating schema objects and granting permissions. The number of sessions is configured at 100 * ATP cores. You cannot create a tablespace and the default available tablespace is named DATA and the temporary table space is named TEMP. The block size is fixed at 8k, meaning you cannot create indexes over approximately 6k in size. Some additional information about restrictions is provided at Autonomous Transaction Processing for Experienced Oracle Database Users .
ATP-S is configured to not have GRID or RAC installed. That means that FAN is not supported and only WLS GENERIC datasources can be created (Multi Data Source and Active GridLink cannot be used). The driver may try to get FAN events from the ONS server and fail because it isn't configured. To avoid this, we explicitly set the driver property oracle.jdbc.fanEnabled=false. This property is no longer needed if using the 18c or later driver.
To create connections, we need to provide a user and password (variable names "user" and "password") for the datasource. The example uses the Admin user configured when the database was created. More likely, you will create additional users for application use. You can use your favorite tool like SQL*Plus to create the schema objects or you can use WLS utils.Schema to create the objects. The associated SQL DDL statements are described at this link in the user guide. The password will be encrypted in the datasource descriptor.
The remainder of the configuration is focused on setting up two-way SSL between the client and the database. There are two options for configuring this and the credentials are available for both in the wallet zip file.
For either option, we set the two driver properties
oracle.net.ssl_version=1.2 (this should be required only for the 12.x driver)
The first option is to use the Oracle auto-open SSO wallet cwallet.ora. This use of the wallet is to provide the information for two-way SSL connection to the database. It should not be confused with using the wallet to contain the database user/password credentials so they can be removed from the datasource descriptor (described at the wallet blog). When using this option, the only driver property that needs to be set is oracle.net.wallet_location (variable wallet_location) to the directory where the wallet is located.
The second option is to use Java KeyStore (JKS) files truststore.jks and keystore.jks. For this option, we need to set the driver properties for javax.net.ssl.keyStoreType, javax.net.ssl.trustStoreType, javax.net.ssl.trustStore, javax.net.ssl.trustStorePassword, javax.net.ssl.keyStore, and javax.net.ssl.keyStorePassword. We also want to make sure that the password values are stored as encrypted strings.
That wraps up the discussion of datasource configuration using scripts.
If you want to enable Application Continuity on the database service, you need to run a database procedure as documented at this link in the user guide and use the associated replay driver.
This section has screen shots of using the administration console to do the same configuration. The parameters are set as described in the prior section.
From the Home screen, select "Data Sources" under "Services" in the middle of the screen. Use the "New" drop-down to select "Generic Data Source".
On the next screen, fill in the "JNDI Name" and click "Next".
On the next screen, use the default "Database Type" of Oracle and the default "Database Driver" for the Oracle Thin Driver Service connection (you could optionally pick the XA version of the driver or the Application Continuity version of the driver). Click "Next".
On the next screen, you can leave the defaults (if you pick the XA driver, then the screen will look different with no options to select). Click "Next".
On next screen for the "Database Name", enter the name of your Service (of the form "name_tp"). The Host Name is not needed but the console requires that something is entered so for this example I will enter "garbage". Leave the port - it won't be used either. Enter the user name and password that you created on your database. Click "Next".
The next screen is where most of the work is. Modify the "URL" to remove "//garbage:/1521", leaving a URL with just the alias that points into the tnsnames.ora file. Now you need to enter the "Properties", as discussed earlier - oracle.net.tns_admin, user, oracle.net.wallet_location, oracle.jdbc.fanEnabled, oracle.net.ssl_version, and oracle.net.ssl_server_dn_match. Alternatively, you can use the parameters for a JKS file. When using the console, JKS passwords should be encrypted using the process described at this encrypted properties blog. You can click on the "Test Configuration" button to see that you typed everything in correctly. Then click "Next".
On the next screen, select the check boxes for the desired targets and click "Finish" to create and target the data source.
That will bring you back to the summary of JDBC Data Sources. Click on the link for the data source that you just created. Click on the "Monitoring" tab and the "Testing" tab under Monitoring. Select the button for the WLS Server that you want to test and click on "Test Data Source".
The screen should show that the test is successful.
There are several problems that you may run into when using ATP that you won't see on a non-ATP database. Maybe some of these will save you some time.
1. Application Continuity doesn't work with the 19.3 Oracle driver against a 220.127.116.11 Oracle database server. This is fixed in the 19.6 driver.
2. The object oracle.sql.Blob is not serializable in the 19.3 Oracle driver. The following sequence fails:
Blob blob = BLOB.getEmptyBLOB();
ObjectOutputStream oos = new ObjectOutputStream(new ByteArrayOutputStream());
We saw this problem in the WebLogic job scheduler that is using Blob values. We were able to work around it by using Connection.createBlob() instead of oracle.sql.BLOB.empty_lob() (fixed in WebLogic 18.104.22.168.0 but it is not fixed in WebLogic 22.214.171.124.0).
This has been fixed in the 19.6 Oracle driver.
3. There is a limit of 100 sessions per core and it's tricky to figure out this is the cause of the failure "no more data to read from socket" when the maximum number of sessions is reached.
4. When using the "_high" service instead of the "_tp" service, we saw some very long elapsed times in the AWR report. Stick with the "_tp" service as documented above.
5. ATP-S has a Listener rate limit of 100 connections per second to throttle connection requests (since ATP-S is a shared resource). This would generally only be a problem in WebLogic Server when trying to initialize a datasource with a minimum count greater than 100 connections. So one way to get around this is to set the minimum count to a value less than 100. In WebLogic Server 126.96.36.199.0, we added driver connection properties to limit maximum number of threads that can create connections: weblogic.jdbc.maxConcurrentCreateRequests and weblogic.jdbc.concurrentCreateRequestsTimeoutSeconds (see the documentation for more information).