Database SOS – 第106页 – Professional Database Recovery Support@+8617813235971

large pool太小导致shared server异常

数据库出现如下错误

Fri Oct  5 09:33:54 2012
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20

重启后错误提示变为

Successfully onlined Undo Tablespace 1.
Fri Oct  5 09:34:41 2012
SMON: enabling tx recovery
Fri Oct  5 09:34:41 2012
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:54:52 2012
found dead shared server 'S000', pid = (11, 1)
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")

通过这里的错误,我们可以看到是large pool不能分配72 bytes的连续内存空间而使得S000进程报错.那这两者有什么联系:我们知道S000是shared server的进程,那shared server为什么导致large pool不足呢？查询官方文档得出,如下三种情况会使用large pool

Session memory for the shared server and the Oracle XA interface
(used where transactions interact with more than one database)
I/O server processes
Oracle backup and restore operations

主要也就是shared server/parallel query buffers/backup restore这几个操作会使用到large pool.在该案例中很明显的可以看到是因为shared server进程需要分配large pool中一部分空间,而没有连续空间从而出现该错误.数据库相关参数配置

SQL> show parameter mts;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mts_circuits                         integer     555
mts_dispatchers                      string      (PROTOCOL=TCP) (SERVICE=oraXDB)
mts_listener_address                 string
mts_max_dispatchers                  integer     5
mts_max_servers                      integer     20
mts_multiple_listeners               boolean     FALSE
mts_servers                          integer     1
mts_service                          string      ora
mts_sessions                         integer     550
SQL> show parameter large;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 16777216

这里可以看出来,数据库明显配置了MTS,因为数据库在启动时候,最少会建立一个shared server进程,而这个时候因为large pool太小(16M),导致该进程无法正常建立,从而出现上述alert中相关错误,临时处理方法增加large pool.后续需要关注业务特点,考虑是否可以采用Oracle Dedicated server模式来处理.

在哪些情况下,数据库不启动附加日志不能正常的被Logminer捕获到的,这里做了一个简单的测试,说明在不启用附加日志的情况下,很多操作不能被捕获,不仅仅是行迁移的数据记录.当然本实验仅供参考,因为在不同的数据库版本,不同的平台,甚至不同的操作都可能出现不同的结果.如果想要数据库日志通过Logminer获得较为完整的sql语句,强烈建议打开附加日志(当然会产生多一点日志,可能增加磁盘io的负担,凡事都有两面性,则其善而从之)
数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

插入数据

--为了减少测试redo影响,切换归档日志
SQL> alter system switch logfile;
System altered.
SQL> show user;
USER is "CHF"
--当前scn
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949934814
--当前redo logfile
SQL> select member from v$logfile where group# in(
  2  select group# from v$log where status='CURRENT');
MEMBER
------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--创建测试表并插入数据
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949934864
--数据存储的datafile 和blocknum
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
  2  dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
        ID ROWID                FILE_NUM  BLOCK_NUM
---------- ------------------ ---------- ----------
         1 AAAMuvAAJAAAAmkAAA          9       2468
         2 AAAMuvAAJAAAAmkAAB          9       2468
         3 AAAMuvAAJAAAAmkAAC          9       2468
--dump数据块
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock内容
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.3ff09  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.019.0000010d  0x00800b85.0111.2f  --U-    3  fsc 0x0000.0003ff0e
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
data_block_dump,data header at 0xcf6c464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0cf6c464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f6b
avsp=0x1f53
tosp=0x1f53
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f89
0x14:pri[1]     offs=0x1f7a
0x16:pri[2]     offs=0x1f6b
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
tab 0, row 2, @0x1f6b
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 8]  58 69 46 65 6e 46 65 69
--可以清楚的看到这三条记录都存在一个数据块中,并未发生行迁移等情况
--dump redo log
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
  2    scn min 42949934814 scn max 42949934864;
System altered.
--dump redo logfile内容
CHANGE #9 TYP:0 CLS: 4 AFN:9 DBA:0x024009a3 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:13.28
Low HWM
      Highwater::  0x024009a9  ext#: 0      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 5
  mapblk  0x00000000  offset: 0
lfdba:  0x024009a1 CHANGE #10 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0008.019.0000010d    uba: 0x00800b85.0111.2d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
CHANGE #11 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.0003fe8f SEQ:  1 OP:5.2
ktudh redo: slt: 0x0019 sqn: 0x0000010d flg: 0x0012 siz: 108 fbi: 0
            uba: 0x00800b85.0111.2d    pxid:  0x0000.000.00000000
CHANGE #12 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  2 OP:11.2
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800b85.0111.2e
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
CHANGE #13 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  3 OP:11.2
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800b85.0111.2f
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [ 8]  58 69 46 65 6e 46 65 69
--这里可以看到，只有redo的信息，没有太多undo信息(因为是插入数据)
--使用Logminer
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
TABLE_NAME                       SQL_REDO
-------------------------------- -------------------------------------------------------
XIFENFEI                         create table xifenfei(id number,name varchar2(4000));
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--这里可以明确的看到,Logminer没有找到任何关于这个表的dml操作,也就是说三条insert都没有被找到

说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句

简单更新操作

SQL> alter system switch logfile;
System altered.
SQL> conn chf/xifenfei
Connected.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949941538
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
---------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949941552
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.4192a  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.019.0000010d  0x00800b85.0111.2f  C---    0  scn 0x000a.0003ff0e
0x02   0x0003.004.0000014e  0x0080002b.01a6.3b  --U-    3  fsc 0x0000.0004192d
data_block_dump,data header at 0xdf83464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0df83464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f3d
tosp=0x1f3d
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f54
0x14:pri[1]     offs=0x1f3d
0x16:pri[2]     offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f54
tl: 23 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f3d
tl: 23 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
tab 0, row 2, @0x1f28
tl: 21 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 04
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
  2    scn min 42949941538 scn max 42949941552;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000013.00000002.0010 LEN: 0x0408 VLD: 0x0d
SCN: 0x000a.0004192d SUBSCN:  1 09/26/2012 23:31:27
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  5 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0003.004.0000014e    uba: 0x0080002b.01a6.39
Block cleanout record, scn:  0x000a.0004192a ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 251
ncol: 2 nnew: 1 size: 8
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
CHANGE #2 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.00041781 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0004 sqn: 0x0000014e flg: 0x0012 siz: 136 fbi: 0
            uba: 0x0080002b.01a6.39    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  1 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 8
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #4 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  2 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3b
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 6
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
--OP:11.5 Update Row Piece
--包含了修改后的值(后镜像)
CHANGE #5 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.0004192d SEQ:  1 OP:5.4
ktucm redo: slt: 0x0004 sqn: 0x0000014e srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080002b.01a6.3b ext: 0 spc: 416 fbi: 0
--OP:5.4  Commit transaction (transaction table update)
CHANGE #6 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.00041780 SEQ:  2 OP:5.1
ktudb redo: siz: 136 spc: 750 flg: 0x0012 seq: 0x01a6 rec: 0x39
            xid:  0x0003.004.0000014e
ktubl redo: slt: 4 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x0080002b.01a6.37
prev ctl max cmt scn:  0x000a.00040ff1  prev tx cmt scn:  0x000a.00041076
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8391493  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 251
ncol: 2 nnew: 1 size: -8
col  1: [ 8]  78 69 66 65 6e 66 65 69
CHANGE #7 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ:  1 OP:5.1
ktudb redo: siz: 96 spc: 612 flg: 0x0022 seq: 0x01a6 rec: 0x3a
            xid:  0x0003.004.0000014e
ktubu redo: slt: 4 rci: 57 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.39
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -8
col  1: [ 8]  58 49 46 45 4e 46 45 49
CHANGE #8 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ:  2 OP:5.1
ktudb redo: siz: 96 spc: 514 flg: 0x0022 seq: 0x01a6 rec: 0x3b
            xid:  0x0003.004.0000014e
ktubu redo: slt: 4 rci: 58 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -6
col  1: [ 8]  58 69 46 65 6e 46 65 69
--OP:5.1 Undo block or undo segment header
--包含了前镜像(修改前的值,其实就是undo中记录)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
no rows selected
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--Logminer无任何记录，证明没有被捕获到

说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句

行迁移情况

SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949943145
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--制造行迁移
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949943162
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
  2  dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
        ID ROWID                FILE_NUM  BLOCK_NUM
---------- ------------------ ---------- ----------
         1 AAAMuvAAJAAAAmkAAA          9       2468
         2 AAAMuvAAJAAAAmkAAB          9       2468
         3 AAAMuvAAJAAAAmkAAC          9       2468
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump datablock
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.41f6e  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.41  --U-    3  fsc 0x000c.00041f78
0x02   0x0003.004.0000014e  0x0080002b.01a6.3b  C---    0  scn 0x000a.0004192d
data_block_dump,data header at 0xec3f464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0ec3f464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x19
tosp=0x25
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0xfef
0x14:pri[1]     offs=0x31
0x16:pri[2]     offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 4009 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
tab 0, row 1, @0x31
tl: 4009 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
tab 0, row 2, @0xfda
tl: 9 fb: --H----- lb: 0x1  cc: 0
nrid:  0x024009a8.0    <--发生行迁移，指向下一个迁移数据块
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2468 maxblk 2468
--找到下个数据块的block num
SQL> select to_number('9a8','xxxxx') from dual;
TO_NUMBER('9A8','XXXXX')
------------------------
                    2472
SQL> alter system dump datafile 9 block 2472;
System altered.
Block header dump:  0x024009a8
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.3ff09  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.40  --U-    1  fsc 0x0000.00041f78
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
data_block_dump,data header at 0xec3f47c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ec3f47c
bdba: 0x024009a8
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0xfbd
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 4015 fb: ----FL-- lb: 0x1  cc: 2
hrid: 0x024009a4.2   <--迁移对应的起点数据块
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2472 maxblk 2472
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
  2    scn min 42949943145 scn max 42949943162;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000015.00000002.0010 LEN: 0x1180 VLD: 0x0d
SCN: 0x000a.00041f6e SUBSCN:  1 09/27/2012 00:36:34
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ:  4 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0008.02f.00000113    uba: 0x00800085.011b.3d
Block cleanout record, scn:  0x000a.00041f6e ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x000a.0004192d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 174
ncol: 2 nnew: 1 size: 3986
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
CHANGE #2 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.00041ebd SEQ:  1 OP:5.2
ktudh redo: slt: 0x002f sqn: 0x00000113 flg: 0x0012 siz: 168 fbi: 0
            uba: 0x00800085.011b.3d    pxid:  0x0000.000.00000000
--OP:5.2 Update rollback segment header
CHANGE #3 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041ebc SEQ:  1 OP:5.1
ktudb redo: siz: 168 spc: 862 flg: 0x0012 seq: 0x011b rec: 0x3d
            xid:  0x0008.02f.00000113
ktubl redo: slt: 47 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800085.011b.3c
prev ctl max cmt scn:  0x000a.000416cd  prev tx cmt scn:  0x000a.000416d0
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8388734  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0008.019.0000010d uba: 0x00800b85.0111.2f
                      flg: C---    lkc:  0     scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 174
ncol: 2 nnew: 1 size: -3986
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
REDO RECORD - Thread:1 RBA: 0x000015.0000000c.0010 LEN: 0x10d4 VLD: 0x05
SCN: 0x000a.00041f71 SUBSCN:  1 09/27/2012 00:36:40
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f6e SEQ:  1 OP:5.1
ktudb redo: siz: 104 spc: 692 flg: 0x0022 seq: 0x011b rec: 0x3e
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 61 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -3986
col  1: [16]  57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f6e SEQ:  1 OP:11.5
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 12
ncol: 2 nnew: 1 size: 3986
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
REDO RECORD - Thread:1 RBA: 0x000015.00000015.010c LEN: 0x1098 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN:  2 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ:  1 OP:5.1
ktudb redo: siz: 60 spc: 516 flg: 0x0022 seq: 0x011b rec: 0x40
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 63 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.0003ff0e SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0008.02f.00000113    uba: 0x00800085.011b.40
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 1  ispac: 24  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x1  cc: 2   <--这里没有H表明是发生了行迁移过来的记录(对应的flag可以转化为0x0c)
hrid: 0x024009a4.2
null: --
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
REDO RECORD - Thread:1 RBA: 0x000015.0000001e.0078 LEN: 0x0124 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN:  3 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ:  2 OP:5.1
ktudb redo: siz: 124 spc: 454 flg: 0x0022 seq: 0x011b rec: 0x41
            xid:  0x0008.02f.00000113
ktubu redo: slt: 47 rci: 64 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.3f
KDO Op code: ORP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 21
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 04
col  1: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f74 SEQ:  1 OP:11.6
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800085.011b.41
KDO Op code: ORP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x1  cc: 0  <--这里可以看到对应的块只有header信息无L,也就是发生了行迁移
nrid:  0x024009a8.0   <--通block dump说明
null:
--OP:11.6 Overwrite Row Piece
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
SQL> col sql_redo for a80
SQL> col TABLE_NAME for a15
SQL> set lines 134
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where scn>=42949943145 and scn<=42949943162;
TABLE_NAME      SQL_REDO
--------------- --------------------------------------------------------------------------------
XIFENFEI        update "CHF"."XIFENFEI" set "NAME" = 'XXXX……XXXXX' where "NAME" = 'WWW.XIFENFEI.COM'
                and ROWID = 'AAAMuvAAJAAAAmkAAB';
XIFENFEI        Unsupported
XIFENFEI        Unsupported
XIFENFEI        update "CHF"."XIFENFEI" set "ID" = NULL, "NAME" = NULL where "ID" = '3' and "NAM
                E" = 'www.orasos.com' and ROWID = 'AAAMuvAAJAAAAmkAAC';
                commit;
7 rows selected.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--获得了第二条记录(第一条没有任何记录,第三条因为行迁移,所以出现了update更新相关列为null,从而没有被Logminer正在的捕获)

说明：1)在发生行迁移之时,Logminer不能获得正常的sql语句,而是直接提示Unsupported;2)不发生行迁移也不一定能够获得update语句

删除操作

SQL> conn chf/xifenfei
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949953508
SQL> select member from v$logfile where group# in(
  2  select group# from v$log
  3  where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949953524
--原始数据所在block
SQL> alter system dump datafile 9 block 2468;
System altered.
--发生行迁移的block
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump block 内容
Block header dump:  0x024009a4
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.447e9  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.41  C---    0  scn 0x000a.00041f78
0x02   0x0004.014.000000da  0x00800398.00bf.02  --U-    3  fsc 0x1f55.000447f2
data_block_dump,data header at 0xdcb9464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcb9464
bdba: 0x024009a4
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x25
tosp=0x1f80
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0xfef
0x14:pri[1]     offs=0x31
0x16:pri[2]     offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x31
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0xfda
tl: 2 fb: --HD---- lb: 0x2
Block header dump:  0x024009a8
 Object id on Block? Y
 seg/obj: 0xcbaf  csc: 0x0a.447ef  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24009a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02f.00000113  0x00800085.011b.40  C---    0  scn 0x000a.00041f78
0x02   0x0004.014.000000da  0x00800399.00bf.01  --U-    1  fsc 0x0fad.000447f2
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
data_block_dump,data header at 0xdcb947c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0dcb947c
bdba: 0x024009a8
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0x1f6c
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 2 fb: ---DFL-- lb: 0x2
end_of_block_dump
--通过数据块dump证明，记录确实已经被删除
--dump redo logfile
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
  2    scn min 42949953508 scn max 42949953524;
System altered.
--dump redolog
REDO RECORD - Thread:1 RBA: 0x000016.0000000b.0010 LEN: 0x1170 VLD: 0x0d
SCN: 0x000a.000447e9 SUBSCN:  1 09/27/2012 20:03:36
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f78 SEQ:  1 OP:11.3
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0004.014.000000da    uba: 0x00800397.00bf.06
Block cleanout record, scn:  0x000a.000447e9 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP:11.3 Drop Row Piece
CHANGE #2 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ae SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x000000da flg: 0x0012 siz: 4172 fbi: 0
            uba: 0x00800397.00bf.06    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:24 AFN:2 DBA:0x00800397 OBJ:4294967295 SCN:0x000a.000447ad SEQ:  1 OP:5.1
ktudb redo: siz: 4172 spc: 5804 flg: 0x0012 seq: 0x00bf rec: 0x06
            xid:  0x0004.014.000000da
ktubl redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800397.00bf.05
prev ctl max cmt scn:  0x000a.00043852  prev tx cmt scn:  0x000a.00043862
txn start scn:  0x0000.00000000  logon user: 59  prev brb: 8389522  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0003.004.0000014e uba: 0x0080002b.01a6.3b
                      flg: C---    lkc:  0     scn: 0x000a.0004192d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4009
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [4000]
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
REDO RECORD - Thread:1 RBA: 0x000016.00000014.0010 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447e9 SUBSCN:  1 09/27/2012 20:03:36
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.00041f74 SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 3
REDO RECORD - Thread:1 RBA: 0x000016.00000015.0010 LEN: 0x1100 VLD: 0x05
SCN: 0x000a.000447ed SUBSCN:  1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447e9 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4108 fbi: 80
            uba: 0x00800398.00bf.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ec SEQ:  1 OP:5.1
ktudb redo: siz: 4108 spc: 1630 flg: 0x000a seq: 0x00bf rec: 0x01
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00800397
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800397.00bf.06
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 4009
fb: --H-FL-- lb: 0x0  cc: 2
null: --
col  0: [ 2]  c1 03
col  1: [4000]
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447e9 SEQ:  1 OP:11.3
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.01
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)
REDO RECORD - Thread:1 RBA: 0x000016.0000001d.0190 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447ed SUBSCN:  1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447e9 SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 5
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0010 LEN: 0x0118 VLD: 0x05
SCN: 0x000a.000447ee SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ed SEQ:  1 OP:5.1
ktudb redo: siz: 96 spc: 4040 flg: 0x0022 seq: 0x00bf rec: 0x02
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 1 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.01
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x0  cc: 0
nrid:  0x024009a8.0
null:
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447ed SEQ:  1 OP:11.3
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800398.00bf.02
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a4  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 2(0x2)
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0128 LEN: 0x10fc VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ed SEQ:  1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4100 fbi: 84
            uba: 0x00800399.00bf.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800399 OBJ:4294967295 SCN:0x000a.000447ef SEQ:  1 OP:5.1
ktudb redo: siz: 4100 spc: 3942 flg: 0x000a seq: 0x00bf rec: 0x01
            xid:  0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00800398
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x0  cc: 2
hrid: 0x024009a4.2
null: --
col  0: [ 2]  c1 04
col  1: [4000]
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
CHANGE #3 TYP:2 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.00041f78 SEQ:  1 OP:11.3
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0004.014.000000da    uba: 0x00800399.00bf.01
Block cleanout record, scn:  0x000a.000447ef ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x024009a8  hdba: 0x024009a3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)
REDO RECORD - Thread:1 RBA: 0x000016.00000027.00b4 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN:  1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447ed SEQ:  1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 7 newstate: 5
--可以看到redo部分没有太多记录,而undo部分的信息比较全(因为是delete操作)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XXXXXXXXXXXXXXXXXXXX
…………
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' and ROWID = 'AAAMu
vAAJAAAAmkAAB';
Unsupported
Unsupported
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--也只是捕获了第二条记录,第一条无任何信息,第三条因为行迁移所以提示Unsupported

说明:我们可以看到对于delete操作,有部分不能被Logminer正常捕获,行迁移的直接提示Unsupported

启用数据库附加日志

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949959788
SQL> select member from v$logfile where group# in(
  2  select group# from v$log where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo02.log
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
   42949959845
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
create table xifenfei(id number,name varchar2(4000));
insert into "CHF"."XIFENFEI"("ID","NAME") values ('1','xifenfei');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('2','XIFENFEI');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('3','XiFenFei');
update "CHF"."XIFENFEI" set "NAME" = 'www.xifenfei.com' where "NAME" = 'xifenfei
' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'WWW.XIFENFEI.COM' where "NAME" = 'XIFENFEI
' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "NAME" = 'www.orasos.com' where "NAME" = 'XiFenFei'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
update "CHF"."XIFENFEI" set "NAME" = 'FFFFFFF…………FFFF' where
"NAME" = 'www.xifenfei.com' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'XXXXXXXXX…………XX' where
"NAME" = 'WWW.XIFENFEI.COM' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "ID" = '3', "NAME" = 'CCCCC…………CCCCCCCCC'
where "ID" = '3' and "NAME"= 'www.orasos.com' and ROWID = 'AAAMwEAAJAAAAmkAAC';
delete from "CHF"."XIFENFEI" where "ID" = '1' and "NAME" = 'FFFFFF…………FF'
and ROWID = 'AAAMwEAAJAAAAmkAAA';
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XX…………XXX'
and ROWID = 'AAAMwEAAJAAAAmkAAB';
delete from "CHF"."XIFENFEI" where "ID" = '3' and "NAME" = 'CCCCCCCC…………CCC'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
19 rows selected.

测试证明,启动附加日志后,数据库的相关操作都能够捕获,包括行迁移

使用plsql抢救数据

“在oracle出现ORA-8103/ORA-1578/ORA-376″等情况下抢救数据的争论没有停止过,很多人想到的是使用bbed,dul等工具来抢救,其实在很多时候我们使用pl/sql也可以完美的抢救数据.在这里我们通过模拟ORA-8103错误，然后使用plsql来找回数据.这中处理方法相对于bbed风险小,但是缺点是如果数据量大处理时间可能比较长,可能比dul有的一比,但是dul的工具不是任何人都有的.所以整体来说,在大部分情况下,这种方法处理某个数据块错误,抢救某个对象数据,还是很好的方法.
1.有非空列index情况

--创建测试表
SQL> create table xifenfei
  2  as
  3  select * from dba_objects;
Table created.
--修改某个项为非空值
SQL> alter table xifenfei modify object_id not null;
Table altered.
--创建一个唯一index
SQL> create unique index ind_xifenfei  on xifenfei(object_id);
Index created.
--表总记录
SQL> select count(*) from xifenfei;
  COUNT(*)
----------
     50088
--extent的分布情况
SQL> set pages 100
SQL>  select file_id,block_id,block_id+blocks-1
  2    from dba_extents
  3   where segment_name ='XIFENFEI' AND owner='CHF';
   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         9       1545              1552
         9       1553              1560
         9       1561              1568
         9       1569              1576
         9       1577              1584
         9       1585              1592
         9       1593              1600
         9       1601              1608
         9       1609              1616
         9       1617              1624
         9       1625              1632
         9       1633              1640
         9       1641              1648
         9       1649              1656
         9       1657              1664
         9       1665              1672
         9       1673              1800
         9       1801              1928
         9       1929              2056
         9       2057              2184
         9       2185              2312
21 rows selected.
--2200数据块包含记录
SQL> select   count(*)
  2  from chf.xifenfei where dbms_rowid.rowid_block_number(rowid)=2200;
  COUNT(*)
----------
        69
--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf  bs=8192  count=1 seek=2200 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000151554 seconds, 54.1 MB/s
--启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
--查询结果
SQL>  select /*+ full(xifenfei) */ count(*) from chf.xifenfei;
 select /*+ full(xifenfei) */ count(*) from chf.xifenfei
                                                *
ERROR at line 1:
ORA-08103: object no longer exists
SQL> create table chf.xifenfei_new
  2  as
  3  select * from chf.xifenfei;
select * from chf.xifenfei
                  *
ERROR at line 3:
ORA-08103: object no longer exists
--创建备份表
SQL> create table chf.xifenfei_new
  2  as
  3  select * from chf.xifenfei where 1=0;
Table created.
--创建坏块相关rowid记录表
SQL> create table chf.bad_rows (row_id rowid, oracle_error_code number);
Table created.
--执行plsql脚本
DECLARE
 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
 CURSOR c1 IS  select /*+ index(xifenfei ind_xifenfei) */ rowid
 from chf.xifenfei
 where object_id is NOT NULL;
 r RowIDTab;
 rows  NATURAL := 20000;
 bad_rows number := 0 ;
 errors number;
 error_code number;
 myrowid rowid;
BEGIN
 OPEN c1;
 LOOP
   FETCH  c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into chf.xifenfei_new
     select /*+ ROWID(A) */ *
     from chf.xifenfei A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
       error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
       if error_code in (1410, 8103) then
         myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
         bad_rows := bad_rows + 1;
         insert into chf.bad_rows values(myrowid, error_code);
       else
         raise;
       end if;
     END LOOP;
     END;
   END;
  commit;
 END LOOP;
 commit;
 CLOSE c1;
 dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
--查询错误记录
SQL> select count(*) from chf.bad_rows ;
  COUNT(*)
----------
        69
SQL> select * from chf.bad_rows where rownum<10;
ROW_ID             ORACLE_ERROR_CODE
------------------ -----------------
AAAMugAAJAAAAiYAAA              8103
AAAMugAAJAAAAiYAAB              8103
AAAMugAAJAAAAiYAAC              8103
AAAMugAAJAAAAiYAAD              8103
AAAMugAAJAAAAiYAAE              8103
AAAMugAAJAAAAiYAAF              8103
AAAMugAAJAAAAiYAAG              8103
AAAMugAAJAAAAiYAAH              8103
AAAMugAAJAAAAiYAAI              8103
9 rows selected.
--查询备份表记录
SQL> select count(*) from chf.xifenfei_new;
  COUNT(*)
----------
     50019
50088-50019=69和被破坏块中记录一致,证明所有好块中记录全部被找回来

2.无非空列index情况

--创建表
SQL> CONN CHF/XIFENFEI
Connected.
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects;
Table created.
--表中记录总数
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     50086
--extent分布
SQL> SET PAGES 100
SQL>  select file_id,block_id,block_id+blocks-1
  2    from dba_extents
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';
   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         9          9                16
         9         17                24
         9         25                32
         9         33                40
         9         41                48
         9         49                56
         9         57                64
         9         65                72
         9         73                80
         9         81                88
         9         89                96
         9         97               104
         9        105               112
         9        113               120
         9        121               128
         9        129               136
         9        137               264
         9        265               392
         9        393               520
         9        521               648
         9        649               776
21 rows selected.
--700数据块中记录数
SQL> select   count(*)
  2  from chf.t_xifenfei where dbms_rowid.rowid_block_number(rowid)=700;
  COUNT(*)
----------
        73
--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏block 700的数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf  bs=8192  count=1 seek=700 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000156576 seconds, 52.3 MB/s
--启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
--查询报错
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-08103: object no longer exists
--创建备份表
SQL> CREATE TABLE T_XIFENFEI_NEW
  2  AS
  3  SELECT * FROM T_XIFENFEI WHERE 1=0;
--找回记录
set serveroutput on
set concat off
DECLARE
 nrows number;
 rid rowid;
 dobj number;
 ROWSPERBLOCK number;
BEGIN
 ROWSPERBLOCK:=1000;  --估算最大的一个块中记录条数
 nrows:=0;
 select data_object_id  into dobj
 from dba_objects
 where owner = 'CHF'
 and object_name = 'T_XIFENFEI'
-- and subobject_name = '<table partition>'  Add this condition if table is partitioned
 ;
 for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
           from dba_extents
           where owner = 'CHF'
             and segment_name = 'T_XIFENFEI'
-- and partition_name = '<table partition>' Add this condition if table is partitioned
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
          order by extent_id)
 loop
   for br in i.block_id..i.totblocks loop
    for j in 1..ROWSPERBLOCK loop
    begin
      rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
      insert into CHF.T_XIFENFEI_NEW
      select /*+ ROWID(A) */ *
      from CHF.T_XIFENFEI A
      where rowid = rid;
      if sql%rowcount = 1 then nrows:=nrows+1; end if;
      if (mod(nrows,10000)=0) then commit; end if;
    exception when others then null;
    end;
    end loop;
  end loop;
 end loop;
 COMMIT;
 dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/
--找回记录数
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI_NEW;
  COUNT(*)
----------
     50013
50086-50013=73  证明非坏块中的数据都被完全寻找回来

参考：
ORA-8103 Troubleshooting, Diagnostic and Solution [ID 268302.1]
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS [ID 422547.1]

《前世今生的轮回》—歌词改写—《专一爱》

转载朋友改写《前世今生的轮回》为《专一爱》的歌词
前世今生的轮回
=================================
第1章：相遇
——————————————
挥手之间，我就爱上了你
把这份爱牢牢的，放在心里
海誓山盟，的爱，也给了你
把你的样子留在了，脑海里
时间飞逝，爱的那么甜蜜
鱼和猫眯，从此也不再对立
感动上天，与你相偎相依
今生的相遇，不，会，轻，言，分离

第2章：相识
——————————————
前世的轮回，注定了爱你
奈何桥上等着你
梦婆的汤里，留下了回忆
下辈子你能否记的起
心中的秘密，注定是天意
就像牛郎和织女
好好的爱你，乞求天的旨意
我们这辈子要在一起

第3章：相谈
——————————————
一世的情缘，我拥有了你，
把这份甜蜜好好的，去珍，惜
沧海桑田，的爱带给了你，
把你的纯洁，留给了你自己
时间飞逝，爱的那么彻底
老鼠大米推了世俗，在，一起
感动上天，与你经历风雨
今生的相遇，不会，随便，放弃

第4章：相思
——————————————
爱你在心里，没人能代替
把你拥在我怀里
感受你的气息，体会你的爱意
真的真的不能没有你
想你在梦里，念你在心底
失去你，我不愿意
把你的甜蜜，抓在我的手里
会用一生好好的陪你

第5章：相知
——————————————
挥手之间，我们在了一起
彼此的分离有了，心的相聚
罗曼蒂克，式的爱情洗礼
把我们心真正，牵随，在一起
时间飞逝，爱的那么清晰
蜜蜂与蜂蜜，永远不会离弃
感动上天，死心塌地爱你
今生的相遇，注定了，我，爱，你

第6章：相别
——————————————
今世的轮回，爱了你无悔
下辈子还要相会
相思的美味，梦里的相对
来世要紧紧，的跟随
蝴蝶空中飞，述说的很美
感动了月老，的体会
来生的相会，红线来牵随

第7章：结局
————————
生生世世与你一起轮回
生生世世与你一起轮回

专一爱
======================================
第1章：相遇
——————————————
那天下午，我们不期而遇
听这个暖洋洋的，浪漫歌曲
点点滴滴，回忆，那么有趣
把这个午后打扮成，文艺剧
三生石上，刻着你的名字
千山过尽，有缘千里来相聚
红尘擦肩，有你无所畏惧
今生的相遇，你，是，我，的，伴侣

第2章：相识
——————————————
时间的左右，再次的相遇
沧海水边听一曲
巫山的云上，写下了一句
这辈子我要以身相许
难得有情郎，注定是伴侣
就像牛郎和织女
好好的爱你，送你春光和煦
月上了梢头相思屡屡

第3章：相谈
——————————————
风云的流转，白头不相离，
把这份甜蜜好好的，去继，续
惊世不凡，的情无需根据，
幸福里享受，化作相思一缕
日影如飞，带着太多情趣，
此情绵绵守候在你，人，生旅
花香满园，拥抱旷世细雨
风情的款款，他日，还再，相聚

第4章：相思
——————————————
微笑里感动，寄卿又一曲
不问曲终人散聚
感受你的气息，体会你的爱意
真的真的不能没有你
必经的路上，幸福和情趣
遍地是，情思缕缕
多年的以后，还要与你耳语
会用一生好好的陪你

第5章：相知
——————————————
这天下午，我们谈着嫁娶
彼此的心中有了，点点犹豫
习惯想念，你的字字句句
把甜蜜事刻在，这首，老歌曲
人约黄昏，声音那么犹豫
人情和情人，奇妙感觉些许
天长地久，死心塌地赢取
今生的相遇，全部是，你，步，履

第6章：相别
——————————————
相思无尽处，突然的离去
生活也变得无趣，
一世的光阴，何日再相聚
留下这片片，的思虑
朦胧的月光，低叹着情绪
守候着往日，的歌曲
往后的岁月，注定难继续

第7章：结局
——————————————
倦鸟回巢伴着衣衫褴褛
倦鸟回巢伴着衣衫褴褛

密码保护：dul抽取异常asm文件

ORA-00600[kccpb_sanity_check_2]

alert日志出现如下错误
数据库在mount的时候，因为出现ORA-00600[kccpb_sanity_check_2]错误导致数据库不能正常被mount成功

--sqlplus中报错
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  2273256 bytes
Variable Size            1486573592 bytes
Database Buffers         2801795072 bytes
Redo Buffers                4325376 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-600: internal error code, arguments: [kccpb_sanity_check_2], [2825],
[2824], [0x000000000], [], [], [], []
--alert日志
Mon Sep 24 16:35:37 2012
ALTER DATABASE   MOUNT
Mon Sep 24 16:35:41 2012
Errors in file /opt/app/oracle/admin/lhgk/udump/lhgk_ora_17034.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [2825], [2824], [0x000000000], [], [], [], []
Mon Sep 24 16:35:41 2012
ORA-600 signalled during: ALTER DATABASE   MOUNT...

错误原因

ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf
transaction.

解决方法

1) restore a backup of a controlfile and recover
OR
2) recreate the controlfile
OR
3) restore the database from last good backup and recover
NOTE:  If you do not have any special backup of control file to restore and you are using Multiple Control File
copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one.
If you are able to mount the database with any of these control file copies you can then issue
'alter database backup controlfile to trace' to recreate controlfile.

在win中运行bbed程序

学习ORACLE三剑客:互联网,ORACLE资料,交流.今天在朋友的帮助下,了结了一个心结:在WIN平台中完美的使用bbed(8i/9i部分版本/10g 11g不支持),今天在朋友的帮助下解决了该问题
9i中bbed相关程序

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\XIFENFEI>cd E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win
C:\Users\XIFENFEI>e:
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>dir
 驱动器 E 中的卷没有标签。
 卷的序列号是 000C-3B41
 E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win 的目录
2012/09/24  19:45    <DIR>          .
2012/09/24  19:45    <DIR>          ..
2006/07/29  13:33           147,728 bbed.exe
2006/06/28  11:32            20,752 heteroxa9.dll
2004/10/26  16:35            49,152 kpp95rdr.dll
2004/10/26  16:35            45,056 kpp97rdr.dll
2012/09/24  19:49               250 log.bbd
2006/07/29  02:08           229,648 njssl9.dll
2006/06/28  11:33            61,712 ocijdbc9.dll
2002/04/26  16:22           733,184 oip9.dll
2006/07/29  14:02         1,589,520 oraclient9.dll
2006/07/29  15:25           602,384 oracommon9.dll
2006/06/28  09:54           651,536 ORACORE9.DLL
2006/06/27  08:44         1,736,976 oractxx9.dll
2006/07/29  14:02         2,691,344 orageneric9.dll
2006/07/29  13:56            24,576 oraimr9.dll
2006/07/29  15:19         3,014,656 orajox9.dll
2006/06/28  03:09           155,920 oraldapclnt9.dll
2006/06/28  03:09            24,848 oraldapjclnt9.dll
2006/07/29  02:18           741,648 oran9.dll
2006/07/29  02:10            41,232 oranad9.dll
2006/07/29  02:06            33,040 oranbeq9.dll
2006/07/29  02:08            24,848 ORANCDS9.DLL
2006/07/29  02:07            98,576 orancrypt9.dll
2006/07/29  02:07            33,040 ORANGSS9.DLL
2006/07/29  02:07            24,848 oranhost9.dll
2006/07/29  02:06            33,040 oranipc9.dll
2006/07/29  02:08           176,400 oranjni9.dll
2006/07/29  02:07           213,264 ORANK59.DLL
2006/07/29  02:06           180,496 oranl9.dll
2006/07/29  02:07            74,000 oranldap9.dll
2006/07/29  02:08            28,944 oranldapj9.dll
2006/06/28  06:32           499,984 ORANLS9.DLL
2006/07/29  02:06            33,040 orannmp9.dll
2006/07/29  02:07            53,520 orannts9.dll
2006/07/29  02:07            28,944 orannzentr9.dll
2006/07/29  02:08            28,944 ORANNZMCS9.DLL
2006/07/29  02:07           586,000 orannzsbb9.dll
2006/07/29  02:07            24,848 oranoname9.dll
2006/07/29  02:08            57,616 oranoncj9.dll
2006/07/29  02:07            45,328 ORANRAD9.DLL
2006/07/29  02:06           225,552 oranro9.dll
2006/07/29  02:08            41,232 oransgr9.dll
2006/07/29  02:06            49,424 orantcp9.dll
2006/07/29  02:07            53,520 ORANTCPS9.DLL
2006/07/29  02:07            28,944 orantns9.dll
2006/07/29  13:58            57,784 oraobjop9.dll
2006/07/29  14:58           311,568 oraocci9.dll
2006/07/29  14:02            24,848 oraodm9.dll
2006/06/26  19:19         2,314,240 oraolapapi9.dll
2006/07/29  13:58            57,784 oraolapop9.dll
2006/07/18  08:53           377,104 oraordim9.dll
2006/07/29  12:39            61,712 ORAPLC9.DLL
2006/07/29  12:40           909,584 ORAPLP9.DLL
2006/07/29  12:39         3,281,168 ORAPLS9.DLL
2006/07/29  13:58            57,784 ORAPRTOP9.DLL
2003/01/22  18:59            20,480 orarac9.dll
2002/04/26  17:18            28,944 ORASLAX9.DLL
2006/06/28  06:33            69,904 ORASNLS9.DLL
2006/06/28  16:04           483,600 ORASQL9.DLL
2002/04/29  14:04           246,032 oratrace9.dll
2002/04/29  14:04            86,288 oratracepls9.dll
2006/06/28  06:32            69,904 ORAUNLS9.DLL
2006/07/29  14:02            24,848 oravsn9.dll
2005/03/29  17:17            24,576 orawsec9.dll
2006/07/29  14:02            24,848 orawtc9.dll
2001/04/16  19:19           191,248 orawwg9.dll
2006/06/27  01:50           520,464 ORAXML9.DLL
2006/06/27  01:50            41,232 ORAXMLG9.DLL
2006/06/27  01:50           172,304 ORAXSD9.DLL
2010/06/29  05:38           181,560 vspp97.dll
2010/06/29  05:38            83,256 vsqp9.dll
2010/06/29  05:38           177,464 vsw97.dll
              72 个文件     25,234,522 字节
               2 个目录 58,747,236,352 可用字节

执行bbed程序报错

E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Message 112 not found; No message file for product=RDBMS, facility=BBED
BBED-00113: file not found

分析原因:因为在11g中ORACLE并没有在?/RDBMS/MESG/中提供bbedus.msb程序,导致bbed运行时候无法显示相关提示信息,从而出现类此该错误
解决方法:在11g环境中需要从10g库中拷贝bbedus.msb文件放置到?/RDBMS/MESG/中即可

运行bbed程序

E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Sep 24 19:45:40 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info all;
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
BBED> show all;
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       2048
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

密码保护：dul实现对数据文件内容更新

模拟普通ORA-08103并解决

在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei as
  2   select * from dba_objects where rownum<3000;
Table created.
SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
  2  FROM dba_extents
  3  WHERE segment_name='T_XIFENFEI' AND owner='CHF';
OWNER                          SEGMENT_NAME     EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF                            T_XIFENFEI               0          4         57          8
CHF                            T_XIFENFEI               1          4         65          8
CHF                            T_XIFENFEI               2          4         73          8
CHF                            T_XIFENFEI               3          4         81          8
CHF                            T_XIFENFEI               4          4         89          8
CHF                            T_XIFENFEI               5          4         97          8
6 rows selected.
SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
  2  dbms_rowid.rowid_relative_fno(rowid) file#
  3   FROM t_xifenfei
  4   ORDER BY 2,1;
      BLK#      FILE#
---------- ----------
        60          4
        61          4
        62          4
        63          4
        64          4
        65          4
        66          4
        67          4
        68          4
        69          4
        70          4
      BLK#      FILE#
---------- ----------
        71          4
        72          4
        74          4
        75          4
        76          4
        77          4
        78          4
        79          4
        80          4
        81          4
        82          4
      BLK#      FILE#
---------- ----------
        83          4
        84          4
        85          4
        86          4
        87          4
        88          4
        90          4
        91          4
        92          4
        93          4
        94          4
      BLK#      FILE#
---------- ----------
        95          4
        96          4
        97          4
        98          4
37 rows selected.

模拟ORA-08103

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s
SQL> STARTUP
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 104860124 bytes
Database Buffers 205520896 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
*
ERROR at line 1:
ORA-08103: object no longer exists
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
/u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50

测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作

找到出现ORA-08103数据块

SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL>  alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL>  alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
……………………
ERROR:
ORA-08103: object no longer exists
2700 rows selected.
--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f
SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;
TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
                            16777311
SQL> select
  2  dbms_utility.data_block_address_file(16777311) FILE_NO,
  3  dbms_utility.data_block_address_block(16777311) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4         95

bbed继续破坏异常块

BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/users01.dbf
BBED> set block 95
        BLOCK#          95
BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95                                    Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> set count 32
        COUNT           32
BBED> d
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets:    0 to   31           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 1
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> d offset 8180
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01010101
 <32 bytes per line>
BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000

测试修改为坏块效果

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
      2918

通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的

模拟极端ORA-08103并解决

ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei  as
  2   select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     74504
SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         75592      75592
SQL> select file_id,block_id,block_id+blocks-1
  2      from dba_extents
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';
   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4        680               687
         4        688               695
         4        696               703
         4        704               711  <---注意
         4        712               719
         4        720               727
         4        728               735
         …………
24 rows selected.
SQL> Select segment_name,header_file,header_blocK
  2  from dba_segments where
  3  segment_name in ('T_XIFENFEI') and owner='CHF'
  4  ;
SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI                               4          682
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 682;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc
--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000  [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000  [................]
B6CFD620 00000000 00000018 00000480 00000A9C  [................]
B6CFD630 00000017 0000003F 00000080 010007BF  [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F  [............?...]
B6CFD650 00000000 00000000 00000000 00000017  [................]
B6CFD660 0000003F 00000080 010007BF 00000000  [?...............]
B6CFD670 00000017 00000000 0000043F 01000780  [........?.......]
B6CFD680 01000780 00000000 00000000 00000000  [................]
B6CFD690 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434  [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781  [................]
B6CFD6F0 010002A9 00000000 00000000 00000000  [................]
B6CFD700 00000000 00000000 00000018 00000000  [................]
B6CFD710 00012748 10000000 010002A8 00000008  [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008  [................]
B6CFD730 010002C0 00000008 010002C8 00000008  [................]   <----dump中找到下面值
……
  Extent Map
  -----------------------------------------------------------------
   0x010002a8  length: 8
   0x010002b0  length: 8
   0x010002b8  length: 8
   0x010002c0  length: 8     <-----选择第四个exent
   0x010002c8  length: 8
   …………
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x010002a8 Data dba:  0x010002ab
   Extent 1     :  L1 dba:  0x010002a8 Data dba:  0x010002b0
   Extent 2     :  L1 dba:  0x010002b8 Data dba:  0x010002b9
   Extent 3     :  L1 dba:  0x010002b8 Data dba:  0x010002c0  <---同上
   Extent 4     :  L1 dba:  0x010002c8 Data dba:  0x010002c9
   Extent 5     :  L1 dba:  0x010002c8 Data dba:  0x010002d0
   …………
  --------------------------------------------------------
--确定Extent 3的记录在file 4 block 682 的偏移量为304
------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;
TO_NUMBER('730','XXXXX')
------------------------
                    1840
SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;
TO_NUMBER('600','XXX')
----------------------
                  1536
SQL> SELECT 1840-1536 FROM DUAL;
 1840-1536
----------
       304
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误

bbed修改相关值

[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
        MODE            Edit
BBED> INFO
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     4  /u01/oracle/oradata/ora11g/users01.dbf                               0
BBED> SET FILE 4 BLOCK 682
        FILE#           4
        BLOCK#          682
BBED> D
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 23a20000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> m /x 1100
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd

重现ORA-08103

SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             239078260 bytes
Database Buffers           67108864 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
ERROR at line 1:
ORA-08103: object no longer exists

定位坏块位置

SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
                  *
ERROR at line 1:
ORA-08103: object no longer exists
--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 9]  44 49 43 54 2e 42 41 53 45
col  1: [ 1]  32
col  2: [32]
…………
end_of_block_dump
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped
SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
                                  16777898
SQL> select
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4        682

检查坏块

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006
DBVERIFY - Verification complete
Total Pages Examined         : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 391
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 898278 (0.898278)
--对应块
SQL> select
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4        682

通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据

dul和odu常规操作

DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI
DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary
DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087
DUL: Error: While processing unknown file block# 1087
DUL: Error: Could not read/parse data block
ODU> unload table chf.t_xifenfei
Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
corrupted block
0 rows unloaded

通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据

扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能

ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2
scan extent start: 2012-9-23 2:47:51
scanning extent...
scanning extent finished.
scan extent completed: 2012-9-23 2:47:51
ODU> unload table chf.t_xifenfei object  75592
Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
74504 rows unloaded

试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决