What is the bulletproof way to specify Easy Connect string for Oracle?
Oracle describes the Easy Connect line 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 users often have the problem that this format does not work on the client's site and for convenience, we refer to TNSNAMES.ORA and the alias, which makes Oracle Instant Client a little less "instantaneous".
But, in essence, the connection string is centrally managed, so this alias must be the same on all computers in the system that connect to the database, which is a maintenance issue.
Errors are caused by our ambiguity about all the bits and parts of the Oracle Naming system, SID values, service name, hostnames, global service names, naming methods, local database and global naming options, SQLNET and TNS listener configurations ...
Since I haven't been able to dig up one, consistent and comprehensive link on this topic, I thought I was passing this question on to the community.
a source to share
If you are installing client application software into existing client environments, then perhaps the easiest task is to convert their SQL * Net configuration. I would do this:
- migrating to a full Oracle client workstation where SQL * Net is configured with the correct tnsnames.ora and sqlnet.ora files.
- run the tnsping utility with the database of interest, which will produce output like this:
$ tnsping testdb
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production 12-MAY-2009 08:28:11
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Parameter files used: / home / oracle / oracle / product / 11.1.0 / network / admin / sqlnet.ora
TNSNAMES adapter used to resolve alias Attempt to connect (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = testhost (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb.world))) OK (0ms)
This will tell you the hostname and how the database listener is expected to wait for a connection (port and SERVICE_NAME versus SID). You now have information for specifying the Easy Connect line. By the way, the tnsping utility is available for Window clients as well.
a source to share