Configuring sqlnet.ora and tnsnames.ora on the client side

Question

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:

DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

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:

jdbc:oracle:oci:@server:port:sid

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:

TRACE_LEVEL_CLIENT=10
TRACE_DIRECTORY_CLIENT=/tmp

I also created a tnsnames.ora file:

APPDB =
    (DESCRIPTION =
        (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?

Answer 1

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.

Answer 2

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.