连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常

平台系统版本相关信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter cluster;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.16.11

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.orasos.com" from dual;

www.orasos.com
-------------------
2012-05-30 11:07:12

pmon和监听负载
pmon和LISTENER进程负载均比较高

  PID     %CPU ResSize    Char Command    
10617230  72.9  143924 21014  ora_pmon_ahunicom1  
22675560  49.9  142000  1547  oracleahunicom1 (LOCAL=NO)
 5243206  30.6   49728  2579  /oracle10/app/product/db/10.2.0/bin/tnslsnr LISTENER -inherit

监听日志
每秒钟很多类此pmon注册监听信息

27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0

通过这两点可以确定是因为pmon在不停的动态注册监听导致监听日志,pmon,listener进程异常

查询MOS[ID 982068.1]
问题原因

After altering the value of the parameter REMOTE_LISTENER, 
excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly. 
Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered.
Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second. 
REMOTE_LISTENER had been set to null twice

查询alert日志,果真发现:
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH SID='AHUNICOM1';
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

解决方案

alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1';
alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1';
--然后重启节点,pmon和监听恢复正常

提高短连接性能方法测试

创建测试脚本
通过在三个会话中同时执行test_login.sh脚本,模拟当数据库多个短连接情况性能

[oracle@xifenfei tmp]$ more test_login.sh 
#!/bin/bash
echo "start login database `date`*********" >>/tmp/test_1.log
e=2000
for((i=1;i<=$e;i=i+1))
do
/tmp/login_oracle.sh 
done
echo "end login database `date`*********" >>/tmp/test_1.log

[oracle@xifenfei tmp]$ more login_oracle.sh 
#!/bin/bash
sqlplus chf/xifenfei@ORA11G_P<<XFF>/dev/null 
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
exit
XFF 

--ORA11G_P根据不同的测试情景指定不同名称

情况1:一个监听情况下

start login database Tue May  1 18:03:32 CST 2012*********
start login database Tue May  1 18:03:35 CST 2012*********
start login database Tue May  1 18:03:37 CST 2012*********

end login database Tue May  1 18:08:20 CST 2012*********
end login database Tue May  1 18:08:25 CST 2012*********
end login database Tue May  1 18:08:26 CST 2012*********

--计算2000个会话登录/查询/推出时间
4:48
4:40
4:49

情况2:三个监听,客户端配置tns负载均衡

--监听配置
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER = /u01/oracle

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
    )
  )
SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER1 = /u01/oracle


LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523))
    )
  )
SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER2 = /u01/oracle

--tns配置
ORA11G_M =
  (DESCRIPTION =
      (LOAD_BALANCE=ON)
      (FAILOVER=ON)
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523))
       (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
     (SERVER=DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

--测试结果
start login database Tue May  1 17:51:45 CST 2012*********
start login database Tue May  1 17:51:49 CST 2012*********
start login database Tue May  1 17:51:51 CST 2012*********

end login database Tue May  1 17:55:58 CST 2012*********
end login database Tue May  1 17:56:06 CST 2012*********
end login database Tue May  1 17:56:09 CST 2012*********

--计算2000个会话登录/查询/推出时间
4:13
4:17
4:18

情况2:使用常驻连接池DRCP(11g新特性)

--启动默认DRCP
SQL> exec dbms_connection_pool.start_pool();

PL/SQL procedure successfully completed.

--tns配置
ORA11G_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVER=POOLED)  --注意
      (SERVICE_NAME = ora11g)
    )
  )

--执行结果
start login database Tue May  1 18:19:58 CST 2012*********
start login database Tue May  1 18:20:01 CST 2012*********
start login database Tue May  1 18:20:03 CST 2012*********

end login database Tue May  1 18:23:16 CST 2012*********
end login database Tue May  1 18:23:19 CST 2012*********
end login database Tue May  1 18:23:21 CST 2012*********

--计算2000个会话登录/查询/推出时间
3:16
3:18
3:19

总结
如果在数据库短连接过程中发现监听是瓶颈的时候,可以考虑使用多个监听+tns 负载均衡,从一定程度上缓解监听瓶颈.如果是11g数据库可以考虑使用其心功能DRCP,从而很大程度上提高短连接过程中数据库的效率.因为DRCP还属于11g的新功能稳定性不知道如何?使用该功能前,请一定要做好相关测试工作.如有可能还是建议从应用层面尽可能的使用长连接,提高数据库会话效率.

xdb组件中关于ftp/http监听

发现监听进程监听8080和2100端口

--监听端口
[oracle@xifenfei ~]$ netstat -nap|grep tnslsnr
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:8080                0.0.0.0:*                   LISTEN      29866/tnslsnr
tcp        0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN      29866/tnslsnr
tcp        0      0 0.0.0.0:2100                0.0.0.0:*                   LISTEN      29866/tnslsnr

--进程名称
[oracle@xifenfei ~]$ ps -ef|grep 29866
oracle   29866     1  0 00:20 pts/0    00:00:00 /u01/oracle/9.2.0/db_1/bin/tnslsnr LISTENER -inherit

查看监听状态

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 00:26:50

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                08-MAY-2012 00:20:47
Uptime                    0 days 0 hr. 6 min. 3 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 2 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully
--从这里可以看出oracle的listener确实监听了8080和2100端口

查看listener.ora文件

[oracle@xifenfei ~]$ more /u01/oracle/9.2.0/db_1/network/admin/listener.ora
# LISTENER.ORA Network Configuration File: /u01/oracle/9.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )
--从中确认未监听8080和2100端口,看来这两个端口是在动态注册监听的时候加入进去的.
--我们知道oracle的xdb组件可能会启用http和ftp功能,这两个功能可能会开启响应端口,我们分析xdb组件.

查看xdb组件是否工作正常

SQL> select 
  2  comp_name, status, version from 
  3  DBA_REGISTRY where 
  4  comp_name='Oracle XML Database'; 

COMP_NAME                      STATUS                 VERSION
------------------------------ ---------------------- ----------
Oracle XML Database            VALID                  9.2.0.4.0

SQL> select  count(*) from  dba_objects where owner='XDB' and status='INVALID';

  COUNT(*)
----------
         0

SQL>   show parameter dispatchers;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
dispatchers                          string                 (PROTOCOL=TCP) (SERVICE=xffXDB)
max_dispatchers                      integer                5
mts_dispatchers                      string                 (PROTOCOL=TCP) (SERVICE=xffXDB)
mts_max_dispatchers                  integer                5
--查询证明xdb组件应该工作正常

查看xdb中ftp和http启动相关端口

SQL> select dbms_xdb.GETFTPPORT() from dual; 
select dbms_xdb.GETFTPPORT() from dual
       *
ERROR at line 1:
ORA-00904: "DBMS_XDB"."GETFTPPORT": invalid identifier


SQL> select dbms_xdb.GETHTTPPORT() from dual; 
select dbms_xdb.GETHTTPPORT() from dual
       *
ERROR at line 1:
ORA-00904: "DBMS_XDB"."GETHTTPPORT": invalid identifier
--9i中为提供上述查询端口的相关程序.

SQL> set long 10000
SQL> set pagesize 0
SQL> SELECT dbms_xdb.cfg_get FROM dual;
--从中找到类此这样记录,确实ftp启用2100端口,http启用8080端口
     <ftpconfig>
        <ftp-port>2100</ftp-port>
        <ftp-listener>local_listener</ftp-listener>
        <ftp-protocol>tcp</ftp-protocol>
        <session-timeout>6000</session-timeout>
     </ftpconfig>
     <httpconfig>
        <http-port>8080</http-port>
        <http-listener>local_listener</http-listener>
        <http-protocol>tcp</http-protocol>
        <session-timeout>6000</session-timeout>
        <server-name>XDB HTTP Server</server-name>
     </httpconfig>
--到这里我们可以确定是由于xdb组件中的ftp和http功能自动注册导致监听了2100和8080端口

修改xdb中监听ftp和http端口

SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),'
   2 /xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',8888));

Call completed.

SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
   2 '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()',2222));

Call completed.

SQL> commit;

Commit complete.

SQL>  exec dbms_xdb.cfg_refresh;

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 00:57:13

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                08-MAY-2012 00:20:47
Uptime                    0 days 0 hr. 36 min. 26 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=2222))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 2 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully

xdb中ftp和http监听

SQL> alter system reset dispatchers  scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> show parameter dispatchers;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ---------
dispatchers                          string
max_dispatchers                      integer                5
mts_dispatchers                      string
mts_max_dispatchers                  integer                5

--SELECT dbms_xdb.cfg_get FROM dual;中信息
      <ftpconfig>
        <ftp-port>2222</ftp-port>
        <ftp-listener>local_listener</ftp-listener>
        <ftp-protocol>tcp</ftp-protocol>
        <session-timeout>6000</session-timeout>
      </ftpconfig>
      <httpconfig>
        <http-port>8888</http-port>
        <http-listener>local_listener</http-listener>
        <http-protocol>tcp</http-protocol>
        <session-timeout>6000</session-timeout>
        <server-name>XDB HTTP Server</server-name>
      </httpconfig>

[oracle@xifenfei dbs]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 01:10:07

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                08-MAY-2012 00:20:47
Uptime                    0 days 0 hr. 49 min. 20 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xifenfei" has 2 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully
--证明已经关闭了xdb 组件的ftp/http监听

xdb组件中的ftp/http监听在9i数据库中,只要你安装了xdb组件,会自动启用这功能.
在10g及其11g中默认不启用.所以为了你的数据库安全,如果不使用这些功能,建议手工关闭

IP=FIRST作用说明

我相信细心的朋友,估计都会发现我们在使用netca创建rac(10g/11g)的监听的时候,会发现 IP=FIRST,如下面展示

LISTENER_VENUS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521)(IP = FIRST))
        (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)(IP = FIRST))
      )
    )
  )

那么 IP=FIRST表示什么含义呢?我通过下面的试验证明

1. 主机相关配置

[oracle@localhost ~]$ hostname 
localhost.localdomain
[oracle@localhost ~]$ more /etc/hosts
127.0.0.1               localhost.localdomain localhost
[oracle@localhost ~]$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:15:17:67:9C:39  
          inet addr:192.168.8.121  Bcast:192.168.15.255  Mask:255.255.248.0
          inet6 addr: fe80::215:17ff:fe67:9c39/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:378351781 errors:0 dropped:0 overruns:0 frame:0
          TX packets:357773718 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:65412948319 (60.9 GiB)  TX bytes:92608894986 (86.2 GiB)
          Base address:0x1100 Memory:88020000-88040000 

eth0:0    Link encap:Ethernet  HWaddr 00:15:17:67:9C:39  
          inet addr:202.91.244.3  Bcast:202.91.247.255  Mask:255.255.248.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Base address:0x1100 Memory:88020000-88040000 

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:40636368 errors:0 dropped:0 overruns:0 frame:0
          TX packets:40636368 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:3385490475 (3.1 GiB)  TX bytes:3385490475 (3.1 GiB)

2. 当前监听配置

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = mcrm)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME =mcrm)
    )

  )

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

3. 监听状态

 [oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-MAR-2012 00:17:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                03-MAR-2012 23:56:18
Uptime                    16 days 0 hr. 20 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "mcrm" has 2 instance(s).
  Instance "mcrm", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mcrm", status READY, has 1 handler(s) for this service...
Service "mcrmXDB" has 1 instance(s).
  Instance "mcrm", status READY, has 1 handler(s) for this service...
Service "mcrm_XPT" has 1 instance(s).
  Instance "mcrm", status READY, has 1 handler(s) for this service...
The command completed successfully

这里可以看出来监听的还是(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))),也就是说监听的是主机名

4. 当前监听IP和端口

[oracle@localhost ~]$ netstat -an |grep 1521|grep LISTEN
tcp        0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN

这里看出来,监听所有网卡

5. 修改监听文件

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = mcrm)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME =mcrm)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521) (IP=FIRST) ) 
)
  )

6. 重启监听

[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-MAR-2012 00:18:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(IP=FIRST)))
The command completed successfully
[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-MAR-2012 00:18:47

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

Starting /opt/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                20-MAR-2012 00:18:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "mcrm" has 1 instance(s).
  Instance "mcrm", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

通过(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))),已经发现,这里只监听127.0.0.1

7. 查看监听IP和端口

[oracle@localhost ~]$ netstat -an |grep 1521|grep LISTEN
tcp        0      0 127.0.0.1:1521              0.0.0.0:*                   LISTEN  

这里进一步验证监听的ip地址已经只有了127.0.0.1而没有了其他网卡的地址

总结说明
通过这里的试验证明IP = FIRST的作用使得当我们使用主机名为监听中的host配置的时候,它只会监听hostname解析出来的ip地址,而不是默认情况下所有网卡地址。

hosts中缺少localhost.localdomain导致监听启动时间超长

0、基本信息

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-194.el5PAE #1 SMP Tue Mar 16 22:00:21 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@localhost ~]$ hostname
localhost.localdomain
[oracle@localhost ~]$ /sbin/ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:14:22:10:96:AE  
          inet addr:192.168.9.66  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96ae/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:6597875 errors:0 dropped:0 overruns:0 frame:0
          TX packets:9785915 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:2526369384 (2.3 GiB)  TX bytes:295978844 (282.2 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:14:22:10:96:AE  
          inet addr:外网ip  Bcast:211.155.227.175  Mask:255.255.255.240
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 00:14:22:10:96:AF  
          inet addr:192.168.11.50  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96af/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:999903 errors:0 dropped:0 overruns:0 frame:0
          TX packets:283 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:104688608 (99.8 MiB)  TX bytes:22777 (22.2 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:8816738 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8816738 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:2726275798 (2.5 GiB)  TX bytes:2726275798 (2.5 GiB)
[oracle@localhost ~]$ more /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=localhost.localdomain

1、hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1                 localhost

2、监听文件

[oracle@localhost ~]$ more /opt/oracle/product/10g/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gaxt)
      (ORACLE_HOME = /opt/oracle/product/10g)
      (SID_NAME = gaxt)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 外网IP)(PORT = 1521)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

3、启动监听

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-DEC-2011 23:27:32

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

Starting /opt/oracle/product/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10g/network/admin/listener.ora
Log messages written to /opt/oracle/product/10g/network/log/listener.log
Trace information written to /tmp/listener_trc.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=外网IP)(PORT=1521)))
--这里卡了很久很久才显示成功
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                15-DEC-2011 23:27:32
Uptime                    0 days 0 hr. 3 min. 9 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10g/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10g/network/log/listener.log
Listener Trace File       /tmp/listener_trc.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "gaxt" has 1 instance(s).
  Instance "gaxt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4、开启监听trace

--在/opt/oracle/product/10g/network/admin/listener.ora中加入下列参数
TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER= LISTENER_TRC
TRACE_DIRECTORY_LISTENER=/tmp

6、重启监听查看trace文件

[15-DEC-2011 23:33:32:938] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:33:32:938] New trace stream is /tmp/listener_trc.trc
[15-DEC-2011 23:33:32:938] New trace level is 16
…………………………
[15-DEC-2011 23:33:32:981] nsnainconn: no native services in use - returning
[15-DEC-2011 23:33:32:981] nsnainconn: signalling that calling function should not continue
[15-DEC-2011 23:33:32:982] nsnainconn: normal exit
[15-DEC-2011 23:33:32:982] nsnaconn: normal exit
[15-DEC-2011 23:33:32:982] nsaccept: exit (0)
[15-DEC-2011 23:36:41:965] nsglma: WARNING - SNMP master agent is not running OR snmp.ora file does not exist.
[15-DEC-2011 23:36:41:965] nsevwait: entry
[15-DEC-2011 23:36:41:965] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:36:41:965] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:36:41:965] nsevwait: waiting for transport event (1 thru 4)...
[15-DEC-2011 23:36:41:965] nsevwait: 1 newly-posted event(s)

这里可以看出监听启动的过程中,在这个地方等待了3分钟之久
MOS:Lsnrctl Start is Extremely Slow or Appears to Hang

7、修改hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1               localhost.localdomain localhost

8、重启监听查看trace文件

[15-DEC-2011 23:43:44:555] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:43:44:555] New trace stream is /tmp/listener_trc.trc
……………………
[15-DEC-2011 23:43:44:627] nsevwait: entry
[15-DEC-2011 23:43:44:627] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:43:44:627] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:43:44:627] nsevwait: waiting for transport event (1 thru 4)...

这里可以看出监听启动一共使用时间为:几十毫秒

9、结论
在主机名为localhost.localdomain的hosts文件中缺少127.0.0.1 localhost.localdomain,哪怕监听中配置的是ip地址,也将导致监听启动时间非常长。如果主机名不是localhost.localdomain,那hosts中缺少localhost.localdomain没有影响监听启动。