Practical Guide to Setting Up Oracle Connection

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=OID

These 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 specified

Here'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.ora

where 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 descriptor

If 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 descriptor

So, 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";
Reference

If 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.

Common Issues

If your Windows is 64-bit but you installed 32-bit Oracle client and wish to configure ODBC, run C:\Windows\SysWOW64\odbcad32. If instead you launched the default ODBC Data Source Admin tool, which is C:\Windows\System32\odbcad32, the configuration would not work. Process Monitor tracing would show incorrect DLLs being searched for.
Config files such as tnsnames.ora, sqlnet.ora on *nix (UNIX or Linux) must have correct line endings for the platform. There will be a problem if the files are transferred in binary mode from Windows. Check with cat -vet thefile to make sure lines end with $ but not ^M$. If needed, run tr -d '\015' < thefile > tmp; mv tmp thefile (or dos2unix thefile) to correct it. On Windows, the files with UNIX type line endings won't cause a problem but it's inconvenient to edit them with Notepad.
If there's a firewall between the Oracle server and client, obviously the firewall admin has to allow the client to go to the server IP on port 1521 (or whatever port the listener listens on), and if SQL*Net Inspection (which could be named differently) is enabled on the firewall, most likely it must be disabled. If the database is 11g or higher RAC (cluster database), the firewall has to open for 3 IPs behind the SCAN IP plus each VIP on each of the cluster nodes. For example, a 2-node cluster has to open for the 5 IP's highlighted below:
```
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
Role: default
LDAP Server: oidsvr.example.com:389
Context: cn=OracleContext,dc=example,dc=com

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

Host: ldap://oidsvr.example.com
Port: 1521/cn=OracleContext,dc=example,dc=com
SID: the short connection identifier you want to use, e.g. orcl

Appendix 3: Change JDBC URL for Weblogic:

Go to Services -> Data Sources -> for each specific data source -> Configuration -> Connection Pool

Change URL from
jdbc:oracle:thin://@hardcoded_database_hostname:port/SID
to
jdbc:oracle:thin:@ldap://oidsvr.example.com:389/orcl,cn=OracleContext,dc=example,dc=com