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的监听器。