The relationship between listener.ora File and tnsnames.ora
The contents of the listener.ora file are as follows:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = d10063) (PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ oracle\ ora92)
(PROGRAM = extproc)
(GLOBAL_DBNAME = gsid)
(ORACLE_HOME = D:\ oracle\ ora92)
(SID_NAME = paul)
(GLOBAL_DBNAME = paul)
(ORACLE_HOME = D:\ oracle\ ora92)
(SID_NAME = paul)
)
)
The tnsnames.ora file is as follows:
PAUL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.163) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = paul)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.163) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gsid)
)
)
SQL > show parameter service_names
NAME TYPE VALUE
Service_names string gsid, paul,paultest
From the above, you can know that the listener provides static and dynamic registration service names.
The tnsnames.ora file connects to the client based on the service name.
And primary, these are connection strings.
C:\ Documents and Settings\ Paul Yi > lsnrctl status
LSNRCTL for 32-bit Windows: Version 9.2.0.4.0-Production on 18-DEC-2007 09:47:
twenty-one
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=d10063) (PORT=1521)
STATUS of the LISTENER
-
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.4.0-Produc
Tion
Start Date 18-DEC-2007 09:40:34
Uptime 0 days 0 hr. 6 min. 46 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File D:\ oracle\ ora92\ network\ admin\ listener.ora
Listener Log File D:\ oracle\ ora92\ network\ log\ listener1.log
Listening Endpoints Summary...
(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=d10063.avocado.local) (PORT=1521))
(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (PIPENAME=\\.\ pipe\ EXTPROC0ipc))
Services Summary...
Service "gsid" has 2 instance (s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...
Instance "paul", status READY, has 1 handler (s) for this service...
Service "paul" has 1 instance (s).
Instance "paul", status READY, has 1 handler (s) for this service...
Service "paultest" has 1 instance (s).
Instance "paul", status READY, has 1 handler (s) for this service...
The command completed successfully
As can be seen from lsnrctl status,
Initialization parameter settings: instance_name=paul service_names=paul,gsid, paultest
Therefore, dynamic registration will also provide three services: paul and gsid, paultest
C:\ Documents and Settings\ Paul Yi > tnsping primary
TNS Ping Utility for 32-bit Windows: Version 9.2.0.4.0-Production on 18-DEC-20
07 09:50:59
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
D:\ oracle\ ora92\ network\ admin\ sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP))
(HOST = 192.168.1.163) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = gsid)
OK (0 msec)
So to sum up: instance_name has a unique name, while service_name can have multiple for tnsnames.ora client connections
The connection string can also be named at will.
For the client, it does not care about the name of the database and the name of the instance, it only needs to know the name of the service provided by the database, which may or may not be the same as the name of the instance.
