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

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

坏块分类与检测

很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分，其实根据物理和逻辑坏块的分类，还可以继续分下去；对于坏块的检查，很多人知道物理和逻辑坏块使用dbv和rman检测，那其他种类的坏块该怎么办呢？这篇文章整理自MOS，给大家整理个分析坏块的思路，在后续文章中，会给出各种坏块的解决思路
http://www.xifenfei.com/wp-content/uploads/2012/03/Corruption_block_type_and_analysis.pdf
附件:hout和hcheck

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地址，而不是默认情况下所有网卡地址。

ORA-7445[__milli_memcpy]分析

帮朋友分析日志的过程中发现在两个节点上多次出现类此错误
节点1日志分析

Mon Mar  5 08:19:20 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:21 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:22 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+464] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFF0] [] []
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件

/u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	HP-UX
Node name:	HDDB1
Release:	B.11.23
Version:	U
Machine:	ia64
Instance name: hddms1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 18556, image: oracle@HDDB1 (TNS V1-V3)
*** 2012-03-05 08:19:20.675
*** SESSION ID:(100.40701) 2012-03-05 08:19:20.602
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x9fffffffbf580000, PC: [0x400000000182a9f0, __milli_memcpy()+2448]
  r1: 6000000000524900       r20:                8       br5:                0
  r2:                0       r21: 9fffffffbf4c21ec       br6: 40000000018ad000
  r3:               37       r22:                8       br7: e000000120001400
  r4:                0       r23: c00000005e3bac98        ip: 400000000182a9f0
  r5: 60000000005311d8       r24: 9fffffffbf4c2268      iipa:                0
  r6:         40000004       r25: 9fffffffbf4c2268       cfm:       2a54295428
  r7: 9fffffffffff1ba8       r26: 9fffffffbf580000        um:               1a
  r8: 9fffffffbf4c2268       r27: 9fffffffbf57ff00       rsc:               1f
  r9:             8000       r28: 9fffffffbf580008       bsp: 9fffffffbf802150
 r10: 9fffffffbf4ba260       r29: 9fffffffbf57ff08  bspstore: 9fffffffbf802150
 r11:                0       r30:                0      rnat:                0
 r12: 9ffffffffffe5240       r31:                0       ccv:         20000000
 r13: 9fffffffbf5a1420      NaTs:                0      unat:                0
 r14: 9fffffffbf4c23e8       PRs: ffffffffffff4837      fpsr:    9804c8274433f
 r15: 9fffffffbf580028       br0: 400000000202a7e0       pfs: c000000000001b3e
 r16:  3fffffffffffdff       br1:                0        lc:  3ffffffffffce89
 r17: 9fffffffbf4c2258       br2:                0        ec:                5
 r18:                0       br3:                0       isr: 9fffffffbf802150
 r19:               80       br4:                0       ifa:                0
Reason code: 0008
*** 2012-03-05 08:19:20.735
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Current SQL statement for this session:
update pmis.PM_BZ_DC_DWRFHSJ set ZGFDFH=:v_result where PARINST_ID=:v_pid and T_ID>:v_mdtbegin and T_ID<=:v_mdtend
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c00000008975ebc0        49  procedure PMIS.PM_DC_SJJGGC_EXECINF
c0000000971ef530         2  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+528         call     ksedst()             000000001 ?
                                                   C050000000000996 ?
                                                   4000000002A53E60 ?
ssexhd()+1184        call     ksedmp()             000000003 ?
                                                   C0500000000010A9 ?
                                                   40000000025E1420 ?
                                                   FFFFFFFFFFFF9075 ?
                                                   9FFFFFFFFFFDF2E0 ?
                                                   6000000000514428 ?
                                                   6000000000514420 ?
                                                   60000000005178E0 ?
<kernel>             call     ssexhd()             400000000086EFB0 ?
                                                   000000015 ? 000000007 ?
                                                   C050000000000085 ?
__milli_memcpy()+24  call     <kernel>
48
$cold_updgrh()+1088  call     __milli_memcpy()     000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   9FFFFFFFBF564348 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
upduaw()+224         call     $cold_updgrh()       C0000000A2665550 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   C000000000000C1E ?
                                                   4000000001916600 ?
                                                   000000000 ? 00000E137 ?
kdusru()+720         call     upduaw()             C0000000A2665550 ?
                                                   C000000000005BBF ?
                                                   4000000001912AC0 ?
                                                   00000E135 ?
                                                   9FFFFFFFFFFE52B0 ?
                                                   C0000000A2665660 ?
                                                   9FFFFFFFFFFE63F0 ?
                                                   9FFFFFFFBF5590D8 ?
kauupd()+544         call     kdusru()             9FFFFFFFBF4C5D24 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   000000000 ?
                                                   C00000008B5D1108 ?
                                                   C00000008B5D1138 ?
                                                   000000018 ?
                                                   9FFFFFFFBF5591E0 ?
updrow()+3024        call     kauupd()             9FFFFFFFFFFE65D0 ?
                                                   000000000 ?
                                                   6000000000530010 ?
                                                   000000000 ?
                                                   C00000009202CF38 ?
                                                   00000010B ? 00001AA7A ?
                                                   C000000086F2B778 ?
qerupFetch()+608     call     updrow()             9FFFFFFFBF4C89F0 ?
                                                   C0000000A2665550 ?
                                                   C000000000000C1D ?
                                                   40000000018F5BF0 ?
                                                   000000000 ?
updaul()+1008        call     qerupFetch()         C0000000A0DB24D0 ?
                                                   000000000 ?
                                                   C0000000A2665550 ?
                                                   000007FFF ?
updThreePhaseExe()+  call     updaul()             C000000091A57FA0 ?
432                                                000000006 ? 000000000 ?
                                                   C0000000000019BB ?
                                                   40000000019DF9B0 ?
                                                   000008E23 ?
                                                   60000000001D30F0 ?
                                                   0000000BD ?
updexe()+624         call     updThreePhaseExe()   C000000091A57FA0 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFFFFF1AF0 ?
                                                   C000000000000FA6 ?
                                                   40000000019DFCA0 ?
                                                   000000000 ?
opiexe()+6944        call     updexe()             C000000091A57FA0 ?
                                                   9FFFFFFFBF4C8A00 ?
                                                   C000000000002858 ?
                                                   4000000001857620 ?
                                                   000008E25 ?
                                                   9FFFFFFFFFFF1AD0 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFBF4C88D4 ?
opipls()+3488        call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF1D30 ?
                                                   C000000000002C60 ?
                                                   40000000019852D0 ?
                                                   000000000 ? 000007123 ?
                                                   9FFFFFFFFFFF1B40 ?
opiodr()+3088        call     opipls()             9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF2F58 ?
                                                   000000004 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E123 ?
                                                   9FFFFFFFFFFF2E70 ?
                                                   9FFFFFFFFFFF2DE0 ?
rpidrus()+304        call     opiodr()             9FFFFFFFFFFF3810 ?
                                                   9FFFFFFFFFFF37E0 ?
                                                   9FFFFFFFFFFF36F8 ?
                                                   9FFFFFFFFFFF36F4 ?
skgmstack()+288      call     rpidrus()            9FFFFFFFFFFF4DA0 ?
                                                   C000000000000716 ?
                                                   4000000001862FE0 ?
                                                   9FFFFFFFFFFF47F0 ?
                                                   600000000004F3C0 ?
                                                   9FFFFFFFFFFF4DB8 ?
                                                   6000000000524900 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpidru()+256         call     skgmstack()          9FFFFFFFFFFF4DC0 ?
                                                   600000000004EDF0 ?
                                                   00000F618 ?
                                                   400000000129E400 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpiswu2()+784        call     rpidru()             00000057B ?
                                                   C000000000001329 ?
                                                   4000000001870CE0 ?
rpidrv()+2096        call     rpiswu2()            C00000007D7B3708 ?
                                                   9FFFFFFFFFFF5510 ?
                                                   6000000000515CE8 ?
                                                   60000000000502E8 ?
                                                   9FFFFFFFFFFF5520 ?
                                                   000000205 ?
                                                   6000000000530010 ?
                                                   6000000000531090 ?
psddr0()+256         call     rpidrv()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   00000003A ?
psdnal()+672         call     psddr0()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   000000030 ?
pevm_EXIM()+400      call     psdnal()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFFFFF8440 ?
                                                   6000000000531A04 ?
                                                   600000000064BAA0 ?
                                                   C000000090880CC8 ?
                                                   600000000063EA80 ?
pfrrun()+37120       call     pevm_EXIM()          9FFFFFFFBF567AD8 ?
                                                   C000000090880CC8 ?
                                                   C000000000001532 ?
                                                   400000000173DAD0 ?
                                                   000006269 ?
                                                   4000000002190B40 ?
                                                   9FFFFFFFBF4BA068 ?
                                                   9FFFFFFFBF5C08A0 ?
peicnt()+544         call     pfrrun()             9FFFFFFFBF567CDC ?
                                                   C000000000000D1D ?
                                                   4000000001676FE0 ?
kkxexe()+832         call     peicnt()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFBF567AD8 ?
                                                   C000000000000818 ?
                                                   40000000021A3A80 ?
                                                   00000FEAB ?
                                                   9FFFFFFFFFFF7EB0 ?
                                                   9FFFFFFFBF567CDC ?
                                                   000000000 ?
opiexe()+12592       call     kkxexe()             9FFFFFFFFFFF8430 ?
                                                   C000000000002858 ?
                                                   4000000001858C30 ?
                                                   0000080AB ?
                                                   9FFFFFFFFFFF8420 ?
                                                   9FFFFFFFBF4F3A6C ?
                                                   9FFFFFFFBF4F3A68 ?
                                                   9FFFFFFFFFFF84A0 ?
opiall0()+3456       call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF9A80 ?
                                                   9FFFFFFFFFFF8830 ?
                                                   C0000000000024D1 ?
                                                   40000000017A3350 ?
                                                   000000000 ? 00000C82B ?
                                                   9FFFFFFFFFFF8640 ?
kpoal8()+2272        call     opiall0()            000000002 ?
                                                   9FFFFFFFFFFF9A10 ?
                                                   9FFFFFFFFFFFA2B4 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFF9954 ?
                                                   9FFFFFFFFFFFA2BC ?
                                                   000000000 ?
                                                   FFFFFFFFFFFFDFFF ?
opiodr()+3088        call     kpoal8()             9FFFFFFFFFFFA0B0 ?
                                                   000000000 ? 000000000 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000F82F ?
                                                   9FFFFFFFFFFFA130 ?
                                                   9FFFFFFFFFFFA1D0 ?
ttcpip()+1888        call     opiodr()             9FFFFFFFFFFFA460 ?
                                                   9FFFFFFFFFFFA430 ?
                                                   9FFFFFFFFFFFA348 ?
                                                   9FFFFFFFFFFFA344 ?
                                                   C0000000000018B9 ?
                                                   400000000172CB70 ?
                                                   000000000 ? 00000C0AB ?
opitsk()+1920        call     ttcpip()             6000000000052C40 ?
                                                   000000002 ?
                                                   9FFFFFFFFFFFB950 ?
                                                   6000000000273AA0 ?
                                                   9FFFFFFFFFFFBAB0 ?
                                                   9FFFFFFFFFFFB8C4 ?
                                                   9FFFFFFFBF75A660 ?
                                                   4000000000D2C7AA ?
opiino()+2656        call     opitsk()             000000000 ? 000000000 ?
                                                   C000000000000D1F ?
                                                   400000000236CE90 ?
                                                   000000000 ?
opiodr()+3088        call     opiino()             60000000005DEC18 ?
                                                   6000000000531170 ?
                                                   9FFFFFFFFFFFF4C0 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E825 ?
                                                   9FFFFFFFFFFFD7C0 ?
                                                   9FFFFFFFFFFFF4D0 ?
opidrv()+1088        call     opiodr()             9FFFFFFFFFFFDF10 ?
                                                   9FFFFFFFFFFFDEE0 ?
                                                   9FFFFFFFFFFFDDF8 ?
                                                   9FFFFFFFFFFFDDF4 ?
sou2o()+48           call     opidrv()             9FFFFFFFFFFFEF60 ?
                                                   000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main()+352           call     sou2o()              9FFFFFFFFFFFF4E0 ?
                                                   00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main_opd_entry()+80  call     main()               000000000 ?
                                                   9FFFFFFFFFFFF9B0 ?
                                                   C000000000000004 ?
                                                   C00000000002FA60 ?
--------------------- Binary Stack Dump ---------------------

观察节点2，发现类此错误不同之处有

Wed Mar  7 08:19:11 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:12 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:13 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:14 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kghalf()+993] [SIGSEGV] [Address not mapped to object] [0xC0000000A8B01DA0] [] []
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件不同之处

*** 2012-03-07 08:19:11.582
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c000000090c4a818      1356  procedure PMIS.PM_DC_SJJGGC_QDX
c0000000a25512c0        43  procedure PMIS.PM_DC_SJJGGC_EXECINF
c00000009a8306e8         2  anonymous block

通过这两个节点的错误记录，都包含PMIS.PM_DC_SJJGGC_EXECINF和anonymous，所以初步怀疑是某个程序在这个时间定运行某个任务(包含PMIS.PM_DC_SJJGGC_EXECINF过程)导致。
继续观察trace日志发现两个节点都是类此情况相同的程序，相同的主机

  SO: c00000007d7b3708, type: 4, owner: c00000007d6d0190, flag: INIT/-/-/0x00
    (session) trans: c000000082974470, creator: c00000007d6d0190, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-002A-0000693D, short-term DID: 0000-0000-00000000
              txn branch: 0000000000000000
              oct: 6, prv: 0, sql: c0000000a229ab38, psql: c000000084e6b510, user: 517/PMIS
    O/S info: user: Administrator, term: DMIS_SWITCH2, ospid: 5600:3020, machine: WORKGROUP\DMIS_SWITCH2
              program: PMTDCtrl.exe
    application name: PMTDCtrl.exe, hash value=0
    last wait for 'enqueue' blocking sess=0x0 seq=32849 wait_time=127
                name|mode=54540004, id1=1, id2=10
    temporary object counter: 0

现在更加怀疑是PMTDCtrl.exe运行某过程导致数据库出现这些错误

错误总结
查看MOS发现该问题是一个bug导致(Bug 6166690:ORA-7445 [MILLI_MEMCPY] WHEN REF CURSOR AS OUT PARAMETER)
错误原因:It is caused by a Dynamic SQL used to obtain a REF CURSOR from a procedure (that opens the cursor).
处理建议:通过个bug，我们可以分析PMIS.PM_DC_SJJGGC_EXECINF过程，看看是否符合bug描述,如果符合建议改写过程或者打上补丁(Patch 2709343)
补丁:p2709343_92080_HPUX-IA64

weblogic相关日志介绍

对于一个系统，数据库维护人员来说，对系统/数据库的运行日志都会比较感兴趣。很多情况下在出现故障前，我们可以通过日志发现预兆，把故障扼杀在摇篮中；有些问题发生我们不能及时发现或者当时分析问题不够全面，我们需要结合日志进一步分析问题，寻找原因，提出解决方案，避免类此问题再次出现。对于weblogic系统的维护也不例外，我们也需要关注起日志情况。
一、日志汇总

[oracle@xifenfei logs]$ pwd
/u01/weblogic/user_projects/xff/xifenfei/servers/AdminServer/logs
--其中user_projects/xff/xifenfei需要根据你的实际情况决定
[oracle@xifenfei logs]$ ll *.log
-rw-r-----  1 oracle oinstall  32557 Mar 18 00:01 access.log
-rw-r-----  1 oracle oinstall  49055 Mar 17 19:22 AdminServer-diagnostic.log
-rw-r-----  1 oracle oinstall 741595 Mar 18 00:22 AdminServer.log
-rw-r-----  1 oracle oinstall 306831 Mar 18 00:01 xifenfei.log
--access.log HTTP访问日志
--AdminServer-diagnostic.log weblogic的诊断日志(开发模式下)
--AdminServer.log WebLogic SERVER运行日志
--xifenfei.log DOMAIN运行日志(名称和你的域相同)

二、日志详细介绍
1.access.log
在WebLogic中可以对用HTTP，HTTPS协议访问的服务器上的文件都做记录，该LOG文件默认的名字为Access.log,内容如下，该文件具体记录在某个时间，某个IP地址的客户端访问了服务器上的那个文件。
格式如:

192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /css/fmw.css HTTP/1.1" 200 3250
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /css/fmw_bottom_area.css HTTP/1.1" 200 2689
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /JSLibrary/educational_text.js HTTP/1.1" 200 39124
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /JSLibrary/coneEventHandeler.js HTTP/1.1" 200 7689
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /JSLibrary/getIllustration.js HTTP/1.1" 200 1802
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /JSLibrary/GeneratePopup.js HTTP/1.1" 200 32682
192.168.1.1 - - [17/Mar/2012:16:23:05 +0800] "GET /images/oracle_logo_red.png HTTP/1.1" 200 7202

2.AdminServer-diagnostic.log
该日志在生产环境中没有，主要是记录程序运行中的一些错误的详细信息(程序员最关注的东西)
格式如:

MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "OWSM_TargetRepos" is invalid.
ORA-06550: line 1, column 12:
PLS-00201: identifier 'MDS_INTERNAL_SHREDDED.GETREPOSITORYVERSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

3.AdminServer.log
假如WebLogic SERVER在启动或运行过程中有错误发生，错误信息会显示在屏幕上，并且会记录在一个LOG文件中，该文件默认名为AdminServer.log。该文件也记录WebLogic的启动及关闭等其他运行信息。可在Gernal属性页中设置该文件的路径及名字，错误的输出的等级等

4.xifenfei.log
记录一个DOMIAN的运行情况，一个DOMAIN中的各个WebLogic SERVER可以把它们的一些运行信息（比如：很严重的错误）发送给一个DOMAIN的ADMINISTRATOR SERVER上，ADMINISTRATOR SERVER把这些信息些到DOMAIN 日志中。默认名为：domain_name.log

三、图形界面操作
登录weblogic后台进行相关操作
1.日志相关参数设置
左侧菜单:环境–>服务器
右侧菜单:服务器–>日志记录
只找到AdminServer.log、access.log，进行一些操作
如:设置日志文件的回滚,设置日志文件名等

2.日志查看
左侧菜单:环境–>服务器
右侧菜单:点击服务器名称
左侧菜单:诊断–>日志文件
右侧菜单:选中相关日志点击查看

通过修改col$.col#改变列展示顺序

有网友提出在表中新增加一列，然后让这个列在中间(大家都知道默认情况下，增加一列，这列会在其他列之后)，通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表，有id和name列，现在要改变这两列的默认展示顺序
1.创建模拟表

SQL> create table chf.t_xff (id number,name varchar2(100));
Table created.
SQL> insert into chf.t_xff values(1,'xifenfei');
1 row created.
SQL> insert into chf.t_xff values(2,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> desc chf.t_xff;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
SQL> col name for a30
SQL> select * from chf.t_xff;
        ID NAME
---------- ------------------------------
         1 xifenfei
         2 www.xifenfei.com

2.修改col$.col#

SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols
2    where table_name='T_XFF' AND OWNER='CHF';
 COLUMN_ID COLUMN_NAME
---------- ------------------------------------------------------------
         2 NAME
         1 ID
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF';
 OBJECT_ID
----------
     75598
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      1
SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      2
SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    1
ID                                      2
SQL> COMMIT;
Commit complete.

3.验证结果

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xff;
NAME                                   ID
------------------------------ ----------
xifenfei                                1
www.xifenfei.com                        2
SQL> desc chf.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 NAME                                               VARCHAR2(100)
 ID                                                 NUMBER

_allow_resetlogs_corruption和adjust_scn解决ORA-01190

一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
         5 OFFLINE               868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(last_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
         1        868874
         2        868874
         3        868874
         4        868874
         5        868810                     868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        868874                     868871
         2        868874                     868871
         3        868874                     868871
         4        868874                     868871
         5        868810                     787897

二、隐含参数设置

SQL> create pfile='/tmp/pfile' from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
在pfile中增加
_allow_resetlogs_corruption=true
_allow_error_simulation=TRUE(10g及其以上版本需要)

三、打开数据库,online离线文件

SQL> startup pfile='/tmp/pfile' mount;
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             293603848 bytes
Database Buffers           67108864 bytes
Redo Buffers                6205440 bytes
Database mounted.
--在mount状态下执行
SQL> alter session set events '10015 trace name adjust_scn level 2';
Session altered.
--[一定要]在mount状态下执行online操作
SQL> alter database datafile 5 online;
Database altered.
SQL> recover database until cancel;
ORA-00279: change 868810 generated at 03/13/2012 22:19:37 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_27_777766629.dbf
ORA-00280: change 868810 for thread 1 is in sequence #27
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
no rows selected

姊妹篇:bbed解决ORA-01190

bbed解决ORA-01190

当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
         5 OFFLINE               868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(last_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
         1        868874
         2        868874
         3        868874
         4        868874
         5        868810                     868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        868874                     868871
         2        868874                     868871
         3        868874                     868871
         4        868874                     868871
         5        868810                     787897
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

二、bbed修改相关项

下面两项与resetlogs相关
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
下面四项与数据库文件scn相关
kscnbas (at offset 140) – SCN of last change to the datafile.
kcvcptim (at offset 148) - Time of the last change to the datafile.
kcvfhcpc (at offset 176) – Checkpoint count.
kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/system01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000
BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484
   ub4 kscnbas                           @484      0x000d4495
   ub2 kscnwrp                           @488      0x0000
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077
BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/xifenfei01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5bc6e5
BBED> set mode edit
        MODE            Edit
BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000c05b9
   ub2 kscnwrp                              @120      0x0000
BBED> m /x 07420d00
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000
BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484
   ub4 kscnbas                           @484      0x000d41ca
   ub2 kscnwrp                           @488      0x0000
BBED> set offset 484
        OFFSET          484
BBED> m /x 95440d00
BBED-00209: invalid number (95440d00)
BBED> m /x 9544
BBED> set offset +2
        OFFSET          486
BBED> m /x 0d00
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000003
BBED> m /x 78000000
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000002
BBED> m /x 77000000
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077
BBED> sum
Check value for File 0, Block 1:
current = 0xe079, required = 0x5940
BBED> sum apply
Check value for File 0, Block 1:
current = 0x5940, required = 0x5940

三、数据文件online

[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             301992456 bytes
Database Buffers           58720256 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        869528                     868871
         2        869528                     868871
         3        869528                     868871
         4        869528                     868871
         5        869525                     868871
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.

姊妹篇:_allow_resetlogs_corruption和adjust_scn解决ORA-01190

在RAC中lsnrctl和srvctl操作监听区别

朋友今天询问了一个问题RAC中使用srvctl 操作监听和lsnrctl 操作监听结果不一样，下面我通过实验说明问题
0.listener.ora文件内容

LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)(IP = FIRST))
    )
  )
SID_LIST_LISTENER_RAC1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

1.srvctl 启动监听

rac1-> srvctl start listener -n rac1
rac1-> lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:09:34
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:07:21
Uptime                    0 days 0 hr. 2 min. 13 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_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac2
ora.devdb.db   application    ONLINE    ONLINE    rac2
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

srvctl操作监听，自动反馈到crs中

2.使用srvctl关闭监听

rac1-> srvctl stop listener -n rac1
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac2
ora.devdb.db   application    ONLINE    ONLINE    rac2
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    OFFLINE   OFFLINE
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

3.使用lsnrctl查看监听状态

rac1-> lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:15:54
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
rac1-> lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:16:55
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status listener_rac1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

这里可以发现问题:
1)如果当前没有监听在运行，使用lsnrctl status的时候，会去检查默认的监听名称为listener的监听，如果该监听不存在不会使用hostname填充到hostname项中(注意下面的启动默认监听过程)
2)lsnrctl查看指定监听为listener_rac1，发现和listener.ora中配置相同

4.lsnrctl 关闭监听

rac1-> srvctl start listener -n rac1
rac1-> lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:43:14
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空
The command completed successfully
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac2
ora.devdb.db   application    ONLINE    ONLINE    rac2
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    OFFLINE   OFFLINE
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里可以说明问题：
1)lsnrctl stop虽然是要停止掉默认监听，但是也会停止掉非默认监听
2)lsnrctl stop如果默认监听不存在，那么注册host也为空

5.使用lsnrctl启动默认监听

rac1-> lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:17:37
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:17:37
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/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=rac1)(PORT=1521))) <--主机名
The listener supports no services
The command completed successfully
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac2
ora.devdb.db   application    ONLINE    ONLINE    rac2
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    OFFLINE   OFFLINE
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里发现问题：
1)监听的ip只有主机名的一个，和srvctl启动的监听不一样
2)虽然监听启动了，crs中依然显示为offline状态

6.使用lsnrctl启动listener_rac1监听

LSNRCTL> start listener_rac1
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     listener_rac1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:19:04
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/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac2
ora.devdb.db   application    ONLINE    ONLINE    rac2
ora....b1.inst application    ONLINE    ONLINE    rac1
ora....b2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里可以说明两个问题:
1)使用lsnrctl启动监听和srvctl启动一样
2)启动listener_rac1后，crs中监听资源变成online

7.问题原因分析

rac1-> srvctl config listener -n rac1
rac1 LISTENER_RAC1

通过这里可以发现，其实srvctl操作的监听就是LISTENER_RAC1，所以当我使用lsnrctl 操作LISTENER_RAC1监听时候crs会自动offline或者online，而lsnrctl 操作默认监听时crs不会online