1、数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production2、创建测试表
SQL> create table t as select rownum id , 'test' name from dual connect by level < 1000000;
Table created.
SQL> alter system flush buffer_cache ;
System altered.
SQL> select rowid,t.* from t where rownum=1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test
SQL>
SQL>
SQL> @rowid AAAWDkAABAAAXHhAAA
old 1: select rowid, dbms_rowid.rowid_relative_fno('&&1') file#, dbms_rowid.rowid_block_number('&&1') block from dual
new 1: select rowid, dbms_rowid.rowid_relative_fno('AAAWDkAABAAAXHhAAA') file#, dbms_rowid.rowid_block_number('AAAWDkAABAAAXHhAAA') block from dual
ROWID FILE# BLOCK
------------------ ---------- ----------
AAAAB0AABAAAAOhAAA 1 94689
SQL> 这里都以第一行数据来说明，第一行数据所在的块为1号文件94689块。
3、制造一个需要回滚的场景
会话1
SQL> update t set name = upper('name'); <====更新过程中，会话2abort数据库
update t set name = upper('name')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12865
Session ID: 18 Serial number: 41
SQL>
会话2
[oracle@QXY ~]$ o
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 16:00:14 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown abort;
ORACLE instance shut down.
SQL> 4、使用bbed查看T表的第一行数据的内容
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 1,94689
DBA 0x004171e1 (4288993 1,94689)
BBED> map
File: /u01/app/oradata/QXY/system01.dbf (1)
Block: 94689 Dba:0x004171e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @116
struct kdbt[1], 4 bytes @130
sb2 kdbr[524] @134
ub1 freespace[822] @1182
ub1 rowdata[6184] @2004
ub4 tailchk @8188
BBED> x /rnc *kdbr[0]
rowdata[6173] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: test <=====update的数据没有写盘，如果写盘的话为NAME
BBED> 5、启动数据库到mount，设置10513等待事件
[oracle@QXY ~]$ o
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 16:06:16 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 478154376 bytes
Database Buffers 289406976 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL> alter system set events '10513 trace name context forever,level 2';
System altered.
SQL> alter database open ;
Database altered.
SQL> 6、查看alert日志
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Nov 06 16:06:33 2020
OS Pid: 13121 executed alter system set events '10513 trace name context forever,level 2'
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 3080 KB redo, 202 data blocks need recovery
Started redo application at
Thread 1: logseq 257, block 5017
Recovery of Online Redo Log: Thread 1 Group 2 Seq 257 Reading mem 0
Mem# 0: /u01/app/oradata/QXY/redo02.log
Completed redo application of 2.66MB
Completed crash recovery at
Thread 1: logseq 257, block 11178, scn 3975618
202 data blocks read, 202 data blocks written, 3080 redo k-bytes read
Fri Nov 06 16:06:40 2020
Thread 1 advanced to log sequence 258 (thread open)
Thread 1 opened at log sequence 258
Current log# 3 seq# 258 mem# 0: /u01/app/oradata/QXY/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov 06 16:06:40 2020
SMON: enabling cache recovery
[13121] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:55950294 end:55950324 diff:30 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Nov 06 16:06:41 2020
QMNC started with pid=22, OS id=13127
Completed: alter database open
Fri Nov 06 16:06:41 2020
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Nov 06 16:06:41 2020
Starting background process CJQ0
Fri Nov 06 16:06:41 2020
CJQ0 started with pid=23, OS id=131417、启动数据库之后，再次使用bbed查看数据块的内容
BBED> set dba 1,94689
DBA 0x004171e1 (4288993 1,94689)
BBED> map
File: /u01/app/oradata/QXY/system01.dbf (1)
Block: 94689 Dba:0x004171e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @116
struct kdbt[1], 4 bytes @130
sb2 kdbr[524] @134 --//该块一共有524行数据
ub1 freespace[822] @1182
ub1 rowdata[6184] @2004
ub4 tailchk @8188
BBED> x /rnc *kdbr[0]
rowdata[6173] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: NAME <=======发现已经修改为NAME，说明已经前滚应用redo日志，但是事物没有提交，正常的情况值应该为test,说明事物没有回滚。
发现已经修改为NAME，说明已经前滚应用redo日志，但是事物没有提交，正常的情况值应该为test,说明事物没有回滚。
8、开启新会话，查看第一行数据
SQL> select rowid,t.* from t where rownum=1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test
SQL> 可以看到，虽然数据文件中块的数据为NAME，因为数据修改没有提交，所以查询的时候还是修改前的test内容
9、dump一行数据的的内容
SQL> @rowid AAAWDkAABAAAXHhAAA
old 1: select rowid, dbms_rowid.rowid_relative_fno('&&1') file#, dbms_rowid.rowid_block_number('&&1') block from dual
new 1: select rowid, dbms_rowid.rowid_relative_fno('AAAWDkAABAAAXHhAAA') file#, dbms_rowid.rowid_block_number('AAAWDkAABAAAXHhAAA') block from dual
ROWID FILE# BLOCK
------------------ ---------- ----------
AAAAB0AABAAAAOhAAA 1 94689
SQL>
SQL> alter system dump datafile 1 block 94689;
System altered.
SQL>
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13204.trc
SQL>
--//undo数据
Block header dump: 0x004171e1
Object id on Block? Y
seg/obj: 0x160e4 csc: 0x00.3c09ba itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.003c09ba
0x02 0x0008.01e.00000fa0 0x00c004df.024f.12 ---- 524 fsc 0x0000.00000000 --Lck=524，说明该事物ITL上面有524行被修改，
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x004171e1
data_block_dump,data header at 0x7f8d57e46a74
===============
tsiz: 0x1f88
hsiz: 0x42a
pbl: 0x7f8d57e46a74
76543210
flag=--------
ntab=1
nrow=524
frre=-1
fsbo=0x42a
--//其中第二行ITL如下：
0x02 0x0008.01e.00000fa0 0x00c004df.024f.12 ---- 524 fsc 0x0000.00000000
--Lck=524，说明该事物ITL上面对应有524行被修改
0x00c004df.024f.12 为undo的信息，也就是数据被修改前的数据，其中0x00c004df为undo的地址，024f为seq，wrap次数
10、转换undo数据信息
SQL>
SQL> @ton 00c004df
old 1: select to_number(replace('&1',' ',''),'xxxxxxxx') from dual
new 1: select to_number(replace('00c004df',' ',''),'xxxxxxxx') from dual
TO_NUMBER(REPLACE('00C004DF','',''),'XXXXXXXX')
-----------------------------------------------
12584159
SQL> @tof 12584159
old 1: select dbms_utility.data_block_address_file('&1') as "file", dbms_utility.data_block_address_block('&1') as "block" from dual
new 1: select dbms_utility.data_block_address_file('12584159') as "file", dbms_utility.data_block_address_block('12584159') as "block" from dual
file block
---------- ----------
3 1247
SQL>
SQL> alter system dump datafile 3 block 1247;
System altered.
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13288.trc
SQL>
--//undo块信息
********************************************************************************
UNDO BLK:
xid: 0x0008.01e.00000fa0 seq: 0x24f cnt: 0x45 irb: 0x45 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f74 0x02 0x1f00 0x03 0x1e8c 0x04 0x1e18 0x05 0x1da4
0x06 0x1d30 0x07 0x1cbc 0x08 0x1c48 0x09 0x1bd4 0x0a 0x1b60
0x0b 0x1aec 0x0c 0x1a78 0x0d 0x1a04 0x0e 0x1990 0x0f 0x191c
0x10 0x18a8 0x11 0x1834 0x12 0x17c0 0x13 0x1754 0x14 0x16e0
0x15 0x166c 0x16 0x15f8 0x17 0x1584 0x18 0x1510 0x19 0x149c
0x1a 0x1428 0x1b 0x13b4 0x1c 0x1340 0x1d 0x12cc 0x1e 0x1258
0x1f 0x11e4 0x20 0x1170 0x21 0x10fc 0x22 0x1088 0x23 0x1014
0x24 0x0fa0 0x25 0x0f2c 0x26 0x0eb8 0x27 0x0e44 0x28 0x0dd0
0x29 0x0d5c 0x2a 0x0ce8 0x2b 0x0c74 0x2c 0x0c00 0x2d 0x0b8c
0x2e 0x0b18 0x2f 0x0aa4 0x30 0x0a30 0x31 0x09bc 0x32 0x0948
0x33 0x08d4 0x34 0x0860 0x35 0x07ec 0x36 0x0778 0x37 0x0704
0x38 0x0690 0x39 0x061c 0x3a 0x05a8 0x3b 0x0534 0x3c 0x04c0
0x3d 0x044c 0x3e 0x03d8 0x3f 0x0364 0x40 0x02f0 0x41 0x027c
0x42 0x0208 0x43 0x0194 0x44 0x0120 0x45 0x00ac
--//seq: 0x24f irb=12的数据
*-----------------------------
* Rec #0x12 slt: 0x1e objn: 90340(0x000160e4) objd: 90340 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c004df.024f.11
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004171e1 hdba: 0x004171e0
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 523(0x20b) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
col 1: [ 4] 74 65 73 74 --//转换之后为test
SQL> select display_raw('74657374','VARCHAR2') from dual;
DISPLAY_RAW('74657374','VARCHAR2')
--------------------------------------------------------------------------------
test
SQL> 所以查询第一行数据得到的的test是通过undo来获取的(块本身的数据内容已经应用redo，第一行块数据为NAME)
11、checkpoint之后再次使用bbed查看数据块的信息
SQL> select rowid, t1.* from t t1 where rownum = 1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test
SQL>
SQL> alter system checkpoint;
System altered.
SQL>
--//使用bbed查看块内容
BBED> set dba 1,94689
DBA 0x004171e1 (4288993 1,94689)
BBED> map
File: /u01/app/oradata/QXY/system01.dbf (1)
Block: 94689 Dba:0x004171e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @116
struct kdbt[1], 4 bytes @130
sb2 kdbr[524] @134
ub1 freespace[822] @1182
ub1 rowdata[6184] @2004
ub4 tailchk @8188
BBED> x /rnc *kdbr[0]
rowdata[6173] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: NAME <=====块内容还是NAME，理论应该为test，因为修改没有提交
BBED> 其实，这里是受10513的影响，如果没有event等待事件，这里肯定是要被修改为test的。
12、查看10046的追踪
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL>
SQL> select rowid, t1.* from t t1 where rownum = 1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13406.trc
SQL>
=====================
select rowid, t1.* from t t1 where rownum = 1
END OF STMT
PARSE #139887783016960:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=508354683,tim=1604652047243255
EXEC #139887783016960:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=508354683,tim=1604652047243380
WAIT #139887783016960: nam='db file sequential read' ela= 17 file#=1 block#=94688 blocks=1 obj#=90340 tim=1604652047243617
select rowid, t1.* from t t1 where rownum = 1
END OF STMT
PARSE #139887783016960:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=508354683,tim=1604652047243255
EXEC #139887783016960:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=508354683,tim=1604652047243380
WAIT #139887783016960: nam='db file sequential read' ela= 17 file#=1 block#=94688 blocks=1 obj#=90340 tim=1604652047243617
WAIT #139887783016960: nam='direct path read' ela= 38 file number=1 first dba=94904 block cnt=8 obj#=90340 tim=1604652047244505
WAIT #139887783016960: nam='Disk file operations I/O' ela= 16 FileOperation=2 fileno=3 filetype=2 obj#=0 tim=1604652047244583
WAIT #139887783016960: nam='db file sequential read' ela= 7 file#=3 block#=240 blocks=1 obj#=0 tim=1604652047244600
WAIT #139887783016960: nam='db file sequential read' ela= 8 file#=3 block#=1247 blocks=1 obj#=0 tim=1604652047244665
WAIT #139887783016960: nam='db file sequential read' ela= 7 file#=3 block#=1246 blocks=1 obj#=0 tim=1604652047244753
WAIT #139887783016960: nam='db file sequential read' ela= 11 file#=3 block#=1245 blocks=1 obj#=0 tim=1604652047244894
WAIT #139887783016960: nam='db file sequential read' ela= 133 file#=3 block#=1244 blocks=1 obj#=0 tim=1604652047245128
WAIT #139887783016960: nam='db file sequential read' ela= 8 file#=3 block#=1243 blocks=1 obj#=0 tim=1604652047245262
WAIT #139887783016960: nam='db file sequential read' ela= 6 file#=3 block#=1242 blocks=1 obj#=0 tim=1604652047245364
WAIT #139887783016960: nam='db file sequential read' ela= 4 file#=3 block#=1241 blocks=1 obj#=0 tim=1604652047245442
WAIT #139887783016960: nam='db file sequential read' ela= 5 file#=3 block#=1240 blocks=1 obj#=0 tim=1604652047245516
WAIT #139887783016960: nam='db file sequential read' ela= 7 file#=3 block#=1238 blocks=1 obj#=0 tim=1604652047245620
FETCH #139887783016960:c=2000,e=2235,p=26,cr=529,cu=0,mis=0,r=1,dep=0,og=1,plh=508354683,tim=1604652047245669
STAT #139887783016960 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=529 pr=26 pw=0 time=2227 us)'
STAT #139887783016960 id=2 cnt=1 pid=1 pos=1 obj=90340 op='TABLE ACCESS FULL T (cr=529 pr=26 pw=0 time=2219 us cost=3 size=41394734 card=1335314)'
WAIT #139887783016960: nam='SQL*Net message from client' ela= 246 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1604652047246055
FETCH #139887783016960:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=508354683,tim=1604652047246088
WAIT #139887783016960: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1604652047246100
*** 2020-11-06 16:40:55.051
WAIT #139887783016960: nam='SQL*Net message from client' ela= 7805608 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1604652055051765
CLOSE #139887783016960:c=0,e=10,dep=0,type=0,tim=1604652055051861
=====================其中nam='db file sequential read' ela= 7 file#=3这些就是读取undo信息。file#=3就是undo的数据文件。
到目前为止，因为设置了10513等待事件，导致不能通过undo数据来修改数据块的内容，所以造成每次查询数据块时都需要通过undo来找修改前的镜像。
如果这个时候把undo数据块破坏掉，会出现什么情况呢，根据刚才dump 数据块的信息知道uba的地址为0x00c004df
13、undo数据转换
SQL>
SQL> @ton 00c004df
old 1: select to_number(replace('&1',' ',''),'xxxxxxxx') from dual
new 1: select to_number(replace('00c004df',' ',''),'xxxxxxxx') from dual
TO_NUMBER(REPLACE('00C004DF','',''),'XXXXXXXX')
-----------------------------------------------
12584159
SQL> @tof 12584159
old 1: select dbms_utility.data_block_address_file('&1') as "file", dbms_utility.data_block_address_block('&1') as "block" from dual
new 1: select dbms_utility.data_block_address_file('12584159') as "file", dbms_utility.data_block_address_block('12584159') as "block" from dual
file block
---------- ----------
3 1247现在把3号文件的1247块标记为坏块
BBED> set dba 3,1247
DBA 0x00c004df (12584159 3,1247)
BBED> map
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 1247 Dba:0x00c004df
------------------------------------------------------------
Undo Data
struct kcbh, 20 bytes @0
struct ktubh, 154 bytes @20
ub1 freespace[18] @174
ub1 undodata[7996] @192
ub4 tailchk @8188
BBED> d /v offset 8188
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 1247 Offsets: 8188 to 8191 Dba:0x00c004df
-------------------------------------------------------
45027b5b l E.{[
<16 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x02
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00c004df
ub4 bas_kcbh @8 0x003c5b7b
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x45
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xadf5
ub2 spare3_kcbh @18 0x0000
BBED> set offset 8190
OFFSET 8190
BBED> d
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 1247 Offsets: 8190 to 8191 Dba:0x00c004df
------------------------------------------------------------------------
7b5b
<32 bytes per line>
BBED> m /x 1111
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 1247 Offsets: 8190 to 8191 Dba:0x00c004df
------------------------------------------------------------------------
1111
<32 bytes per line>
BBED> sum apply
Check value for File 3, Block 1247:
current = 0xe79f, required = 0xe79f
BBED>
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oradata/QXY/undotbs01.dbf
BLOCK = 1247
Block 1247 is corrupt
Corrupt block relative dba: 0x00c004df (file 0, block 1247)
Fractured block found during verification
Data in bad block:
type: 2 format: 2 rdba: 0x00c004df
last change scn: 0x0000.003c5b7b seq: 0x45 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x11110245
check value in block header: 0xe79f
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
BBED>
14、undo块已经标记为坏块，再次执行select语句查询第一行数据
SQL> select rowid, t1.* from t t1 where rownum = 1;
select rowid, t1.* from t t1 where rownum = 1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 1247)
ORA-01110: data file 3: '/u01/app/oradata/QXY/undotbs01.dbf'
后台日志
Hex dump of (file 3, block 1247) in trace file /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13570.trc
Corrupt block relative dba: 0x00c004df (file 3, block 1247)
Fractured block found during buffer read
Data in bad block:
type: 2 format: 2 rdba: 0x00c004df
last change scn: 0x0000.003c5b7b seq: 0x45 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x11110245
check value in block header: 0xe79f
computed block checksum: 0x0
Reading datafile '/u01/app/oradata/QXY/undotbs01.dbf' for corruption at rdba: 0x00c004df (file 3, block 1247)
Reread (file 3, block 1247) found same corrupt data (no logical check)
Fri Nov 06 16:52:04 2020
Corrupt Block Found
TSN = 2, TSNAME = UNDOTBS1
RFN = 3, BLK = 1247, RDBA = 12584159
OBJN = 0, OBJD = -1, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Errors in file /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13570.trc (incident=542562):
ORA-01578: ORACLE data block corrupted (file # 3, block # 1247)
ORA-01110: data file 3: '/u01/app/oradata/QXY/undotbs01.dbf'
Incident details in: /u01/app/diag/rdbms/qxy/QXY/incident/incdir_542562/QXY_ora_13570_i542562.trc15、把修改的undo还原回去
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/u01/app/oradata/QXY/undotbs01.dbf', block 1247
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> sum apply
Check value for File 3, Block 1247:
current = 0xadf5, required = 0xadf5
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oradata/QXY/undotbs01.dbf
BLOCK = 1247
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> 如果把undo上面的事务信息清除，会出现什么情况
通过dump数据块的itl信息可以知道(0x02 0x0008.01e.00000fa0 0x00c004df.024f.12 ---- 524 fsc 0x0000.00000000 )
0x0008.01e.00000fa0 其中0008代表8号回滚段
16、dump 8号回滚段
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1_3724004606$
2 _SYSSMU2_2996391332$
3 _SYSSMU3_1723003836$
4 _SYSSMU4_1254879796$
5 _SYSSMU5_898567397$
6 _SYSSMU6_1263032392$
7 _SYSSMU7_2070203016$
8 _SYSSMU8_517538920$
9 _SYSSMU9_1650507775$
10 _SYSSMU10_1197734989$
11 rows selected.
SQL>
SQL> alter system dump undo header "_SYSSMU8_517538920$";
System altered.
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_13615.trc
SQL>
--//undo段头信息
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0fa1 0x001c 0x0000.003c0a25 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649566
0x01 9 0x00 0x0fa0 0x0004 0x0000.003c0862 0x00c004c6 0x0000.000.00000000 0x00000002 0x00000000 1604649093
0x02 9 0x00 0x0fa1 0x0009 0x0000.003c0a5c 0x00c004d1 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x03 9 0x00 0x0fa1 0x0019 0x0000.003c59f0 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649767
0x04 9 0x00 0x0f9f 0x0016 0x0000.003c0894 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649146
0x05 9 0x00 0x0fa0 0x000c 0x0000.003c0a9e 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649622
0x06 9 0x00 0x0f9f 0x000f 0x0000.003c0911 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649266
0x07 9 0x00 0x0fa0 0x0011 0x0000.003c0a55 0x00c004cb 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x08 9 0x00 0x0f9f 0x0005 0x0000.003c0a77 0x00c004d1 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x09 9 0x00 0x0f9f 0x0020 0x0000.003c0a5e 0x00c004d4 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x0a 9 0x00 0x0fa1 0x0021 0x0000.003c5a1a 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x0b 9 0x00 0x0f9d 0x0002 0x0000.003c0a5b 0x00c004d1 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x0c 9 0x00 0x0f9e 0x000d 0x0000.003c0ab3 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x0d 9 0x00 0x0fa0 0x0015 0x0000.003c0acb 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x0e 9 0x00 0x0f9f 0x000a 0x0000.003c5a07 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x0f 9 0x00 0x0f9f 0x0000 0x0000.003c09a7 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649506
0x10 9 0x00 0x0fa1 0x001b 0x0000.003c5af3 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649771
0x11 9 0x00 0x0f9e 0x001f 0x0000.003c0a57 0x00c004ce 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x12 9 0x00 0x0f9f 0x0008 0x0000.003c0a75 0x00c004d6 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x13 9 0x00 0x0f9e 0x0014 0x0000.003c0815 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604648985
0x14 9 0x00 0x0f9f 0x0001 0x0000.003c0846 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604649085
0x15 9 0x00 0x0fa0 0x0018 0x0000.003c0ade 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x16 9 0x00 0x0f9d 0x0006 0x0000.003c08a6 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649146
0x17 9 0x00 0x0f9f 0x000e 0x0000.003c5a00 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x18 9 0x00 0x0f9e 0x0003 0x0000.003c0b0a 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649661
0x19 9 0x00 0x0f9f 0x0017 0x0000.003c59f8 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649767
0x1a 9 0x00 0x0f9e 0x0013 0x0000.003c07c8 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604648845
0x1b 9 0x00 0x0f9f 0xffff 0x0000.003c5b4b 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649772
0x1c 9 0x00 0x0f8d 0x001d 0x0000.003c0a51 0x00c004ca 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x1d 9 0x00 0x0f9e 0x0007 0x0000.003c0a53 0x00c004cb 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x1e 10 0x90 0x0fa0 0x0003 0x0000.003c5b79 0x00c00667 0x0000.000.00000000 0x000000a0 0x00000000 0
0x1f 9 0x00 0x0f8d 0x000b 0x0000.003c0a59 0x00c004ce 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x20 9 0x00 0x0f9f 0x0012 0x0000.003c0a60 0x00c004d2 0x0000.000.00000000 0x00000005 0x00000000 1604649621
0x21 9 0x00 0x0fa0 0x0010 0x0000.003c5ae3 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1604649768
EXT TRN CTL:: 发现index=0x1e的state=10,说明上面有事物
现在把这个10修改为9，把上面的事务清除
首先找到undo对应的文件号和块号
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='_SYSSMU8_517538920$';
HEADER_FILE HEADER_BLOCK
----------- ------------
3 240
SQL> 17、使用bbed修改上面的事务
[oracle@QXY ~]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 6 17:07:26 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 3,240
DBA 0x00c000f0 (12583152 3,240)
BBED> map
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 240 Dba:0x00c000f0
------------------------------------------------------------
BBED-00400: invalid blocktype (38)
BBED> find /x 0a90 <=====有事物信息的行比较特殊，使用find查找
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 240 Offsets: 7416 to 7927 Dba:0x00c000f0
------------------------------------------------------------------------
0a900300 00000000 00000000 00000000 a0000000 00000000 8d0f0000 ce04c000
590a3c00 00000000 09000b00 00000000 00000000 00000000 01000000 9502a55f
9f0f0000 d204c000 600a3c00 00000000 09001200 00000000 00000000 00000000
05000000 9502a55f a00f0000 00000000 e35a3c00 00000000 09001000 00000000
00000000 00000000 00000000 2803a55f 08000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 0900
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oradata/QXY/undotbs01.dbf (3)
Block: 240 Offsets: 7416 to 7927 Dba:0x00c000f0
------------------------------------------------------------------------
09000300 00000000 00000000 00000000 a0000000 00000000 8d0f0000 ce04c000
590a3c00 00000000 09000b00 00000000 00000000 00000000 01000000 9502a55f
9f0f0000 d204c000 600a3c00 00000000 09001200 00000000 00000000 00000000
05000000 9502a55f a00f0000 00000000 e35a3c00 00000000 09001000 00000000
00000000 00000000 00000000 2803a55f 08000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 3, Block 240:
current = 0x3f66, required = 0x3f66
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oradata/QXY/undotbs01.dbf
BLOCK = 240
Block Checking: DBA = 12583152, Block Type = System Managed Segment Header Block
ERROR: SMU Segment Header Corrupted. Error Code = 38505
ktu4smck: number of txn in commit list not equal to inactive txn
TRN CTL:: seq: 0x0252 chd: 0x001a ctl: 0x001b inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c004d6.024f.29 scn: 0x0000.003c07a0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.024f.28 ext: 0x2 spc: 0xb06
uba: 0x00000000.024f.15 ext: 0x2 spc: 0x7b0
uba: 0x00000000.024f.0b ext: 0x2 spc: 0x86c
uba: 0x00000000.024f.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub bcl cmt
-----------------------------------------------------------------------------------------
0x00 9 0x00 0x0fa1 0x001c 0x0000.003c0a25 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649566
0x01 9 0x00 0x0fa0 0x0004 0x0000.003c0862 0x00c004c6 0x0000.000.00000000 0x00000002 0x00000000 1604649093
0x02 9 0x00 0x0fa1 0x0009 0x0000.003c0a5c 0x00c004d1 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x03 9 0x00 0x0fa1 0x0019 0x0000.003c59f0 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649767
0x04 9 0x00 0x0f9f 0x0016 0x0000.003c0894 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649146
0x05 9 0x00 0x0fa0 0x000c 0x0000.003c0a9e 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649622
0x06 9 0x00 0x0f9f 0x000f 0x0000.003c0911 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649266
0x07 9 0x00 0x0fa0 0x0011 0x0000.003c0a55 0x00c004cb 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x08 9 0x00 0x0f9f 0x0005 0x0000.003c0a77 0x00c004d1 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x09 9 0x00 0x0f9f 0x0020 0x0000.003c0a5e 0x00c004d4 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x0a 9 0x00 0x0fa1 0x0021 0x0000.003c5a1a 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x0b 9 0x00 0x0f9d 0x0002 0x0000.003c0a5b 0x00c004d1 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x0c 9 0x00 0x0f9e 0x000d 0x0000.003c0ab3 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x0d 9 0x00 0x0fa0 0x0015 0x0000.003c0acb 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x0e 9 0x00 0x0f9f 0x000a 0x0000.003c5a07 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x0f 9 0x00 0x0f9f 0x0000 0x0000.003c09a7 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649506
0x10 9 0x00 0x0fa1 0x001b 0x0000.003c5af3 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649771
0x11 9 0x00 0x0f9e 0x001f 0x0000.003c0a57 0x00c004ce 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x12 9 0x00 0x0f9f 0x0008 0x0000.003c0a75 0x00c004d6 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x13 9 0x00 0x0f9e 0x0014 0x0000.003c0815 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604648985
0x14 9 0x00 0x0f9f 0x0001 0x0000.003c0846 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604649085
0x15 9 0x00 0x0fa0 0x0018 0x0000.003c0ade 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649627
0x16 9 0x00 0x0f9d 0x0006 0x0000.003c08a6 0x00c004c6 0x0000.000.00000000 0x00000001 0x00000000 1604649146
0x17 9 0x00 0x0f9f 0x000e 0x0000.003c5a00 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649768
0x18 9 0x00 0x0f9e 0x0003 0x0000.003c0b0a 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649661
0x19 9 0x00 0x0f9f 0x0017 0x0000.003c59f8 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649767
0x1a 9 0x00 0x0f9e 0x0013 0x0000.003c07c8 0x00c004c3 0x0000.000.00000000 0x00000001 0x00000000 1604648845
0x1b 9 0x00 0x0f9f 0xffff 0x0000.003c5b4b 0x00c004d6 0x0000.000.00000000 0x00000001 0x00000000 1604649772
0x1c 9 0x00 0x0f8d 0x001d 0x0000.003c0a51 0x00c004ca 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x1d 9 0x00 0x0f9e 0x0007 0x0000.003c0a53 0x00c004cb 0x0000.000.00000000 0x00000003 0x00000000 1604649621
0x1e 9 0x00 0x0fa0 0x0003 0x0000.003c5b79 0x00c00667 0x0000.000.00000000 0x000000a0 0x00000000 0
0x1f 9 0x00 0x0f8d 0x000b 0x0000.003c0a59 0x00c004ce 0x0000.000.00000000 0x00000001 0x00000000 1604649621
0x20 9 0x00 0x0f9f 0x0012 0x0000.003c0a60 0x00c004d2 0x0000.000.00000000 0x00000005 0x00000000 1604649621
0x21 9 0x00 0x0fa0 0x0010 0x0000.003c5ae3 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1604649768
EXT TRN CTL::
usn: 8
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> 修改之后，发现index=0x1e上面已经没有事物信息
18、再次查询表的第一行数据
SQL> select rowid, t1.* from t t1 where rownum = 1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 NAME
SQL>
再次查询发现name变成了NAME，因为修改的数据还没有提交，不应该为NAME，应该为test，但是现在手动清除了UNDO上面的事物信息，虽然数据块上面还是ITL信息，但是已经无法通过undo找到前镜像(这个操作其实和使用_corrupted_rollback_segments参数功能类似)。
19、再次回退修改的undo信息
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/u01/app/oradata/QXY/undotbs01.dbf', block 240
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> sum apply
Check value for File 3, Block 240:
current = 0xaf65, required = 0xaf65
--//再次查询数据
SQL> alter system flush buffer_cache;
System altered.
SQL> select rowid, t1.* from t t1 where rownum = 1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test <======有可以通过undo找到修改前的数据
20、清除10513event，重启数据库
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 478154376 bytes
Database Buffers 289406976 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
SQL> 21、因为已经清除了10513，所以再次查询表的内容时，因为修改的内容没有提交，会通过undo的前镜像修改表的数据块。
--//查询数据库
SQL>
SQL> select rowid, t1.* from t t1 where rownum = 1;
ROWID ID NAME
------------------ ---------- ----
AAAWDkAABAAAXHhAAA 1 test
BBED> set dba 1,94689
DBA 0x004171e1 (4288993 1,94689)
BBED> map
File: /u01/app/oradata/QXY/system01.dbf (1)
Block: 94689 Dba:0x004171e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @116
struct kdbt[1], 4 bytes @130
sb2 kdbr[524] @134
ub1 freespace[822] @1182
ub1 rowdata[6184] @2004
ub4 tailchk @8188
BBED> x /rnc *kdbr[0]
rowdata[6173] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: test <=====因为取消了10513，所以select查询时，通过undo进行了回滚。
BBED> eg:
数据库启动的时候设置10513 event，可以避免数据库异常关闭时的异常事物的回滚，但是当需要查到异常关闭的数据块时，还是会通过块上的uba信息去找块被修改前的前镜像，但是找到之后并不会修改表的数据块，每次访问都会访问undo信息。取消10513 event之后，再次查询数据块时就会把数据块的内容改成被修改之前的数据。 还有一点，如果真正是undo块有问题，10513 event是不会解决的。
311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
