How to obtain the list of Oracle's SIDs

Question

I have the host, port, user id and password but missing SID for connecting to Oracle DBMS. How can I find the list of SIDs on that server?

Answer 1

The short answer is that you need access to the host OS:

For Unix, ps -ef|grep pmon will show you one or more processes with names like ora_pmon_xxxx, and xxxx is the instance name.

In Windows I guess there is a similar signature in the task list.

In practice, this information is usually given to you by whoever administers the database when your connecting account is created.

Answer 2

A better way is, if you have access to the host machine and the Oracle install is to use the command: lsnrctl status. This works on both Unix, Linux, and Windows machines. The status command will show you all the listeners (and their associated SIDs).

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-JUN-2009 16:16:34
Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date                13-JUN-2009 12:04:14
Uptime                    2 days 4 hr. 12 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oracle\XE\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File         C:\oracle\XE\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ThinkpadT61)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
   Instance "xe", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
   Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
   Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

In the above example you can connect to the XE database using the Conect Strings XEXDB, XE_XPT or XE.

Answer 3

The question comes down to : which ORACLE_SID's or services are supported by the listener that is running on host X port Y. Depending on how secure this listener is configured you can see this using the lsnrctl command from a client that has lsnrctl installed. To be able to do this you do need an oracle server installation on that client. When you have that you can issue

lsnrctl
set current_listener (description=(address=(host=X)(port=Y)(protocol=tcp)))
status

The default setting of the 10g listener fill cause the following result: TNS-01189: The listener could not authenticate the user

This is because from 10g oracle default has Security ON: Local OS Authentication meaning that only the local OS user that started the listener can issue lsnrctl commands to the listener. The listener will refuse to answer any other user.

Answer 4

Another option to consider is the file /etc/oratab on Unix or its equivilent on Windows, which I think is a registry hive.

The oratab should list all SIDs on a host, whether currently running or not.