Common Oracle connection problems (4226047)

How to troubleshoot connection errors when logging into a supported Oracle database

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

ORA-12154: TNS: could not resolve service name can happen when having a second tnsnames.ora for your second ORACLE_HOME that is being used.
Please search your machine if there is a second file tnsnames.ora for a second homecontaining alsoTNS-Entries.

After checking each step in the Connection Worksheet, most connection issues will be resolved. Please note there are extra suggestions for Toad version 7.6 to 9.7. For Toad for Oracle version 10.x and above, please see SOL64853.

FOR ALL TOAD VERSIONS:

1. Verify the System path variable contains the desired <Oracle Home>\bin directory and it is listed at the beginning before any other Oracle directories. Toad uses the path to determine the correct directory and will stop at the first one listed in the path. For example, C:\Orawin95\Bin, C:\Orant\Bin or D:\oracle\ora92\ (depending on if you have Win95, NT Win2k, XP, 2003 Operating System).

To verify the value of the PATH variable, from a DOS prompt type: PATH

If the Oracle client path is not at the front of the entire system path statement, select Start | Control Panel | System | Advanced tab | Environment Variables | from the System Variable list, click PATH | click EDIT and move the Oracle path to the front of the entire entry followed by a semi colon.

Below is an example of the PATH:

C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;D:\C:\WINDOWS\system32;C:\WINDOWS;

2. Ensure you can Tnsping (SQL*Net) or Tnsping80 (Net8) your instance. This will validate your connection to the database. From the DOS prompt type: Tnsping <service_name> or Tnsping80 <service_name>

For example:

TNSPING TESTDB
or
TNSPING80 TESTDB

This should return OK in milliseconds.

If you are unable to TNSPING the database, it might indicate that the Database Server is not up and running. Please check with the Database Administrator (DBA) for more information.

3. From the Windows Explorer, search for ORA*.dll in the %Oracle_Home%\bin directory, i.e. C:\Oracle\bin directory. Toad requires an Oracle client to connect to the database, this is a good indication if you have an Oracle client installed. You should find the following dll based on your Oracle client version:

Oracle Version Name of DLL
----------------------------------------
Oracle 7 Ora7x.dll
Oracle 8 Ora8xx.dll
Oracle 8i Oraclient8.dll
Oracle 9i Oraclient9.dll
Oracle 10G Oraclient10.dll

If you have multiple dlls in the same folder, rename any that you do not wish to use, as TOAD will use the first one it finds.

For example, if you have ORA806.dll and ORACLIENT8.dll in the same bin directory, it will use ORA806.dll. So you would rename ORA806.dll to ORA806.old to ensure we use the ORACLIENT8.dll.

4. Check the registry via Start | Run | type in Regedit, and look under HKEY_LOCAL_MACHINE | SOFTWARE | ORACLE | ALL_HOMES:

What is the HOME_COUNTER value? If it is more than 1 you will have multiple Oracle homes such as ID0, ID1, ID2, etc up to the HOME_COUNTER value minus one. Simply ensure the LAST_HOME value is the correct ID value which points to the path of the current Oracle_Home. In other words, if ID1 has a path to the C:\Orawin95 on the computer, then the LAST_HOME should have the value 1 since Toad looks to LAST_HOME for the Oracle application extensions.

Example of Registry path where Oracle home resides: (3 Oracle Home install)
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME2

NOTE: For Oracle 10g client, a separate entry is created that does not contain HOME value in the Registry. For example:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1

The computer should recognize the 10g client as the default Oracle client to connect to a particular database. If you would like to use a different Oracle client, refer to Step 1 above to change the PATH, moving the desired Oracle clients path to the front or refer to the Toad 7.6 or above, Step 1a below to used the Toad Installed Client drop down list.

5. Add the following oci dll command to the Toad Desktop icon Properties by right clicking the icon | select Properties | Shortcut Tab and enter the following after the executable statement in the Target Command line.

For example the Target path will look similar to the following:

C:\Program Files\Quest Software\Toad\Toad.exe ocidll=C:\Oracle\Bin\Oraclient9.dll

Refer to the list in point 2 above to determine which client dll to use.

6. Search for the tnsnames.ora file, if tnsnames.ora file is being used as a method of connection. Check that this TNSNAMES.ora file is valid and located in the correct folder. By default (unless specified using the TNS_ADMIN variable), it should be in your %ORACLE_HOME%\Network\Admin folder. Open the tnsnames.ora file via Notepad or a text editor application and verify that all entries are correct such as the HOST, SID/SERVICE_NAME, PORT number information.

If TNS_ADMIN has been set under the registry or as an environment variable, then make sure that the TNSNAMES.ORA file is in the directory specified for TNS_ADMIN. If a TNS_ADMIN environment variable does not exist, add a new entry and enter the correct path.


7. 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 = PROD.QUEST.CORP exists in sqlnet.ora, then make sure PROD.QUEST.CORP is add to aliases to get

JLIN10G.PROD.QUEST.CORP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ALV106750)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlin10g)
)
)


8. If the above suggestions fails to resolve the connection, the Oracle client or SQL*Net components may need to be reinstalled.


Additional troubleshooting suggestions for TOAD FOR ORACLE 7.6 AND ABOVE:

1. From the Toad Server Login window | in the Installed Clients section:

a. Click the drop down 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 Toad 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 SQLNET Editor | verify that the information is correct. Check with the DBA (Database Administrator) for more information.

d. Click on the TNSnames Editor | verify that the tnsnames entries are correct for the desired database.


ADDITIONAL HELP:

Verify hardware and software requirements. Keep in mind that for any version before 11.6, Toad does not support 64bit Oracle Clients.

Refer to the Connection Assistant for assistance on resolving some of the most common errors. This utility contains screenshots to assist with the connection troubleshooting for the following errors:

ORA-12154: TNS: could not resolve service name <ora12154.asp>
ORA-12505: TNS: listener could not resolve SID given in connect descriptor <ora12505.asp>
Error: 1.0 [Load Interface dlls failed] <error1.asp>
Initialization error: Could not locate OCI dll <OCIdll.asp>
http://support.quest.com/ConnectionAssistant/

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.

To find ifthe actual valid tnsnames.ora(that belongs to the actual ORACLE_HOME)containsthe correct entries (if TNS_ADMIN is not set):
- Start a command line prompt(in Windows click on Start | run and enter CMD), then
- Type SET to check the ORACLE_HOME - Variable
-If ORACLE_HOME isnt set, typeSET ORACLE_HOME=C:\oracle\product\10.2.0\client_1 (for example) andthen connect within the command line SQL-Plus (not the GUI) to your database.