我有一台运行在 xx.xx.xx.xx 地址上的 Oracle 数据库,我知道登录名、密码和初始目录。
我正在尝试在 Oracle SQL Developer 中设置新的连接,以便能够访问此数据库并查看它。为了做到这一点,我需要知道 SID 或服务名称。
很久以前,我会运行 TNSPing.exe 来获取服务名称,但是这个程序不再可用了。Oracle 不再分发它。
有没有办法在其他地方获得这些信息呢?
谢谢
我有一台运行在 xx.xx.xx.xx 地址上的 Oracle 数据库,我知道登录名、密码和初始目录。
我正在尝试在 Oracle SQL Developer 中设置新的连接,以便能够访问此数据库并查看它。为了做到这一点,我需要知道 SID 或服务名称。
很久以前,我会运行 TNSPing.exe 来获取服务名称,但是这个程序不再可用了。Oracle 不再分发它。
有没有办法在其他地方获得这些信息呢?
谢谢
如果您可以访问服务器,那么您可以确定监听器正在运行多少个进程,它正在监听哪些IP地址和端口。
以下是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
$TNS_ADMIN
被定义,则会覆盖$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 ~]$
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
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
例如,服务名称ssps_db3
是不正确的。工具tnsping仅诊断侦听器对地址和端口的响应,而不检查服务名称的正确性。
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
记录无法连接的日志到监听器日志文件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
例如,服务的名称ssps_db2
是正确的。
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>
在日志文件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
lsnrctl status
SQL> select name from dba_services;
NAME
---------------------------------------
SYS$BACKGROUND
SYS$USERS
db122XDB
db122
但如果您除了IP地址没有其他访问权限,那么您需要向某人(DBA)请求服务名称。正如Alex所说,tnsping并不会“揭示”任何信息,它只是尝试连接您已经知道的服务名称。
$ ps -ef | grep pmon
; (或者 $ cat /etc/oratab
- 格式:ORACLE_SID:ORACLE_HOME
)$ cat $ORACLE_HOME/network/admin/tnsnames.ora
。使用 tnsnames.ora 格式,可以查看 SERVICE_NAME。net_service_name=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))
tnsping.exe
仍然可用 - 它只是不再是Oracle Instant Client的一部分。如果您下载完整的客户端并在Oracle通用安装程序中启用实用工具选项,您将获得它。 - Wernfried Domscheit