1003
Oracle - ORA-12505, TNS:listener does not currently know of SID given in connect descriptor 解决
java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: 192.168.149.128:1521:orcl at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801) at oracle.eclipse.tools.database.connectivity.db.OracleJDBCConnection.createConn(OracleJDBCConnection.java:199) at oracle.eclipse.tools.database.connectivity.db.OracleJDBCConnection.createConnection(OracleJDBCConnection.java:149) at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:104) at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:53) at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:72) at oracle.eclipse.tools.database.connectivity.db.OracleJDBCConnectionFactory.createConnection(OracleJDBCConnectionFactory.java:42) at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83) at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:355) at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76) at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
原因/解决方法：
1、分析Oracle的启动过程，没有发现问题，其启动信息如下：
oracle@suse10:~> lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-APR-2010 17:05:04 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse10.site)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 17-APR-2010 17:05:06 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse10.site)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully oracle@suse10:~> sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 17 17:05:07 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 436207616 bytes Fixed Size 1219832 bytes Variable Size 125829896 bytes Database Buffers 301989888 bytes Redo Buffers 7168000 bytes Database mounted. Database opened.
2、在sqlplus中查询数据，同样正常：
oracle@suse10:/app/oracle/oracle/product/10.2.0/db_1/network/admin> sqlplus servicemn/servicemn
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 17 18:56:50 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select abstract_service_code,status,perdefined from abstract_service;
ABSTRACT_SERVICE_CODE STATUS PERDEFINED
--------------------- ---------- ----------
1001 1 1
1002 1 0
7777 5 0
2002 1 0
4.4444E+15 5 0
8888 5 0
1003 5 0
2001 1 0
1109 5 0
1 5 0
9999 5 0
11 rows selected.
看来，数据库是没有问题的，那问题应该出在了监听器上。
3、打开Oracle的 listener.ora 文件：
# listener.ora Network Configuration File: /app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = suse10.site)(PORT = 1521))
)
)
修改它的 SID_LIST_LISTENER 中的内容，修改后的内容如下：
# listener.ora Network Configuration File: /app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /app/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = suse10.site)(PORT = 1521))
)
)上面的粗体文字是增加的内容。 4、重启Oracle的监听器。
最后更新：2017-04-03 14:53:41