Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently，ora12505

Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently，ora12505
使用java串連oacle12c時報錯：
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
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:711)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:558)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at demo1.DBHelp.getCon(DBHelp.java:16)
at demo1.DBHelp.main(DBHelp.java:28)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:272)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
... 8 more
明顯的，這是由於監聽程式不能識別串連標識符中的資料庫SID，到網上查了一下

網上的解決方案如下：找到tnsnames.ora檔案使用記事本或者其他的工具開啟，注意，我這是oracle12c的檔案的路徑，其他的oracle的不同的版本在不同的路徑下

D:\oracle\app\Administrator\product\12.1.0\dbhome_1\NETWORK\ADMIN下的tnsnames.ora
檔案的內容如下
# tnsnames.ora Network Configuration File: D:\oracle\app\Administrator\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_TEST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER_ORACLE12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORACLE12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle12c)
    )
  )

//把原來紅色的部分改成上面所示的內容，上面加綠色的部分是當初你安裝時資料庫的執行個體名稱，

D:\oracle\app\Administrator\product\12.1.0\dbhome_1\NETWORK\ADMIN的listener.ora檔案

檔案的內容如下：

# listener.ora Network Configuration File: D:\oracle\app\Administrator\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\app\Administrator\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\app\Administrator\product\12.1.0\dbhome_1\bin\oraclr12.dll")

    )

 (SID_DESC =
      (GLOBAL_DBNAME = oracle12c) 
      (ORACLE_HOME = D:\oracle\app\Administrator\product\12.1.0\dbhome_1)
      (SID_NAME = oracle12c)
    )

  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

紅色的部分是添加上去的，儲存，然後重新啟動oracle服務。

測試的內容如下所示

package demo1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBHelp {
private static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oracle12";
private static String USERNAME = "system";
private static String PASSWORD = "940109";
public static Connection getCon(){
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
if(conn!=null)
System.out.println("connect successful");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* @param args
*/
public static void main(String[] args) {
System.out.println(new DBHelp().getCon());
}
public static void close(Connection con,Statement sm,ResultSet rs){
try {
if(con!=null){
con.close();
}
if(sm!=null){
sm.close();
}
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

//就不會再報錯了

connect successful
oracle.jdbc.driver.T4CConnection@1edf1c96

也就是當初在安裝oracle12c是自己改了執行個體的名稱，如果是按照預設的執行個體進行的安裝的話，那麼上面的內容基本上是不需要自己修改的。

不是按照預設的執行個體進行的安裝。