Oracle Database 19c and 18c JDBC drivers and Java connection pool (UCP) introduce several new features in the performance, high availability, security, and scalability areas. New connection and security features were added to simplify connecting to Autonomous Transaction Processing (ATP) and Autonomous Data Warehousing (ADW). This blog gives you a summary of these features.
Quick Summary:
- Connection: Easy Connect Plus for easier TCPS connections and passing connection properties (19c only); new ojdbc.properties file to set connection properties; ways for setting TNS_ADMIN; setting server’s domain name (DN) cert as a connection property; and support of new wallet property (my_wallet_directory)
- Performance: Reactive Streams Ingest (RSI) for streaming data into the Oracle Database (19c only); Oracle connection manager (CMAN) in traffic director mode (CMAN-TDM)
- Scalability: Oracle RAC data affinity; and shard routing APIs for mid-tiers
- High Availability: Transparent Application Continuity (TAC); AC support in DRCP; and AC support for legacy Oracle JDBC types implemented as concrete Java classes
- Security: Automatic Provider Resolution (OraclePKIProvider); support for Key Store Service (KSS); and HTTPS proxy support
- Data types: Accessing PL/SQL associative arrays; Oracle REF CURSOR as IN bind parameter; and JSON datatype validation
Connectivity Enhancements:
Easy Connect Plus
The traditional Easy Connect string had limited capabilities and allowed only TCP connections. In Oracle Database 19c release, the Easy Connect Plus enhances the connection string to be more powerful and self-sufficient for TCPS connections and multiple hosts etc.,The connection string is extended to accept multiple connection parameters through name-value pairs.
Easy Connect Plus can now be used for:
- specifying multiple hosts and ports
- TCPS connections and can pass wallets/JKS related connection properties
- passing connection properties
Easy Connect Plus for specifying the multiple hosts and ports.
Long Form Connection URL
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver1)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=testserver2)(PORT=1522))(ADDRESS=(PROTOCOL=tcp)(HOST=testserver3)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=test)))
Easy Connect Plus:
jdbc:oracle:thin:@tcp://testserver1:1521, testserver2, testserver3:1522/test
Easy Connect Plus for specifying the connection properties as name-value pairs. jdbc:oracle:thin:@tcp://myorclhostname:1521/myorclservicename? oracle.jdbc.implicitStatementCacheSize=100
New connection properties file (ojdbc.properties)
Starting with Oracle Database Release 18c, the JDBC driver supports a new file ojdbc.properties that makes it easier to pass connection properties to Java applications. Oracle Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) include ojdbc.properties file as part of the client credentials download that is pre-populated with the required connection property.
The JDBC driver handles the property files as follows:
The driver attempts to load the default file i.e., ojdbc.properties from the default location: $TNS_ADMIN where the tnsnames.ora resides. TNS_ADMIN can be set as a Java system property, as an environment variable or as part of the connection URL. For other non-default ways of setting ojdbc.properties file, check out Appendix [1].
Multiple ways to set TNS_ADMIN
In the older releases, TNS_ADMIN was supported as a system property (oracle.net.tns_admin). With Oracle Database 18c JDBC drivers, TNS_ADMIN can be set as an environment variable and can also be appended to the connection URL.
jdbc:oracle:thin:@//myhost:1521/orcl?TNS_ADMIN=/home/oracle/network/admin/
Setting Server’s Domain Name (DN) cert as a connection property
In the older releases, the server’s domain name (DN) cert has to be set as part of the connection URL and also enabled through a connection property. Starting with Oracle Database 18c, the JDBC driver can use a connection property to set the server’s DN cert. When the certificate is set as part of the connection URL or through a connection property then there is no need to set the additional property oracle.net.ssl_server_cert_dn_match=true.
oracle.net.ssl_server_cert_dn="CN=test.test.com,OU=ST,O=TEST,ST=TEST,C=IT"
Support of new property (my_wallet_directory) for wallets
Starting with Oracle Database Release 18c, the JDBC driver supports a new property my_wallet_directory for specifying the location of the wallets. You can add this property in security section of the connection URL or in the properties file or directly in the code. The wallet location may contain environment variables and ${identifier} is supported for environment variables. A sample connection URL with this property is as shown below.
jdbc:oracle:thin:@dbaccess where dbaccess is defined in tnsnames.ora like this:
dbaccess = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(Host=hostname)(Port=1522)) (CONNECT_DATA=(SERVICE_NAME=myservicename)) (Security=(my_wallet_directory=$TNS_ADMIN/jnetadmin_c/)))
Performance Features
Oracle Connection Manager (CMAN) in Traffic Director Mode (CMAN-TDM)
Starting with Oracle Database 18c, a new capability called Connection Manager in traffic director mode (CMAN-TDM); it is a database proxy which is placed between the database clients (i.e., JDBC, ODP.net, OCI) and the database instances. The database operations (TTC messages) sent from the clients are intercepted and parsed by CMAN-TDM then routed to the appropriate database. CMAN-TDM transfers the results from the databases to the clients (TTC responses).
Scalability Features
Oracle RAC data affinity
The Java connection pool (UCP) in Oracle database release 18.3, supports Oracle RAC Data Affinity. When the data affinity is enabled on the Oracle RAC database, the tables are partitioned in such a way that a particular partition or subset of rows for a table is affinitized to a particular Oracle RAC database instance. The affinity leads to higher performance and scalability for the applications due to improved cache locality and reduced inter-node synchronization and block pings among the RAC instances.
Shard routing APIs for mid-tiers
The Java connection pool (UCP) introduces a mid-tier shard routing feature. Through this capability, applications may couple a mid-tier for each data center or cloud and route client requests directly to the relevant mid-tier based on the shard that contains the relevant data. Java applications use the mid-tier routing API getShardInfoForKey(shardKey, superShardKey) method of the OracleShardRoutingCache class. For more details refer to the Mid-tier Routing using UCP blog.
High Availability Features
Transparent Application Continuity (TAC)
Application Continuity (AC) is a feature introduced in 12.1 that masks database outages both planned and unplanned to the application and improves the end user’s experience. A database request is a unit of work that is demarcated by the transaction boundaries or the check-out and check-in of a connection from a connection pool. AC needs to be enabled on both the server and client sides. The clients record the interactions with the database then replay the in-flight transaction upon unplanned outages.
AC requires the use of JDBC replay datasource (oracle.jdbc.replay.OracleDataSourceImpl). In an effort to minimize the application changes and making AC more transparent, the 19.3 JDBC driver implicitly adds beginRequest on each new connection created using a replay datasource and when FAILOVER_TYPE on the database service is set to AUTO. If you want to turn off this feature explicitly, you can set the value of the Java system property oracle.jdbc.beginRequestAtConnectionCreation to false. The default value of this property is true.
As TAC detects and injects implicit request boundaries automatically, this feature should be used with caution for applications that change server session states during a request. The JDBC Thin driver provides the oracle.jdbc.enableImplicitRequests property to turn off implicit requests, if needed. This property can be set at the system level, which applies to all connections, or at the connection level, which applies to a particular connection. By default, the value of this property is true, which means that support for implicit request is enabled.
Oracle Connection Manager (CMAN) in Traffic Director Mode (CMAN-TDM)
CMAN-TDM add transparency to high availability by hiding the planned/unplanned outages as well as the relocation of Pluggable Databases (PDBs) from one root or container database (a.k.a. CDB) to the other. CMAN-TDM automatically detects, redirects, and re-establishes the database connections in such scenarios.
AC support for concrete classes
The 18.3 JDBC driver supports the following concrete classes with Application Continuity: oracle.sql.CLOB, oracle.sql.NCLOB, oracle.sql.BLOB, oracle.sql.BFILE, oracle.sql.STRUCT, oracle.sql.REF, oracle.sql.ARRAY
AC support for DRCP
The 18.3 JDBC driver supports Application Continuity when Database Resident Connection Pooling (DRCP) is enabled on the server side. For using Application Continuity with DRCP, you must configure an application service to a server that uses DRCP. The following connection string shows how to configure AC with DRCP:
String url = "jdbc:oracle:thin:@(DESCRIPTION =
(TRANSPORT_CONNECT_TIMEOUT=3000)
(RETRY_COUNT=20)(RETRY_DELAY=3)(FAILOVER=ON)
(ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)
(HOST=TEST-CLOUD-SCANVIP.example.com)(PORT=5221))
(CONNECT_DATA=(SERVICE_NAME=ac-service(SERVER=POOLED)))";Security Features
Automatic Provider Resolution
The 18.3 JDBC driver resolves the provider as long as the provider implementation is on the CLASSPATH eliminating the need to register the provider. For example., If the oraclepki.jar file is on the CLASSPATH, then the JDBC driver automatically loads the OraclePKIProvider and will be able to establish the connection. Similarly, if the oracle.net.wallet_location connection property is set, the JDBC driver attempts to automatically load the Oracle PKI provider.
The following key store types map to a known provider:
- SSO: oracle.security.pki.OraclePKIProvider
- KSS:oracle.security.jps.internal.keystore.provider.FarmKeyStoreProvider
Support for HTTPS Proxy Configuration
The 18.3 JDBC driver supports HTTPS proxy configuration for TCPS connections. HTTPS proxy comes in handy in accessing the public cloud database service through internal corporate network without the requirement to open an outbound port on a client side firewall. Refer to the connection string that configures HTTPS proxy.
(DESCRIPTION=(ADDRESS=(HTTPS_PROXY=test-proxy)(HTTPS_PROXY_PORT=8080)(PROTOCOL=TCPS)(HOST=test2)(PORT=443))(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))
Support for Key Store Service (KSS)
The 18.3 JDBC driver supports Key Store Service (KSS). So, if you have configured a Key Store Service in a WebLogic server, then JDBC applications can now integrate with the existing Key Store Service configuration.
The driver can load the key stores that are managed by the Key Store Service. If the value of the javax.net.ssl.keyStore property or the javax.net.ssl.trustStore property is a URI with kss:// scheme, then the driver loads the key store from Key Store Service.
For permission-based protection, permission needs to be granted to access the keystore.
Data Types
Accessing PL/SQL Associative Arrays
The 18.3 JDBC driver supports accessing both the keys (indexes) and values of associative arrays and also supports object types associative arrays. The following methods can be used for the new functionality.
- Array createOracleArray(String arrayTypeName,Object elements) throws SQLException
- ARRAY createARRAY(String typeName,Object elements)throws SQLException
Oracle REF CURSOR Type
The 18.3 JDBC drivers support REF CURSOR as IN bind variables. Stored procedures can accept or return cursor variables of the REF CURSOR type. Refer to RefCursorInSample.java for reference.
JSON Datatype verification
The 18.3 JDBC driver has a new method isColumnJSON() available in oracle.jdbc.OracleResultSetMetaData to verify whether a column returned in the ResultSet is a JSON column or not.
Appendix
[1] There are other variations of the property file supported. These are listed for the reference.
(a) ojdbc_<tnsalias>.properties file: If the TNS alias is used in the connection then the TNS alias can be appended to the property file name. Example;., ojdbc_orcl.properties. If both ojdbc.properties and ojdbc_<tnsalias>.properties files are present then ojdbc_<tnsalias>.properties takes the precedence.
(b) Custom properties file: The property file can have any other name and can be placed at any location. Use the property oracle.jdbc.config.file or set the connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. There can be multiple property files. Multiple files can be mentioned as a comma delimited list: “fileOne, fileTwo, fileThree”. Higher precedence is given to the file that appear last in the list. Example., fileThree would override fileTwo’s definition of the property. Likewise, fileTwo’s values can override fileOne’s.