How to troubleshoot connection errors when logging into a supported Oracle database from SQL Navigator.
Common Oracle connection errors:
* ORA-12154: TNS: could not resolve service name
* ORA-12505 TNS:listener could not resolve SID given in connect descriptor
* Error: 1.0 [Load Interface dlls failed]
* Initialization error: Could not find Oracle_Home\bin\oraXYZ.dll
* Initialization error: Could not locate OCI.dll
*OCI.dll, The specified module could not be found
1. From the SQL Navigator Server Login window:
a. Click the drop down at the top of the window that contains the list of clients that are installed on the computer. Select the desired client to use when connecting to a database.
NOTE: A RED Oracle Client entry means that there is a problem with that particular client and SQL Navigator will not be able to connect.
b. Click on the ellipses icon next to Oracle Client drop down list to access the Oracle Home Editor window and check all the HOME paths to make sure they are correct.
c. Click on the TNSnames Editor and verify that the tnsnames entries are correct for the desired database.
2. Check the System Requirements for supported client and server versions, by clicking on the following link and select the "Sys Reqs" tab.
http://www.quest.com/sql-navigator/
NOTE: Please check release notes for more detail information
3. Verify a 32-bit Oracle client is installed on the client machine. 64-bit Oracle clients are not supported. If the client is 64-bit, please install a 32-bit client and choose to connect using this client in the SQL Navigator connection window's Connect Using field.
Note: Starting in version 11.6, Toad now supports 64-bit Oracle clients. If you would like to use a 64-bit client, please download the 64-bit installer and install the 64-bit version of Toad.
4. When working in a 64-bit Operating System, verify that the Toad for Oracle install is NOT installed in a directory that includes parenthesis. It should not be in the Program Files (x86) directory. An incorrect path sample: C:\Program Files (x86)\Quest Software\Toad for Oracle.
5. Verify that you are able to connect via Oracle's SQL*Plus, either from SQL*Plus GUI or command line. SQL*Plus does not use SQLNet as a method of connection like Toad for Oracle, however it is a good test just to make sure you are able to established a connection from your PC. If you are unable to connect using Oracle's SQL*Plus, then you will not be able to connect in Toad for Oracle. If this is the case, please contact Oracle support or your own admin support to review your Oracle client install.
6. Verify that the server can be pinged and the database instance can be tnspinged.
a. Go to Windows Start | Run and type cmd for command prompt
b. Type: PING <hostname> press return for result.
c. Type: TNSPING <instance> press return key for result
7. Verify the Oracle Home BIN directory is the first listing in the Environmental Variables | System Variable | Path value.
For XP:
a. Right click on My Computer and select Properties.
b. In the Advanced tab | click Environment Variables.
c. Under System Variables, highlight Path and click Edit.
d. Move the Oracle home bin directory to the front of the entire path including the semicolon, if it is not listed first.
For Windows 7:
a. Right click on Computer and select Properties.
b. Click on "Advanced System Settings" on the left hand side of the window.
c. Click the Environment Variables button.
d. Under System Variables, highlight Path and click Edit.
e. Move the Oracle home bin directory to the front of the entire path including the semicolon, if it is not listed first.
8. Verify you are using the correct tnsnames.ora file. You can set the tnsnames.ora file you want used by setting the TNS_Admin environment variable.
For XP:
a. Right click on My Computer icon and select properties.
b. In the Advanced tab, select Environment Variables.
c. In the System Variables section (bottom portion), see if there is a TNS_Admin variable set up. If there is, make sure the path value is to the tnsnames.ora file you want to use. If there is not, click the New button.
d. Variable Name is TNS_Admin and Variable Value is the path to where your TNSNames.ora file is.
For Windows 7:
a. Right click on Computer and select Properties.
b. Click on "Advanced System Settings" on the left hand side of the window.
c. Click the Environment Variables button.
d. In the System Variables section (bottom portion), see if there is a TNS_Admin variable set up. If there is, make sure the path value is to the tnsnames.ora file you want to use. If there is not, click the New button.
e. Variable Name is TNS_Admin and Variable Value is the path to where your TNSNames.ora file is.
9. If your Sqlnet.ora file contains NAMES.DEFAULT_DOMAIN entry but the tnsnames.ora aliases does not show proper NAMES.DEFAULT_DOMAIN extension, you may want to try the following:
Add NAMES.DEFAULT_DOMAIN extension for the aliases in the tnsnames.ora file to match your sqlnet.ora.
E.g. If NAMES.DEFAULT_DOMAIN = ORACLE.CORP exists in sqlnet.ora, then make sure ORACLE.CORP is add to aliases in the tnsnames.ora file (i.e. not just "TOAD", but "TOAD.ORACLE.CORP").
Disclaimer : Quest does not provide support for problems that arise from improper modification of the registry. The Windows registry contains information critical to your computer and applications. Make sure you back up the registry before modifying it. For more information on the Windows Registry Editor and how to back up and restore it, refer to Microsoft Article ID 256986 "Description of the Microsoft Windows registry": http://support.microsoft.com/default.aspx?kbid=256986.
