Oracle监听数据库实例状态显示status UNKNOWN

叁歲伎倆 2022-11-30 01:40 464阅读 0赞

\[oracle@localhost admin\]$ lsnrctl status

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

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  
\[oracle@localhost 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

\[oracle@localhost admin\]$

重新启动数据库和监听器，查看状态：

$ lsnrctl start  
$ lsnrctl status  
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-AUG-2020 20:07:52

$ 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

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)  
    )  
  )

\[oracle@localhost admin\]$