Author Archives: 惜分飞

logminer相关操作验证

Posted on by

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

Posted on by

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

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

Posted on by

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

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

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

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

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

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

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

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

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

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

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


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

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

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

ORA-00600[kccpb_sanity_check_2]

Posted on by

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程序

Posted on by

学习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