Automic Automation v12.3 SQL (db jobs)

[Genarro Montano]

Hello,

We are facing a new issue with our new installation of Automic v 12.3. The issue we are seeing is when executing our SQL job it will fail with the Agent(PLOG) only saying that the ocijdbc12 driver cannot be found. This error is occuring on a Linux OS machine where we are using an Oracle Agent.There isnt any more log information either on the Agent itself, nor from the AWI's reporting function. While working through this, we tried several things including setting our LD_LIBRARY_PATH to $ORACLE_HOME/lib which in our case is an instant client location. We have also changed our PATH env to include the jre location and we have our TNS_ADMIN variable pointing to the directory that contains our ldap.ora, sqlnet.ora and tnsnames.ora file. In my search through documentation, I have yet to uncover any solution or workaround for this particular error.
I have two questions regarding this,
1.) Is there a way to increase the logging of an agent or job itself to gather more information on the job being executed?
2.) If anyone has dealt with this issue what steps did you take to resolve this? 

Thank you,
Genarro Montano

[Carsten Schmitz]

Hi.

Is​ this about the SQL agent?

If so: Did you put the jdbc driver into the agent's bin/jdbc directory? For example, I copied ojdbc6.jar in to my Automic agent's bin/jdbc directory. It needs to be readable by the user that the agent runs as.

Automic doesn't care that much about the Oracle conventions, it simply expects to find a copy of the jdbc driver in that path.

Hth,
Carsten

[Genarro Montano]

Hi,
Yes it is about the sql agent itself and a job running on it. Yes the jdbc driver that we are using exists in the Agents bin directory. (we are using ojdbc7.jar.)

[Nicole Boeger]

Dear Genarro,

try another ojdbc driver. I had problems with that in 12.2, too. My version searched only for ojdbc7.jar.
And make sure the driver is in the agents home/bin/jdbc, as Carsten already mentioned.


------------------------------
Regards, Nicole
------------------------------

[Ahmed Intizar]

Dear All,

There seems to be some unknown issue with SQL agent V12.3. In version 12.2.2 everything was working fine while agent running running on a windows system and using jdbc8.jar. After upgrade to V12.3, agent seems to ignore useOraClient=1 setting in the ini file and immediately throws error (IO Error: Invalid connection string format, a valid format is: "host:port:sid".) when any sql job is executed.

Answer from support:

Add the database SID in your connection string.

For Example: String url="jdbc:oracle:thin:@localhost:1521:SID_GOES_HERE";

According tot he documentation, the suggested url is not required when using oracle client and this has always worked perfectly until agent upgraded to v12.3.
So, still in discussion with the support, asking them to look deeper into the issue instead of answering "default" 1st level support answers. :-).

[Carsten Schmitz]

​Hi,

I'm sure @Nicole Boeger's suggestion will help.

Also, here's my LD_LIBRARY_PATH and ORACLE_HOME I use for my (working) SQL agent. I've taken this from /proc/<pid>/environ, so it's the effective setting.

LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib/oracle/12.1/client64/lib
ORACLE_HOME=/usr/lib/oracle/12.1

We use the 11.2 client first because I have been told by Automic that they always compile against the lowest supported version, so that's the safe bet. If you still have an 11.2 client, you might want to try putting that in your search path and bin/jdbc directory.

Here's my file list for these directories:

/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
/usr/lib/oracle/11.2/client64/lib/ojdbc6.jar
/usr/lib/oracle/11.2/client64/lib/orai18n.jar
/usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib/oracle/11.2/client64/lib/xstreams.jar
/usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
/usr/lib/oracle/11.2/client64/lib/libheteroxa11.so
/usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib/oracle/11.2/client64/lib/orai18n-mapping.jar
/usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
/usr/lib/oracle/12.1/client64/lib/libheteroxa12.so
/usr/lib/oracle/12.1/client64/lib/libocci.so.12.1
/usr/lib/oracle/12.1/client64/lib/libmql1.so
/usr/lib/oracle/12.1/client64/lib/libipc1.so
/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
/usr/lib/oracle/12.1/client64/lib/ojdbc6.jar
/usr/lib/oracle/12.1/client64/lib/ojdbc7.jar
/usr/lib/oracle/12.1/client64/lib/libons.so
/usr/lib/oracle/12.1/client64/lib/libocijdbc12.so
/usr/lib/oracle/12.1/client64/lib/libsqlplus.so
/usr/lib/oracle/12.1/client64/lib/orai18n.jar
/usr/lib/oracle/12.1/client64/lib/xstreams.jar
/usr/lib/oracle/12.1/client64/lib/libnnz12.so
/usr/lib/oracle/12.1/client64/lib/libociei.so
/usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
/usr/lib/oracle/12.1/client64/lib/orai18n-mapping.jar
/usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
/usr/lib/oracle/12.1/client64/lib/glogin.sql
/usr/lib/oracle/12.1/client64/lib/liboramysql12.so
/usr/lib/oracle/12.1/client64/lib/libsqlplusic.so
/usr/lib/oracle/12.1/client64/lib/libheteroxa12.so
/usr/lib/oracle/12.1/client64/lib/libocci.so.12.1
/usr/lib/oracle/12.1/client64/lib/libmql1.so
/usr/lib/oracle/12.1/client64/lib/libipc1.so
/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
/usr/lib/oracle/12.1/client64/lib/ojdbc6.jar
/usr/lib/oracle/12.1/client64/lib/ojdbc7.jar
/usr/lib/oracle/12.1/client64/lib/libons.so
/usr/lib/oracle/12.1/client64/lib/libocijdbc12.so
/usr/lib/oracle/12.1/client64/lib/libsqlplus.so
/usr/lib/oracle/12.1/client64/lib/orai18n.jar
/usr/lib/oracle/12.1/client64/lib/xstreams.jar
/usr/lib/oracle/12.1/client64/lib/libnnz12.so
/usr/lib/oracle/12.1/client64/lib/libociei.so
/usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
/usr/lib/oracle/12.1/client64/lib/orai18n-mapping.jar
/usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
/usr/lib/oracle/12.1/client64/lib/glogin.sql
/usr/lib/oracle/12.1/client64/lib/liboramysql12.so
/usr/lib/oracle/12.1/client64/lib/libsqlplusic.so
/usr/lib/oracle/12.1/client64/bin/sqlplus
/usr/lib/oracle/12.1/client64/bin/genezi
/usr/lib/oracle/12.1/client64/bin/adrci
/usr/lib/oracle/12.1/client64/bin/wrc
/usr/lib/oracle/12.1/network/admin/tnsnames.ora
/usr/lib/oracle/12.1/network/admin/sqlnet.ora

My agent is ​12.2.0+build.970.

All I can say is, the above is a working combo at least for the agent mode that uses the ucsrv.ini file (it has two different modes, details are a bit sketchy).

Hth,

[Genarro Montano]

The issue more seems to be when enabling the agent (12.3) to use the client authentication. Oracle db type and the value 1 selected for oraclient. This is found in the .ini file of the agent. When attempting to connect using this setting, the 1 value seems to be ignored. If I change the everything involved to use just Oracle and setting 0, the job runs fine. It does not seem to be a driver issue based on the evidence that as long as I change the connection object to use oracle, and then edit the ini file to use oracle, the job is able to run successfully. Unfortunately for us, most if not all of our db jobs execute using the OracleOCI setting so manually changing these would not particularly be viable. 
One thing that we are attempting is instead of using the instant client on our agent location, is installing the full client for Oracle 12.1. I am not sure if this will help.

Thank you everyone for your responses and help with this situation. 
I appreciate all the information and will use these as test cases.

[Ahmed Intizar]

We are using oracle full client since long time. Because all our db's are RAC databases.
And as I said earlier, jobs were working fine until upgrade to 12.3. Seeminlgy agent is ignoring useOraClient=1 setting or due to any other unknown bug.

[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
https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Script/Reference/GET_CONNECTION.htm?

------------------------------
Thx & rgds
Christian
------------------------------

[Ahmed Intizar]

Thanks a lot Christian, indeed it works.
So, the behavior is changed but not correctly documented.

[Genarro Montano]

We tried that same fix, but in our case that does not resolve the issue. During our original configuration, we quickly saw the error of the oraclient setting. As I said, most of our db jobs if not all run on our old 11.1 environment with the setting 1 toggled in the agent .ini file and with the Oracle_OCI setting selected in our connection objects. So in our new implementation we followed those same steps and now in the 12.3 system none of these jobs run. We receive an error either saying that the TNS listener could not resolve or if we change the settings to just Oracle in the connection object it will say that we are missing the ocijdbc12 driver (this would reside in our Oracle client lib directory, and can confirm it does). Our TNS_ADMIN env variable is set to our location of where the client was installed and where the tnsnames.ora file is located. On our connection object, it contains the relevant information for another database within our env. So, we need to use the Oracle Client setting to be able to connect with our other db (not our automation engine db). This is what is failing, when we run db jobs using the oracle setting to our automation engine, those are successful. It is when we attempt to execute sql jobs that are meant to query a different db than our automation engine.

[Genarro Montano]

We have our environment functioning properly now, for us using the full client as well as changing the settings of the agent .ini file to Oracle_OCI seems to be working for us right now. 

Thank you all who took the time to suggest solutions and talk this through with me, 
appreciate all the help!
Genarro Montano