By Laurent Schneider
EZCONNECT is the easy connect protocol, available in 10g, whenever
you want to connect to a database without tnsnames and without ldap.
s$ grep -iw directory_path $TNS_ADMIN/sqlnet.ora
names.directory_path=EZCONNECT
$ sqlplus scott/tiger@//srv01:1521/db01
Connect to server srv01 on port 1521 for service
db01
HOSTNAME was the old-fashion way to connect to a database, where
hostname = sid and port = 1521. In this regard EZCONNECT is just an
extension of the hostname method.
sqlplus scott/tiger@db01
Connect to server db01 on port 1521 for service
db01
There is a behavior change between 10g and 11g. In 10g, the default
service name defaulted to the DNS alias used to connect. In 11g, the
default is null.
$ nslookup db01
Server: ns001.example.com
Address: 198.0.0.30
Name: srv01.example.com
Address: 198.0.0.60
Aliases: db01.example.com
$ nslookup db02
Server: ns001.example.com
Address: 198.0.0.30
Name: srv01.example.com
Address: 198.0.0.60
Aliases: db02.example.com
Both DB01 and DB02 DNS aliases point to the same server. First, let�s
try it with 10g
$ sqlplus -L scott/tiger@db01.example.com
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:46:53 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB01.EXAMPLE.COM
SQL> quit
$ sqlplus -L scott/tiger@db02.example.com
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:47:33 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB02.EXAMPLE.COM
This looks o.k. Now we will try it with 11g sqlplus
$ sqlplus -L scott/tiger@db01.example.com
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 7 15:50:27 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
It no longer works. Period. This is documented as Problem
556996.1 in Metalink. A 10g tnsping will reveal:
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 -
Production on 07-FEB-2011 15:52:34
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=
SERVICE_NAME=db01.example.com))(ADDRESS= (PROTOCOL=TCP)(HOST=198.0.0.60)
(PORT=1521)))
OK (80 msec)
In 10g the service_name is the connection dns alias
used. In contrast, the 11g tnsping service name is
null
:
$ tnsping db01.example.com:1521
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 -
Production on 07-FEB-2011 15:56:55
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=
(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)
The tnsping works, but the service_name is empty. How can this be
fixed?
First, you specify the SID in easy connect (Yes, this really is easy!)
$ tnsping db01.example.com:1521/db01.example.com
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 -
Production on 07-FEB-2011 15:59:10
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=
(
SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)
(PORT=1521)))
OK (10 msec)
Then you use 10g, or 10g behavior in 11g with patch 9271246
(available only on a limited number of platforms, os
and db versions), and you specify a default service for your
listener:
$ vi listener.ora
DEFAULT_SERVER_LISTENER=DB01
$ lsnrctl reload
$ sqlplus -L scott/tiger@db01
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
This is a bit confusing if you are servicing more than one
database per listener, as all dns aliases will default to the same
database. So I would not recommend using a default service name if
there is more than one service name.
|
|
 |
|
Get the Complete
Oracle Tuning Details
The landmark book
"Oracle
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
|
|
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
| |
| |
Copyright ©
1996 -2017 by Burleson. All rights reserved.
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Corporation.
Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks
|
|
