Oracle Database 21c Express Edition（20220215） ORA-12541 TNS沒有監聽器 JAVA Connection ORA-28040

 Oracle Database Express Edition | Oracle 台灣

Oracle Database Express Edition (XE) Downloads | Oracle 台灣

下載安裝完Oracle Database 21c Express Editio

Run services.msc check service

 

OracleServiceXE

OracleOraDB21Home1TNSListener

OracleOraDB21Home1MTSRecoveryService

 

 

Run sqlplus.exe

Use command login : / as sysdba

SQL> $lsnrctl status

=如果OracleOraDB21Home1TNSListener沒有執行如下==

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 15-2月 -2022 09:53:32

 

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

 

連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourIP)(PORT=1521)))

TNS-12541: TNS:沒有監聽器

 TNS-12560: TNS:協定介面程式錯誤

  TNS-00511: 沒有監聽器

   64-bit Windows Error: 61: Unknown error

連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

TNS-12541: TNS:沒有監聽器

 TNS-12560: TNS:協定介面程式錯誤

  TNS-00511: 沒有監聽器

   64-bit Windows Error: 2: No such file or directory

 

========順利如下========

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 15-2月 -2022 09:56:27

 

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

 

連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourIP)(PORT=1521)))

監聽器的狀態

------------------------

別名                     LISTENER

|

省略

|

監聽器參數檔案           C:\app\yourname\product\21c\homes\OraDB21Home1\network\admin\listener.ora

監聽器日誌檔             C:\app\ LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 15-2月 -2022 09:56:27

 

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

 

連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourIP)(PORT=1521)))

監聽器的狀態

------------------------

別名                     LISTENER

|

省略

|

監聽器參數檔案           C:\app\yourname\product\21c\homes\OraDB21Home1\network\admin\listener.ora

監聽器日誌檔             C:\app\yourname\product\21c\diag\tnslsnr\PC_Name\listener\alert\log.xml

監聽端點摘要...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourIP)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\APP\YOURNAME\PRODUCT\21C\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))

服務摘要...

|

省略

|

命令順利完成

 

====== listener.ora 內容 =====

# listener.ora Network Configuration File: C:\app\ yourname \product\21c\homes\OraDB21Home1\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

 

DEFAULT_SERVICE_LISTENER = XE

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\ yourname \product\21c\dbhomeXE)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\ yourname \product\21c\dbhomeXE\bin\oraclr.dll")

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = yourIP)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

接著下載sqldeveloper
SQL Developer | Oracle 台灣
Oracle Database Express Edition (XE) Downloads | Oracle 台灣

結論：

因為本身的電腦有兩張網卡一個有線的IP一個無線，在安裝時會自動找一個IP當做我SQL的監聽IP，所以用上述指令可以用來確定執行狀態，與執行IP和port來確認我的Oracle Database 21c Express Edition在何種狀態執行藉以確認我的sqldeveloper能否正常執行SQL

簡略筆記指令：

sqlplus.exe

 / as sysdba

$lsnrctl status

services.msc

PS.純個人筆記，如果有問題 請自行確認自己的狀態，還要看當時的版本架設環境。

寫的不好見諒，有什麼文本編輯問題歡迎留言。

java 使用筆記

ORA-28040: 沒有相符的認證協定

去下載
Oracle JDBC and UCP 21c (21.3) Download page
ojdbc11.jar 更換原jdbc 的jar

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@yourIP:1521/xe", "sys", "yourInstallPassword");
Statement stmt = con.createStatement();

ORA-28009: 以 SYS 身分連線應改為以 SYSDBA 或 SYSOPER 身分連線
出現這只省一下歩，把username sys改為 sys as sysdba

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@yourIP:1521/xe", "sys as sysdba", "yourInstallPassword");
Statement stmt = con.createStatement();

接下來就和以前一樣好好享用您的Java + Oracle SQL啦。