Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How to get SID, Service Name and Port for Oracle database?

Tags:

oracle

connection-string

database-connection

tnsping

I have oracle database running on address xx.xx.xx.xx I know login, password and initial catalog

I am trying to set up new connection in Oracle SQL Developer so I can access this database to take a look at it. In order to do that, I need to know SID or Service Name.

Long time ago I would run TNSPing.exe to get service name, but this program is not available any more. Oracle is not distributing it.

Is there a way to access this info somewhere?

Thanks

like image 722
bobetko Avatar asked Jun 07 '18 22:06

bobetko

People also ask

How to find Sid in Oracle?

How to find SID in Oracle | In Oracle we can find SID by using SQL * Plus just by typing a simple select query or we can use “key registry” (only in Windows). The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

What is the difference between $Oracle_Sid and service_name?

The SID is the same as the $ORACLE_SID environment variable in the sense that the SID is the system is for a unique instance on the server. On the other hand, the SERVICE_NAME is used to register an instance with the listener. In most all cases, Oracle recommends that the value of the service_name be...

How to connect to Oracle thin driver without Sid?

There are two drivers to select i.e., Oracle thin driver and other is other driver. Select other drivers and enter service name in database column. Now you can connect directly using service name without SID. More importantly this allows you to fully specify the connection URL unlike the Thin Driver.

Can instance_name/Sid and DB_name be different?

Hi.. The instance_name/sid and the db_name can be different, but for convenience oracle recommends that sid and the db name should be same.I am using a test database where the instance name and the db name is different, and its working properly till now. 1.

2 Answers

If you have access to the server, then you can determine how many processes the listener is running, what IP addresses and ports it is listening to. An example for Linux.

[oracle@krw-ssps-db-02 ~]$ ps -ef|grep tnslsnr
oracle    6090     1  0  2015 ?        01:11:39 /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle    6712  6653  0 05:59 pts/1    00:00:00 grep tnslsnr
oracle   17064     1  0  2017 ?        00:19:15 /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER_SSPS -inherit

If the variable $TNS_ADMIN is defined, it will override $ORACLE_HOME/network/admin

[oracle@krw-ssps-db-02 ~]$ env |grep ORA
ORACLE_UNQNAME=ssps_db2
ORACLE_SID=ssps
ORACLE_BASE=/home/oracle/database
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
[oracle@krw-ssps-db-02 ~]$ env |grep TNS
TNS_ADMIN=/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@krw-ssps-db-02 ~]$

For a start you can make a SQL query

select * from v$listener_network;

LOCAL LISTENER  (ADDRESS=(PROTOCOL=TCP)(HOST=krw-ssps-db-02)(PORT=1521))
SERVICE NAME    ssps_db2
SERVICE NAME    ssps_db2


[oracle@krw-ssps-db-02 ~]$ ifconfig -a
eth0      Link encap:Ethernet  HWaddr 00:50:56:8F:78:59
          inet addr:10.*.*.244  Bcast:10.*.*.255  Mask:255.255.255.192
          ...

eth0:1    Link encap:Ethernet  HWaddr 00:50:56:8F:78:59
          inet addr:10.*.*.242  Bcast:10.*.*.255  Mask:255.255.255.192
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          .....


[oracle@krw-ssps-db-02 ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2018 06:00:48

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                04-DEC-2015 08:48:38
Uptime                    419 days 18 hr. 44 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/database/diag/tnslsnr/krw-ssps-db-02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.*.*.244)(PORT=1521)))
Services Summary...
Service "ssps" has 1 instance(s).
  Instance "ssps", status UNKNOWN, has 1 handler(s) for this service...
Service "sspsXDB" has 1 instance(s).
  Instance "ssps", status READY, has 1 handler(s) for this service...
Service "ssps_db2" has 1 instance(s).
  Instance "ssps", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@krw-ssps-db-02 ~]$ lsnrctl status LISTENER_SSPS

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2018 06:01:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SSPS
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-DEC-2017 06:37:30
Uptime                    178 days 23 hr. 23 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/database/diag/tnslsnr/krw-ssps-db-02/listener_ssps/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.*.*.242)(PORT=1521)))
Services Summary...
Service "asut" has 1 instance(s).
  Instance "asut", status UNKNOWN, has 1 handler(s) for this service...
Service "ssps" has 1 instance(s).
  Instance "ssps", status UNKNOWN, has 1 handler(s) for this service...
Service "tch7" has 1 instance(s).
  Instance "tch7", status UNKNOWN, has 1 handler(s) for this service...
Service "tch8" has 1 instance(s).
  Instance "tch8", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@krw-ssps-db-02 ~]$ lsnrctl services LISTENER_SSPS

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2018 06:02:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1525)))
Services Summary...
Service "asut" has 1 instance(s).
  Instance "asut", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:102970 refused:0
         LOCAL SERVER
Service "ssps" has 1 instance(s).
  Instance "ssps", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:222841 refused:0
         LOCAL SERVER
Service "tch7" has 1 instance(s).
  Instance "tch7", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:107112 refused:0
         LOCAL SERVER
Service "tch8" has 1 instance(s).
  Instance "tch8", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:102970 refused:0
         LOCAL SERVER
The command completed successfully
[oracle@krw-ssps-db-02 ~]$ lsnrctl services LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2018 06:04:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "ssps" has 1 instance(s).
  Instance "ssps", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:47 refused:8
         LOCAL SERVER
Service "sspsXDB" has 1 instance(s).
  Instance "ssps", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: krw-ssps-db-02, pid: 16980>
         (ADDRESS=(PROTOCOL=tcp)(HOST=krw-ssps-db-02)(PORT=53933))
Service "ssps_db2" has 1 instance(s).
  Instance "ssps", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:103158 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Example of a listener.ora

[oracle@krw-ssps-db-02 admin]$  more listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*.*.244)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = ssps)
        (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = ssps)
    )
  )

LISTENER_SSPS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*.*.242)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_SSPS =
  (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = ssps)
        (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = ssps)
    )
     (SID_DESC=
        (SID_NAME=tch8)
        (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
        (PROGRAM=dg4msql)
          )
    (SID_DESC=
         (SID_NAME=tch7)
         (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
         (PROGRAM=dg4msql)
         )
     (SID_DESC=
        (SID_NAME=asut)
        (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
        (PROGRAM=dg4msql)
       )
  )


ADR_BASE_LISTENER = /home/oracle/database

Example the name of the service ssps_db3 is incorrect. The utility tnsping diagnoses only the answer of the listener to the address and port. The correctness of the service name is not checked.

C:\oracle\ora92\network\admin>tnsping ssps_db 3

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-JUN-2018 11:43:29

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*.*.244)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ssps_db3)))
OK (20 msec)
OK (0 msec)
OK (0 msec)

C:\oracle\ora92\network\admin>sqlplus system@ssps_db

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 9 11:43:38 2018

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

Log unsuccessful connection in the log file listener.log 

09-JUN-2018 11:43:29 * ping * 0
09-JUN-2018 11:43:29 * ping * 0
09-JUN-2018 11:43:29 * ping * 0
Sat Jun 09 11:43:45 2018
09-JUN-2018 11:43:45 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ssps_db3)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplus.EXE)(HOST=DEMIN)(USER=DeminDV))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.*.*.45)(PORT=61522)) * establish * ssps_db3 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Example the name of the service ssps_db2 is correct C:\oracle\ora92\network\admin>tnsping ssps_db 3

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-JUN-2018 11:45:18

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.89.251.244)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ssps_db2)))
OK (0 msec)
OK (10 msec)
OK (0 msec)

C:\oracle\ora92\network\admin>sqlplus system@ssps_db

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 9 11:45:23 2018

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[krw-ssps-db-02.krw.rzd] system@ssps>

Log successful connection in the log file listener.log

09-JUN-2018 11:45:18 * ping * 0
09-JUN-2018 11:45:18 * ping * 0
09-JUN-2018 11:45:18 * ping * 0
Sat Jun 09 11:45:28 2018
09-JUN-2018 11:45:28 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ssps_db2)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplus.EXE)(HOST=DEMIN)(USER=DeminDV))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.*.*.45)(PORT=61529)) * establish * ssps_db2 * 0
like image 59
Dmitry Demin Avatar answered Sep 22 '22 00:09

Dmitry Demin

'Cause you've said that you have "I know login, password and initial catalog". Then I said:

Get Oracle SID: $ ps -ef | grep pmon; (or $ cat /etc/oratab - format: ORACLE_SID:ORACLE_HOME)

Get Service Name: $ cat $ORACLE_HOME/network/admin/tnsnames.ora. With tnsnames.ora format, you can cat SERVICE_NAME

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS=(protocol_address_information))
   (CONNECT_DATA= 
     (SERVICE_NAME=service_name)))

In order to execute tnsping, you can install Oracle Client appropriate to your machine.

like image 40
PhamNguyenDucDuong Avatar answered Sep 20 '22 00:09

PhamNguyenDucDuong
Sign in to Comment

Related questions

Double/Random alias name in select clause on Oracle 11g does not throw invalid identifier exception
How to handle leap seconds in Oracle
Hibernate parameterized sql query slow and active oracle sessions
Unable to add performance counters of Oracle OLEDB provider
How to implement context based DB auditing?
Let Oracle transform OR-connected predicates into UNION ALL operations
PHP PDO installation on windows (xampp)
How to determine the language(English, Chinese...) of a given string in Oracle?
How do I pass a user-defined type as an input to a stored procedure?
Visual Studio 2015 Web Test with Data from Oracle Database
Create Oracle Docker image with custom data
Setting up JNDI Datasource with oracle in Tomcat
File processing on two different machine using spring batch
java.sql.SQLException: Io exception: Socket read timed out vs Closed Connection
How to SUM() each row into another column
PHP/Oracle: Time representation
Full path traversed for each ID with cycle Oracle
Visual Studio 2013 ADO.net entity model does not have Oracle data source
@Temporal(TemporalType.DATE) with Oracle 12
Why oracle stored procedure execution time is greatly increased depending on how it is executed?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!