ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 186578

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine 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/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#AWA/Objects/obj_job_SQL.htm?Highlight=%22server:port%22

Cause

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

Environment

Release: 12.3.0 to 12.3.2

Component: AUTOMATION ENGINE

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: The issue will be fixed in version 12.3.3