Oracle監聽資料庫執行個體狀态顯示status UNKNOWN

Oracle監聽資料庫執行個體狀态顯示status UNKNOWN

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-AUG-2020 19:38:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                22-AUG-2020 10:26:47

Uptime                    0 days 9 hr. 12 min. 10 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

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

Services Summary...

Service "orcl" has 2 instance(s).

  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orcl002" has 2 instance(s).

  Instance "orcl002", status UNKNOWN, has 1 handler(s) for this service...

  Instance "orcl002", status READY, has 1 handler(s) for this service...

Service "orcl002XDB" has 1 instance(s).

  Instance "orcl002", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

[[email protected] admin]$ 

Instance "orcl", status UNKNOWN : 這表明該orcl執行個體不是自動注冊、而是通過搜尋 listener.ora 主動找到比對的 Service 後進行注冊的。主動監聽不管資料庫執行個體是否啟動。

Instance "orcl", status READY   : 這表明該orcl執行個體是自動注冊的，而不是主動監聽。等資料庫執行個體啟動OK後，PMON 背景程序監視到執行個體啟動OK，會動态注冊 SID 資訊。

Oracle 11g 有一個 PMON 背景程序監視，如果先啟動資料庫執行個體，再啟動監聽器，則 listener.ora 檔案中不需要配置相關的 SID 資訊，因為 PMON 會自動對監測到的 Service 進行自動注冊。如果你的監聽器端口為預設的 1521端口，那麼 listener.ora 可以忽略。

監聽服務如果晚于資料庫執行個體如啟動較長時間的話，狀态變化也會有一定的延時。

檢視監聽器配置如下：

$ cat $ORACLE_HOME/network/admin/listener.ora 

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

SID_LIST_LISTENER = 

  (SID_LIST =

     (SID_DESC =

       (GLOBAL_DBNAME = orcl002)

       (SID_NAME = orcl002)

     )

     (SID_DESC =

       (GLOBAL_DBNAME = orcl)

       (SID_NAME = orcl)

     )

  )

ADR_BASE_LISTENER = /opt/oracle

可見我配置了 SID_LIST_LISTENER ，将兩個SID都靜态配置了。

接下來修改監聽器配置，删除靜态配置：

$ sqlplus  / as sysdba

SQL> shutdown immediate

SQL> quit

$ lsnrctl stop

$ vim $ORACLE_HOME/network/admin/listener.ora

修改後的内容如下（删除了SID_LIST_LISTENER）

$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = /opt/oracle

[[email protected] admin]$ 

重新啟動資料庫和監聽器，檢視狀态：

$ lsnrctl start

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-AUG-2020 20:07:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                22-AUG-2020 20:07:49

Uptime                    0 days 0 hr. 0 min. 2 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

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

The listener supports no services

The command completed successfully

$ sqlplus  / as sysdba

SQL> startup 

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size                  2213736 bytes

Variable Size            1224738968 bytes

Database Buffers          352321536 bytes

Redo Buffers                7434240 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-AUG-2020 20:08:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                22-AUG-2020 20:07:49

Uptime                    0 days 0 hr. 0 min. 18 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

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

Services Summary...

Service "orcl" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orcl002" has 1 instance(s).

  Instance "orcl002", status READY, has 1 handler(s) for this service...

Service "orcl002XDB" has 1 instance(s).

  Instance "orcl002", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

可以看到顯示的資料庫執行個體狀态為：status READY ，沒有 status UNKNOWN 了

另外附上我的nsnames.ora配置

$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL002 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl002)

    )

  )

ORCL =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

[[email protected] admin]$