oracle11gr2 监听服务不起作用

最新推荐文章于 2021-08-26 23:57:02 发布

原创最新推荐文章于 2021-08-26 23:57:02 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

28 篇文章

订阅专栏

本文档描述了在Oracle 11gr2中遇到的监听服务问题，表现为sqlplus无法连接，tnsping失败。通过检查监听端口、修改local_listener设置并执行alter system register命令，部分问题得到解决，但lsnrctl status显示push服务存在一个unkown状态的实例。在listener.ora文件中查看了相关配置，并参考论坛帖子进行了故障排查。

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

1. 监听端口打开：

192.168.44.97:1521 0.0.0.0:* LISTEN

2. sqlplus 连不上，tnsping也失败

3. alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.44.97)(PORT=1521))' scope=both;

alter system register;解决，但lsnrctl status发现push service有2条push instance 记录，一个为ready动态注册)，另一个为unkown（静态注册）。

======================================================

oracle@uma1:~/product/11.2.0/dbhome_2/network/admin> cat listener.ora
# listener.ora Network Configuration File: /home/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.44.97)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = push)
(SID_NAME = push)
)
)
ADR_BASE_LISTENER = /home/oracle

=========================================================================

SQL> host lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-8月 -2012 11:28:16

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.44.97)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                25-6月 -2012 14:59:03
Uptime                    48 days 20 hr. 29 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/diag/tnslsnr/uma1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.44.97)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "push" has 2 instance(s).
Instance "push", status UNKNOWN, has 1 handler(s) for this service...
Instance "push", status READY, has 1 handler(s) for this service...
Service "pushXDB" has 1 instance(s).
Instance "push", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      push
db_unique_name                       string      push
global_names                         boolean     FALSE
instance_name                        string      push
lock_name_space                      string
log_file_name_convert                string
service_names                        string      push
SQL>

SQL> show parameter listen

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = 192.168.44.97)(PORT = 152
                                                 1))
remote_listener                      string

============================参考论坛帖子====================

http://www.itpub.net/forum.php?mod=viewthread&tid=1603547

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 19-APR-2012 10:18:46

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date             13-APR-2012 13:50:07
Uptime                   5 days 20 hr. 28 min. 39 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File       /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@vm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@vm1 admin]$ vi listener.ora
[oracle@vm1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Apr 19 10:19:18 2012

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521))' scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
[oracle@vm1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 19-APR-2012 10:20:01

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date             13-APR-2012 13:50:07
Uptime                   5 days 20 hr. 29 min. 54 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File       /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))
Services Summary...
Service "PROD" has 2 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "PROD", status READY, has 6 handler(s) for this service...
Service "PROD_XPT" has 1 instance(s).
  Instance "PROD", status READY, has 6 handler(s) for this service...
The command completed successfully
[oracle@vm1 admin]$

============================RAC 监听配置==========

SQL> show parameter listener

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
listener_networks                    string
local_listener                       string                            (DESCRIPTION=(ADDRESS_LIST=(AD
                                                                       DRESS=(PROTOCOL=TCP)(HOST=222.
                                                                       248.3.141)(PORT=1521))))
remote_listener                      string                            sc-cluster-scan:1521