如何為 oracle 實例設置 listener.ora 和 tnsnames.ora

我設置了listener.ora和tnsnames.ora，但是不知道設置好不好。當我呼叫該lsnrctl status命令時，我得到了未知的 rcadb 和 proddb 實例。

我是 Oracle 的初學者，我安裝了預建構的 Oracle 12c。

現在我做了兩個數據庫，我有這個問題，我找不到錯誤。

我在安裝時獲得的數據庫 orcl12c，我更改了 host = 0.0.0.0

監聽器.ora：

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = orcl12c)
     (SID_NAME = orcl12c)
     (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = prodDB)
     (SID_NAME = prodDB)
     (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = rcatDB)
     (SID_NAME = rcatDB)
     (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
   )
 )

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1524))
   )
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1525))
   )
 )

我像這樣設置 tnsnames.ora：

ORCL12C =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl12c)
   )
 )

prodDB =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.2)(PORT = 1524))
   (CONNECT_DATA =
      (SERVICE_NAME = prodDB)
   )
)

rcatDB =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.3)(PORT = 1525))
   (CONNECT_DATA =
      (SERVICE_NAME = rcatDB)
   )
)


LISTENER_ORCL12C =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )

lsnrctl 狀態

Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.2)(PORT=1524)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.3)(PORT=1525)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
 Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
 Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
 Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
 Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
 Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "prodDB" has 1 instance(s).
 Instance "prodDB", status UNKNOWN, has 1 handler(s) for this service...
Service "rcatDB" has 1 instance(s).
 Instance "rcatDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

編輯：

[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ . ./.profile_productionDB
[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 15 05:33:45 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
PRODDB

SQL> 

沒有什麼不妥。

偵聽器控制實用程序 SERVICES 命令

• UNKNOWN 表示實例在 listener.ora 文件中靜態註冊，而不是在服務註冊中動態註冊。因此，狀態未知。

狀態 UNKNOWN 不一定是問題。如果您在數據庫之前啟動偵聽器，則可能會發生這種情況，那麼您的偵聽器不知道數據庫的狀態。

當你這樣做：

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

您正在本地連接到您的數據庫，而不使用您的偵聽器。您可以停止監聽器並嘗試連接。它應該工作。

如果你想測試你的 tnsnames/listener，首先創建一個測試使用者：

[oracle@localhost ~]$ sqlplus / as sysdba
create user test_tns identified by "test_tns";
grant create session to test_tns;
exit;

然後測試：

[oracle@localhost ~]$ sqlplus test_tns/test_tns@rcadb

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