after Upgrade V12.3 Oracle RAC SQL Agent - Jobs fail

[Frank Muffke]

Good morning,

after Upgrade from AE11.2.10 to V12.3.GA.HF3 we observed that no SQL jobs were sucessfully running on those (Oracle RAC) agents.

Err message: 

PLOG

2019-11-02 00:12:22 - U02000005 Job 'JOBS.UC4.REORG_DB.SQLEXEC' with RunID '342960657' started.

2019-11-02 00:12:22 - U02012031 The database driver returned the following error message:

2019-11-02 00:12:22             IO Error: Invalid connection string format, a valid format is: "host:port:sid"

2019-11-02 00:12:22 - U02004025 Job-script execution was aborted.

In V11.2.10 they ran fine and there was definitely no change on job content.

SQL Agents were upgraded with new ini file (and values from old inifiles took over), so there was no change as well - at least to my best knowledge....

Any Hints from your side?

many thanks
Wolfgang

------------------------------
I know I do NOT really know it!
------------------------------

[Tim Quakulinsky]

Hi,

I'm waiting for the support guy who writes: "if you are using one of the latest version of AWA ( = 12.3.1 or 12.2.​4 or 12.1.6) please get in contact with Support to open a ticket. Otherwise update/upgrade your system and check if the problem still exists." ;-)

Sorry Wolfgang, but I have no idea and cannot help you (besides the hint to upgrade to 12.3 SP1).

Good luck,
Tim

------------------------------
Automation Evangelist
Fiducia & GAD IT AG
---
Mitglied des deutschsprachigen Automic-Anwendervereins FOKUS e.V.
Member of the German speaking Automic user association FOKUS e.V.
------------------------------

[Frank Muffke]

Hi Tim,

Thank you for the kind words, I fear the answer from support as well...

You are waiting way to long, they (we) did this occasionally :-P

It is somehow related to oracle OCI - my colleagues found out.

cheers, Wolfgang


------------------------------
I know I do really know it!
------------------------------

[Christian Boeck]

Hi,
I had a similar problem regarding ignore useOraClient=1 and I changed the type in the sql-agent-ini from type=ORACLE to type=ORACLE_OCI.
After restart of the agent everything works fine. It's not documented but it works.
For the docu of get_connection the type is documented but not for the ini of the sql-agent
docs.automic.com/documentation/webhelp/english/AA/12.3/...

[Frank Muffke]

Hi Christian,

THX for the hint, will give it  try.

cheers, Wolfgang

------------------------------
I know I do really know it!
------------------------------

[Leon Carroll]

Hi.  I have the same issue and have a support case open - 20098835.  I did the upgrade from 12.2 to 12.3.0_HF3 build date of 25th October and DB events and Oracle Jobs failed.  I had to change all Connection objects from Oracle to Oracle_OCI.  With the SQL_Agent on 12.3.0_HF3  the SQL Jobs failed. 
I upgraded to 12.3.1 (Build 1/11/2019) with the same issue.  I uninstalled the Oracle 12c drivers and installed Oracle 19c.
The 12.2 SQL agent works but 12.3.0_HF3 or 12.3.1 does not.

Changing the Agent type in the ini from ORACLE to ORACLE_OCI does work with V12.3.1, and I can run SQL jobs again.  I'll continue testing and checking if the DBService works with the events.

[Frank Muffke]

Thanks a lot  guys that (ORACLE to ORACLE_OCI ) worked for me too!.

KR
Wolfgang

------------------------------
I know I do really know it!
------------------------------

[Tony Ferraro]

Great stuff..i hit this issue too.

Broadcom - The question is, is this change officially documented somewhere?

[Richard Beck]

I saw the ORACLE_OCI solution but just out of curiosity, did you have useOraClient=1  in you INI previously?

[Leon Carroll]

Yes, but changing it to 0 did not help.  Jobs still failed when using ORACLE_OCI connections and TNS names.

[Frank Muffke]

Yes - we did not change it.

cheers, Wolfgang

------------------------------
Support Info:
if you are using one of the latest version of UC4 / AWA / One Automation please get in contact with Support to open a ticket.
Otherwise update/upgrade your system and check if the problem still exists.
------------------------------

[Thomas Hollensteiner]

hello,

this is a bug and was introduced by removing the LICENCE check within the SQL agent - therefore the parameter 'useOraClient=1' is ignored - for now the workaround is to use ORACLE_OCI as type - once the fix is available the parameter must be used again - and the type must be changed back to ORACLE.

internal bug ticket AE-23202 can be used as a reference for usage with support.

best regards
holli

[Michele Adamski]

Thanks, @Leon Carroll. I've still not gotten it working but am trying a few things. 

@Thomas Hollensteiner:  If I'm on v12.2.0 of the Automation Engine and v12.2.0 for the SQL agents, and I can TNS ping to the Oracle RAC DB, shouldn't I be unaffected by the bug? ​​

[Thomas Hollensteiner]

hello,

if you are on a release before the licence check was removed - this must work as it did for all other customers as well and mentioned here.
did that work ever in your env. or is this something new?

if you enable the PLOG on the JOB what is written there - why is it not connecting?

best regards
holli

[Michele Adamski]

I'm on v12.2.0 of Automation Engine. The agents are v 12.2.4.

Should I try maybe installing an older version of the agents, as below? 

Connecting to Oracle RAC is new for us. I was able to connect to a single server, with a single Oracle DB on it, but I cannot connect to the cluster name in the connection object. 

I don't have any jobs set up as of yet, because I can't get the CONN working. 

Again, I've tried connecting unsuccessfully to "database type = Oracle" and the "server port = clustername:port" so that it references tnsnames.ora and the database name referenced therein. 

I've also tried connecting unsuccessfully to "database type = Oracle OCI", with no server port specified and the database name referenced. 

Right now the SQL agents, which are installed on the three servers that comprise the cluster are all "type=ORACLE_OCI", but I've tried it with "type=ORACLE" with no success.

[Michele Adamski]

I have three servers comprising the cluster for Oracle RAC. In each SQL agent, I've configured them, first, as type=ORACLE. Then after reading this post, I tried type=ORACLE_OCI. 

I have the full Oracle client, with tnsnames, and setting the connection object as Database type Oracle, and that won't connect (before changing the agent ini.)

I also have tried Oracle OCI, and when testing the connection, it just hangs then times out. 

I see that this solution worked for some of you. Am I missing something? 

I've got a ticket opened with support, but I'm running out of time and they don't seem to work on my clock any longer.

[Leon Carroll]

Hi Michelle

i never got anything useful back from Support.  i found that we had two issues we had to resolve.
The first was that we had to change all the connection objects to Oracle OCI, in some places we had these set up to use Oracle using the hostname.  These would also fail.
The second was to change the Agent to be of type ORACLE_OCI
I was running with the SQL Oracle agent at 12.2.2 (not 12.3.0 HF2 as the bug is there too)  for a couple of days before I got this working.