I have an application server talking to an Oracle database, using the Oracle instant client. We are troubleshooting a specific bug, and it was asked if I could add the following flags to to my sqlnet.ora file:


Prior to this request, we did not need a sqlnet.ora file on the client side, with instant client, we could reference the classpath of ojdbc6.jar and the ld_library_path of the directory of the jar, and use a URL like this:


Looking at the Oracle Docs, it appeared that I could reference a sqlnet.ora file and/or a tnsnames.ora file on the client side if I added environment variables for ORACLE_HOME and TNS_ADMIN.

I created the sqlnet.ora file with the parameters above, and added some trace parameters so that I could verify that the file was being read:


I also created a tnsnames.ora file:

        (ADDRESS LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = *service*)))

Finally, I added environment variables, and restarted the application. I tried with the original simpler URL, and it was clear that the settings weren't picking up, as I would expect with the trace flags that a file would be created if it were being read. Next I tried to create the URL using the TNS name, which per the docs sounded like it would look in TNS_ADMIN for additional files to connect with.

jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = *service*)))

So far, I haven't seen any trace files come up, and I have a feeling I'm doing a part of this wrong, or missing a step, or using the wrong URL. I tried a few variants of connection strings that would reference "APPDB" as in my tnsnames.ora file, but either it doesn't work at all, or it connects, but does not seem to be picking up the settings from sqlnet.ora.

Any ideas on what I could be missing, or if in fact applying these settings is only possible on the DB side?

  • 1
    Those settings are meant for disabling OCI signal handlers, by the looks of things - nothing to do with logging. Don't really see how it'll help troubleshooting a problem with a java app. What's the actual problem you're trying to diagnose? (Ignore everything you've been told to do, tell us the original problem) – Philᵀᴹ Oct 17 '14 at 23:49
  • Sorry, I wasn't totally clear on that, my goal was to disable the signal handlers, but there wasn't a clear way to identify that they were disabled, so I added the trace to verify that the sqlnet.ora file was being read, which would imply that the signal handler changes were also read. – edinor Oct 18 '14 at 1:11
  • Oh, the original issue is that with the 11.2.x driver, the Java app will have a random segfault. If we run 11.1.x, no issue. This was independent of Java version, OS version, or our app version, the only thing we had to change for unstable->stable is 11.2 to 11.1. There is a bug identified (Bug 9367425) that was supposed to be fixed in, but it appears that we are still hitting it in, so it was suggested that we attempt the same workaround to see if that fixes the stability. – edinor Oct 18 '14 at 1:17
  • @Phil: DIAG_ADR_ENABLED=OFF is related to logging – miracle173 Oct 20 '14 at 16:17
  • 1
    The difference of oracle instant client and the traditionally installed oracle client is in how they are installed. The Oracle universal installer maintains e registry which the instan client does not have. but the same softwre components are the same independen of the method you use to install. If you use the jdbc oci driver then you use a layer above the you use the layer above oracle oci client (which is used the oracle client libraries that use – miracle173 Oct 20 '14 at 16:37

The difference of oracle instant client and the oracle client installed by Oracle Universal Installer is in how they are installed. The Oracle universal installer maintains a registry which the instant client does not have or use. But the software components are the same independent of the method you use to install. If you use the jdbc oci driver then you use java to access the libraries of the Oracle OCI client which is the well known client that use all the clients that can use a tnsname.ora. These are sqlplus, ODBC program, C-Programs that use the Oracle Precompiler, ... Access with the OCI client can be configured with tnsnames.ora and sqlnet.ora and the well known environment variables (like ORACLE_HOME, ORACLE_SID, TNS_ADMIN,...). In java there is a new way to access the Oracle database by using the jdbc thin client. This is a client completely implemented in java that does not use the oracle OCI libraries but has implemented the necessary oracle net protocols in java to communicate with an Oracle database. This client cannot be influenced by tnsnames.ora, sqlnet.ora or the environment variables. It does not even need these files. It only needs an appropriate jar file that implements the thin client.

If you use the oci client for a program then in the environment that starts this program the Oracle relevaant variables must be set. So if a program is started by a shell script this shell script should set and export the relevant variables. The directory stored in the variable TNS_ADMIN is searchd for the tnsnames.ora and sqlnet.ora. If this variable is not set then other directories are searched for these files (or similar files). This depends on the operating system and of the value of ORACLE_HOME, if set. If no appropriate file is found default values are used.

DIAG_ADR_ENABLED=OFF determines if ADR-logging (available since 11.1) is used or the traditional logging is used. If ADR-logging is not disabled the TRACE_DIRECTORY_CLIENT parameter will be ignored and the sqlnet trace files will be found somewhere in the diag-directories used by ADR-logging.

| improve this answer | |

I figured it out. Instead of having the environment variables set for the user, they had to be asserted when starting the JVM for the application, just like you have to set the LD_LIBRARY_PATH and CLASSPATH for the JDBC Driver. If everything is set when starting up the JVM, and you have a sqlnet.ora file in the directory you've set TNS_ADMIN, the file will be read.

| improve this answer | |
  • +1 that is true for all environment variables: they are inherited from a process (e.g. shell ) to its subprocesses (e.g. application program started by the shell that starts the process) – miracle173 Oct 20 '14 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.