oracle监听多台主机配置,一台机器配置多个监听的问题定位

环境:

OS:AIX 4.3

DB:8.1.7

今天刚接触一个数据库,在服务器上执行lsnrctl发现监听没有启动,但用户却能连接到该数据库,后来发现是该服务器上配置了多个监听器(其中有些是不用了的)导致的,下面是问题定位过程.

1.进入监听,查看状态信息

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 16-OCT-2012 17:54:03

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

IBM/AIX RISC System/6000 Error: 79: Connection refused

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

IBM/AIX RISC System/6000 Error: 79: Connection refused

LSNRCTL>第一反应是监听没启动,查看OS进程

2.查看OS进程

$ ps -ef|grep lsnr

oracle 24300     1   0 02:51:34      -  0:27 /usr/oracle/app/oracle/product/8.1.7/bin/tnslsnr lsnrm203 -inherit

oracle 28286 31382   0 17:14:27  pts/0  0:00 grep lsnr

这里有监听进程在运行.

3.客户端能tnsping通该数据库

$ tnsping m203_db

TNS Ping Utility for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 16-OCT-2012 17:55:23

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.228)(PORT=1521))

OK (10 msec)

这里可以tnsping通.

郁闷中,于是检查listener.ora文件

4.查看listener.ora文件

$ more listener.ora

# LISTENER.ORA Network Configuration File: /usr/oracle/app/oracle/product/8.1.7/

network/admin/listener.ora

# Generated by Oracle configuration tools.

LSNRP203 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.230)(PORT = 1521))

)

LSNRM203 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.228)(PORT = 1521))

)

SID_LIST_LSNRM203 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = m203_db)

(ORACLE_HOME = /usr/oracle/app/oracle/product/8.1.7)

(SID_NAME = m203)

)

)

SID_LIST_LSNRP203 =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /usr/oracle/app/oracle/product/8.1.7)

(SID_NAME = p203)

)

)呵呵,看来是配置了多个监听器的问题了.

查看具体的监听器lsnrm203

5.查看具体的监听器

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 16-OCT-2012 17:59:15

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status lsnrm203

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.228)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     lsnrm203

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production

Start Date                16-OCT-2012 02:51:34

Uptime                    0 days 15 hr. 7 min. 56 sec

Trace Level               off

Security                  OFF

SNMP                      ON

Listener Parameter File   /usr/oracle/app/oracle/product/8.1.7/network/admin/listener.ora

Listener Log File         /usr/oracle/app/oracle/product/8.1.7/network/log/lsnrm203.log

Services Summary...

m203          has 1 service handler(s)

The command completed successfully

LSNRCTL>可以看出监听是正常的.

-- The End --