Unable to connect to AE (AutoSys) instance which uses Oracle 12c

Products

• CA Workload Automation iXP

Issue/Introduction

After configuring an AE (AutoSys) instance that uses an Oracle 12c database, iXp does not retrieve the instance jobs and data.

The ixpdaemon.log file show errors connecting to Oracle 12c such as the following:

java.sql.SQLException: Listener refused the connection with the following error:ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Environment

iXp connecting to AE using an Oracle 12c Multitenant configuration with a pluggable database.

Cause

The installation guide and iXp interface suggest the Oracle jdbc connection string in the iXp Admin GUI should be:

jdbc:oracle:thin:@hostname:port:SID

Oracle 12c has introduced a new option called Oracle Multitenant which allows a multitenant container database to hold many pluggable databases. See here for more info: https://www.oracle.com/database/multitenant/

If your AE Oracle 12c database is a pluggable database in a multitenant environment, the jdbc connection string in the iXp instance configuration needs to explicitly state the service name.

The issue is encountered because the connection string, while it does contain the SID, it does not explicitly state the service name with the necessary connection string flag SERVICE_NAME.

Resolution

Enter the jdbc connection string using the following format:
jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp) (port=<port>)(host=<hostname>)))(connect_data=(SERVICE_NAME=<SID>)))

For example:
jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp) (port=1521)(host=oracletest.example.com)))(connect_data=(SERVICE_NAME=AEDB)))

Whereoracletest.test.com = The Oracle DB server hostname
1521 = The Oracle DB server port number
AEDB = The AE (AutoSys) database SID