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.
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.
The documentation for ATP requires the use of JDK 8 or later and the Oracle 12.x drivers or later. The earliest version of WLS that supports JDK 8 is WLS 12.1.3; earlier versions don't apply. You need to be running a fairly current version of 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;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.
Versions of WLS before 188.8.131.52.0 shipped with the 184.108.40.206 Oracle driver. That driver needs a patch to 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. Alternatively, you can and may need to update to a later version of jar files.
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 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.
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.