Oracle Database - How to retrieve the connect descriptor parameters? (host, port, service name and SID)

About

When you need to install, configure a new software, you need to give a connect descriptor in order to connect to a database. The connect descriptor contains parameters such as hostname, port, service name and/or sid of the database. Below is an how-to on how to retrieve this data.

How-to

When you don't know the host, the port and the service, you can ask the listener status with this command :

lsnrctl status

Then in the response, you will see all informations.

C:\Documents and Settings\Nicolas>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-JUN-2009 14:12:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ngerard)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Production
Start Date                09-JUN-2009 09:50:02
Uptime                    1 days 4 hr. 22 min. 58 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listener Trace File       C:\oracle\product\10.2.0\db_1\network\trace\listener.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NGERARD)(PORT=1521)))
Services Summary...
Service "orcl.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Host and port

The host NGERARD and the port 1521 can be found on this line :

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NGERARD)(PORT=1521)))

Service Name

The service name “orcl.localdomain” can be found on this line :

Service "orcl.localdomain" has 1 instance(s).

SID

The Oracle Database - System Identifier (SID) “orcl” can be found on this line :

Instance "orcl", status READY, has 1 handler(s) for this service...

You may have two different names between the service name and the SID