Oracle - Diagnosing Connection Problems

If you are having problems connecting to your Oracle database, then you should follow the following steps for diagnosing this:

• when you fail to connect, a file sqlnet.log is often created (see below). This can contain useful information about how the Oracle Client tried to connect, and the error it received.
• open a Windows command window and enter tnsping ORCL where ORCL is the name of the Oracle Service you are trying to connect to. If you are unsure of the Oracle Service name, from the AQT signon screen click on your Oracle database then click on Configure - the Oracle Service name is given in the field TNS Service Name.

  tnsping will try to connect to the Oracle database, and will provide useful information about how it is doing this and the error it has received.

tnsnames.ora

The information about the Oracle service names, and how to connect to them, is given in the Oracle file tnsnames.ora. In many cases, connection problems have happened because the wrong tnsnames.ora file is being used.

Oracle looks at the following locations for tnsnames.ora:

• the directory referred to in environment variable TNS_ADMIN
• the directory ORACLE_HOME\network\admin. ORACLE_HOME is given in the ORACLE_HOME environment variable, or the Windows registry.

To complicate matters:

• a user may have multiple ORACLE_HOMEs
• Oracle products may have their own ORACLE_HOME (and thus tnsnames.ora). So SQL*PLUS may be using one tnsnames.ora file but (unknown to you), AQT is using another.

To clear up this uncertainty, it is recommended that the TNS_ADMIN environment variable is set to refer to directory where tnsnames.ora is located. All Oracle products and AQT will then use this tnsnames.ora file.

To view environment variables, open a Windows command window and enter SET. To permanently set an environment variable, go to the Windows Control Panel > System. Click on the Advanced tab and then the Environment Variables button (this is for Windows XP - other Windows versions may have these in a different location).

sqlnet.log

If you fail to connect, the Oracle client will generally write diagnostic information to sqlnet.log. Note that this does not include information on which tnsnames.ora file is being used, which is often the cause of many connection problems.

In earlier versions of Windows, sqlnet.log was written in the same directory as the AQT executable (eg. C:\Program Files\Advanced Query Tool v11). However for more recent versions of Windows, access to the Program Files directories is restricted. As a result the file can often be created in a Virtual Store directory. You may wish to look for sqlnet.log in either:

• C:\Users\<username>\AppData\Local\VirtualStore\Program Files\Advanced Query Tool v11
• C:\Users\<username>\AppData\Local\VirtualStore\Windows\System32

Running AQT on a 64-bit version of Windows

If you are running AQT on a 64-bit version of Windows, you may fail to connect with message:

TNS could not resolve the connect identifier

This can happen due to a bug in the Oracle client in the 64-bit environment. This is described below.

By default, AQT will be installed into C:\Program Files (x86)\Advanced Query Tool v11. The Program Files (x86) directory structure is used for 32-bit applications. However there is a bug in the Oracle client - when you run a program which has a bracket in the path, the Oracle client will fail to parse tnsnames.ora correctly, resulting in the above message.

The resolution to this problem is to install AQT into a directory that doesn't have a bracket in the name.

Note that this problem has been fixed in recent versions of the Oracle Client.