Oracle10g 監聽註冊問題 - Ready及Unknown狀態

 

Oracle 9i 資料庫軟體安裝的時候最後自動會跳出配置監聽器的畫面， 我們可以配置監聽，然後會生成listener.ora 及

tnsnames.ora 檔案， DBCA建庫的時候會將相應的資料庫名稱寫入listener.ora及tnsnames.ora中 。那麼開啟一般監聽是是靜態註冊，

監聽Listener.ora 一般格式是： 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = pce-tsg-036)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
  )

然後在DBCA建立庫之後，會在 SID_LIST_LISTENER 中加入庫的sid_name 及global_name . 
在監聽中有資料庫資訊的情況下，監聽開啟後都會有UNKNOWN 狀態的情況存在。 

 

tnsnames.ora 最初的一般格式為：

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = epdvdb)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = )
    )
  )

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

EPDV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.164.68.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = epdv)
    )
  )

 

 

 

 

 

但是Oracle 10g 中安裝資料庫軟體的時候不再提示配置監聽器，而需要安裝軟體完成後，自己NETCA來建立，才能生成listener.ora

及tnsnames.ora 檔案。

如果在netca之前已經透過dbca建庫了，再次netca 建立的listener.ora 及tnsnames.ora 中不會有所建庫的相關service name
資訊， lsnrctl  start  開啟後也不會有庫的相關監聽資訊，客戶端不能正常訪問，會報錯。 

 

正常情況下安裝Oracle10g 軟體後，Netca 建立監聽資訊，生成listener.ora及tnsnames.ora 檔案 。
Listener.ora 格式如下 (不再包含有資料庫例項名稱資訊，可能是由於動態註冊的原因)：   

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/product/oracle)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.158)(PORT = 1526))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

 

正常情況下的tnsnames.ora檔案如下：

MXDELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.158)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mxdell)
    )
  )

LISTENER_MXDELL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.158)(PORT = 1526))

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

 
同時初始話引數 local_listener 會被設定為 local_listener='LISTENER_MXDELL' 

以上正常的情況下，lsnrctl status 看到的是動態註冊的資訊(有時候需要等2~3分鐘
才能看到)：

Listener Parameter File   /u01/product/oracle/network/admin/listener.ora
Listener Log File         /u01/product/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.13.67.158)(PORT=1526)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "mxdell" has 1 instance(s).
  Instance "mxdell", status READY, has 1 handler(s) for this service...
Service "mxdellXDB" has 1 instance(s).
  Instance "mxdell", status READY, has 1 handler(s) for this service...
Service "mxdell_XPT" has 1 instance(s).
  Instance "mxdell", status READY, has 1 handler(s) for this service...
The command completed successfully 

 

如果在類似9i 手工在 listener.ora 中設定了資料庫例項的資訊，那麼lsnrctl status
出現如下資訊 ， READY 及 UNKNOWN 狀態都會出現。 

Service "mxdell" has 2 instance(s).
  Instance "mxdell", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mxdell", status READY, has 1 handler(s) for this service...

 

動態註冊情況下，關閉和開啟資料庫的時候，監聽會隨之關閉及開啟 。