在 Linux 上設置 Oracle XE

我正在嘗試在我的 Linux 機器上設置 oracle-xe。我只需要一個使用 Oracle SQL 的環境；這不是為了生產。

當我嘗試啟動 SQLPLUS 時，我不斷收到以下錯誤之一：

ORA-12541: TNS:no listener

ORA-28547: connection to server failed, probable Oracle Net admin error

有很多關於類似問題的文章，但即使在嘗試了許多不同的“解決方案”之後，我仍然迷失了方向。

我可能做錯了什麼可怕的事情。請參閱以下輸出：

[oracle@roshan ~]$ whoami
oracle
[oracle@roshan ~]$ pwd
/usr/lib/oracle
[oracle@roshan ~]$ echo $ORACLE_HOME
/usr/lib/oracle/product/11.2.0/xe
[oracle@roshan ~]$ echo $ORACLE_SID
PLSExtProc
[oracle@roshan ~]$ cat product/11.2.0/xe/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File:

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )

EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
   )
   (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
   )
 )

SQL_AUTHENTICATION_SERVICES = (NONE)
[oracle@roshan ~]$ product/11.2.0/xe/bin/lsnrctl start    

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-OCT-2017 12:24:03

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

TNS-01106: Listener using listener name LISTENER has already been started
[oracle@roshan ~]$ product/11.2.0/xe/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-OCT-2017 12:24:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                22-OCT-2017 12:21:18
Uptime                    0 days 0 hr. 2 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /usr/lib/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /usr/lib/oracle/diag/tnslsnr/roshan/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=roshan.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
 Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@roshan ~]$ sqlplus SYSTEM/foobar@localhost:8080/PLSExtProc


SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 22 12:24:14 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener


Enter user-name: %                                                              [oracle@roshan ~]$ sqlplus SYSTEM/foobar@localhost:1521/PLSExtProc


SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 22 12:24:27 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error


Enter user-name: %   
[oracle@roshan ~]$ su
Password: 
[root@roshan oracle]# /etc/rc.d/oracle-xe status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-OCT-2017 12:32:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                22-OCT-2017 12:21:18
Uptime                    0 days 0 hr. 10 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /usr/lib/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /usr/lib/oracle/diag/tnslsnr/roshan/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=roshan.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
 Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

這些看起來是不是很不對勁？

1. 您應該將 ORACLE_SID 設置為“xe”，而不是“PLSExtProc”。‘PLSExtProc’ 是外部過程的一個特例。在超過 25 年的時間裡，我從來沒有理由使用它。

2）當你像這樣呼叫 sqlplus 時：

sqlplus username/password

您正在呼叫程序間連接，並繞過所有網路問題，包括偵聽器。偵聽器僅涉及通過網路的請求。

當你像這樣呼叫 sqlplus 時：

sqlplus username/password@mydb

您正在呼叫網路連接，詳細資訊要從 tnsnames.ora 文件中的“mydb”條目中解析

當您像這樣呼叫 sqlplus 時（如@Balazs Papp 所建議）

sqlplus username/password@localhost:1521:xe

您正在呼叫網路連接，但繞過使用 tnsnames.ora 並直接提供關鍵資訊（主機名、埠、sid）。並不是所有三個元素都必須與“lisnrctl status”報告的內容相匹配。在您的情況下，主機不是“localhost”而是“roshan.localdomain”。

您的客戶端（sqlplus）和數據庫在同一台機器上這一事實並不重要。如果您使用呼叫網路連接的語法，則請求仍會通過所有網路協議。

您可以在http://edstevensdba.com/category/oracle-tns/和http://edstevensdba.com/category/oracle-listener/閱讀有關 oracle 如何處理網路連接的更多資訊

引用自：https://dba.stackexchange.com/questions/189027