SQL Vara Connection test Oracle OCI RAC error | Fetch error message failed!

Products

• Automic SaaS
• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When attempting to use Oracle OCI connections for SQL Variables in a SaaS environment, the connection test fails. The primary error encountered is:

• U02012064 Internal Error: Fetch error message failed!

Users may also see errors indicating a driver mismatch between OCI and JDBC or a "Remote system is not available" message. This typically occurs when a standard Oracle connection works, but the specific requirements for Oracle RAC (Real Application Clusters) failover and load balancing are not being met by the default OCI configuration.

Environment

 

• Product: Automic SaaS

• Component: SQL Variables / Connection Objects

• Database: Oracle RAC

• Version: 24.x

 

Resolution

Keep in mind that you will need to have a DB_SERVICE agent installed on-premise as the Automation Engine on SaaS cannot reach your servers' databases directly without extra settings. 

To resolve this issue and support RAC failover without relying on the local Oracle OCI client, switch to a Generic JDBC connection using the ojdbc thin driver.   

1. Open the Connection Object (CONN) intended for the Oracle RAC database.   
   
   
2. Change the Database Type to Generic JDBC.   
3. In the Connection String field, use the following syntax to point directly to the service: jdbc:oracle:thin:@[full_tnsnames_entry]   
   
   
4. Paste your full tnsnames.ora description into the string. For example, if the tnsnames.ora file has:
   
   AUTOMIC = 
    (DESCRIPTION_LIST=
       (LOAD_BALANCE=off) (FAILOVER=on)
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-a.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-b.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
     )
   
   You can copy everything from (DESCRIPTION_LIST=...  and beyond:
   
   jdbc:oracle:thin:@(DESCRIPTION_LIST=
       (LOAD_BALANCE=off) (FAILOVER=on)
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-a.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-b.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
     )
   
   
5. Ensure the ojdbc.jar file is present in the agent's bin/jdbc directory.   
   
   
6. Perform a Connection Test to verify connectivity.

It may be enough to point to the service name which has a slightly different syntax.  The steps to use this are below:

1. Open the Connection Object (CONN) intended for the Oracle RAC database.   
   
   
2. Change the Database Type to Generic JDBC.   
   
   
3. In the Connection String field, use the following syntax to point directly to the service: jdbc:oracle:thin:@//[host where tnsnames.ora is located]/[service_name]
   
   
4. For example, if the tnsnames.ora file on automic-db-a.example.com has:
   
   AUTOMIC = 
    (DESCRIPTION_LIST=
       (LOAD_BALANCE=off) (FAILOVER=on)
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-a.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
       (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
         (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=automic-db-b.example.com)(PORT=1541)))
         (CONNECT_DATA=(SERVICE_NAME=automicsvc.example.com)))
     )
   
   You can use the service name like:
   
   jdbc:oracle:thin:@//automic-db-a.example.com/automicsvc.example.com
   
   
5. Ensure the ojdbc.jar file is present in the agent's bin/jdbc directory.   
   
   
6. Perform a Connection Test to verify connectivity.
   
   The drawback to this is that it can only point to one server's tnsnames.ora entry.  So if the Oracle client is on both automic-db-a.example.com and automic-db-b.example.com, you would need a connection object for each.

Additional Information

The U02012064 error often hides the underlying "ORA-12514: TNS:listener does not currently know of service requested" or similar listener errors when the SaaS agent cannot correctly resolve the local Oracle client environment. Using the full description list in the thin driver connection string bypasses these local configuration issues.