CA WA Database Agent: SQL jobs with .ORA-12521, TNS:listener does not currently know of instance requested in connect descriptor

Products

• CA Workload Automation DE - Business Agents (dSeries)
• CA Workload Automation DE
• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)
• CA Workload Automation DE - Scheduler (dSeries)

Issue/Introduction

The SQL or database job fails with this error.

LStatus - java.sql.SQLException: Listener refused the connection with the following error:.ORA-12521, TNS:listener does not currently know of instance requested in connect descriptor.

Environment

Release : 11.x

Component : CA WORKLOAD AGENT DATABASE

Cause

The Oracle SID or Service name is correct setup.  

03/XX/2020 09:00:00.000 EST-0500 5 DatabasePlugin.database Internal Thread.DbConnection.connectToDb[:163] - Connecting to jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbbox.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB123) (INSTANCE_NAME = DB123)))#......
....
03/XX/2020 09:00:00.000 EST-0500 1 DatabasePlugin.database Internal Thread.DbConnection.connectToDb[:187] - java.sql.SQLException: Listener refused the connection with the following error:
 ORA-12521, TNS:listener does not currently know of instance requested in connect descriptor

Resolution

Provide either the service name or SID in the JDBC.  Only one is need for the JDBC format.

jdbc:oracle:thin:@//HOSTNAME:PORT/[SERVICENAME or SID]

The agentpam.txt can be update like the above format.

Example:

jdbc:oracle:thin:@dbbox.example.com:1521/db123