SQL Agent: Errors with Agent Type Oracle doesn't match ORACLE OCI

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The SQL Agent job fails to start.  

Possible errors:  

1. Agent DB-Type 'ORACLE' doesn't match Connection DB-Type 'ORACLE_OCI'

Issue occurred after upgrading to 12.3 from 12.2, jobs were failing where the connection was set to Oracle.
Changing these to Oracle_OCI fixed those but some jobs still fail when the connection is set to Oracle_OCI.
Other jobs with the same connection and agent and almost the same SQL work.  On rolling back to the previous 12.2 agent this works.

https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1435&MessageKey=07c6392d-3530-4f6f-9f28-5167fdd99890&CommunityKey=2e1b01c9-f310-4635-829f-aead2f6587c4&tab=digestviewer&ReturnUrl=%2Fenterprisesoftware%2Fcommunities%2Fcommunity-home%2Fdigestviewer%3Ftab%3Ddigestviewer%26CommunityKey%3D2e1b01c9-f310-4635-829f-aead2f6587c4

 

or

2. IO Error: Invalid connection string format, a valid format is: "host:port:sid" 

when the Agent is configured to use Oracle OCI interface rather than JDBC (useOraClient=1).

According to the documentation (see link below) it is possible to define just the TNS identifier to get the job working. Jobs taking advantage of this will fail in version 12.3.

Link: https://docs.automic.com/documentation/WEBHELP/English/all/components/DOCU/21.0/Automic%20Automation%20Guides/Content/AWA/Objects/obj_job_SQL.htm?tocpath=Using%7CDesigning%7CObject-Specific%20Properties%7CJobs%20(JOBS)%7C_____10#link2

Environment

Release: 12.3.0 to 12.3.3

Component: AUTOMATION ENGINE

Cause

SQL AGENT is not using USEORACLIENT parameter since LICENCE check was removed

Resolution

Workarounds

In case of error 1:

This applies when your Agent is still using ORACLE while the connection object is using ORACLE_OCI.

In the Agent .ini file, open and look for "useOraClient="

By default, this is set to 0 (which is for ORACLE) but the fact that the error message mention you are using ORACLE_OCI.

In that case, change that value to 1, so it specifies useOraClient=1

Then, stop and restart the agent.

 

In case of error 2:

There's no other workaround than downgrading the Agent to 12.2.0.

 

Solution: This was fixed with the database agent on 12.3.4