What is the bullet-proof way to specify the Easy Connect string for Oracle?

Question

Oracle describes the Easy Connect string as

username@[//]host[:port][/service_name][:server][/instance_name]

(from http://www.oracle.com/technology/products/oraclenet/files/OracleNetServices_NetEasyConnect.pdf)

However, some of our service folks frequently have the issue that this format is not working on a customer site and for ease, we resort to TNSNAMES.ORA and an alias, which makes the Oracle Instant Client a little less "instant".

But, as matters are, the connection string is centrally managed and so this alias needs to be the same on all machines in the system that connect to the database, which is a maintenance hassle.

The failures are caused by our cluelessness regarding all bits and pieces of the Oracle Naming system, the meaning of "SID", "service name", "host names", "global services names", naming methods, database local and global naming settings, SQLNET and TNS listener configurations.

Since I was unable to dig up a single, consistent and comprehensive reference on the topic, I thought I'd pass that question to the community.

Answer 1

If you are installing client application software into existing customer environments then perhaps the easiest thing to do is to reverse-engineer their SQL*Net configuration. I would do this by:

• getting onto a workstation with the full Oracle client that had SQL*Net configured with the proper tnsnames.ora and sqlnet.ora files
• running the tnsping utility against the database of interest, which will produce output similar to this:

$ tnsping testdb

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 12-MAY-2009 08:28:11

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files: /home/oracle/oracle/product/11.1.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = testhost (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME=testdb.world))) OK (0 msec)

This will tell you the host name and how the database listener is expecting connections (port and SERVICE_NAME vs. SID). You now have the information to specify the Easy Connect string. By the way, the tnsping utility is available on Window clients as well.