Oracle Database - Connect Descriptor (TNS Connect String)

About

The Connect Descriptor (known also as TNS Connect String) is a type of connect identifier. It defines the parameters that need the Oracle Net Service to connect to a database service :

the protocol
the host
the port
the SID
the service name
the database connection type

You can gives a connect descriptor:

manually The Connect descriptor must be given in 1 line or you have to use single quote. See here for more information on the syntax: connect identifier syntax.
or through different mechanisms named naming method.

Articles Related

Syntax

A connect descriptor is comprised of one or more protocol addresses of the listener and the connect information for the destination service.

(DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) 
  (CONNECT_DATA= 
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales))
     (SERVER=shared)))
)

where

the ADDRESS section contains the following:
- PROTOCOL parameter, which identifies the listener protocol address. The protocol is tcp for TCP/IP.
- HOST parameter, which identifies the host name. The host is sales-server.
- PORT parameter, which identifies the port. The port is 1521, the default port number.
The CONNECT_DATA section contains the following:
- SID parameter, which identifies the SID of the Oracle database. The SID is sales.
- SERVICE_NAME parameter, which identifies the service. The destination service name is a database service named sales.us.example.com.
- INSTANCE_NAME parameter, which identifies the database instance. The instance name is optional.
- SERVER parameter which identifies the service handler type where the parameter value can be
  - shared (default) to use the dispatcher of a shared server architecture. However, the client will use a dedicated server if no dispatchers are available,
  - dedicated to use a dedicated server architecture,
  - pooled to use the database resident connection pooling. If database resident connection pooling is not enabled on the server, then the client request is rejected, and the user receives an error message.

Database connection type

If shared server is configured in the initialization parameter file, you can select Dedicated Server to force the listener to spawn a dedicated server, bypassing shared server configuration. If shared server is configured in the initialization parameter file and you want to guarantee the connection always uses shared server, select Shared Server.

Enhancing Service Accessibility using Multiple Listeners

For some configurations, such as Oracle Real Application Clusters, multiple listeners on multiple nodes can be configured to handle client connection requests for the same database service. In the following example, sales.us.example.com can connect using listeners on either sales1-server or sales2-server.

(DESCRIPTION=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)))

A multiple-listener configuration also enables you to leverage the failover and load balancing features, either individually or in combination with each other. The following topics describe the features:

Connect-time Failover
Transparent Application Failover
Client Load Balancing
Connection Load Balancing

More … Enhancing Service Accessibility using Multiple Listeners

How to get the TNS connect string ?

You can get the connect string by performing a tnsping on the tnsname.

C:\Documents and Settings\Nicolas>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-JUN-2009 11:09:18

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ngerard)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED
) (SID = orcl)))
OK (80 msec)

C:\Documents and Settings\Nicolas>tnsping qsdb > c:\tns.txt

You can then retrieve the TNS Connect Descriptor from the file tns.txt of from the screen. The TNS Connect string is :

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ngerard)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = QSDB)))