Oracle 动态监听注册不上的问题

问题描述

在搭建单机数据库的时候碰到了一件奇怪的事情，动态监听无法进行注册，在lsnrctl status命令下只有刚刚通过netmgr生成的静态监听。

Services Summary...
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

此时使用alter system register也无法触发PMON进程使其将诸如pdb的数据库注册到动态监听当中。

解决

首先我们去$ORACLE_HOME/network/admin/tnsnames.ora(没有的话新建)配置如下内容：

LOCAL_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = [Your host address])(PORT = 1521))

之后前往数据库:

SQL> alter system set local_listener=LOCAL_LIST scope=both;

查看监听:

Services Summary...
Service "01cc101ebe512ddee0633358a8c08bfd" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1" has 2 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb_orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

完成！

思考

一定要使用local_listener参数来解决该问题嘛？

SQL> alter system reset local_listener scope=both;
SQL> show parameter local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

重启监听

$ lsnrctl stop
$ lsnrctl start
Services Summary...
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

重启监听后发现，又只剩下了静态监听，加下来需要重新register database:

SQL> alter system register;

验证:

Services Summary...
Service "01cc101ebe512ddee0633358a8c08bfd" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1" has 2 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb_orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

动态监听重新回来，并且无论是之后重启数据库或者主机都可以实现动态监听注册。