large pool太小导致shared server异常

发表于 2012 年 10 月 6 日 由 惜分飞

数据库出现如下错误

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模式来处理.

发表在 ORA-xxxxx | 评论关闭

logminer相关操作验证

发表于 2012 年 10 月 5 日 由 惜分飞

在哪些情况下,数据库不启动附加日志不能正常的被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.

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

发表在 Oracle | 2 条评论

使用plsql抢救数据

发表于 2012 年 9 月 30 日 由 惜分飞

“在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]

发表在 Oracle备份恢复 | 2 条评论