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_co de,status,perdefined from abstract_service; ABSTRACT_SERVICE_CO DE 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的监听器。
作者:熊哥club
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明。文章部分内容来源网络,如有版权问题,请留言告知,博主会尽快删除.
微信扫描下方二维码关注【熊哥club】,回复1024获取程序员必备IT电子书,回复java获取全套学习资源