数据库的版本是11.2.0.4
创新互联建站坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站制作、成都网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的通州网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，数据库版本
| www.htz.pw > select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production |
2，创建测试表
| www.htz.pw > create table scott.htz tablespace users as select * from dba_objects where rownum<2;
Table created.
www.htz.pw > select rowid from scott.htz;
ROWID ------------------ AAASkGAAEAAABkzAAA
www.htz.pw > @rowid_to_info.sql Enter value for rowid: AAASkGAAEAAABkzAAA ROWID_TYPE: 1 OBJECT_NUMBER: 76038 RELATIVE_FNO: 4 BLOCK_NUMBER: 6451 ROW_NUMBER: 0
PL/SQL procedure successfully completed. |
3，模拟实验
3.1 更新行记录
| www.htz.pw > conn scott/oracle Connected. www.htz.pw > select owner from htz;
OWNER ------------------------------ SYS
www.htz.pw > update htz set owner='HUANGTINGZHONG';
1 row updated. |
3.2 将BUFFER_CACHE中的块刷到数据文件中
这里另外打开一个窗口来执行
| www.htz.pw > alter system flush buffer_cache;
System altered.
www.htz.pw > select owner from scott.htz;
OWNER ------------------------------ SYS |
3.3 DUMP数据文件中的块
这里dump块的目的是为了查看值是否发生了变化，以及行的标识符信息
| www.htz.pw > select file_name from dba_data_files;
FILE_NAME -------------------------------------------------------------------------------- /oracle/app/oracle/oradata/orcl1123/users01.dbf /oracle/app/oracle/oradata/orcl1123/undotbs1.dbf /oracle/app/oracle/oradata/orcl1123/sysaux01.dbf /oracle/app/oracle/oradata/orcl1123/system01.dbf /oracle/app/oracle/oradata/orcl1123/undotbs2.dbf /oracle/app/oracle/oradata/orcl1123/htz01.dbf /oracle/app/oracle/oradata/orcl1123/tbs_data.dbf /oracle/app/oracle/oradata/orcl1123/undotbs3.dbf www.htz.pw > @dump_block.sql Enter value for datafile: 4 Enter value for block_id: 6451 old 1: alter system dump datafile &datafile block &block_id new 1: alter system dump datafile 4 block 6451
System altered. |
在trace文件中可以看到如下的信息
| Block header dump: 0x01001933 Object id on Block? Y seg/obj: 0x12906 csc: 0x00.24e77c itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x1001930 ver: 0x01 opc: 0 inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0024e747 0x02 0x000d.003.000000d7 0x0140026c.0202.04 ---- 1 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x01001933 data_block_dump,data header at 0x2a97c20a7c =============== tsiz: 0x1f80 hsiz: 0x14 pbl: 0x2a97c20a7c 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1edb avsp=0x1f14 tosp=0x1f14 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1edb block_row_dump: tab 0, row 0, @0x1edb tl: 88 fb: --H-FL-- lb: 0x2 cc: 14 col 0: [14] 48 55 41 4e 47 54 49 4e 47 5a 48 4f 4e 47 col 1: [ 5] 49 43 4f 4c 24 col 2: *NULL* col 3: [ 2] c1 15 col 4: [ 2] c1 03 col 5: [ 5] 54 41 42 4c 45 col 6: [ 7] 78 6f 09 11 0a 2f 0e col 7: [ 7] 78 6f 09 11 0a 3b 01 col 8: [19] 32 30 31 31 2d 30 39 2d 31 37 3a 30 39 3a 34 36 3a 31 33 col 9: [ 5] 56 41 4c 49 44 col 10: [ 1] 4e col 11: [ 1] 4e col 12: [ 1] 4e col 13: [ 2] c1 02 |
4,bbed修改块的相关的内容
其实bbed只需要改2个地址，一个地方是改itl中的flg，更改为提交，另一个地方是清除行中的itl的值
| BBED> p ktbbh struct ktbbh, 96 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00012906 ub4 ktbbhod1 @24 0x00012906 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0024e77c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 3 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01001930 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0xffff ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x00000000 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0024e747 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000d ub2 kxidslt @70 0x0003 ub4 kxidsqn @72 0x000000d7 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x0140026c ub2 kubaseq @80 0x0202 ub1 kubarec @82 0x04 ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
BBED> set mode edit MODE Edit
BBED> set count 16 COUNT 16 这里有一点需要注意的是，测试平台是X86的，如果是小机需要注意了，有一点不一样的地方，不需要高低位转换 BBED> modify /x 0080 offset 84 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0) Block: 6451 Offsets: 84 to 99 Dba:0x00000000 ------------------------------------------------------------------------ 00800000 00000000 00000000 00000000
<32 bytes per line>
BBED> p ktbbh.ktbbhitl[1] struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000d ub2 kxidslt @70 0x0003 ub4 kxidsqn @72 0x000000d7 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x0140026c ub2 kubaseq @80 0x0202 ub1 kubarec @82 0x04 ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
这里可以看到，下面报了行为的itl值有问题
BBED> verify DBVERIFY - Verification starting FILE = /oracle/app/oracle/oradata/orcl1123/users01.dbf BLOCK = 6451
Block Checking: DBA = 16783667, Block Type = KTB-managed data block data header at 0x2a98b8727c kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=2 ktbbhitc=3 Block 6451 failed with check code 6101
DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 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> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8023 0x2c
BBED> set offset 8023 OFFSET 8023
BBED> dump File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0) Block: 6451 Offsets: 8023 to 8038 Dba:0x00000000 ------------------------------------------------------------------------ 2c020e0e 4855414e 4754494e 475a484f
<32 bytes per line>
BBED> modify /x 2c00 File: /oracle/app/oracle/oradata/orcl1123/users01.dbf (0) Block: 6451 Offsets: 8023 to 8038 Dba:0x00000000 ------------------------------------------------------------------------ 2c000e0e 4855414e 4754494e 475a484f
<32 bytes per line>
BBED> sum apply Check value for File 0, Block 6451: current = 0xbebc, required = 0xbebc 下面再也没有报错了 BBED> verify DBVERIFY - Verification starting FILE = /oracle/app/oracle/oradata/orcl1123/users01.dbf BLOCK = 6451
DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 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 |
6，查看bbed更改后的数据
另外开一个窗口来查看表中的数据
| www.htz.pw > select owner from scott.htz;
OWNER ------------------------------ HUANGTINGZHONG |
这里可以看到数据已经被更新了。
不过需要注意的是在update窗口，执行rollback语句，还是可以回退的
| 如果在原窗口执行rollback命令的时候，仍然会回滚记录的。
www.htz.pw > rollback;
Rollback complete.
www.htz.pw > select owner from scott.htz;
OWNER ------------------------------ SYS |
