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]$