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).
The blog Configuring a WebLogic Data Source to use ATP has screen shots of creating a new ATP database from the OCI console, during which you specify the passwords for the database ADMIN user and the client credentials wallet. This blog assumes you have already completed that process and downloaded the wallet zip file. For consistency, this blog assumes the same directory structure with the wallet files being stored in /shared/atp. You shouldn’t need to modify any of these files for use with WLS (one exception is described below). 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 dbnnnnnnnnnnnn_tp; this service is configured correctly for WLS transaction processing.
The blog link above has screen shots of using the WLS administration console to create the WLS datasource for the ATP database. When using the console, JKS passwords can be encrypted using the process described at this encrypted properties blog.
This blog has functional scripts for creating the datasource using either online WLST or REST. Before running the scripts, we need to check a few prerequisites.
WebLogic Server188.8.131.52.0 and later are certified with ATP. The following sections have some information about using earlier 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.
JDBC Driver Prerequisite:
WLS 184.108.40.206.0 shipped with the 220.127.116.11 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 18.104.22.168.0 shipped with the 22.214.171.124 Oracle driver. WLS version 10.3.6 through 12.1.2 shipped with the 126.96.36.199 driver. The 188.8.131.52 driver works with the 18.3 database server but may not work with later database versions. It is possible to upgrade the 184.108.40.206 driver to to the 220.127.116.11 driver using information at this link (driver upgrades are only supported for WLS, not JRF or FA). The 18.104.22.168 and 22.214.171.124 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 18.3 version of oraclepki.jar, osdt_core.jar, and osdt_cert.jar to the oracle_common/modules directory (download them 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).
HTTP Proxy Support:
You can and may need to update to a later version of Oracle driver jar files (18.3 or later) if you want some newer features, such as HTTP proxy configuration. Note that this requires the environment to be running JDK8. Also note that up-coming releases 126.96.36.199.0 and 188.8.131.52.0 will ship with the 19.3 driver so this upgrade is not required.
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 18.3 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 dbnnnnnnnnnnnn_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.
Configuration using WLS or REST:
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 . 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 . 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 descriptor 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 dbnnnnnnnnnnnn_tp is taken from the tnsnames.ora file. The URL is generated by using an @alias format "jdbc:oracle:thin:@dbnnnnnnnnnnnn_tp". For this to work, we also need to provide the directory where tnsnames.ora file is located (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 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.
The current version of ATP (ATP-S) provides access to a single Pluggable DataBase (PDB) in a Container DataBase (CDB). Most of the operations on the PDB are similar to a normal Oracle database. 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 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 need to set the driver property oracle.jdbc.fanEnabled=false. This property is no longer needed if using the 18.3 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 SQLPlus 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.
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.