The SQL agent throws an exception after startup: no ocijdbc12 in java.library.path

Products

• CA Automic One Automation

Issue/Introduction

When trying to start the SQL Agent and connect to the Oracle database, immediately after starting, the agent throws the following error message:
/opt/###/uc4/java/jre1.8.0_131/bin/java -Xrs -Xmx256M -jar /opt/###/uc4/agent-sql/ucxjsqlx.jar -i/opt/###/uc4/agent-sql/ucxjsqlx_oracle_EXP2-A.ini -Djava.library.path=/opt/oclient/default/lib:/opt/oclient/default/jlib 
com.uc4.ex.sql.ConnectionException: java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path 
at com.uc4.ex.sql.ConnectionManager.getConnection(ConnectionManager.java:245) 
at com.uc4.ex.sql.SQLConsumer.ensureConnection(SQLConsumer.java:330) 
at com.uc4.ex.sql.SQLConsumer.execute(SQLConsumer.java:152) 
at com.uc4.ex.Job.run(Job.java:466) 
at com.uc4.ex.ThreadPool$RunnableDecorator.run(ThreadPool.java:53) 
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) 
at java.util.concurrent.FutureTask.run(FutureTask.java:266) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 
at java.lang.Thread.run(Thread.java:748) 

Environment

SQL Agent connected to an Oracle database 

Cause

Wrong or too many ojdbc*.jar files in the agent's jdbc sub directory. The jdbc directory is located in the agent’s bin folder (e.g.: … automation.engine/agent-sql/jdbc)

The agents search during the startup process in the jdbc subdirectory for the database driver. If you have more than one ojdbc*.jar file located in this directory the agents use the file in a random order.

If for example:
ojdbc6.jar 
ojdbc7.jar 
ojdbc8.jar are located in the jdbc subdirectory it might be possible that the agent uses the false ojdbc*.jar driver 

When a connection to the DB is required, the JDBC driver searches the paths specified in LD_LIBRARY_PATH for the OCI shared libraries. Crucially, different versions of the Oracle JDBC driver require different versions of the Oracle OCI client. 

In the described error above the problem was that the highest numbered jdbc file in the jdbc subdirectory was used (ojdbc8.jar)
And therefore, this error was thrown:
com.uc4.ex.sql.ConnectionException: java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path
 

Resolution

To resolve such issue please try the following steps:
*remove all ojdbc*.jar files from the jdbc subdirectory of the SQL agent directory except the one you want to use.
*adjust the relevant environment variables:
$ export PATH=/bin:/usr/bin:/usr/local/bin:/opt/###/uc4/utilities/bin:/opt/oclient/default/bin:/opt/###/uc4/agent-sql
$ export LD_LIBRARY_PATH=/lib:/usr/lib:/opt/oclient/default/lib
*restart the agent

Additional Information

N/A