amar on web

• home
• On Oracle database
• On Oracle Forms&Reports
• On Oracle iAS
• On linux
• On PMP certification
• My blog
• My Profile
• family link

Simplified database connection with "Easy connect naming" method (EZCONNECT)

Oracle introduced an "Easy connect naming" method in release 10g that allows Oracle clients to connect to the database without the need of TNSNAMES.ORA file. It is one less failure point for applications that rely on TNSNAMES.ORA file for connection. I have personally come across connection issues that took lot of resolution time only to find out that TNSNAMES.ORA was not setup properly. That apart, some organizations do have controlled promotes for TNSNAMES.ORA and the required formalities may take time to rectify the cause. With this option, DBA has one less headache to maintain TNSNAMES.ORA file(s) for all the databases in the organization.

Another important advantage is that this feature is unaffected by changes in the registry. For instance changing the ORACLE_HOME could point the TNS_ADMIN variable to a different folder where the needed connection settings are not defined.

The easy connect string now supports the hostname and port number in its syntax.

[oracle@amar scripts]$ sqlplus test/test@amar.localdomain.com:1522/am02

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 25 11:36:53 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
AM02:TEST>

You need the additional information of the server name and the port number. The ezconnect string contains the following:-

username/password@hostname:port-number/dbname

- username/password is your login id
- hostname is the server name where the listener is running.
- port-number is the listener port that provides connection, not required if it is default port number 1521.
- dbname is the database you are trying to connect to.
Here is an example to differentiate between the normal and easy connect methods. I am trying this on windows workstation.

D:\amar\scripts>set tns
TNS_ADMIN=d:\amar\scripts

I have set the TNS_ADMIN environment variable to point to a local directory where the tnsnames.ora file is placed.

D:\amar\scripts>sqlplus amar/amar@am02

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 13 12:03:50 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
AM02:AMAR> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL*Plus connects using the TNSNAMES.ORA file. Now I will rename the file and try again.

D:\amar\scripts>ren tnsnames.ora tnsnames.ora.org
D:\amar\scripts>sqlplus amar/amar@am02

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 13 12:04:08 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: ^c

SQL*Plus fails in normal connection method, easy connection goes through.

D:\amar\scripts>sqlplus amar/amar@amserver:1522/am02

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 13 12:06:34 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
AM02:AMAR>

The password can also be specified separately, this additionally requires that the string after the '@' symbol be put in quotes (single or double).

AM02:AMAR> conn amar@"amserver:1522/am02"
Enter password:
Connected.

Not specifying quotes results in error.

AM02:AMAR> conn amar@amserver:1522/am02
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Instead of the hostname, IP Address can also be used.

D:\>sqlplus amar@'192.168.235.128:1522/am02'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 13 14:40:22 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

When invoking SQL*Plus from Windows command prompt, the double quotes failed to work when password is not provided in the connect string. The sinqle quote worked instead. Double Quotes however work once you are insided SQL*Plus.

D:\>sqlplus amar@"192.168.235.128:1522/am02"
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 13 14:41:51 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name: amar@"192.168.235.128:1522/am02"
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

When connecting from a Linux/Unix workstation, specify '\' symbol for the quotes, like below.

[oracle@amar scripts]$ sqlplus amar@\'amar.localdomain.com:1522/am02\'

The TNSPING command also supports easy connection naming, I tried the below on my Linux prompt.

[oracle@amar scripts]$ tnsping amar.localdomain.com:1522/am02
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 25-JAN-2008 12:59:03
Copyright (c) 1997, 2007, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/scripts/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=am02))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.235.128)
(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.235.128)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.235.128)(PORT=1522)))

OK (20 msec)

The hostname could also be a URL, this requires '//' to be prefixed for hostname. I could not test this option though. Here is an example.

connect user/pwd@//host:7779/service_name

This feature is supported from release 10g client. I could use easy connect to log into release 9i database and work on it. This feature is also supported on Oracle Instant client that does not rely on any registry settings or environment files for giving you a connection option to the database. I did the above testing using Instant client 10g also. This feature requires TCP/IP support on both the client and the server. Oracle also recommends that the SQLNET.ORA file have the EZCONNECT method mention in the search option.

names.directory_path = (EZCONNECT, TNSNAMES, HOSTNAME)

As good as the feature looks, I quickly noticed some disadvantages. Yes, the hostname and port should be known/stored along with the login details. It may be possible that the database name is different from the alias provided in TNSNAMES.ORA. For instance, the database name is HR-PROD and the TNSNAMES.ORA provides an alias of HR-LIVE on the network. For easy connect, the database service name is required as it will not refer the TNSNAMES.ORA file.

Oracle notes also state that this feature cannot be used with connection pooling, external procedure calls and Heterogenous services. This is a big disadvantage as it defeats the purpose of using easy connect as a sole option on the network and completely eliminate TNSNAMES.ORA file. I have raised a product enhancement request with Oracle for this (Bug 6817279), this will be considered for a future release.

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 18-feb-2008. Last updated on 18-feb-2008.
please forward all queries to amar@amar-padhi.com