API Gateway: Errors seen when connecting to an Oracle Database server built on 12.2 or higher.

Products

• CA API Gateway

Issue/Introduction

This article will discuss how to implement the Oracle 12.2 JDBC driver, moving away from the built-in Progress DataDirect JDBC drivers when connecting to an Oracle database server. If the Oracle DB backend is built on Oracle version 12.2 (such as 19c), the Oracle driver should be used to ensure maximum compatibility especially if the backend database server is using any rare configurations.

In some cases, if using the built-in Progress DataDirect JDBC driver, the following error may be encountered, in which case the resolution noted in this KB article should be followed: 

Testing failed: invalid connection properties setting. 
Oracle Error : ORA-12650

Environment

This article applies to all supported versions of the API Gateway where the above conditions are seen while connecting to an Oracle database server over JDBC.

Resolution

The recommended resolution is to move away from the build-in Progress DataDirect JDBC drivers the API Gateway ships with, and use the official Oracle database JDBC driver. If running an Oracle database server, access to the official JDBC driver should be included.

1. Remove the ojdbc6.jar file from /opt/SecureSpan/Gateway/runtime/lib/ext if it exists already before adding the ojdbc8.jar file.
   • Note: ojdbc6.jar is built on Oracle version 11.2.04, whereas ojdbc8.jar is built on Oracle version 12.2.0.1.
2. Upload the ojdbc8.jar file for Oracle Client to the /opt/SecureSpan/Gateway/runtime/lib/ext directory on the API Gateway.
3. Run the following commands to change the permission and owner: 
   • chmod 444 ojdbc8.jar
   • chown layer7:layer7 ojdbc8.jar
4. Add the following line at the bottom of the /opt/SecureSpan/Gateway/node/default/etc/conf/system.properties file (note the new driver class is the last one \noracle.jdbc.OracleDriver)
   • com.l7tech.server.jdbcDriver=com.mysql.jdbc.Driver\ncom.l7tech.jdbc.mysql.MySQLDriver\ncom.l7tech.jdbc.db2.DB2Driver\ncom.l7tech.jdbc.oracle.OracleDriver\ncom.l7tech.jdbc.sqlserver.SQLServerDriver\noracle.jdbc.OracleDriver
5. Modify the Driver Class Selection List in the cluster-wide properties:
   1. In Policy Manager, go to Tasks > Global Settings > Manage Cluster Wide Properties
   2. Click Add, then add the following in the appropriate boxes:
      1. Key: jdbcConnection.driverClass.defaultList
      2. Value: oracle.jdbc.OracleDriver
   3. Click OK to save the CWP.
6. Restart the API Gateway SSG service with the following command on a command prompt: service ssg restart
7. Set the JDBC Connection Properties for the Oracle connection to use the new driver class that was added above.

Additional Information

The steps above are a specific implementation of the steps outlined in the official product documentation, which should be reviewed as well for a complete picture of the process.