There're two ways to set up a connection to an Oracle database: using a short connect identifier (some call it connection string), and fully specifying the connection details. In most cases, the preferred way is to use a short connect identifier.
A connect identifier is simply an alphanumeric string, such as orcl, prod1. Most Oracle clients (i.e. tools or applications) can use the short connect identifier with sqlnet.ora and either tnsnames.ora or ldap.ora. Using tnsnames.ora is quite common. This note is more about using ldap.ora, on the condition that a company-wide Oracle OID i.e. LDAP is used.
sqlnet.ora: NAMES.DIRECTORY_PATH=(ldap,tnsnames,ezconnect) ldap.ora: DIRECTORY_SERVERS=oidsvr.example.com:389:636 DEFAULT_ADMIN_CONTEXT="dc=example,dc=com" DIRECTORY_SERVER_TYPE=OIDThese two files should be under %oracle_home%\network\admin on Windows ($ORACLE_HOME/network/admin on Linux or UNIX), unless %tns_admin% (or $TNS_ADMIN) environment variable is set, which points to the directory where the two files are checked first. (The %string% notation here, taken from an environment variable, points to a Windows folder. For example, %oracle_home% could be C:\oracle\product\11.2.0\client_1 or D:\app\oracle\product\12.1.0\db, etc.)
When the Oracle client needs the detailed connection information from a connect identifier, it first checks the NAMES.DIRECTORY_PATH directive in sqlnet.ora. Since the example given above has ldap listed first, it reads the ldap.ora file, where the client is instructed to contact Oracle LDAP server (a.k.a OID server) oidsvr.example.com at port 389 for detailed connection info. (If the client insists on the secure LDAPS connection, it knows the port to connect to is 636.)
If LDAP does not have that entry, it uses the second method as listed in NAMES.DIRECTORY_PATH, which is tnsnames. So it reads the file tnsnames.ora in the same directory. If the entry is still not found, or tnsnames.ora does not exist at all, it uses the third method, ezconnect, which we won't cover here.
Because NAMES.DIRECTORY_PATH is gone through sequentially, if you want to set up a private connect identifier that happens to already exist in LDAP, you can place tnsnames in front of ldap.
For most developers, the biggest problem with setting up Oracle connection is due to the fact that the two configuration files are not consulted at all, typically shown as error
ORA-12154: TNS:could not resolve the connect identifier specifiedHere's a systematic troubleshooting procedure. If you're on Windows, go to command prompt (or called command console or mistakenly DOS prompt), and use tnsping command. Take connect identifier orcl as an example:
C:\>tnsping orcl TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 10-MAY-2010 15:19:14 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsvr.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbsvc.example.com)))) OK (80 msec)Oracle 10g and up client will tell you where it looks for the config file sqlnet.ora under "Used parameter files:". If it's not the directory you expected it to be, you know where to go to fix it. If the connection is resolved successfully, you're informed of the method ("adapter") tnsping uses to resolve it.
Unfortunately, the location where tnsping finds the config files may not be where your application looks for the files. Unless you're intimately familiar with the configuration of the app, you can use a generic method to find where the app looks for the Oracle config files.
On Windows, download Process Monitor. Run it, stop Capture, go to Filter, specify that (e.g.) Image Path contains sqlnet.ora, or Process Name is sqldeveloper.exe, and start Capture. Then launch your app (SQL Developer in this case) and make an Oracle connection. You'll see in the window of Process Monitor where SQL Developer looks for sqlnet.ora first, and second, third,... You can search the string with Control-F, or save it to a text type file (csv e.g.) and search in the file. Alternatively, use a command line tool such as NtTrace, and in the console, run nttrace tnsping connidentifier | findstr /i sqlnet.ora.
On Linux, you can do the same with command
strace -f your_app 2>&1 | grep sqlnet.orawhere strace may need to be installed with yum install strace (assume Red Hat Linux). Change strace to truss on most flavors of UNIX.
If tnsping successfully fetches detailed connection info (from OID or tnsnames.ora) but it can't ping the database (i.e. the last line OK does not show up), make sure the hostname(s) and port(s) are correct. All Oracle databases should at least be pingable unless the system or network admins explicitly blocked ICMP packets.
Once tnsping check works, we can go on to sqlplus check. Just because you get tnsping to work doesn't mean sqlplus or your app works, because tnsping does not care about the CONNECT_DATA part of the detailed connection info. Suppose your CONNECT_DATA has SERVICE_NAME=dbsvc instead of SERVICE_NAME=dbsvc.example.com, tnsping still says OK but sqlplus throws error ORA-12514 (note it's not ORA-12154 as seen above)
ORA-12514: TNS:listener does not currently know of service requested in connect descriptorIf you used the deprecated SID attribute instead of Oracle-recommended SERVICE_NAME and the SID is wrong, the error would be
ORA-12505: TNS:listener does not currently know of SID given in connect descriptorSo, always pay attention to the exact error.
If your application uses JDBC and can use OCI driver (type 2 driver), the JDBC URL is simply
db_url = "jdbc:oracle:oci:@orcl";where the example connection orcl can be resolved by either OID or tnsnames.ora.
Applications using JDBC thin driver require special configuration. Most developers settle with this rudimentary setup
db_url = "jdbc:oracle:thin:@db_host_name:1521:database_sid";In this case, if the DBAs change the host on which the Oracle instance runs for administrative or failure recovery reasons, the URL must be changed. Using SID instead of service name disables the capability of cluster-wide load balancing if the database is RAC (even if it's not RAC, using SID causes loss of service-specific performance statistics). Instead, a simple connect identifier as defined in the local or centralized tnsnames.ora (don't forget -Doracle.net.tns_admin=path_to_tnsname_file), or defined in Oracle LDAP is recommended.
db_url = "jdbc:oracle:thin:@orcl";
db_url = "jdbc:oracle:thin:@ldap://oidsvr.example.com:389/orcl,cn=OracleContext,dc=example,dc=com"; ReferenceIf your application uses very old Oracle client such as Oracle8 (not Oracle8i or later), or your Oracle client is some old 10g Instant Client which has buggy LDAP support, then LDAP name resolution is not an option. In that case, you can only use a tnsnames.ora file and add or delete entries when needed.
C:\temp>nslookup orcl-scan Server: dnsserver.example.com Address: dnsIP Name: orcl-scan.example.com Addresses: 10.113.71.106 10.113.71.105 10.113.71.104 SQL> select value from gv$parameter where name = 'local_listener'; VALUE ------------------------------------------------------------------ (ADDRESS=(PROTOCOL=TCP)(HOST=10.113.71.102)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.113.71.103)(PORT=1521))
Appendix 1: Configure SQL Developer to use Oracle LDAP
Choose these options when creating a new database connection:
Connection Type: LDAP
LDAP Server: oidsvr.example.com:389
Click Load, which populates the DB Service drop-down. Choose the short connection identifier you want to use (e.g. orcl) in the DB Service drop-down.
Appendix 2: Configure Aqua Studio to use Oracle LDAP
On the Register Server screen, set
SID: the short connection identifier you want to use, e.g. orcl
Appendix 3: Change JDBC URL for Weblogic:
Login as admin at http://weblogicserver:7001/console/
Go to Services -> Data Sources -> for each specific data source -> Configuration -> Connection Pool
Change URL from
To my OraNotes Page