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