Author Archives: 惜分飞

Oracle Database常用补丁集Patch号

Posted on by

Patchset / PSU Patch Number 

11.2.0.4	13390677      11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

11.2.0.3.8	16902043      DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7	16619892      DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6	16056266      DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013) 
11.2.0.3.5	14727310      DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4	14275605      DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3	13923374      DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2	13696216      DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1	13343438      DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3	10404530      11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
11.2.0.2.11	16619893      DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10	16056267      DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013) 
11.2.0.2.9	14727315      DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8	14275621      DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7	13923804      DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6	13696224      DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5	13343424      DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4	12827726      DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3	12419331      DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2	11724916      DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1	10248523      DATABASE PSU 11.2.0.2.1
11.2.0.2	10098816      11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
11.2.0.1.6	12419378       DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5	11724930      DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4	10248516      DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3	9952216      DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2	9654983      DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1	9352237      DATABASE PSU 11.2.0.1.1
        	 
11.1.0.7.16	16619896 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15	16056268  [*]      DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013) 
11.1.0.7.14	14739378 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13	14275623 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12	13923474      DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11	13621679      DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10	13343461      DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9	12827740      DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8	12419384      DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7	11724936      DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6	10248531      DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5	9952228      DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4	9654987      DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3	9352179      DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2	9209238      DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1	8833297      DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7	6890831      11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.2.0.5.12	16619894 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11	16056270  [*]      DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10	14727319 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9	14275629 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8	13923855 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7	13632743 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6	13343471 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5	12827745 [*]      DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4	12419392      DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3	11724962      DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2	10248542      DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1	9952230      DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5	8202632      10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.2.0.4.17	16619897 [*]      DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16	16056269 [*]      DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15	14736542 [*]      DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14	14275630 [**]      DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13	13923851 [*]      DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12	12879933 [*]      
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11	12879929 [*]      DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10	12827778      DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9	12419397      DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8	11724977      DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7	10248636      DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6	9952234      DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010) 
10.2.0.4.5	9654991      DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010)    [overlay PSU]
10.2.0.4.4	9352164      DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3	9119284      DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2	8833280      DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1	8576156      DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4	6810189      10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3	5337014      10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2	4547817      10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.1.0.5	4505133      10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4	4163362      10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3	3761843      10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
        	 
9.2.0.8	4547809      9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7	4163445      9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6	3948480      9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5	3501955      ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4	3095277      9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3	2761332      9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2	2632931      9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
        	 
9.0.1.5	3301544      9.0.1.5 PATCHSET
9.0.1.4	2517300      9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3	2271678      9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
        	 
8.1.7.4	2376472      8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3	2189751      8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2	1909158      8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER

NOTE:
[*] 10.2.0.4 and 10.2.0.5 are now in extended support mode and PSU’s released after Aug 01,2011 will need ES License to download them.
参考:Quick Reference to Patchset Patch Numbers [ID 753736.1]

10.2.0.5出现ORA-00600[kcblasm_1]

Posted on by

今天朋友执行一条如下sql

SELECT YF,
       KHBH,
       MAX(KHXM),
       MAX(YYBDM),
       MAX(YYBMC),
       MIN(ZJZH),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       MAX(NVL(ZC, 0)) ZGZC,
       SUM(NVL(ZRZC, 0)) ZRZC,
       SUM(NVL(ZCZC, 0)) ZCZC,
       SUM(NVL(FDYK, 0)) FDYK,
       SUM(NVL(ZRGF, 0)) ZRGF,
       SUM(NVL(ZCGF, 0)) ZCGF,
       AVG(NVL(ZC, 0)) PJZC,
       SUM(NVL(CJJE, 0)) CJJE,
       SUM(NVL(YJ, 0)) YJ,
       SUM(NVL(ZJFSS, 0)) ZJFSS,
       SUM(NVL(ZYJ, 0)) ZYJ,
       SUM(NVL(CJCS, 0)) CJCS
  FROM CRM_FX2_KHZJHZ P
 WHERE YF = 201203
 GROUP BY YF, KHBH;

出现ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], []错误

数据库版本

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

通过MOS发现该错误在10.2.0.5上有相关描述

This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed.
Problem is affecting 10.2.0.5 to 10.2.0.5.3.
Cause
This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454.

The problem was introduced in 10.2.0.5, by the fix of Bug:7523755.
This has been corrected in PSU 10.2.0.5.4 and 11.2.

解决方法

To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later.
All alternative solutions for this problem are listed below: 
•	Upgrade the database to 11.2.

OR
•	Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed. 
The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1)

OR
•	Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3):
o	For UNIX / Linux platforms apply Patch 7612454 available for download on MOS.
o	For Windows platforms apply Patch 3 or higher. 
Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.

这个错误在10.2.0.5上很容易发生，官方也没有给出发生这个错误的原因，以及如何改写相关sql，给出的方案是升级到10.2.0.5.4或者更高。这里可以看出升级需要慎重，可能导致原库有小部分sql不能正常执行。

lob类型数据转换为系统文件

Posted on by

1.插入一条blob数据

SQL> create directory ULTLOBDIR as '/home/oracle';

Directory created.

SQL> create table blobtest(col1 BLOB);

Table created.

SQL> declare
a_blob BLOB;
  2    3  bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak');
  4  begin
  5  insert into blobtest values (empty_blob())
  6  returning col1 into a_blob;
  7  dbms_lob.fileopen(bfile_name);
  8  dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
  9  dbms_lob.fileclose(bfile_name);
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(col1) from blobtest;

DBMS_LOB.GETLENGTH(COL1)
------------------------
                    4829

SQL> !pwd
/home/oracle

SQL> !ls -l tt.txt.bak
-rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak

2.创建存储过程

CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB,
                                                 FILE_PATH IN VARCHAR2,
                                                 FILE_NAME IN VARCHAR2) IS
  DATA_BUFFER   RAW(32767);
  POSITION      INTEGER := 1;
  FILEHANDLE    UTL_FILE.FILE_TYPE;
  ERROR_NUMBER  NUMBER;
  ERROR_MESSAGE VARCHAR2(100);
  BLOB_LENGTH   INTEGER;
  CHUNK_SIZE    BINARY_INTEGER := 32767;
BEGIN
  BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
  FILEHANDLE  := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
  WHILE POSITION < BLOB_LENGTH LOOP
    DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
    UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
    POSITION    := POSITION + CHUNK_SIZE;
    DATA_BUFFER := NULL;
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      ERROR_NUMBER  := SQLCODE;
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
      UTL_FILE.FCLOSE_ALL;
    END;
END;
/

3.测试读取blob到系统

SQL> declare 
  2  tmp_blob blob default empty_blob(); 
  3  begin 
  4  dbms_lob.createtemporary(tmp_blob, true); 
  5  select col1 into tmp_blob from blobtest; 
  6  retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt'); 
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> !pwd
/home/oracle

SQL> !ls -l xifenfei.txt
-rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt

坏块分类与检测

Posted on by

很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分，其实根据物理和逻辑坏块的分类，还可以继续分下去；对于坏块的检查，很多人知道物理和逻辑坏块使用dbv和rman检测，那其他种类的坏块该怎么办呢？这篇文章整理自MOS，给大家整理个分析坏块的思路，在后续文章中，会给出各种坏块的解决思路

附件:hout和hcheck

IP=FIRST作用说明

Posted on by

我相信细心的朋友,估计都会发现我们在使用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地址，而不是默认情况下所有网卡地址。