oracle动态监听和静态监听

已于 2022-06-08 17:49:14 修改
阅读量3.8k 收藏 10
点赞数
分类专栏： 数据库 文章标签： oracle 数据库 database
于 2022-06-08 15:55:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/thinklog2018/article/details/125183951
版权
本文档描述了在Oracle 11gR2数据库中遇到的一个问题，即重启后手动添加的listener.ora文件导致监听器无法识别实例。解决方案包括切换到动态监听和配置静态监听。动态监听通过删除或重命名listener.ora文件并执行`alter system register`命令实现，而静态监听则需要正确配置listener.ora文件中的SID_LIST，确保监听器能够识别服务实例。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

项目场景：

oracle 11gR2 数据库，在某次重启后维护人员手动添加了listener.ora文件，在启动监听后，发现没有监听的实例。命令显示如下：

[oracle@233-szsjdatabase-backup admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:29:44

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JUN-2022 14:18:52
Uptime                    0 days 0 hr. 10 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

无论等多久，都是看不到监听的实例的。  但1521端口是打开的，看下这时监听文件的配置：

[oracle@233-szsjdatabase-backup admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /u01/app/oracle

由于localhost对应的是127.0.0.1，所以看到的监听端口如下：

[oracle@233-szsjdatabase-backup admin]$ netstat -an | grep 1521
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN

既然只在127.0.0.1 上监听1521，那么外部的客户端和业务系统是无法访问数据库的。

原因分析：

     正常情况下，数据库会采用动态监听，也就是无需手动配置listener.ora这个文件。

      如果是静态监听，才需要对这个文件进行配置，但host不能是localhost。

解决方案：

提示：这里填写该问题的具体解决方案：

（1）采用动态监听。

1. 删除$ORACLE_HOME/network/admin目录下的listener.ora文件，或者改成其它文件名。

2. 重启监听，如果报错，杀掉监听进程，然后重启监听。

[oracle@233-szsjdatabase-backup admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:54:53

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@233-szsjdatabase-backup admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:54:55

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

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Error listening on: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

Listener failed to start. See the error message(s) above...

[oracle@233-szsjdatabase-backup admin]$ ps -ef | grep tnsl
oracle   14651     1  0 14:18 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr LISTENER -inherit
oracle   28028 13418  0 14:55 pts/2    00:00:00 grep --color=auto tnsl
[oracle@233-szsjdatabase-backup admin]$ kill -9 14651
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:55:20

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

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JUN-2022 14:55:20
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@233-szsjdatabase-backup admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:55:24

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JUN-2022 14:55:20
Uptime                    0 days 0 hr. 0 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))
The listener supports no services
The command completed successfully

动态监听启动时稍慢一些，等待60秒左右再查看状态既可看到动态监听实例了。（急性子可以执行下alter system register命令）

[oracle@233-szsjdatabase-backup admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 14:57:58

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JUN-2022 14:57:00
Uptime                    0 days 0 hr. 0 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))
Services Summary...
Service "hrmis" has 1 instance(s).
  Instance "hrmis", status READY, has 1 handler(s) for this service...
Service "hrmisXDB" has 1 instance(s).
  Instance "hrmis", status READY, has 1 handler(s) for this service...
The command completed successfully

（2）采用静态监听。

修改listener.ora文件，参考官网的示例。

SID_LIST_listener=
(SID_LIST=
 (SID_DESC=
  (GLOBAL_DBNAME=sales.us.example.com)
  (SID_NAME=sales)
  (ORACLE_HOME=/u01/app/oracle/11g)))
[oracle@233-szsjdatabase-backup admin]$ vi listener.ora
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 15:20:57

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2022 15:21:03

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

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JUN-2022 15:21:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/233-szsjdatabase-backup/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=233-SZSJDatabase-backup.cviclocal)(PORT=1521)))
Services Summary...
Service "hrmis" has 1 instance(s).
  Instance "hrmis", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@233-szsjdatabase-backup admin]$
[oracle@233-szsjdatabase-backup admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#LISTENER =
#  (DESCRIPTION =
#    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
#  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hrmis)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
      (SID_NAME = hrmis)
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle

注意上面的监听文件中将LISTEN=部分注释掉了，里面监听host地址是localhost，会造成只在127.0.0.1地址监听的问题。注释后静态监听是在0.0.0.0监听。

等待几分钟后可以看到，静态和动态监听是可以共存的。

取消动态监听的办法：在listener.ora文件中加入DYNAMIC_REGISTRATION_LISTENER = off

，然后重启监听。

总结：

1. 动态监听

 （1）network/admin目录中 删除或改名listener.ora文件

 （2）alter system register

 

2. 静态监听

（1）配置network/admin目录中的listener.ora文件，加入下面两句

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hrmis)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
      (SID_NAME = inst_name)
    )
  )

DYNAMIC_REGISTRATION_LISTENER = off

（2） 重启监听

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值