[20190129]块内重整3.txt

最新推荐文章于 2025-01-12 07:30:00 发布

转载最新推荐文章于 2025-01-12 07:30:00 发布 · 135 阅读

本文通过实例演示Oracle数据库中块内重整（块内重组）的过程，重点解析行目录更新机制及记录重用过程，展示了如何使用BBED工具进行数据块分析和恢复。

[20190129]块内重整3.txt

--//链接http://blog.itpub.net/267265/viewspace-2287110/,与别人交流,提到一个情况,kdbr行目录的更新并不一定发生在

--//当到达顶部时,会出现一次块内重整(也有叫块内重组).通过例子说明:

1.环境:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table empy as select * from emp;

Table created.

SCOTT@book> select rowid,empy.* from empy where rownum=1;

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

AAAWQiAAEAAAAITAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

SCOTT@book> @ rowid AAAWQiAAEAAAAITAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

91170 4 531 0 0x1000213 4,531 alter system dump datafile 4 block 531 ;

2.测试:

--//先删除1条记录.然后修改1条记录看看.

SCOTT@book> delete from empy where EMPNO=7499;

1 row deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system checkpoint ;

System altered.

--//通过bbed观察:

BBED> set dba 4,531

DBA 0x01000213 (16777747 4,531)

BBED> p kdbr

sb2 kdbr[0] @142 8026

sb2 kdbr[1] @144 7983

sb2 kdbr[2] @146 7940

sb2 kdbr[3] @148 7899

sb2 kdbr[4] @150 7854

sb2 kdbr[5] @152 7813

sb2 kdbr[6] @154 7772

sb2 kdbr[7] @156 7732

sb2 kdbr[8] @158 7694

sb2 kdbr[9] @160 7651

sb2 kdbr[10] @162 7613

sb2 kdbr[11] @164 7575

sb2 kdbr[12] @166 7536

sb2 kdbr[13] @168 7497

SCOTT@book> update empy set JOB=lower(JOB) where EMPNO=7369;

1 row updated.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system checkpoint ;

System altered.

BBED> p kdbr

sb2 kdbr[0] @142 8026

sb2 kdbr[1] @144 7983

sb2 kdbr[2] @146 7940

sb2 kdbr[3] @148 7899

sb2 kdbr[4] @150 7854

sb2 kdbr[5] @152 7813

sb2 kdbr[6] @154 7772

sb2 kdbr[7] @156 7732

sb2 kdbr[8] @158 7694

sb2 kdbr[9] @160 7651

sb2 kdbr[10] @162 7613

sb2 kdbr[11] @164 7575

sb2 kdbr[12] @166 7536

sb2 kdbr[13] @168 7497

--//行目录没有变化.因为修改长度没有变化.

SCOTT@book> update empy set JOB=upper(JOB)||'A' where EMPNO=7369;

1 row updated.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system checkpoint ;

System altered.

--//长度发生变化,kdbr[0]的指向的偏移现在最小.

BBED> set dba 4,531

DBA 0x01000213 (16777747 4,531)

BBED> p kdbr

sb2 kdbr[0] @142 7458

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbr[1] @144 -1

sb2 kdbr[2] @146 7940

sb2 kdbr[3] @148 7899

sb2 kdbr[4] @150 7854

sb2 kdbr[5] @152 7813

sb2 kdbr[6] @154 7772

sb2 kdbr[7] @156 7732

sb2 kdbr[8] @158 7694

sb2 kdbr[9] @160 7651

sb2 kdbr[10] @162 7613

sb2 kdbr[11] @164 7575

sb2 kdbr[12] @166 7536

sb2 kdbr[13] @168 7497

BBED> p kdbh

struct kdbh, 14 bytes @124

ub1 kdbhflag @124 0x00 (NONE)

sb1 kdbhntab @125 1

sb2 kdbhnrow @126 14

sb2 kdbhfrre @128 1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbhfsbo @130 46

sb2 kdbhfseo @132 7458

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbhavsp @134 7493

sb2 kdbhtosp @136 7493

--//可以发现kdbr[1]=-1,注意看下划线的值.这样可以重用kdbr[1]的记录,理论讲只要没有覆盖恢复还可可行的.

--//感觉这个应该是行目录整理,oracle总是把一些事情分散完成,而不是一次完成.

3.尝试恢复看看.

BBED> set count 20

COUNT 20

BBED> set offset 7458

OFFSET 7458

BBED> find /x 3c curr

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 531 Offsets: 8107 to 8126 Dba:0x01000213

--------------------------------------------------------------------------------------------------

3c020803 c24b6405 414c4c45 4e085341 4c45534d

<64 bytes per line>

BBED> assign offset 8107=0x2c;

ub1 rowdata[0] @8107 0x2c

BBED> x /rnccntnn offset 8107

rowdata[525] @8107

------------

flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8108: 0x02

cols@8109: 8

col 0[3] @8110: 7499

col 1[5] @8114: ALLEN

col 2[8] @8120: SALESMAN

col 3[3] @8129: 7698

col 4[7] @8133: 1981-02-20 00:00:00

col 5[2] @8141: 1600

col 6[2] @8144: 300

col 7[2] @8147: 30

BBED> map

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 531 Dba:0x01000213

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 96 bytes @20

struct kdbh, 14 bytes @124

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

struct kdbt[1], 4 bytes @138

sb2 kdbr[14] @142

ub1 freespace[7412] @170

ub1 rowdata[606] @7582

ub4 tailchk @8188

--//kdbh偏移在124,有3个ITL槽.

--//8107 - 124 = 7983,行目录保存的是相对偏移.

BBED> assign kdbr[1] =7983;

sb2 kdbr[0] @144 7983

BBED> sum apply

Check value for File 4, Block 531:

current = 0x2e9a, required = 0x2e9a

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 531

Block Checking: DBA = 16777747, Block Type = KTB-managed data block

data header at 0x7f4d1bed427c

kdbchk: row locked by non-existent transaction

table=0 slot=1

lockid=2 ktbbhitc=3

Block 531 failed with check code 6101

--//全表扫描已经ok了.

SCOTT@book> select * from empy;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERKA 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

---//继续修改看看:

BBED> assign kdbh.kdbhfrre=-1;

sb2 kdbhfrre @128 -1

BBED> sum apply

Check value for File 4, Block 531:

current = 0xd164, required = 0xd164

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 531

Block Checking: DBA = 16777747, Block Type = KTB-managed data block

data header at 0x1fe8e7c

kdbchk: row locked by non-existent transaction

table=0 slot=1

lockid=2 ktbbhitc=3

Block 531 failed with check code 6101

BBED> x /rnccntnn *kdbr[1]

rowdata[525] @8107

------------

flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8108: 0x02

cols@8109: 8

col 0[3] @8110: 7499

col 1[5] @8114: ALLEN

col 2[8] @8120: SALESMAN

col 3[3] @8129: 7698

col 4[7] @8133: 1981-02-20 00:00:00

col 5[2] @8141: 1600

col 6[2] @8144: 300

col 7[2] @8147: 30

BBED> assign offset 8108=0x0;

ub1 rowdata[0] @8108 0x00

BBED> sum apply

Check value for File 4, Block 531:

current = 0xd166, required = 0xd166

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 531

Block Checking: DBA = 16777747, Block Type = KTB-managed data block

data header at 0x1fe8e7c

kdbchk: the amount of space used is not equal to block size

used=614 fsc=0 avsp=7493 dtl=8064

Block 531 failed with check code 6110

--//空间回收的问题就不修复了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2565173/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2565173/