Back to Blog Home

The Complete Guide to Troubleshooting Oracle Connection Errors

Franck Pachot

July 24, 2023

In this blog post, I’ll demonstrate the different error messages you might encounter when a connection to Oracle database fails.  The error messages are not always easy to troubleshoot.

I will connect to Oracle using the:

  1. JDBC Thin Driver, which is a simple JAR commonly used by Java applications since it requires no additional platform-specific libraries
  2. JDBC OCI Driver, which uses the Oracle client library. Its behavior and error messages are the same, no matter if you call it from Java or another language. 

Clearly identifying different connection error scenarios makes troubleshooting complex situations much easier.

Why Troubleshoot Oracle Connection Errors?

Oracle DBAs, developers, and users often encounter issues where they cannot connect to an Oracle database and have to verify what’s wrong with their connection configuration. In terms of YugabyteDB, some of our customers have received connection errors as they migrate from Oracle to YugabyteDB.

This is because with our database migration tool, YugabyteDB Voyager, they need to provide the connection information of the source Oracle database, using the parameters –source-db-user –source-db-password and –oracle-tns-alias. They also need a set of files with at least a tnsnames.ora and probably a wallet full of certificates.

The bottom line is that they have to troubleshoot the Oracle database connection error messages so that they can provide the connection to migrate their data to YugabyteDB.

Testing the Oracle Database Connection From Java

To test the connection to an Oracle database from Java, you can use the following program (TestJDBC.java). 

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class TestJDBC {
 public static void main(String[] args)
 throws SQLException,InterruptedException {
  try (Connection c = (Connection) DriverManager.getConnection(
    args[2],args[0],args[1]) // parameters are: user password url
   ) { System.out.println(" Connected to schema: "+c.getSchema()); }
 }
}

Then compile and run it with the Oracle Instant Client installed

CLASSPATH=".:/usr/lib/oracle/19.10/client64/lib/ojdbc8.jar" javac TestJDBC.java

Then define the CLASSPATH and, for the OCI driver which will need additional libraries, use the LD_LIBRARY_PATH:

CLASSPATH=".:/usr/lib/oracle/19.10/client64/lib/ojdbc8.jar"

LD_LIBRARY_PATH=/usr/lib/oracle/19.10/client64/lib

The program requires three arguments:

  1. Username
  2. Password
  3. JDBC URL

To demonstrate different error messages, I will attempt to make connections using incorrect information, or try to connect to a database that is not opened to users.

Scenarios That Depend on Connection Environment

To begin with, here are two scenarios where my environment is not correctly set:

  1. no CLASSPATH defined to ojdbc.jar:
    $ java TestJDBC  "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@xxx"

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle:oci:@xxx
  2. no LD_LIBRARY_PATH for OCI:
    $ java TestJDBC  "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@xxx"

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle:oci:@xxx

Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc19 in java.library.path: [/usr/java/packages/lib, /lib, /usr/lib, /usr/lib64, /lib64]

    Now that everything is set correctly in the environment, let’s explore other scenarios that depend on the connection settings.

    JDBC Thin and JDBC OCI without TLS

    A JDBC Thin connection uses a JDBC URL that starts with 'jdbc:oracle:thin:@' and includes the necessary Oracle Client JARs in the CLASSPATH. The connection information that follows in the JDBC URL is interpreted in Java by this library. The JAR name depends on the Java version: ojdbc8.jar is compatible with JDK8, ojdbc11.jar with JDK11. The version of the driver itself is not visible in the name. For example, I’ve installed the InstantClient 19.10 and my JAR is /usr/lib/oracle/19.10/client64/lib/ojdbc8.jar.

    For a JDBC OCI connection, utilize a JDBC URL starting with 'jdbc:oracle:oci:@'. The Oracle Client libraries (not Java) interpret the information. NOTE: I’m using the InstantClient). Those libraries are found by the Linux mechanism: LD_LIBRARY_PATH must be set (/usr/lib/oracle/19.10/client64/lib for me).

    Without TLS, no certificate is required, but transmission is unencrypted except for the password. This is not suitable for production data, but let’s start simple before adding complexity.

Scenarios 1: If you try to connect with an incomplete connect identifier, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Connect identifier was empty.
Caused by: oracle.net.ns.NetException: Connect identifier was empty.

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12162: TNS:net service name is incorrectly specified

You already see that it can be useful to test with the Thin and the OCI driver: they don’t show the same messages.

Scenario 2: If you try to connect without the @ sign, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:xxx"

The result will be:

Exception in thread "main" java.sql.SQLException: Invalid Oracle URL specified

JDBC OCI Driver, use::

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:xxx"

The result will be:

Exception in thread "main" java.sql.SQLException: Invalid Oracle URL specified

Scenario 3: If you try to connect with a typo in the connection string, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@host=xxx"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"

Caused by: oracle.net.ns.NetException: Invalid connection string format, a valid format is: "host:port:sid"

Note that this message is misleading. There are many more complex valid formats that we will see later, and this one mentioning a sid should never be used from an application.

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@host=xxx"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Scenario 4: If the host is unknown for DNS resolution, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@xxx"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Unknown host specified
Caused by: oracle.net.ns.NetException: Unknown host specified

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@xxx"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Scenario 5: If the host is known by the DNS but not routable, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@170.61.112.206"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
Caused by: java.io.IOException: No route to host, socket connect lapse 11 ms. /170.61.112.206 1521 0 1 true
Caused by: java.net.NoRouteToHostException: No route to host

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@170.61.112.206"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12543: TNS:destination host unreachable

Scenario 6: If the IP address is routable but unknown (or the server or network is down) then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@192.168.42.42"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
Caused by: java.io.IOException: Connection timed out, socket connect lapse 130867 ms. /192.168.42.42 1521 0 1 true
Caused by: java.net.ConnectException: Connection timed out

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@192.168.42.42"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12170: TNS:Connect timeout occurred

Scenario 7: If the server answers but the port is not opened, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@localhost"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
Caused by: java.io.IOException: Connection refused, socket connect lapse 0 ms. /0:0:0:0:0:0:0:1 1521 0 1 true
Caused by: java.net.ConnectException: Connection refused

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@localhost"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12541: TNS:no listener

Scenario 8: If the port is opened but service is not provided, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@localhost:1522"

The result will be:

Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@localhost:1522"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Scenario 9: If service is given but not known by the listener, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@localhost:1522/XXX"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@localhost:1522/XXX"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Scenario 10: If service is known by the listener but the database does not open, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@localhost:1522/PDB2"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01109: database not open

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@localhost:1522/PDB2"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01109: database not open

Scenario 11: If the database is opened but the username not known, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@localhost:1522/PDB1"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@localhost:1522/PDB1"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Scenario 12: If username exists but the wrong password is provided, then:

JDBC Thin Driver, use:

java TestJDBC "demo" "my_password" "jdbc:oracle:thin:@localhost:1522/PDB1"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

JDBC OCI Driver, use:

java TestJDBC "demo" "my_password" "jdbc:oracle:oci:@localhost:1522/PDB1"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Finally, the best case scenario! If the username and password are correct, then:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@localhost:1522/PDB1"

Connected to schema: DEMO

While there are many other possible messages (e.g., expired password, locked account), these are not tied to the connection string. If you encounter an ORA- error instead of a TNS- error, it indicates a successful database connection. However, failure to connect with the correct account and password can also result from connecting to the wrong database. The error message remains the same whether the user is unknown or known with an invalid password.

Using a Network Service Name Defined in the tnsnames.ora

Instead of using a complete URL with hostname, port, and service (known as Easy Connect or EZCONNECT), you have the option to use a network service name defined in the tnsnames.ora file. This approach may introduce new error messages during the resolution process.

The tnsnames.ora description uses the long format. You can get this from Easy Connect with the tnsping utility:

$ tnsping localhost:1522/PDB1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 08:58:53

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/DB193/network/admin/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
OK (0 msec)

If you like it, you can also use it directly in the JDBC URL:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))"
Connected to schema: DEMO

However, this may be where you prefer to set it in a tnsnames.ora to use an alias:

mkdir /var/tmp/tns
echo "mydb.mydomain=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))" > /var/tmp/tns/tnsnames.ora

This name resolution makes the configuration simpler, but this surfaces a bunch of new scenarios with new possible errors.

Scenario #1: If you use the alias without specifying the tnsnames.ora location, then:

JDBC Thin Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:thin:@mydb.mydomain"

The result will be:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Unknown host specified
Caused by: oracle.net.ns.NetException: Unknown host specified

Note: The JDBC Thin Driver will try to find mydb.mydomain from hostname resolution, ignoring any tnsnames.ora attempt.

JDBC OCI Driver, use:

java TestJDBC "my_user" "my_password" "jdbc:oracle:oci:@mydb.mydomain"

The result will be:

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

The Thin and OCI drivers differ significantly in this scenario. The Thin driver will not search for the tnsnames.ora file unless a location is specified. However, the OCI driver will search for this file in predefined locations. To view these locations, you can trace the system calls using:

strace -fye trace=file -o /dev/stdout java TestJDBC "my_user" "my_password"

The result will be:

648429 faccessat(AT_FDCWD, "/usr/lib/oracle/19.10/client64/lib/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
648429 faccessat(AT_FDCWD, "/home/opc/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
648429 faccessat(AT_FDCWD, "/usr/lib/oracle/19.10/client64/lib/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
648429 faccessat(AT_FDCWD, "/home/opc/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
648429 faccessat(AT_FDCWD, "/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
648429 faccessat(AT_FDCWD, "/usr/lib/oracle/19.10/client64/lib/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

The first attempt is $ORACLE_HOME/network/admin/tnsnames.ora. If not defined, ORACLE_HOME is where the library was found (so LD_LIBRARY_PATH). If not found, the second attempt is $HOME/.tnsnames.ora, in the Linux user’s home directory. The third attempt is in the system-wide configuration directory, /etc/tnsnames.ora. But wait, there’s a bit more.

Prior to searching for tnsnames.ora it searches for sqlnet.ora in the same locations. The location where sqlnet.ora is found will determine where tnsnames.ora is read. In the sqlnet.ora file you can define the resolution method using NAMES.DIRECTORY_PATH specifying a list of methods in order (e.g. EZCONNECT to take the name in the connection string as a hostname; TNSNAMES to take it as a tnsnames.ora entry.)

If this looks too complex, that is because it is. There’s 30 years of name resolution, network protocols and client drivers represented here.

Scenario 2: To look at a non-default location with the OCI driver, you need to define the TNS_ADMIN environment variable.

JDBC OCI Driver, use:

TNS_ADMIN=/var/tmp/tns java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@mydb.mydomain"

The result will be:

Connected to schema: DEMO

This also works for the Thin driver. JDBC Thin Driver, use:

TNS_ADMIN=/var/tmp/tns java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@mydb.mydomain"

The result will be:

Connected to schema: DEMO

There are more possibilities. For example, you could use the Thin driver with a Java variable:

java -Doracle.net.tns_admin=/var/tmp/tns TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@mydb.mydomain"

The result will be:

Connected to schema: DEMO

Or you can even set it within the URL, using:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@mydb.mydomain?tns_admin=/var/tmp/tns"

The result will be:

Connected to schema: DEMO

Note: 19c version of the drivers are being used here. Previous versions may not offer all those possibilities.

Scenarios With Incorrect TNS Name resolution

Now, let’s examine some error messages. The challenge lies in the fact that Oracle consistently returns the same error message, 'could not resolve the connect identifier', for different causes. To troubleshoot on Linux, trace the file opening attempts with:

trace_tns_admin(){
strace -fye trace=file -o /dev/stdout $@ | grep "$TNS_ADMIN"
}

Scenario #1: If the directory is non-existent:

JDBC Thin Driver, use:

TNS_ADMIN=/xxx trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@mydb.mydomain"

The result will be:

634230 newfstatat(AT_FDCWD, "/xxx/ojdbc.properties", 0xfffdd504cce0, 0) = -1 ENOENT (No such file or directory)
634230 faccessat(AT_FDCWD, "/xxx/ojdbc.properties", F_OK) = -1 ENOENT (No such file or directory)
634230 newfstatat(AT_FDCWD, "/xxx/tnsnames.ora", 0xfffdd504d9d0, 0) = -1 ENOENT (No such file or directory)
634230 newfstatat(AT_FDCWD, "/xxx/tnsnames.ora", 0xfffdd504d640, 0) = -1 ENOENT (No such file or directory)

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "mydb.mydomain"
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "mydb.mydomain"

JDBC OCI Driver, use:

TNS_ADMIN=/xxx trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@mydb.mydomain"

The result will be:

649543 newfstatat(AT_FDCWD, "/xxx/ojdbc.properties", 0xfffc5a5fcc70, 0) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/ojdbc.properties", F_OK) = -1 ENOENT (No such file or directory)
649543 newfstatat(AT_FDCWD, "/xxx/tnsnames.ora", 0xfffc5a5fd9e0, 0) = -1 ENOENT (No such file or directory)
649543 openat(AT_FDCWD, "/xxx/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
649543 faccessat(AT_FDCWD, "/xxx/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
649543 newfstatat(AT_FDCWD, "/xxx/ldap.ora", 0xfffc5a5ee2c0, 0) = -1 ENOENT (No such file or directory)

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Scenario #2: If the directory has no tnsnames.ora file, then:

JDBC Thin Driver use:

TNS_ADMIN=/home trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@mydb.mydomain"

The result will be:

634348 newfstatat(AT_FDCWD, "/home/ojdbc.properties", 0xfffcc29dcce0, 0) = -1 ENOENT (No such file or directory)
634348 faccessat(AT_FDCWD, "/home/ojdbc.properties", F_OK) = -1 ENOENT (No such file or directory)
634348 newfstatat(AT_FDCWD, "/home/tnsnames.ora", 0xfffcc29dd9d0, 0) = -1 ENOENT (No such file or directory)
634348 newfstatat(AT_FDCWD, "/home/tnsnames.ora", 0xfffcc29dd640, 0) = -1 ENOENT (No such file or directory)

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "mydb.mydomain"
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "mydb.mydomain"

JDBC OCI Driver, use:

TNS_ADMIN=/home trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@mydb.mydomain"

The result will be:

649650 newfstatat(AT_FDCWD, "/home/ojdbc.properties", 0xffffa88bcc70, 0) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/ojdbc.properties", F_OK) = -1 ENOENT (No such file or directory)
649650 newfstatat(AT_FDCWD, "/home/tnsnames.ora", 0xffffa88bd9e0, 0) = -1 ENOENT (No such file or directory)
649650 openat(AT_FDCWD, "/home/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/opc/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 newfstatat(AT_FDCWD, "/home/opc", {st_mode=S_IFDIR|0700, st_size=8192, ...}, 0) = 0
649650 newfstatat(AT_FDCWD, "/home/opc/oradiag_opc/diag/clients/user_opc/host_1175669419_110", {st_mode=S_IFDIR|0775, st_size=179, ...}, 0) = 0
649650 newfstatat(AT_FDCWD, "/home/opc", {st_mode=S_IFDIR|0700, st_size=8192, ...}, 0) = 0
649650 faccessat(AT_FDCWD, "/home/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/opc/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 faccessat(AT_FDCWD, "/home/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
649650 newfstatat(AT_FDCWD, "/home/ldap.ora", 0xffffa88ae2c0, 0) = -1 ENOENT (No such file or directory)

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Scenario #3: If the tnsnames.ora has no entry for the service name, then:

JDBC Thin Driver, use:

TNS_ADMIN=/var/tmp/tns trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@xxx.mydomain"

The result will be:

634435 newfstatat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", {st_mode=S_IFREG|0600, st_size=88, ...}, 0) = 0
634435 newfstatat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", {st_mode=S_IFREG|0600, st_size=88, ...}, 0) = 0
634435 openat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", O_RDONLY) = 9
634435 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
634435 faccessat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", R_OK) = 0
634435 openat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", O_RDONLY) = 9
634435 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
634435 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
634435 faccessat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", R_OK) = 0
634435 openat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", O_RDONLY) = 9
634435 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "mydb.mydomain"
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "mydb.mydomain"

NOTE: In this case, the file was found and opened, giving a clue that the directory was correctly set. The reason for the error is that the alias name was not found. Be careful with the tnsnames.ora syntax. It’s a tricky one.

JDBC OCI Driver, use:

TNS_ADMIN=/var/tmp/tns trace_tns_admin java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@xxx.mydomain"

The result will be:

649740 newfstatat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", {st_mode=S_IFREG|0600, st_size=88, ...}, 0) = 0
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", {st_mode=S_IFREG|0600, st_size=88, ...}, 0) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/ojdbc.properties", O_RDONLY) = 6
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 faccessat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", R_OK) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", O_RDONLY) = 6
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
649740 faccessat(AT_FDCWD, "/var/tmp/tns/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
649740 faccessat(AT_FDCWD, "/var/tmp/tns/sqlnet.ora", F_OK) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/sqlnet.ora", O_RDONLY) = 6
649740 faccessat(AT_FDCWD, "/var/tmp/tns/sqlnet.ora", F_OK) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/sqlnet.ora", O_RDONLY) = 8
649740 faccessat(AT_FDCWD, "/var/tmp/tns/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
649740 faccessat(AT_FDCWD, "/var/tmp/tns/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
649740 faccessat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", F_OK) = 0
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 openat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", O_RDONLY) = 8
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/ldap.ora", 0xfffc35cde2c0, 0) = -1 ENOENT (No such file or directory)
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0
649740 newfstatat(AT_FDCWD, "/var/tmp/tns/tnsnames.ora", {st_mode=S_IFREG|0600, st_size=114, ...}, 0) = 0

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Adding Transport Layer Security (TLS)

As you migrate the production data (say, if you are migrating to a new database), it is crucial to secure the network using Transport Layer Security (TLS)—previously known as SSL (Secure Sockets Layer).

There two options available:

  1. mTLS (Mutual TLS), where both the client and the server provide an encrypted key, so that each can trust the other.
  2. One-way TLS, where only the server holds the encryption key.

Mutual TLS is necessary when the database is accessible through the public internet, ensuring client trust beyond user/password authentication. However, it requires the downloading of a client wallet. On the other hand, one-way TLS is suitable when the server is accessible only within a trusted network—simplifying the configuration process.

Let’s test both using an Oracle Autonomous Database created in the cloud. The connection string was sourced from Database connection -> Connection strings. I also used the ‘TP’ service rather than the MEDIUM or HIGH services since I don’t want to use parallel query.

JDBC Thin and JDBC OCI With One-Way TLS

JDBC Thin and JDBC OCI With One-Way TLS

JDBC Thin Driver, use:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"

The result will be:

oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12506, TNS:listener rejected connection based on service ACL filtering

JDBC OCI Driver, use:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"

The result will be

651164 faccessat(AT_FDCWD</var/tmp>, "/usr/lib/oracle/19.10/client64/lib/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
651164 faccessat(AT_FDCWD</var/tmp>, "/home/opc/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
651164 faccessat(AT_FDCWD</var/tmp>, "/usr/lib/oracle/19.10/client64/lib/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/ewallet.p12", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/cwallet.sso", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/ewallet.p12", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/cwallet.sso", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/ewallet.p12", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)
651164 newfstatat(AT_FDCWD</var/tmp>, "/etc/ORACLE/WALLETS/opc/cwallet.sso", 0xfffd393da830, 0) = -1 ENOENT (No such file or directory)

...

Exception in thread "main" java.sql.SQLException: ORA-28759: failure to open file

To understand the error message better, I got the traces above the error message with strace -fye trace=file -o /dev/stdout and grep -E "wallet|sqlnet". With the OCI driver and (security=(ssl_server_dn_match=yes)) mentioned in the connection string the attempt to use One-Way TLS failed, but didn’t stop there. It tried to find a certificate for mTLS that we will see in the section below.

As of right now, I do not want to connect with a wallet but with a one-way TLS and then add my IP address (which I will get from curl ifconfig.me to the Access Control List):

Caption: Oracle Autonomous Database Network Access

Caption: Oracle Autonomous Database Access Control List

Caption: Oracle Autonomous Database Mutual TLS Authentication

Now the connection works, using One-Way TLS:

JDBC Thin Driver, use :

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))".

The result will be:

Connected to schema: DEMO

JDBC OCI Driver, use:

java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"

The result will be:

Connected to schema: DEMO

I added this connection description in the tnsnames.ora, as before. All service definitions are accessible in the credential wallet and you only need this file to establish a  TLS connection (not mTLS).

cp /home/opc/wallet_oci_fra/tnsnames.ora /var/tmp/tns
java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@adb_tp?tns_admin=/var/tmp/tns"

Connected to schema: DEMO

JDBC Thin and JDBC OCI With Mutual TLS (mTLS)

Now, let’s say that I connect from another IP that I cannot trust being added to the list of allowed IPs in the Access Control List.

One example is when someone connects from their university. Their allow list is open to anyone visiting or attending the university, unlike companies that only opens access to employees. The only protection that remains for the university’s database is password authentication, but that is not sufficient.

This is the type of scenario where you would want to require a client-side certificate so that the server can authenticate the client with mutual TLS (mTLS). The place where the certificate is stored is different with the JDBC Thin and JDBC OCI drivers.

Doing the same as above, without my IP allowed in Oracle’s Access Control List, doesn’t work, and you get the error message we saw above. Let’s start from there.

So, if you are using one-way TLS without being in the Access Control List (ACL), then:

JDBC Thin Driver:

TNS_ADMIN=/var/tmp/tns java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@adb_tp"
oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12506, TNS:listener rejected connection based on service ACL filtering

JDBC OCI Driver:

TNS_ADMIN=/var/tmp/tns java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@adb_tp"
Exception in thread "main" java.sql.SQLException: ORA-28759: failure to open file

JDBC OCI Driver with mTLS

Let’s look first at the OCI driver since we have a message that says that a file is missing.

Without being on the access list, I need mTLS (Mutual TLS) with a certificate on the client side, and  I need more files from the wallet. I will download and unzip the wallet into /var/tmp/tns and try to connect with the OCI driver and the TNS_ADMIN set to this wallet:

TNS_ADMIN=/var/tmp/tns java TestJDBC "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@adb_tp"
Exception in thread "main" java.sql.SQLException: ORA-28759: failure to open file

The reason for this error is that the sqlnet.ora mentions ?/network/admin which is a shortcut for the /network/admin under the ORACLE_HOME, which defaults to /usr/lib/oracle/19.10/client64/lib.sql in my configuration.

Because I’ve not put the wallet files into this ORACLE_HOME but into a directory specified by the TNS_ADMIN environment variable, I need to modify the sqlnet.ora, but fortunately I can use the Linux environment variable with ${TNS_ADMIN}:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="${TNS_ADMIN}")))
SSL_SERVER_DN_MATCH=yes

I’m now able to connect:

$ TNS_ADMIN=/var/tmp/tns java TestJDBC  "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@adb_tp"
 Connected to schema: DEMO

There is actually only one file—the auto-login wallet cwallet.sso used for OCI connection—in addition to sqlnet.ora (to set the wallet location) and tnsnames.ora (to define the connection description):

$ ls /var/tmp/tns
cwallet.sso  sqlnet.ora  tnsnames.ora

$ cat /var/tmp/tns/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="${TNS_ADMIN}")))
SSL_SERVER_DN_MATCH=yes

$ cat tnsnames.ora
adb_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

$ TNS_ADMIN=/var/tmp/tns java TestJDBC  "demo" "!!P455w0rd!!" "jdbc:oracle:oci:@adb_tp"
 Connected to schema: DEMO

So if the cwallet.sso is not accessible, you’ll get ORA-28759: failure to open file from the OCI driver. As far as I know, the ewallet.p12 is useless here because it requires a password to be opened, and there’s no way to set a password in the connection string.

JDBC Thin with mTLS Using Java KeyStore

To use the Java Keystore, you need to update the file that’s read first by the JDBC Thin driver—ojdbc.properties with the location of the Keystore (${TNS_ADMIN}/truststore.jks). You also need the password you provided when downloading the wallet (javax.net.ssl.trustStorePassword=).

There is actually only one file, truststore.jks used here, in addition to ojdbc.properties (to set the wallet location) and tnsnames.ora (to define the connection description):

$ ls /var/tmp/tns
keystore.jks  ojdbc.properties  tnsnames.ora

$ cat /var/tmp/tns/ojdbc.properties
javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
javax.net.ssl.keyStorePassword=MyP4ssword

$ cat tnsnames.ora
adb_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

$ TNS_ADMIN=/var/tmp/tns java TestJDBC  "demo" "!!P455w0rd!!" "jdbc:oracle:thin:@adb_tp"
 Connected to schema: DEMO

JDBC Thin with mTLS Using the Oracle Wallet

Another possibility is to use the same wallet used by the OCI driver, cwallet.sso, that doesn’t require a password, The definition in ojdbc.properties doesn’t have to change since it references the TNS_ADMIN environment variable:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

However, you may encounter these extremely unclear messages:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: Unable to initialize ssl context.
Caused by: oracle.net.ns.NetException: Unable to initialize the key store.
Caused by: java.security.KeyStoreException: SSO not found
Caused by: java.security.NoSuchAlgorithmException: SSO KeyStore not available

You receive these messages because you require the JDBC Companion JARs in your CLASSPATH to read the Oracle Wallet. I use those from SQLcl:

$ CLASSPATH=".:/usr/lib/oracle/19.10/client64/lib/ojdbc8.jar:/home/opc/sqlcl/lib/oraclepki.jar:/home/opc/sqlcl/lib/osdt_cert.jar:/home/opc/sqlcl/lib/osdt_core.jar"

$ ls /var/tmp/tns
cwallet.sso  ojdbc.properties  tnsnames.ora

$ cat /var/tmp/tns/ojdbc.properties
oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

$ cat tnsnames.ora
adb_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=m0ve2yu64by73db_adb_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

$ TNS_ADMIN=/var/tmp/tns java TestJDBC  "demo" "!!P455w0rd!!" 
"jdbc:oracle:thin:@adb_tp"
 Connected to schema: DEMO

If the CLASSPATH is correct but the wallet not found, the error message suddenly becomes more clear:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
Caused by: oracle.net.ns.NetException: Unable to initialize ssl context.
Caused by: oracle.net.ns.NetException: Unable to initialize the key store.
Caused by: java.io.FileNotFoundException: /var/tmp/tns/cwallet.sso (No such file or directory)

Connection Errors When Migrating from Oracle Database

When migrating from Oracle to YugabyteDB with YugabyteDB Voyager, you can test your connection with a small Java program as I did above. Once the connection works with the right username, password, JDBC OCI URL, and TNS_ADMIN environment variable you are ready to migrate.

  • The username goes to –source-db-user
  • The password goes to –source-db-password
  • The tnsnames.ora alias that you have after ‘jdbc:oracle:thin:@’ is the –oracle-tns-alias

Here is an example starting YugabyteDB Voyager from my Windows laptop, with Docker Desktop. My wallet is unzipped in C:\Downloads\wallet and my working directory is C:\Downloads\mig

docker run -it --rm ^
 -v "C:\Downloads\mig":/mig  ^
 -v "C:\Downloads\wallet":/tns -e TNS_ADMIN=/tns ^
 yugabytedb/yb-voyager ^
 yb-voyager export --export-dir /mig schema ^
 --source-db-type oracle ^
 --oracle-tns-alias adb_tp ^
 --source-db-user demo ^
 --source-db-password "!!P455w0rd!!" ^
 --source-db-name=_this_is_ignored_ ^
 --source-db-schema=SH ^
 --start-clean

When I forgot to change the ?/rdbms/admin to $TNS_ADMIN in sqlnet.ora I get:

GIT_COMMIT_HASH=a21fa577f599be2367aa5e8886760b41b48701fc
Note: Using "/mig" as export directory
Using TNS Alias for export.
export of schema for source type as 'oracle'
[WARNING] Failed to find character set of the source db: failed to query "SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'" for database encoding: params=user=demo password="SECRET-6GBy6mXoQjg=" connectString=adb_tp timezone= poolIncrement=1 poolMaxSessions=1000 poolMinSessions=1 poolSessionMaxLifetime=1h0m0s
poolSessionTimeout=5m0s poolWaitTimeout=30s extAuth=0: ORA-28759: failure to open file
run query "SELECT BANNER FROM V$VERSION" on source: params=user=demo password="SECRET-6GBy6mXoQjg=" connectString=adb_tp timezone= poolIncrement=1 poolMaxSessions=1000 poolMinSessions=1 poolSessionMaxLifetime=1h0m0s
poolSessionTimeout=5m0s poolWaitTimeout=30s extAuth=0: ORA-28759: failure to open file

The error message is there, ORA-28759: failure to open file but it is probably easier to get that from the small Java program above, fix any issue, and then go with YugabyteDB Voyager.

On an additional note, the tnsnames.ora provided by Oracle Autonomous database defines 20 retries every 3 seconds in order to raise an error: (retry_count=20)(retry_delay=3). You probably don’t want to wait an entire minute to get the error, especially if it adds the TCP timeout (i.e. when host is not found) or the logon delay (i.e. when username or password is wrong). I set it to: (retry_count=1)(retry_delay=3).

To Summarize

Connecting to Oracle can be extremely challenging, but this blog post aims to help you dissect all possible error messages in order to troubleshoot your connection issues. We have seen these errors causing headaches for customers migrating from the Oracle Database to Yugabyte using YugabyteDB Voyager.

July 24, 2023
JDBCmTLSOracleOracle MigrationTLS Encryption
Franck Pachot

July 24, 2023

Related Posts

Explore Distributed SQL and YugabyteDB in Depth

Discover the future of data management.
Learn at Yugabyte University
Get Started
Browse Yugabyte Docs
Explore docs
PostgreSQL For Cloud Native World
Read for Free