wake-up-neo.com

Wie erhalte ich die SID, den Servicenamen und den Port für die Oracle-Datenbank?

Ich habe eine Oracle-Datenbank, die unter der Adresse xx.xx.xx.xx ausgeführt wird. Ich kenne Login, Passwort und Erstkatalog

Ich versuche, eine neue Verbindung in Oracle SQL Developer einzurichten, damit ich auf diese Datenbank zugreifen kann, um sie mir anzusehen. Dazu muss ich die SID oder den Dienstnamen kennen.

Vor langer Zeit habe ich TNSPing.exe ausgeführt, um den Dienstnamen abzurufen, aber dieses Programm ist nicht mehr verfügbar. Oracle verteilt es nicht.

Gibt es irgendwo eine Möglichkeit, auf diese Informationen zuzugreifen?

Vielen Dank

1
bobetko

Wenn Sie sich beim Datenbankserver als Betriebssystem angemeldet haben, können Sie Folgendes ausführen:

lsnrctl status

um Details darüber zu erfahren, was der Hörer anbietet. Oder Sie können die Datenbank selbst abfragen, wenn Sie sich lokal anmelden können

SQL> select name from dba_services;

NAME
---------------------------------------
SYS$BACKGROUND
SYS$USERS
db122XDB
db122

Wenn Sie jedoch keinen anderen Zugriff als die IP-Adresse haben, müssen Sie jemanden (DBA) nach dem Dienstnamen fragen. Wie Alex sagte, "enthüllt" tnsping nichts, sondern versucht lediglich, eine Verbindung zu einem Dienstnamen herzustellen, über den Sie bereits Bescheid wissen.

1
Connor McDonald

Weil du gesagt hast, dass du "Ich kenne Login, Passwort und Erstkatalog" hast. Dann sagte ich:

Holen Sie sich die Oracle SID: $ ps -ef | grep pmon; (oder $ cat/etc/oratab - Format Oracle_SID: Oracle_HOME)

Abrufen des Servicenamens: cat $ Oracle_HOME/network/admin/tnsnames.ora. Mit dem Format tnsnames.ora können Sie SERVICE_NAME cat

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

Um tnsping auszuführen, können Sie den für Ihren Computer geeigneten Oracle-Client installieren.

0

Wenn Sie Zugriff auf den Server haben, können Sie bestimmen, wie viele Prozesse der Listener ausführt, welche IP-Adressen und Ports er überwacht. Ein Beispiel für Linux.

[[email protected] ~]$ 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

Wenn die Variable $TNS_ADMIN definiert ist, überschreibt sie $Oracle_HOME/network/admin

[[email protected] ~]$ 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
[[email protected] ~]$ env |grep TNS
TNS_ADMIN=/home/Oracle/app/Oracle/product/11.2.0/dbhome_1/network/admin
[[email protected] ~]$

Zunächst können Sie eine SQL-Abfrage durchführen

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


[[email protected] ~]$ 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
          .....


[[email protected] ~]$ 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
[[email protected] ~]$ 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
[[email protected] ~]$ 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
[[email protected] ~]$ 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

Beispiel für eine listener.ora

[[email protected] 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

Beispiel: Der Name des Dienstes ssps_db3 ist falsch. Das Dienstprogramm tnsping diagnostiziert nur die Antwort des Listeners auf die Adresse und den Port. Die Richtigkeit des Dienstnamens wird nicht überprüft.

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 [email protected]_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

Protokollieren Sie eine erfolglose Verbindung in der Protokolldatei 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

Beispiel: Der Name des Dienstes ssps_db2 ist korrekt. 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 [email protected]_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] [email protected]>

Protokollieren Sie die erfolgreiche Verbindung in der Protokolldatei 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
0
Dmitry Demin