If you are having problems connecting to your Oracle database, then you should follow the following steps for diagnosing this:
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.
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:
To complicate matters:
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).
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 v9). However for more recent versions of Windows (Windows Vista, Windows 7 and Window Server), 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:
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 v9. 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.