-
タイトル
Common Oracle connection issues in Toad for Oracle 10.x & above. Frequently asked questions. -
説明
How to troubleshoot connection errors when logging-in to a supported Oracle database using either a 32-bit or 64-bit Oracle Client.
Common Oracle Connection errors:
- ORA-12154: TNS: could not resolve service name
- ORA-12505 TNS:listener could not resolve SID given in connect descriptor
- ORA-06413: Connection not opened
- 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
- You have no Oracle clients installed
- ORA-12557 TNS: protocol not loadable
Also, sometimes (erroneously) searched with these phrases:
Toad will not work.
Toad will not start.
Toad will not install.
Toad cannot connect.
Probable Oracle Net Admin error. -
対策
NOTES:
- For Toad for Oracle versions older than 10.x, please see Solution Article SOL7712.
- Any version prior to 11.6 is a 32-bit version. Versions 11.6 and higher are now available in 32-bit and 64-bit versions. To verify your Toad version and bit version, go to Toad’s Help Menu | Support Bundle.
- Ensure the “bit” of your Toad for Oracle and your Oracle Client matches or are the same. For example, use Toad 32-bit with Oracle Client 32-bit and use Toad 64-bit with Oracle Client 64-bit. It will not work if they are not the same.
- You can have both 32-bit and 64-bit Oracle Clients installed on your machine at the same time. If you have multiple Oracle Clients installed on your machine, you can choose which client to connect with via the “Connect Using:” drop-down field in the Toad connection window.
- 64-bit versions of the Windows OS will force 32-bit applications into the “C:\Program Files (x86)\” directory. Having Toad installed in the “Program Files (x86)” folder can cause connection issues when using an Oracle Client 10g. To work around this, install Toad outside of the “Program Files (x86)” folder i.e. C:\Quest Software\Toad for Oracle\). This is an Oracle Client 10g issue that is addressed with the Oracle Client 11g, which is an alternative to re-installing Toad into a different directory.
1. Check the Toad for Oracle Release Notes by going to the Toad for Oracle main page | “Release Notes & Guides” tab | “Toad for Oracle xx.x Release Notes” | go to the “System Requirements” section for the supported Oracle Client and Server versions.2. In order to install and run Toad for Oracle, make a connection, and do basic operations, you must have the following privileges.
* Read access to the Oracle Client folder.
* Read/write privileges on the Oracle Homes directories that you use for your connections.
* Be a Power User or Administrator.
Note: Other functionality in Toad may require additional privileges.3. 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; connection tests done using SQL Plus is only to ensure you are able to established a connections 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 installation.
4. Verify that the server can be pinged, and the database instance can be tnsping’ed.
a. Go to Windows Start | Run | type: cmd to open a Command Prompt window.
b. Type: PING | press return for result.
c. Type: TNSPING | press return key for result.5. Verify the Oracle Home's BIN directory is the first listed in the PATH Environmental Variable.
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” section (bottom portion), 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. If it is not there at all, you will need to add it. (Example: C:\app\\product\11.2.0\client_1\bin;)6. 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.
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 none, click the “New” button. Enter “TNS_ADMIN” as the Variable Name and “Variable Value” is the path to where your “tnsnames.ora” file is located.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 = 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").Example:
TOAD.ORACLE.CORP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1234)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORACLE11G)
)
)8. Verify that you using the correct and valid Oracle Home in Toad for Oracle. In the Toad login screen, under the “Installed Clients” section, click on the ellipses icon to open the Oracle Home Editor and determine the correct Oracle home is selected within Toad. What is selected in the “Connect Using:” drop-down field in the connection window is what Toad uses to connect. As a test, try connecting using other clients listed in the drop down too.
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".
-
追加情報
Otras soluciones sobre problemas de conexión (sólo disponibles en inglés de momento)
================================
SOL205157 - Video - How to make a database connection using Direct, TNS and LDAP in Toad for Oracle
SOL71545 - No valid Oracle Client found
SOL61710 - Error: "Can't initialize OCI. Error -1" when connecting to DB on a Windows 7 64-bit or Windows Server 2003 2008 64-bit OS environment.
SOL39689 - Getting "You have no Oracle clients installed" error message when connecting or launching / starting Toad for Oracle.
SOL12495 - Error connecting to database after installing on Windows 64-Bit OS: "ORA-12154 TNS:could not resolve the connect identifier specified"
or "ORA-06413:Connection not opened" or "Can't initialize OCI. Error -1"Frequently asked Questions:
================================SOL33534 - Does Toad for Oracle support a 64-bit Oracle Client? Are there any plans for a 64-bit client support?
SOL17336 - How to install and configure an Oracle Instant Client for use with Toad for Oracle. (Video)
SOL76454 - How to configure TNSNAMES.ORA for use with Oracle Instant Client and Toad. (Video)
SOL76459 - How to remove the Oracle Instant Client. (Video)