DB or SQL job fails with Oracle 19c database, JDBC or listener issue

Products

• Workload Automation Agent

Issue/Introduction

User is trying to run a DB (SQL) job and gets the following messages.

XX/xx/2021 00:00:12.345-0400 1 DatabasePlugin.database Internal Thread.CybWOBRunSqlHandler.processWob[:82] - 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

Release : 12.0

Component : CA Workload Automation Database Agent

Cause

The JDBC URL string is not correctly setup in agentparm.txt or in the job.

Resolution

The Oracle 19c now has services instead of SID.  The connection string will need to point to service name which can also be obtained from tnsnames.ora.

ORCL =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ##.##.#.#)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORAC19)
 )
)

The following is correct example of JDBC string with Oracle service name:

jdbc:oracle:thin:@dbhost.example.com:1521:orac19     <-- Put Service name after port.  See tnsnames.ora for details.

Change the JDBC URL in agentparm.txt and restart the agent.

The JDBC URL may also be added in the job to override the agentparm.txt.