深入解析DEPENDENCY$对象的恢复

Eygle曾经在案例:Move系统表DEPENDENCY$导致索引失效的数据库故障http://www.eygle.com/archives/2005/11/move_dependency_index_unusable.html)里描述了一个将DEPENDENCY$move后,忘记了rebuild索引就shutdown后发现库再也起不来的案例。

本文通过对这个案例的重现、模拟和解决,深入解析数据库字典对象的原理、作用与故障处理,希望能够帮助大家深入理解Oracle数据库的内部体系结构。

1.1     重现Move表失效索引的故障 

首先我们先来重现一下这个问题:

[P550_04_LA:oracle@:/dras20/testdb]#sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 9.2.0.6.0 – Production on Thu Jul 15 12:41:08 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

SQL>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

Database mounted.

Database opened.

通过SQL命令移动系统表:

SQL>alter table DEPENDENCY$ move tablespace system;

Table altered.

注意此时该表的相关索引处于UNUSABLE的状态:

SQL>select owner,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

OWNER      INDEX_NAME           TABLESPACE_NAME                STATUS

————   ——————————      ——————————                 

SYS       I_DEPENDENCY1          SYSTEM                         UNUSABLE

SYS       I_DEPENDENCY2          SYSTEM                         UNUSABLE

如果此时关闭数据库,下一次将无法正常启动数据库:

SQL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

检查告警日志文件,可以发现具体的错误原因,数据库提示索引不可用,递归引导失败,数据库无法启动:

Thu Jul 15 12:46:50 2010

Errors in file /cadrasu01/app/oracle/admin/testdb/udump/testdb_ora_5038294.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

Thu Jul 15 12:46:50 2010

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 5038294

ORA-1092 signalled during: ALTER DATABASE OPEN…

从结果里我们可以看到上述库如我们所期望的那样起不来了,故障得以重现。至于上述库为什么起不来,eygle曾经就这个问题做过精彩描述,我这里引用一下:

这一问题的根本原因在于,数据库启动过程中,会进行如下验证:

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#

 

这一验证会导致如下执行计划:

FETCH #5:c=10000,e=8426,p=3,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=17525911159627

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op=’SORT ORDER BY (cr=3 pr=3 pw=0 time=8434 us)’

STAT #5 id=2 cnt=0 pid=1 pos=1 obj=0 op=’NESTED LOOPS OUTER (cr=3 pr=3 pw=0 time=8418 us)’

STAT #5 id=3 cnt=0 pid=2 pos=1 obj=92 op=’TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=3 pw=0 time=8416 us)’

STAT #5 id=4 cnt=0 pid=3 pos=1 obj=122 op=’INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=3 pw=0 time=8414 us)’

STAT #5 id=5 cnt=0 pid=2 pos=2 obj=18 op=’TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)’

STAT #5 id=6 cnt=0 pid=5 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)’

 

这里的‘INDEX UNIQUE SCAN I_DEPENDENCY1″就导致了最后的错误:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

EXEC #1:c=0,e=633371,p=39,cr=619,cu=1,mis=0,r=0,dep=0,og=2,tim=1105782888673612

ERROR #1:err=1092 tim=1563018104

 

可惜Oracle并不允许置所有索引于不顾,否则就有救了.

有没有办法可以把上述库起起来呢答案是有的注意我这里的方法过于internal我希望朋友们永远不要在生产环境中用到,使用如下方法只是为了将Oracle的原理更好的展现出来,理解和懂得远比技巧更重要

我曾经对很多朋友都说过——外科医生做手术的时候用的是手术刀,我们的手术刀就是BBED

1.2      故障的解决思路

这个问题处理思路就是骗过Oracle,让数据库不知道索引I_DEPENDENCY1I_DEPENDENCY2的存在,这样SQL执行时自然就会选择全表扫描dependency$,从而就在根本上规避了上述错误。一切的核心就在于以下这句SQL的执行计划选择:

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#

要想修改dependency$上的索引I_DEPENDENCY1I_DEPENDENCY2,我们首先要知道oracle如何去判断某个表上是否存在索引?

实际上,oracle判断某个表上是否存在索引只会去检索表ind$

因此,我们此时最直观的思路就是——ind$中的I_DEPENDENCY1I_DEPENDENCY2记录删除就好了,这样oracle就不知道dependency$上存在索引I_DEPENDENCY1I_DEPENDENCY2了。

1.3      获得字典表信息

要想去删除IND$中的记录I_DEPENDENCY1I_DEPENDENCY2,我们必须得知道这两条记录存在数据字典的什么地方,也就是说我们要知道其物理存储位置。

请注意上述库现在是处于未open的状态,所以我们没法去查询数据字典。怎么办呢?

我们可以在其他类似数据库中去查询确认,也可以通过第三方的工具进行数据卸载定位,这里我们用到老熊(laoxiong.net)写的ODU

首先我们将数据字典表obj$ unload出来:

ODU> unload table sys.obj$

 

Unloading table: OBJ$,object ID: 18

Unloading segment,storage(Obj#=18 DataObj#=18 TS#=0 File#=1 Block#=121 Cluster=0)

30009 rows unloaded

然后我们从unload出来的文本文件中就可以知道DEPENDENCY$I_DEPENDENCY1I_DEPENDENCY2object iddata object idts#分别是多少:

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/odu/data]#ls -lrt

total 8256

-rw-r–r–    1 oracle   dba            3999 Jun 02 14:59 SCOTT_TEST_DELETE.txt

……省略显示部分内容

-rw-r–r–    1 oracle   dba             600 Aug 20 16:03 SYS_OBJ$.sql

-rw-r–r–    1 oracle   dba             725 Aug 20 16:03 SYS_OBJ$.ctl

-rw-r–r–    1 oracle   dba         3860753 Aug 20 16:03 SYS_OBJ$.txt

检查其中的内容:

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/odu/data]#cat SYS_OBJ$.txt|grep DEPENDENCY

96|30367|0|DEPENDENCY$|1||2|2002-05-13 02:16:33|2010-08-20 11:43:07|2002-05-13 02:16:33|1|||0||0|1|0x0040007a|95

127|127|0|I_DEPENDENCY1|4||1|2002-05-13 02:16:34|2002-05-13 02:16:34|2002-05-13 02:16:34|1|||0||0|65535|0x0040007b|18

128|128|0|I_DEPENDENCY2|4||1|2002-05-13 02:16:34|2002-05-13 02:16:34|2002-05-13 02:16:34|1|||0||0|65535|0x0040007b|19

……省略显示部分内容

28739||1|SQLDEPENDENCYBLOCKSEQUENCESEQU|1||5|2002-05-13 03:00:46|2002-05-13 03:00:46|2002-05-13 03:00:46|1|||0||6|65535|0x0040755a|42

从结果中我们就可以看出,现在DEPENDENCY$object iddata object idts#分别是96303670,这里因为我们已经对DEPENDENCY$执行了move操作,所以其data object id已经不等于object id了。

I_DEPENDENCY1object iddata object idts#分别是1271270

I_DEPENDENCY2object iddata object idts#分别是1281280

 

现在我们已经知道了I_DEPENDENCY1I_DEPENDENCY2object iddata object idts#,接下来我们需要知道I_DEPENDENCY1I_DEPENDENCY2所在的行记录在ind$中的物理存储位置。

 

我们还是先用ODUind$中的全部记录都unload出来:

ODU> unload table sys.ind$

 

Unloading table: IND$,object ID: 19

Unloading segment,storage(Obj#=19 DataObj#=2 TS#=0 File#=1 Block#=25 Cluster=3)

1296 rows unloaded

接着我们去看一下unload出来的文本文件所对应的sqlldrcontrol文件:

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/odu/data]#ls -lrt

total 8528

-rw-r–r–    1 oracle   dba            3999 Jun 02 14:59 SCOTT_TEST_DELETE.txt

……省略显示部分内容

-rw-r–r–    1 oracle   dba             925 Aug 20 16:27 SYS_IND$.sql

-rw-r–r–    1 oracle   dba             844 Aug 20 16:27 SYS_IND$.ctl

-rw-r–r–    1 oracle   dba          130810 Aug 20 16:27 SYS_IND$.txt

 

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/odu/data]#cat SYS_IND$.ctl

–Generated by ODU,for table “SYS”.”IND$”

OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)

LOAD DATA

INFILE ‘SYS_IND$.txt’ “STR X’0a‘”

APPEND INTO TABLE “SYS”.”IND$”

FIELDS TERMINATED BY X’7c‘ TRAILING NULLCOLS

(

    “BO#” ,

    “OBJ#” ,

    “DATAOBJ#” ,

    “TS#” ,

    “FILE#” ,

    “BLOCK#” ,

    “INDMETHOD#” ,

    “COLS” ,

    “PCTFREE$” ,

    “INITRANS” ,

    “MAXTRANS” ,

    “PCTTHRES$” ,

    “TYPE#” ,

    “FLAGS” ,

    “PROPERTY” ,

    “BLEVEL” ,

    “LEAFCNT” ,

    “DISTKEY” ,

    “LBLKKEY” ,

    “DBLKKEY” ,

    “CLUFAC” ,

    “ANALYZETIME” DATE “yyyy-mm-dd hh24:mi:ss”,

    “SAMPLESIZE” ,

    “ROWCNT” ,

    “INTCOLS” ,

    “DEGREE” ,

    “INSTANCES” ,

    “TRUNCCNT” ,

    “SPARE1” ,

    “SPARE2” ,

    “SPARE3” ,

    “SPARE4” CHAR(1000),

    “SPARE5” CHAR(1000),

    “SPARE6” DATE “yyyy-mm-dd hh24:mi:ss”

)

注意到老熊这里为了unload数据的方便,他更改了ind$所对应control文件中各个字段的顺序,他把BO#放在了第一位。 

我们来看看BO#的含义,从sql.bsq中我们可以清楚的看到BO#实际上就是这个索引所在的基表的object id,并且ind$是一个index cluster,其聚簇键就是BO#

看到这里,朋友们应该已经明白了老熊为什么要把BO#放在ind$对应control文件的第一位——这是因为对于index cluster而言,其聚簇键就存储在row directory的最前面,老熊这么干就因为这样他unload数据是最方便的。 

如下是sql.bsqind$的定义:

create table ind$                                             /* index table */

( obj#          number not null,                            /* object number */

  /* DO Safe NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE

   * TRANSACTION DURING TRUNCATE */

  dataobj#      number,                          /* data layer object number */

  ts#           number not null,                        /* tablespace number */

  file#         number not null,               /* segment header file number */

  block#        number not null,              /* segment header block number */

  bo#           number not null,              /* object number of base table */

  indmethod#    number not null,    /* object # for cooperative index method */

  cols          number not null,                        /* number of columns */

  pctfree$      number not null, /* minimum free space percentage in a block */

  initrans      number not null,            /* initial number of transaction */

  maxtrans      number not null,            /* maximum number of transaction */

  pctthres$     number,           /* iot overflow threshold, null if not iot */

  type#         number not null,              /* what kind of index is this? */

                                                               /* normal : 1 */

                                                               /* bitmap : 2 */

                                                              /* cluster : 3 */

                                                            /* iot – top : 4 */

                                                         /* iot – nested : 5 */

                                                            /* secondary : 6 */

                                                                 /* ansi : 7 */

                                                                  /* lob : 8 */

                                             /* cooperative index method : 9 */

  flags         number not null,     

                /* mutable flags: anything permanent should go into property */

                                                    /* unusable (dls) : 0x01 */

                                                    /* analyzed       : 0x02 */

                                                    /* no logging     : 0x04 */

                                    /* index is currently being built : 0x08 */

                                     /* index creation was incomplete : 0x10 */

                                           /* key compression enabled : 0x20 */

                                              /* user-specified stats : 0x40 */

                                            /* secondary index on IOT : 0x80 */

                                      /* index is being online built : 0x100 */

                                    /* index is being online rebuilt : 0x200 */

                                                /* index is disabled : 0x400 */

                                                     /* global stats : 0x800 */

                                            /* fake index(internal) : 0x1000 */

                                       /* index on UROWID column(s) : 0x2000 */

                                            /* index with large key : 0x4000 */

                             /* move partitioned rows in base table : 0x8000 */

                                 /* index usage monitoring enabled : 0x10000 */

  property      number not null,    /* immutable flags for life of the index */

  ……省略显示部分内容

  spare6        date                                  /* flashback timestamp */

)

cluster c_obj#(bo#)

/

I_DEPENDENCY1I_DEPENDENCY2base table就是DEPENDENCY$而由前面的结果我们知道DEPENDENCY$object id96,即我们只需要去查unload出来的ind$中全部记录所在的文本文件中那些BO#=96的记录,查到了我们也就知道了I_DEPENDENCY1I_DEPENDENCY2所在的具体的物理存储位置:

[P550_04_LA:oracle@]#cat SYS_IND$.txt|grep 96

77|125|125|0|1|961|0|2|10|2|255||1|0|0||||||||||2||||2|||||2002-05-13 02:16:34|0x0040001e|9

78|126|126|0|1|969|0|1|10|2|255||1|0|0||||||||||1||||1|||||2002-05-13 02:16:34|0x0040001e|10

96|127|127|0|1|977|0|3|10|2|255||1|0|1||||||||||3||||3|||||2002-05-13 02:16:34|0x00400020|5

96|128|128|0|1|985|0|2|10|2|255||1|0|0||||||||||2||||2|||||2002-05-13 02:16:34|0x00400020|6

194|196|196|0|1|1457|0|1|10|2|255||1|0|0||||||||||1||||1|||||2002-05-13 02:16:36|0x00400024|5

268|269|269|0|1|1969|0|3|10|2|255||1|0|0||||||||||3||||3|||||2002-05-13 02:16:38|0x00400028|4

从结果里我们可以知道,I_DEPENDENCY1I_DEPENDENCY2都在0x00400020中,即它们的物理存储位置都在datafile 1block 32中,并且它们对应的段头分别在datafile 1block 977datafile 1block 985

SQL> set serveroutput on

SQL> exec sys.cdba(‘00400020′,’H’);

.

The file is 1

The block is 32

 

PL/SQL procedure successfully completed

cdbaMOS上一个标准的转换RDBA的存储过程,其源码如下:

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS

 

x       NUMBER;

digits# NUMBER;

results NUMBER := 0;

file#   NUMBER := 0;

block#  NUMBER := 0;

cur_digit CHAR(1);

cur_digit# NUMBER;

 

BEGIN

      IF upper(imode) = ‘H’ THEN

           digits# := length( iblock );

           FOR x  IN 1..digits#  LOOP

                cur_digit := upper(substr( iblock, x, 1 ));

                IF cur_digit IN (‘A’,’B’,’C’,’D’,’E’,’F’)  THEN

                     cur_digit# := ascii( cur_digit ) – ascii(‘A’) +10;

                ELSE

                     cur_digit# := to_number(cur_digit);

                END IF;

                results := (results *16) + cur_digit#;

           END LOOP;

      ELSE

           IF upper(imode) = ‘D’ THEN

                –results := to_number(iblock,’XXXXXXXX’);

                results := iblock;

           ELSE

                dbms_output.put_line(‘H = Hex Input … D = Decimal Input’);

                RETURN;

           END IF;

      END IF;

 

      file#  := dbms_utility.data_block_address_file(results);

      block# := dbms_utility.data_block_address_block(results);

 

      dbms_output.put_line(‘.’);

      dbms_output.put_line( ‘The file is ‘ || file# );

      dbms_output.put_line( ‘The block is ‘ || block# );

END;

/

继续运算:

SQL> select dump(96,16) from dual;

 

DUMP(96,16)

——————

Typ=2 Len=2: c1,61

96就等价于0x02c161

 

SQL> select dump(127,16) from dual;

 

DUMP(127,16)

——————–

Typ=2 Len=3: c2,2,1c

127就等价于0x03c2021c

 

SQL> select dump(128,16) from dual;

 

DUMP(128,16)

——————–

Typ=2 Len=3: c2,2,1d

128就等价于03c2021d

 

SQL> select dump(0,16) from dual;

 

DUMP(0,16)

—————

Typ=2 Len=1: 80

0就等价于0x0180

由前面sql.bsq中关于ind$的定义我们可以知道,ind$中非聚簇键的每一行的开头的三个字段依次是object iddata object idts#,又根据上述dump结果我们可以知道,现在我们只需要去datafile 1block 32这个块中去查询字符串03c2021c 03c2021c 018003c2021d 03c2021d 0180就可以知道I_DEPENDENCY1I_DEPENDENCY2所在的行在ind$中的具体位置了。 

好了,现在我们已经可以找到I_DEPENDENCY1I_DEPENDENCY2所在的行在ind$中的具体位置但是接下来我们该如何修改呢

这就需要我们知道一点index cluster internalupdate internal的知识。

1.4      Index Cluster Internal

我现在通过一个例子来跟大家讲述一些index cluster internal的知识点。

首先创建一个名为emp_deptindex cluster,并在上面建表departmentsemployees,聚簇键是department_id

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> create cluster emp_dept(department_id number(4));

 

Cluster created

 

SQL> create table departments

  2  (department_id   number(4) primary key

  3  ,department_name varchar2(30)

  4  ,manager_id      number(6)

  5  ,location_id     number(4))

  6  CLUSTER emp_dept (department_id);

 

Table created

 

SQL> create table employees

  2  (employee_id number(6) primary key

  3  ,last_name      varchar2(25)

  4  ,job_id         varchar2(15)

  5  ,department_id number(4) references departments)

  6  CLUSTER emp_dept (department_id);

 

Table created

这里可以看到对于index cluster而言,如果不建索引,oracle这里是不允许你执行insert操作的:

SQL> insert into departments values(10,’Administration’,200,1700);

 

insert into departments values(10,’Administration’,200,1700)

 

ORA-02032: clustered tables cannot be used before the cluster index is built

 

在建完索引后,我们插入一些记录:

SQL> create index emp_dept_index ON CLUSTER emp_dept;

 

Index created

 

SQL> insert into departments values(10,’Administration’,200,1700);

SQL> insert into departments values(20,’Marketing’,201,1800);

SQL> insert into departments values(30,’Purchasing’,114,1700);

SQL> insert into departments values(40,’Human Resources’,203,2400);

SQL> commit;

SQL> insert into employees values(200,’Whalen’,’AD_ASST’,10);

SQL> insert into employees values(201,’Hartstein’,’MK_MAN’,20);

SQL> insert into employees values(202,’Fay’,’MK_REP’,20);

SQL> insert into employees values(114,’Raphaely’,’PU_MAN’,30);

SQL> commit;

此时我们有了如下示范数据:

SQL> select * from departments;

 

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

————- —————————— ———- ———–

           10 Administration                        200        1700

           20 Marketing                             201        1800

           30 Purchasing                            114        1700

           40 Human Resources                       203        2400

 

SQL> select * from employees;

 

EMPLOYEE_ID LAST_NAME                 JOB_ID          DEPARTMENT_ID

———– ————————- ————— ————-

        200 Whalen                    AD_ASST                    10

        201 Hartstein                 MK_MAN                     20

        202 Fay                       MK_REP                     20

        114 Raphaely                  PU_MAN                     30

从下述测试结果我们可以看到,当我把index cluster上的索引drop掉后,oracle这里连查询都不让我做了:

SQL> drop index emp_dept_index;

 

Index dropped

 

SQL> select * Train from employees;

 

select * from employees

 

ORA-02032: clustered tables cannot be used before the cluster index is built

 

SQL> select * from departments;

 

select * from departments

 

ORA-02032: clustered tables cannot be used before the cluster index is built

 

SQL> create index emp_dept_index ON CLUSTER emp_dept;

 

Index created

 

SQL> select * from employees;

 

EMPLOYEE_ID LAST_NAME                 JOB_ID          DEPARTMENT_ID

———– ————————- ————— ————-

        200 Whalen                    AD_ASST                    10

        201 Hartstein                 MK_MAN                     20

        202 Fay                       MK_REP                     20

        114 Raphaely                  PU_MAN                     30

 

SQL> select * from departments;

 

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

————- —————————— ———- ———–

           10 Administration                        200        1700

           20 Marketing                             201        1800

           30 Purchasing                            114        1700

           40 Human Resources                       203        2400

 

这里我们可以看到,对于index cluster而言,是不能单独的去truncate index cluster中的某个表的:

SQL> truncate table employees;

 

truncate table employees

 

ORA-03292: Table to be truncated is part of a cluster

 

这里我们可以看到,当index cluster里面还有表的时候,你是不能drop整个index cluster的:

SQL> drop cluster emp_dept;

drop cluster emp_dept

ORA-00951: cluster not empty

 

SQL> alter system checkpoint;

System altered

 

SQL> select t.*,

dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from employees t;

EMPLOYEE_ID LAST_NAME                 JOB_ID          DEPARTMENT_ID LOCATION

———– ————————- ————— ————- ————

        200 Whalen                    AD_ASST                    10 116_44490

        201 Hartstein                 MK_MAN                     20 116_44491

        202 Fay                       MK_REP                     20 116_44491

        114 Raphaely                  PU_MAN                     30 116_44492

SQL> select t.*,

dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from departments t;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID LOCATION

————- —————————— ————————————-

           10 Administration                        200        1700 116_44490

           20 Marketing                             201        1800 116_44491

           30 Purchasing                            114        1700 116_44492

           40 Human Resources                       203        2400 116_44493

从上述结果里我们可以看到聚簇键为20的记录都在datafile 116block 44491里。换算一下相关数据的16进制存储格式,如下所示:

 

SQL> select dump(20,16) from dual;

DUMP(20,16)

——————

Typ=2 Len=2: c1,15

 

SQL> select dump(201,16) from dual;

DUMP(201,16)

——————-

Typ=2 Len=3: c2,3,2

 

SQL> select dump(‘Hartstein’,16) from dual;

DUMP(‘HARTSTEIN’,16)

—————————————-

Typ=96 Len=9: 48,61,72,74,73,74,65,69,6e

 

SQL> select dump(‘MK_MAN’,16) from dual;

DUMP(‘MK_MAN’,16)

——————————-

Typ=96 Len=6: 4d,4b,5f,4d,41,4e

 

SQL> select dump(202,16) from dual;

DUMP(202,16)

——————-

Typ=2 Len=3: c2,3,3

 

SQL> select dump(‘Fay’,16) from dual;

DUMP(‘FAY’,16)

———————-

Typ=96 Len=3: 46,61,79

 

SQL> select dump(‘MK_REP’,16) from dual;

DUMP(‘MK_REP’,16)

——————————-

Typ=96 Len=6: 4d,4b,5f,52,45,50

 

SQL> select dump(‘Marketing’,16) from dual;

DUMP(‘MARKETING’,16)

—————————————-

Typ=96 Len=9: 4d,61,72,6b,65,74,69,6e,67

 

SQL> select dump(1800,16) from dual;

DUMP(1800,16)

——————

Typ=2 Len=2: c2,13

 

我们dump一下datafile 116block 44491,根据上述结果,我们可以非常清晰的解释如下dump内容中的每一个字段的含义:

Start dump data blocks tsn: 33 file#: 116 minblk 44491 maxblk 44491

buffer tsn: 33 rdba: 0x1d00adcb (116/44491)

scn: 0x076d.384f2583 seq: 0x01 flg: 0x06 tail: 0x25830601

frmt: 0x02 chkval: 0x41e1 type: 0x06=trans data

Block header dump:  0x1d00adcb

 Object id on Block? Y

 seg/obj: 0x15160  csc: 0x76d.384f2581  itc: 2  flg: O  typ: 1 – DATA

     fsl: 2  fnx: 0x1d00adca ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0003.01e.0001324b  0x12c0000a.337c.01  C—    0  scn 0x076d.384f257c

0x02   0x0007.014.00018974  0x0080006f.4165.1a  –U-    3  fsc 0x0000.384f2583

 

data_block_dump,data header at 0x11027205c

===============

tsiz: 0x1fa0

hsiz: 0x22

pbl: 0x11027205c

bdba: 0x1d00adcb

     76543210

flag=——–

ntab=3  ―― 注意这里表示这个index cluster里有3table,因为聚簇键算做table 0

nrow=4 ―― 注意这里表示这个index cluster里有4行记录,因为聚簇键deparment_id=20也算做一条记录

frre=-1

fsbo=0x22

fseo=0x1f49

avsp=0x1f27

tosp=0x1f27

0xe:pti[0]      nrow=1  offs=0

0x12:pti[1]     nrow=1  offs=1

0x16:pti[2]     nrow=2  offs=2

0x1a:pri[0]     offs=0x1f8a

0x1c:pri[1]     offs=0x1f75

0x1e:pri[2]     offs=0x1f5c

0x20:pri[3]     offs=0x1f49

block_row_dump:

 

tab 0, row 0, @0x1f8a

tl: 22 fb: K-H-FL– lb: 0x0  cc: 1

curc: 3 comc: 3 pk: 0x1d00adcb.0 nk: 0x1d00adcb.0

――curc表示这个block里不算聚簇键有三条记录

――comc表示这个block里不算聚簇键有三条commit后的记录

col  0: [ 2]  c1 15                          ――20

 

tab 1, row 0, @0x1f75

tl: 21 fb: -CH-FL– lb: 0x0  cc: 3 cki: 0

col  0: [ 9]  4d 61 72 6b 65 74 69 6e 67     ――Marketing

col  1: [ 3]  c2 03 02                       ――201

col  2: [ 2]  c2 13                          ――1800

tab 2, row 0, @0x1f5c

tl: 25 fb: -CH-FL– lb: 0x2  cc: 3 cki: 0

col  0: [ 3]  c2 03 02                       ――201

col  1: [ 9]  48 61 72 74 73 74 65 69 6e     ――Hartstein

col  2: [ 6]  4d 4b 5f 4d 41 4e               ――MK_MAN

tab 2, row 1, @0x1f49

tl: 19 fb: -CH-FL– lb: 0x2  cc: 3 cki: 0

col  0: [ 3]  c2 03 03                       ――202

col  1: [ 3]  46 61 79                       ――FAY

col  2: [ 6]  4d 4b 5f 52 45 50               ――MK_REP

end_of_block_dump

End dump data blocks tsn: 33 file#: 116 minblk 44491 maxblk 44491

 

现在我们用BBED去看一下这条记录:

BBED> set file 116 block 44491

        FILE#           116

        BLOCK#          44491

 

BBED> map /v

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491                                 Dba:0x1d00adcb

————————————————————

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0      

    ub1 type_kcbh                           @0      

    ub1 frmt_kcbh                           @1      

    ub1 spare1_kcbh                         @2      

    ub1 spare2_kcbh                         @3      

    ub4 rdba_kcbh                           @4      

    ub4 bas_kcbh                            @8      

    ub2 wrp_kcbh                            @12     

    ub1 seq_kcbh                            @14     

    ub1 flg_kcbh                            @15     

    ub2 chkval_kcbh                         @16     

    ub2 spare3_kcbh                         @18     

 

 struct ktbbh, 72 bytes                     @20     

    ub1 ktbbhtyp                            @20     

    union ktbbhsid, 4 bytes                 @24     

    struct ktbbhcsc, 8 bytes                @28     

    b2 ktbbhict                             @36     

    ub1 ktbbhflg                            @38     

    ub1 ktbbhfsl                            @39     

    ub4 ktbbhfnx                            @40     

    struct ktbbhitl[2], 48 bytes            @44     

 

 struct kdbh, 14 bytes                      @92     

    ub1 kdbhflag                            @92     

    b1 kdbhntab                             @93     

    b2 kdbhnrow                             @94     

    sb2 kdbhfrre                            @96     

    sb2 kdbhfsbo                            @98     

    sb2 kdbhfseo                            @100    

    b2 kdbhavsp                             @102    

    b2 kdbhtosp                             @104    

 

 struct kdbt[3], 12 bytes                   @106    

    b2 kdbtoffs                             @106    

    b2 kdbtnrow                             @108    

 

 sb2 kdbr[4]                                @118    

 

 ub1 freespace[7975]                        @126    

 

 ub1 rowdata[87]                            @8101   

 

 ub4 tailchk                                @8188   

 

BBED> p *kdbr[0]

rowdata[65]

———–

ub1 rowdata[65]                             @8166     0xac

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8166 to 8191           Dba:0x1d00adcb

————————————————————————

 ac000100 0300031d 00adcb00 001d00ad cb000002 c1152583 0601

 

 <32 bytes per line>

 

tab 0, row 0, @0x1f8a

tl: 22 fb: K-H-FL– lb: 0x0  cc: 1

curc: 3 comc: 3 pk: 0x1d00adcb.0 nk: 0x1d00adcb.0

col  0: [ 2]  c1 15                          ――表示聚簇键20

 

#define KDRHFK 0x80  Cluster Key

#define KDRHFC 0x40  Clustered table member

#define KDRHFH 0x20  Head piece of row

#define KDRHFD 0x10  Deleted row

#define KDRHFF 0x08  First data piece|

#define KDRHFL 0x04  Last data piece

#define KDRHFP 0x02  First column continues from Previous piece

#define KDRHFN 0x01  Last column continues in Next piece

注意到这里聚簇键20所在的行的行头不是我们常见的0x2c,而是0xac,因为0xac = 0x80+0x20+0x08+0x04

 

BBED> p *kdbr[1]

rowdata[44]

———–

ub1 rowdata[44]                             @8145     0x6c

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8145 to 8191           Dba:0x1d00adcb

————————————————————————

 6c000300 094d6172 6b657469 6e6703c2 030202c2 13ac0001 00030003 1d00adcb

 00001d00 adcb0000 02c11525 830601

 

 <32 bytes per line>

 

tab 1, row 0, @0x1f75

tl: 21 fb: -CH-FL– lb: 0x0  cc: 3 cki: 0

col  0: [ 9]  4d 61 72 6b 65 74 69 6e 67     ――Marketing

col  1: [ 3]  c2 03 02                       ――201

col  2: [ 2]  c2 13                          ――1800

注意到这里index cluster里所在的非聚簇键行的行头不是我们常见的0x2c,而是0x6c,因为0x6c=0x40+0x20+0x08+0x04

 

BBED> p *kdbr[2]

rowdata[19]

———–

ub1 rowdata[19]                             @8120     0x6c

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8120 to 8191           Dba:0x1d00adcb

————————————————————————

 6c020300 03c20302 09486172 74737465 696e064d 4b5f4d41 4e6c0003 00094d61

 726b6574 696e6703 c2030202 c213ac00 01000300 031d00ad cb00001d 00adcb00

 0002c115 25830601

 

 <32 bytes per line>

 

tab 2, row 0, @0x1f5c

tl: 25 fb: -CH-FL– lb: 0x2  cc: 3 cki: 0

col  0: [ 3]  c2 03 02                       ――201

col  1: [ 9]  48 61 72 74 73 74 65 69 6e     ――Hartstein

col  2: [ 6]  4d 4b 5f 4d 41 4e               ――MK_MAN

 

现在我们再在上述index cluster中插入几条记录,然后来观察我们一直想知道的从index cluster中删除一条记录oracle到底做了什么:

BBED> set file 116 block 44491

        FILE#           116

        BLOCK#          44491

 

BBED> p kdbr

sb2 kdbr[0]                                 @122      8074

sb2 kdbr[1]                                 @124      8053

sb2 kdbr[2]                                 @126      8028

sb2 kdbr[3]                                 @128      5

sb2 kdbr[4]                                 @130      7908

sb2 kdbr[5]                                 @132     -1

sb2 kdbr[6]                                 @134      7946

 

 

BBED> p *kdbr[0]

rowdata[185]

————

ub1 rowdata[185]                            @8166     0xac

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8166 to 8191           Dba:0x1d00adcb

————————————————————————

 ac000100 0400041d 00adcb00 001d00ad cb000002 c115cdff 0602

 

 <32 bytes per line>

现在curccurrent row count)为4comc (commit row count)也为4

 

当我从sqlplus中删掉kdbr[6]后:

BBED> set file 116 block 44491

        FILE#           116

        BLOCK#          44491

 

BBED> p kdbr

sb2 kdbr[0]                                 @122      8074

sb2 kdbr[1]                                 @124      8053

sb2 kdbr[2]                                 @126      8028

sb2 kdbr[3]                                 @128      5

sb2 kdbr[4]                                 @130      7908

sb2 kdbr[5]                                 @132     -1

sb2 kdbr[6]                                 @134      7946

 

BBED> p *kdbr[0]

rowdata[185]

————

ub1 rowdata[185]                            @8166     0xac

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8166 to 8191           Dba:0x1d00adcb

————————————————————————

 ac000100 0400031d 00adcb00 001d00ad cb000002 c1151c77 0601

 

 <32 bytes per line>

从结果里我们可以看出,当我删掉kdbr[6]后,现在comc减了1,但是curc未变。

 

BBED> p *kdbr[1]

rowdata[164]

————

ub1 rowdata[164]                            @8145     0x6c

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8145 to 8191           Dba:0x1d00adcb

————————————————————————

 6c000300 094d6172 6b657469 6e6703c2 030202c2 13ac0001 00040003 1d00adcb

 00001d00 adcb0000 02c1151c 770601

 

 <32 bytes per line>

 

BBED> p *kdbr[6]

rowdata[57]

———–

ub1 rowdata[57]                             @8038     0x7c

 

BBED> dump

 File: /cadras01/oradata/astca/drastbs_19.dbf (116)

 Block: 44491            Offsets: 8038 to 8191           Dba:0x1d00adcb

————————————————————————

 7c020300 03c20304 03466179 064d4b5f 5245507c 02030003 c2030303 46617906

 4d4b5f52 45507c02 030003c2 03020948 61727473 7465696e 064d4b5f 4d414e7c

 02030003 c2030303 46617906 4d4b5f52 45506c00 030003c2 03020948 61727473

 7465696e 064d4b5f 4d414e6c 00030009 4d61726b 6574696e 6703c203 0202c213

 ac000100 0400031d 00adcb00 001d00ad cb000002 c1151c77 0601

 

 <32 bytes per line>

 

从结果里我们可以看出——oracleindex cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc1

1.5      Oracle Update Internal

oracle中,当你update一条记录的时候,oracle在大部分情况下并没有直接修改原来的行,而是新插入一条记录,同时修改原来行在row directory里的指针,我们来看一下这个知识点:

通过构造一个测试案例来仔细说明Oracleupdate internal这个知识点:

SQL>create table update_internal(id number,name varchar2(20));

SQL>insert into update_internal values(1,’A’);

SQL>insert into update_internal values(2,’B’);

SQL>insert into update_internal values(3,’C’);

SQL>commit;

SQL>alter system checkpoint;

SQL>select t.id,t.name,

dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from update_internal t;

 

        ID NAME                 LOCATION

———- ——————– ————————————————

         1 A                    1_52474

         2 B                    1_52474

         3 C                    1_52474

从结果里我们可以看到现在update_internal表里的三条记录在file 1,block 52474里。

 

BBED> set file 1

        FILE#           1

BBED> set block 52474

        BLOCK#          52474

BBED> map  

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474                                 Dba:0x0040ccfa

————————————————————

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0      

 struct ktbbh, 72 bytes                     @20     

 struct kdbh, 14 bytes                      @92     

 struct kdbt[1], 4 bytes                    @106    

 sb2 kdbr[3]                                @110    

 ub1 freespace[8048]                        @116    

 ub1 rowdata[24]                            @8164   

 ub4 tailchk                                @8188   

 

BBED> p wholesale NBA jerseys kdbr

sb2 kdbr[0]                                 @110      8088

sb2 kdbr[1]                                 @112      8080

sb2 kdbr[2]                                 @114      8072

从上述结果里我们可以看出:现在file 1,block 52474row directory里有三条记录,第一条记录的相对offset(指针)8088,第二条记录的相对offset(指针)8080,第三条记录的相对offset(指针)8072——从这里也可以看出,oracleblock里写记录的时候,实际上是从块尾部开始往上(块头)写的,类似于往一个木桶里倒水

现在我们去看一下第一条记录:

BBED> p *kdbr[0]

rowdata[16]

———–

ub1 rowdata[16]                             @8180     0x2c

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474            Offsets: 8180 to 8191           Dba:0x0040ccfa

————————————————————————

 2c010202 c1020141 cdd60601

 

 <32 bytes per line>

 

第一条记录我们插入的是1’A’

SQL> select dump(1,16) from dual;

 

DUMP(1,16)

—————–

Typ=2 Len=2: c1,2

 

SQL> select dump(‘A’,16) from dual;

 

DUMP(‘A’,16)

—————-

Typ=96 Len=1: 41

所以第一条记录在块里一定是02C1020141

 

同理,第二条记录我们插入的是2’B’ 所以第二条记录在块里一定是02C1030142

BBED> p *kdbr[1]

rowdata[8]

———-

ub1 rowdata[8]                              @8172     0x2c

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474            Offsets: 8172 to 8191           Dba:0x0040ccfa

————————————————————————

 2c010202 c1030142 2c010202 c1020141 cdd60601

 

 <32 bytes per line>

 

同理,第三条记录我们插入的是3’C’ 所以第三条记录在块里一定是02C1040143

BBED> p *kdbr[2]

rowdata[0]

———-

ub1 rowdata[0]                              @8164     0x2c

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474            Offsets: 8164 to 8191           Dba:0x0040ccfa

————————————————————————

 2c010202 c1040143 2c010202 c1030142 2c010202 c1020141 cdd60601

 

 <32 bytes per line>

 

BBED的结果我们可以看出,这三条记录oracle里是顺序且连续的(紧挨着在一起)存储,那么现在问题来了,如果我把第一条记录的’A’改为’AA’

SQL> select dump(‘AA’,16) from dual;

 

DUMP(‘AA’,16)

——————-

Typ=96 Len=2: 41,41

第一条记录在块里应该由02C1020141变为02C102024141即第一条记录行的长度变长了!

所以如果oracle还在原来第一条记录的地方直接扩展的话,则第二条、第三条记录都要顺序往上挪(并且其对应的row directory里的指针也都得跟着一起修改)——这个代价就太大了!

 

Oracle不会这么做的。实际上,在大多数情况下(注意这里不是所有的情况oracleupdate的时候,原值并没有抹去,而是插入了一条新记录,oracle这里只是更改了原来那条记录在row directory里的指针位置——这么做的好处是显而易见的,无论update之后的行如何扩展或者如何缩小,oracle这么做后就能保证其他的行不会受到update之后新行的影响。

 

我们来测试一下。在改之前,先dump一下file 1,block 52474

Start dump data blocks tsn: 0 file#: 1 minblk 52474 maxblk 52474

buffer tsn: 0 rdba: 0x0040ccfa (1/52474)

scn: 0x0000.000acdd6 seq: 0x01 flg: 0x06 tail: 0xcdd60601

frmt: 0x02 chkval: 0xb912 type: 0x06=trans data

Block header dump:  0x0040ccfa

 Object id on Block? Y

 seg/obj: 0x76bd  csc: 0x00.acd89  itc: 2  flg: O  typ: 1 – DATA

     fsl: 0  fnx: 0x0 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0012.016.000000d2  0x02c001dc.0026.32  –U-    3  fsc 0x0000.000acdd6

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

 

data_block_dump,data header at 0x11027305c

===============

tsiz: 0x1fa0

hsiz: 0x18

pbl: 0x11027305c

bdba: 0x0040ccfa

     76543210

flag=——–

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f88

avsp=0x1f6d

tosp=0x1f6d

0xe:pti[0]      nrow=3  offs=0

0x12:pri[0]     offs=0x1f98

0x14:pri[1]     offs=0x1f90

0x16:pri[2]     offs=0x1f88

block_row_dump:

tab 0, row 0, @0x1f98

tl: 8 fb: –H-FL– lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [ 1]  41

tab 0, row 1, @0x1f90

tl: 8 fb: –H-FL– lb: 0x1  cc: 2

col  0: [ 2]  c1 03

col  1: [ 1]  42

tab 0, row 2, @0x1f88

tl: 8 fb: –H-FL– lb: 0x1  cc: 2

col  0: [ 2]  c1 04

col  1: [ 1]  43

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 52474 maxblk 52474

 

现在我把第一条记录的’A’改为’AA’

SQL>select * from update_internal;

 

        ID NAME

———- ——————–

         1 A

         2 B

         3 C

 

SQL>update update_internal set name=’AA’ where id=1;

 

1 row updated.

 

SQL>commit;

 

Commit complete.

 

SQL>select * from update_internal;

 

        ID NAME

———- ——————–

         1 AA

         2 B

         3 C

 

SQL>alter system checkpoint;

 

System altered.

我们现在再去看一下这个blockrow directory

BBED> set file 1

        FILE#           1

 

BBED> set block 52474

        BLOCK#          52474

 

BBED> map

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474                                 Dba:0x0040ccfa

————————————————————

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0      

 struct ktbbh, 72 bytes                     @20     

 struct kdbh, 14 bytes                      @92     

 struct kdbt[1], 4 bytes                    @106    

 sb2 kdbr[3]                                @110    

 ub1 freespace[8039]                        @116    

 ub1 rowdata[33]                            @8155   

 ub4 tailchk                                @8188   

 

BBED> p kdbr

sb2 kdbr[0]                                 @110      8063

sb2 kdbr[1]                                 @112      8080

sb2 kdbr[2]                                 @114      8072

从结果里我们可以看出,现在第一条记录在row directory里的相对offset(指针)已经由8088变成了8063,而第二条记录和第三条记录的相对offset(指针)依然还是80808072

现在我们分别去看一下相对offset80888063所指向的记录:

BBED> p kdbr

sb2 kdbr[0]                                 @110      8063

sb2 kdbr[1]                                 @112      8080

sb2 kdbr[2]                                 @114      8072

 

BBED> p *kdbr[0]

rowdata[0]

———-

ub1 rowdata[0]                              @8155     0x2c

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474            Offsets: 8155 to 8191           Dba:0x0040ccfa

————————————————————————

 2c020202 c1020241 412c0002 02c10401 432c0002 02c10301 422c0002 02c10201

 41df3f06 01

 

 <32 bytes per line>

从结果里我们可以看到,现在8063所指向的记录确实就是我们修改后的第一条记录。

 

我们现在再去看一下原来的第一条记录,注意这里虽然原来的第一条记录的相对offset8088,但我们定位的时候,还需要加上一个base offset

BBED> set offset 8180

        OFFSET          8180

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 52474            Offsets: 8180 to 8191           Dba:0x0040ccfa

————————————————————————

 2c000202 c1020141 df3f0601

 

 <32 bytes per line>

从结果里我们可以看到,原先的第一条记录,oracle并没有去修改(仅仅只清除了行头的lock byte,01变成了00),这里还是保持了原来的值。

 

现在我们再去看一下dump的结果:

Start dump data blocks tsn: 0 file#: 1 minblk 52474 maxblk 52474

buffer tsn: 0 rdba: 0x0040ccfa (1/52474)

scn: 0x0000.000adf3f seq: 0x01 flg: 0x06 tail: 0xdf3f0601

frmt: 0x02 chkval: 0xb05c type: 0x06=trans data

Block header dump:  0x0040ccfa

 Object id on Block? Y

 seg/obj: 0x76bd  csc: 0x00.adf3e  itc: 2  flg: O  typ: 1 – DATA

     fsl: 0  fnx: 0x0 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0012.016.000000d2  0x02c001dc.0026.32  C—    0  scn 0x0000.000acdd6

0x02   0x000c.015.0000015c  0x02c00adc.002a.0e  –U-    1  fsc 0x0000.000adf3f

 

data_block_dump,data header at 0x11027305c

===============

tsiz: 0x1fa0

hsiz: 0x18

pbl: 0x11027305c

bdba: 0x0040ccfa

     76543210

flag=——–

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f7f

avsp=0x1f6d

tosp=0x1f6d

0xe:pti[0]      nrow=3  offs=0

0x12:pri[0]     offs=0x1f7f

0x14:pri[1]     offs=0x1f90

0x16:pri[2]     offs=0x1f88

block_row_dump:

tab 0, row 0, @0x1f7f

tl: 9 fb: –H-FL– lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [ 2]  41 41

tab 0, row 1, @0x1f90

tl: 8 fb: –H-FL– lb: 0x0  cc: 2

col  0: [ 2]  c1 03

col  1: [ 1]  42

tab 0, row 2, @0x1f88

tl: 8 fb: –H-FL– lb: 0x0  cc: 2

col  0: [ 2]  c1 04

col  1: [ 1]  43

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 52474 maxblk 52474

Dump的结果中oracle已经把原先的那条修改之前的记录忽略掉了——这是可以理解的,因为oracleblock的时候会完全按照row directory里记录的指针去读行记录。

 

综上所述,我们最后的结论是:

1、  在大多数情况下,oracleupdate的时候,原值并没有抹去,而是插入了一条新记录,oracle这里只是更改了原来那条记录在row directory里的指针位置——这么做的好处是显而易见的,无论update之后的行如何扩展或者如何缩小,oracle这么做后就能保证其他的行不会受到update之后新行的影响。

2、  在大多数情况下,oracleupdate的时候,原值并没有抹去,实际上oracle一点都没有碰原来的行(或者说仅仅只清除了行头的lock byte),也没有像通常的delete那样去修改行头(oracledelete非索引块的时候会在原来的行头递增一个0x10),所以从严格意思上来说,oracle在在大多数情况下做update的时候,并没有去delete原来的行,而只是insert了一个新行,并修改了原来行在row directory里的指针

3、  普通的dump 不太容易看到oracle的这种机制,但用BBED就能够一目了然的看到oracle的这种update机制。

4、  在大多数情况下,oracleupdate的时候,原值并没有抹去,而是插入了一条新记录,oracle这里只是更改了原来那条记录在row directory里的指针位置——所以对修改后的那条记录而言,其rowid是保持不变的

 

1.6      尝试恢复操作

有了上两节的知识点,我们可以开始尝试我们的修复工作了。

首先如法炮制找到I_DEPENDENCY1I_DEPENDENCY2ind$中所在的行记录。先从I_DEPENDENCY1开始:

BBED> set file 1 block 32

        FILE#           1

        BLOCK#          32

 

BBED> find /x 03c2021c03c2021c0180

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  611 to 1122           Dba:0x00400020

————————————————————————

 03c2021c 03c2021c 018002c1 0203c20a 4e018002 c10402c1 0b02c103 03c20338

 ff02c102 02c10202 c102ffff ffffffff ffffff02 c104ffff ff02c104 ffffffff

 07786605 0d031123 6c022407 04c30404 44018002 c10204c3 060752ff ff02c108

 ……省略显示部分内容

 8003c202 1d6c0014 0902c10f 02c10f02 c11f0180 094e4557 244f574e 455202c1

 

 <32 bytes per line>

 

BBED> f

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets: 2250 to 2761           Dba:0x00400020

————————————————————————

 03c2021c 03c2021c 018002c1 0203c20a 4e018002 c10402c1 0b02c103 03c20338

 ff02c102 018002c1 02ffffff ffffffff ffff02c1 04ffffff 02c104ff ffffff07

 7866050d 0311236c 00090703 c2021c02 c10402c1 04018001 80018002 c1040180

 01806c00 090703c2 021c02c1 0302c103 01800180 018002c1 03018001 806c0009

  ……省略显示部分内容

 0d031123 6c000902 03c2020b 02c10302 c1030180 01800180 02c10301 8001806c

 

 <32 bytes per line>

 

BBED> f

BBED-00212: search string not found

 

这里为什么会找到两条记录是因为当你把DEPENDENCY$ move后,其上的索引I_DEPENDENCY1I_DEPENDENCY2状态要由valid变成unusable具体来说,就是需要把I_DEPENDENCY1I_DEPENDENCY2ind$中对应的行记录的FLAGS0改为1,这里可参见sql.bsq中对ind$FLAGS的注释:

flags         number not null,     

                /* mutable flags: anything permanent should go into property */

                                                    /* unusable (dls) : 0x01 */

                                                    /* analyzed       : 0x02 */

                                                    /* no logging     : 0x04 */

                                    ……省略显示部分内容

                                 /* index usage monitoring enabled : 0x10000 */

 

好了,有了上述update internal的知识点,我们就非常好理解这里为什么会出现两次I_DEPENDENCY1所对应的行记录,并且由上述知识点我们也知道了我们应该去修改offset小的那行记录,即第一次查询到的offset 611所在的行记录。

这里又因为要定位到index cluster的非聚簇键所在行的行头,所以要往前移4byte

BBED> set offset 607

        OFFSET          607

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  607 to 1118           Dba:0x00400020

————————————————————————

 6c022107 03c2021c 03c2021c 018002c1 0203c20a 4e018002 c10402c1 0b02c103

 03c20338 ff02c102 02c10202 c102ffff ffffffff ffffff02 c104ffff ff02c104

 ffffffff 07786605 0d031123 6c022407 04c30404 44018002 c10204c3 060752ff

……省略显示部分内容

 02018001 8003c202 1d6c0014 0902c10f 02c10f02 c11f0180 094e4557 244f574e

 

 <32 bytes per line>

 

BBED> x /rnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

rowdata[75]                                 @607    

———–

flag@607:  0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@608:  0x02

cols@609:    33

ckix@610:     7

 

col    0[3] @611: 127

col    1[3] @615: 127

col    2[1] @619: 0

col    3[2] @621: 1

col    4[3] @624: 977

col    5[1] @628: 0

col    6[2] @630: 3

col    7[2] @633: 10

col    8[2] @636: 2

col    9[3] @639: 255

col   10[0] @643: *NULL*

col   11[2] @644: 1

col   12[2] @647: 1

col   13[2] @650: 1

……省略显示部分内容

col   31[0] @674: *NULL*

col   32[7] @675: -0

从结果里可以看到现在I_DEPENDENCY1flags的值确实为1

 

我们再来看其修改之前的flags的值:

BBED> set offset 2246

        OFFSET          2246

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets: 2246 to 2757           Dba:0x00400020

————————————————————————

 6c002107 03c2021c 03c2021c 018002c1 0203c20a 4e018002 c10402c1 0b02c103

 03c20338 ff02c102 018002c1 02ffffff ffffffff ffff02c1 04ffffff 02c104ff

 ffffff07 7866050d 0311236c 00090703 c2021c02 c10402c1 04018001 80018002

 ……省略显示部分内容

 38ff02c1 02018002 c102ffff ffffffff ffffff02 c103ffff ff02c103 ffffffff

 07786605 0d031123 6c000902 03c2020b 02c10302 c1030180 01800180 02c10301

 

 <32 bytes per line>

 

BBED> x /rnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

rowdata[1714]                               @2246   

————-

flag@2246: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@2247: 0x00

cols@2248:   33

ckix@2249:    7

 

col    0[3] @2250: 127

col    1[3] @2254: 127

col    2[1] @2258: 0

col    3[2] @2260: 1

col    4[3] @2263: 977

col    5[1] @2267: 0

col    6[2] @2269: 3

col    7[2] @2272: 10

col    8[2] @2275: 2

col    9[3] @2278: 255

col   10[0] @2282: *NULL*

col   11[2] @2283: 1

col   12[1] @2286: 0

col   13[2] @2288: 1

col   14[0] @2291: *NULL*

col   15[0] @2292: *NULL*

……省略显示部分内容

col   31[0] @2312: *NULL*

col   32[7] @2313: -0

从结果里可以看到修改以前I_DEPENDENCY1flags的值确实为0

 

现在我们把行头由0x6c改为0x7c

BBED> set offset 607

        OFFSET          607

 

BBED> modify /x 7c

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  607 to 1118           Dba:0x00400020

————————————————————————

 7c022107 03c2021c 03c2021c 018002c1 0203c20a 4e018002 c10402c1 0b02c103

 03c20338 ff02c102 02c10202 c102ffff ffffffff ffffff02 c104ffff ff02c104

 ffffffff 07786605 0d031123 6c022407 04c30404 44018002 c10204c3 060752ff

……省略显示部分内容

 02018001 8003c202 1d6c0014 0902c10f 02c10f02 c11f0180 094e4557 244f574e

 

 <32 bytes per line>

 

再来改I_DEPENDENCY2

BBED> set offset 0

        OFFSET          0

 

BBED> find /x 03c2021d03c2021d0180

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  536 to 1047           Dba:0x00400020

————————————————————————

 03c2021d 03c2021d 018002c1 0203c20a 56018002 c10302c1 0b02c103 03c20338

 ff02c102 02c10201 80ffffff ffffffff ffff02c1 03ffffff 02c103ff ffffff07

 7866050d 0311237c 02210703 c2021c03 c2021c01 8002c102 03c20a4e 018002c1

 ……省略显示部分内容

 ff02c111 018003c2 093502c1 02018001 8002c114 6c001409 02c11002 c11003c2

 

 <32 bytes per line>

 

BBED> f

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets: 2122 to 2633           Dba:0x00400020

————————————————————————

 03c2021d 03c2021d wholesale MLB jerseys 018002c1 0203c20a 56018002 c10302c1 0b02c103 03c20338

 ff02c102 01800180 ffffffff ffffffff ff02c103 ffffff02 c103ffff ffff0778

 66050d03 11236c00 090703c2 021d02c1 0602c103 01800180 018002c1 06018001

 ……省略显示部分内容

 0311236c 00090403 c2020c02 c10302c1 03018001 80018002 c1030180 01806c00

 

 <32 bytes per line>

 

BBED> f

BBED-00212: search string not found

 

同理这里也要往前移4byte

BBED> set offset 532

        OFFSET          532

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  532 to 1043           Dba:0x00400020

————————————————————————

 6c022107 03c2021d 03c2021d 018002c1 0203c20a 56018002 c10302c1 0b02c103 Excerpt

 03c20338 ff02c102 02c10201 80ffffff ffffffff ffff02c1 03ffffff 02c103ff

 ffffff07 7866050d 0311237c 02210703 c2021c03 c2021c01 8002c102 03c20a4e

  ……省略显示部分内容

 02c11102 c11102c1 1401800b 53455324 41435449 4f4e5302 c10202c1 140180ff

 ff0180ff ff02c111 018003c2 093502c1 02018001 8002c114 6c001409 02c11002

 

 <32 bytes per line>

 

BBED> modify /x 7c

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets:  532 to 1043           Dba:0x00400020

————————————————————————

 7c022107 03c2021d 03c2021d 018002c1 0203c20a 56018002 c10302c1 0b02c103

 03c20338 ff02c102 02c10201 80ffffff ffffffff ffff02c1 03ffffff 02c103ff

 ffffff07 7866050d 0311237c 02210703 c2021c03 c2021c01 8002c102 03c20a4e

  ……省略显示部分内容

 02c11102 c11102c1 1401800b 53455324 41435449 4f4e5302 c10202c1 140180ff

 ff0180ff ff02c111 018003c2 093502c1 02018001 8002c114 6c001409 02c11002

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 32:

current = 0x3ab4, required = 0x3ab4

 

注意,你这里改完后可以用BBED内置的verify命令去校验这个你修改后的block,从校验的结果可以看出,BBED只不过是调用了一下dbv并把dbv的校验结果print出来了而已:

BBED> verify

DBVERIFY – Verification starting

FILE = /dras20/testdb/system01.dbf

BLOCK = 32

 

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

data header at 0x11013805c

kdbchk:  key comref count wrong

         keyslot=7

Block 32 failed with check code 6121

 

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

 

注意到这里我们只改了非聚簇键所在行的行头(0x6c修改为0x7c),但并没有把这两条记录所对应的聚簇键所在行的行头中记录的comc2。所以在这种情况下,用dbv校验oracle是会报错的:

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/bin]#dbv file=/dras20/testdb/system01.dbf blocksize=8192

 

DBVERIFY: Release 9.2.0.6.0 – Production on Fri Aug 20 19:29:43 2010

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk:  key comref count wrong

         keyslot=7

Page 32 failed with check code 6121

 

 

DBVERIFY – Verification complete

 

Total Pages Examined         : 52480

Total Pages Processed (Data) : 36943

Total Pages Failing   (Data) : 1

Total Pages Processed (Index): 4085

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 1750

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 9702

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 676263 (0.676263)

Oracle这里明确提示我们你这个index cluster block里的comc不对,这个comc的位置就在row directory的第8行(注意这里kdbr是从0开始算起),即kdbr[7]

注意这里不要去直接修改,因为oracleblock的检查种类繁多,而且十分严格。

如果你尝试去修改上述block,一招不慎,你就可能会碰到下述错误,五花八门,不一而足:

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk: bad row tab 3, slot 5

Page 32 failed with check code 6258

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at Miami Dolphins Jerseys 0x11015805c

kdbchk: fsbo(282) wrong, (hsz 280)

Page 32 failed with check code 6129

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk: row count in table index incorrect

Page 32 failed with check code 6125

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk: table index offset incorrect

        tab 4

Page 32 failed with check code 6124

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk: non-existent key referenced

        table=1 slot=0

Page 32 failed with check code 6140

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

data header at 0x11015805c

kdbchk: key curref < comref

        slot=7 curref=15 comref=17

Page 32 failed with check code 6119

 

DBVERIFY – Verification starting;

FILE = /dras20/testdb/system01.dbf

BLOCK = 32

 

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

data header at 0x11013805c

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

        used=6662 fsc=0 avsp=1291 dtl=8096

Block 32 failed with check code 6110

 

DBVERIFY – Verification starting

FILE = /dras20/testdb/system01.dbf

BLOCK = 32

 

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

data header at 0x11013805c

kdbchk: space available on commit is incorrect

        tosp=1434 fsc=0 stb=8 avsp=1434

Block 32 failed with check code 6111

我们先把这个错误留在这里,一会儿再来修改。

 

我们这里先用隐含参数_db_always_check_system_ts=FALSE把上述库给起起来这个参数_db_always_check_system_ts的作用就是告诉Oracle不要去对system表空间下的block执行关于块的各种检查。

 

[P550_04_LA:oracle@:/dras20/testdb]#cat inittestdb.ora

*.aq_tm_processes=1

*.background_dump_dest=’/cadrasu01/app/oracle/admin/testdb/bdump’

*.compatible=’9.2.0.0.0′

……省略显示部分内容

*.log_archive_start=TRUE

*._db_always_check_system_ts=FALSE

 

现在我们已经可以把上述库给起起来了:

SQL>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

 

Total System Global Area  504858456 bytes

Fixed Size                   743256 bytes

Variable Size             285212672 bytes

Database Buffers          218103808 bytes

Redo Buffers                 798720 bytes

Database mounted.

Database opened.

 

现在我们通过手工修改ind$I_DEPENDENCY1I_DEPENDENCY2所在的行已经骗过了oracle,让oracle以为DEPENDENCY$上没有索引,从而成功打开了上述库。

1.7      手工的DDL维护工作

现在我们来做一些收尾工作。

通过10046事件可以知道oracle在进行drop indexDDL操作时,实际上是去修改了系列的数据字典表,那么我这里完全照做一遍,实际上也就达到了把索引I_DEPENDENCY1I_DEPENDENCY2 彻底Drop掉的目的:

SQL>delete from icol$ where obj#=127;

3 rows deleted.

 

SQL>delete from icol$ where obj#=128;

2 rows deleted.

 

SQL>delete from obj$ where obj# =127;

1 row deleted.

 

SQL>delete from obj$ where obj# =128;

1 row deleted.

 

ind$中可以知道I_DEPENDENCY1的段头位置:

SQL>delete from seg$ where ts#=0 and file#=1 and block#=977;

 

1 row deleted.

 

ind$中可以知道I_DEPENDENCY2的段头位置:

SQL>delete from seg$ where ts#=0 and file#=1 and block#=985;

 

1 row deleted.

 

SQL>commit;

 

Commit complete.

好了,我们现在再来重新创建索引I_DEPENDENCY1I_DEPENDENCY2

SQL>create unique index SYS.I_DEPENDENCY1 on SYS.DEPENDENCY$ (D_OBJ#, D_TIMESTAMP, ORDER#) tablespace SYSTEM;

 

Index created.

 

SQL>create index SYS.I_DEPENDENCY2 on SYS.DEPENDENCY$ (P_OBJ#, P_TIMESTAMP) tablespace SYSTEM;

 

Index created.

 

 

SQL>select count(*) from dba_tables;

 

  COUNT(*)

———-

       816

 

SQL>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 – 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 – Production

 

现在上述库基本上已经没有大问题了,我们可以看到现在各个表的数据是能够exp出去的:

[P550_04_LA:oracle@:/dras20/testdb]#exp scott/tiger@testdb file=emp.dmp tables=emp

 

Export: Release 9.2.0.6.0 – Production on Fri Aug 20 20:14:50 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

About to export specified tables via Conventional Path …

. . exporting table                            EMP         14 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

 

现在我们再来修正上述comc的错误:

SQL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

 

BBED> set file 1 block 32

        FILE#           1

        BLOCK#          32

 

BBED> p *kdbr[7]

rowdata[6355]

————-

ub1 rowdata[6355]                           @8012     0xac

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets: 8012 to 8191           Dba:0x00400020

————————————————————————

 ac000100 0f001100 40002000 07004000 20000702 c161ac00 01000500 05004000

 20000600 40002000 0602c160 ac000100 07000700 40002000 05004000 20000502

 c15fac00 01000600 06004000 20000400 40002000 0402c15e ac000100 03000300

 40002000 03004000 20000302 c15dac00 01000800 08004000 20000200 40002000

 0202c15c ac000100 13001300 40002000 01004000 20000102 c15bac00 01000700

 07004000 20000000 40002000 0002c15a a51b0601

 

 <32 bytes per line>

 

BBED> modify /x ac0001000f000f

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/testdb/system01.dbf (1)

 Block: 32               Offsets: 8012 to 8191           Dba:0x00400020

————————————————————————

 ac000100 0f000f00 40002000 07004000 20000702 c161ac00 01000500 05004000

 20000600 40002000 0602c160 ac000100 07000700 40002000 05004000 20000502

 c15fac00 01000600 06004000 20000400 40002000 0402c15e ac000100 03000300

 40002000 03004000 20000302 c15dac00 01000800 08004000 20000200 40002000

 0202c15c ac000100 13001300 40002000 01004000 20000102 c15bac00 01000700

 07004000 20000000 40002000 0002c15a a51b0601

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 32:

current = 0x1edd, required = 0x1edd

 

改完后可以看到现在dbv已经不报错了:

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/bin]#dbv file=/dras20/testdb/system01.dbf blocksize=8192

 

DBVERIFY: Release 9.2.0.6.0 – Production on Fri Aug 20 20:24:39 2010

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

 

 

DBVERIFY – Verification complete

 

Total Pages Examined         : 52480

Total Pages Processed (Data) : 36700

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 4431

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 1750

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 9599

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 698148 (0.698148)

 

现在去掉隐含参数_db_always_check_system_ts后我们一样可以把上述库打开了:

[P550_04_LA:oracle@:/dras20/testdb]#cat inittestdb.ora|grep _db_always_check_system_ts

[P550_04_LA:oracle@:/dras20/testdb]#sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 9.2.0.6.0 – Production on Fri Aug 20 20:26:59 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

 

SQL>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

Database mounted.

Database opened.

1.8     数据字典不一致问题的解决

 

此时数据库虽然已经能够启动,但是还不够完美。

I_DEPENDENCY1I_DEPENDENCY2obj#分别是127128,查询数据字典:

SQL>select count(*) from ind$ where obj#=127;

  COUNT(*)

———-

         1

 

SQL>select /*+ full(ind$) */count(*) from ind$ where obj#=127;

  COUNT(*)

———-

         0

 

SQL>select obj#,dataobj#,ts# from ind$ where obj#=127;

      OBJ#   DATAOBJ#        TS#

———- ———- ———-

     30377      30377          0

 

SQL>select count(*) from ind$ where obj#=128;

  COUNT(*)

———-

         1

 

SQL>select /*+ full(ind$) */count(*) from ind$ where obj#=128;

  COUNT(*)

———-

         0

 

SQL>select obj#,dataobj#,ts# from ind$ where obj#=128;

      OBJ#   DATAOBJ#        TS#

———- ———- ———-

     30378      30378          0

从上述查询中我们可以看到——已经出现了严重的不一致。这当然是因为我们只把ind$中的I_DEPENDENCY1I_DEPENDENCY2所在的行用BBED给删掉了,但是ind$上还有索引I_IND1sql.bsqI_IND1的创建语句如下:

create unique index i_ind1 on ind$(obj#)

I_IND1IND$中已经被删除的I_DEPENDENCY1I_DEPENDENCY2所对应的索引行还在,所以就导致出现了上述不一致的情况。

 

I_IND1是核心bootstrap$obj# < 56)对象,所以你想把I_IND1 drop掉或者rebuild都是不行的,而且event 38003startup migrate都对I_IND1无效

SQL> select obj#,dataobj#,name from obj$ where name=’I_IND1′;

 

      OBJ#   DATAOBJ# NAME

———- ———- ——————————

        39         39 I_IND1

 

SQL>drop index I_IND1;

drop index I_IND1

           *

ERROR at line 1:

ORA-00701: object necessary for warmstarting database cannot be altered

 

SQL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL>startup mount pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

Database mounted.

 

SQL>alter database open migrate;

 

Database altered.

 

SQL>drop index I_IND1;

drop index I_IND1

           *

ERROR at line 1:

ORA-00701: object necessary for warmstarting database cannot be altered

 

怎么办?

是否核心对象其实对BBED来说都是无所谓的。好了,我们现在来用BBED来修正上述不一致。

最直观的思路就是我们去I_IND1中把I_DEPENDENCY1I_DEPENDENCY2所对应的索引行记录给删掉就好了。

 

删除的关键在于如下三个地方:

1、  I_IND1的对应索引行头递增01注意这里跟data block不一样,data block删除的时候是递增0x10),并且修改相应的lock byte

2、  kdxlende递增你删掉的索引行数,kdxlende表示的是当前索引叶子块中被标记为删除的索引行记录的数量;

3、  ktbbh中对应itllock count递增你删掉的索引行数;

 

这里存在一个index delete internal的知识点,朋友们可以用我上文中的方法自己去研究一下oracle在对indexdelete操作的时候到底做了什么。其实oracle做的事情就是我提到的三个关于删除的关键点。

 

好了,我们来看一下具体的操作过程:

 

先对索引I_IND1做一个treedumpI_IND1object id39

SQL>alter session set events ‘immediate trace name treedump level 39’;

 

Session altered.

 

生成的trace文件内容如下所示:

—– begin tree dump

branch: 0x4000f2 4194546 (0: nrow: 3, level: 1)

   leaf: 0x4000f3 4194547 (-1: nrow: 572 rrow: 569)

   leaf: 0x4000f4 4194548 (0: nrow: 271 rrow: 271)

   leaf: 0x4000f5 4194549 (1: nrow: 458 rrow: 458)

—– end tree dump

 

我们现在再dump一下根节点:

SQL> exec sys.cdba(‘004000f2′,’H’);

 

.

The file is 1

The block is 242

 

PL/SQL procedure successfully completed

 

根节点的dump内容如下所示:

Start dump data blocks tsn: 0 file#: 1 minblk 242 maxblk 242

buffer tsn: 0 rdba: 0x004000f2 (1/242)

scn: 0x0000.0002552b seq: 0x02 flg: 0x04 tail: 0x552b0602

frmt: 0x02 chkval: 0x0cde type: 0x06=trans data

Block header dump:  0x004000f2

 Object id on Block? Y

 seg/obj: 0x27  csc: 0x00.2552b  itc: 1  flg: –  typ: 2 – INDEX

     fsl: 0  fnx: 0x0 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0005.011.000000ed  0x008096e1.0037.02  C—    0  scn 0x0000.0002552b

 

Branch block dump

=================

header address 4565966916=0x110272044

kdxcolev 1

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 1

kdxcosdc 1

kdxconro 2

kdxcofbo 32=0x20

kdxcofeo 8038=0x1f66

kdxcoavs 8006

kdxbrlmc 4194547=0x4000f3

kdxbrsno 1

kdxbrbksz 8056

row#0[8047] dba: 4194548=0x4000f4

col 0; len 4; (4):  c3 03 40 41

row#1[8038] dba: 4194549=0x4000f5

col 0; len 4; (4):  c3 03 49 30

—– end of branch block dump —–

End dump data blocks tsn: 0 file#: 1 minblk 242 maxblk 242

 

SQL> select dump(127,16) from dual;

 

DUMP(127,16)

——————–

Typ=2 Len=3: c2,2,1c

 

SQL> select dump(128,16) from dual;

 

DUMP(128,16)

——————–

Typ=2 Len=3: c2,2,1d

 

综合上述treedump和根节点的dump内容,我们知道I_DEPENDENCY1I_DEPENDENCY2所对应的索引记录一定在0x4000f3中。

 

我们dump一下0x4000f3

SQL> exec sys.cdba(‘004000f3′,’H’);

 

.

The file is 1

The block is 243

 

PL/SQL procedure successfully completed

 

Dump后的内容如下所示:

Start dump data blocks tsn: 0 file#: 1 minblk 243 maxblk 243

buffer tsn: 0 rdba: 0x004000f3 (1/243)

scn: 0x0000.00022717 seq: 0x01 flg: 0x06 tail: 0x27170601

frmt: 0x02 chkval: 0xa695 type: 0x06=trans data

Block header dump:  0x004000f3

 Object id on Block? Y

 seg/obj: 0x27  csc: 0x00.226f7  itc: 2  flg: –  typ: 2 – INDEX

     fsl: 0  fnx: 0x0 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.01e.00000063  0x008062f8.001c.01  CBU-    0  scn 0x0000.00021b34

0x02   0x0006.00e.000000db  0x0080759b.0035.0f  –U-    3  fsc 0x002d.00022717

 

Leaf block dump

===============

header address 4565966940=0x11027205c

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 1

kdxcosdc 0

kdxconro 572

kdxcofbo 1180=0x49c

kdxcofeo 1190=0x4a6

kdxcoavs 10

kdxlespl 0

kdxlende 3

kdxlenxt 4194548=0x4000f4

kdxleprv 0=0x0

kdxledsz 6

kdxlebksz 8032

 

row#58[1851] flag: —–, lock: 0, data:(6):  00 40 00 20 00 05

col 0; len 3; (3):  c2 02 1c  ――127I_DEPENDENCY1

row#59[1863] flag: —–, lock: 0, data:(6):  00 40 00 20 00 06

col 0; len 3; (3):  c2 02 1d  ――128I_DEPENDENCY2

 

这里我们可以看到I_DEPENDENCY1所对应的offset1851I_DEPENDENCY2所对应的offset1863,但这些都是相对offset,需要加上一个base offsetbase offset的算法如下:

对于ASSM76+itc-1)*24

对于MSSM68+itc-1)*24

上述叶子块的itc2,且因为是9isystem表空间下的叶子块,所以是MSSM。即这里的base68 + (2-1) * 24 = 92

SQL> select 1851+92 from dual;

 

   1851+92

———-

      1943

 

SQL> select 1863+92 from dual;

 

   1863+92

———-

      1955

 

SQL>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL>exit

好了,准备工作都做好了,我们现在来用BBED

BBED> set file 1 block 243

        FILE#           1

        BLOCK#          243

 

从结果里我们可以看到,现在呈现的是典型的索引块的特征:

BBED> map /v

 File: /dras20/testdb/system01.dbf (1)

 Block: 243                                   Dba:0x004000f3

————————————————————

 KTB Data Block (Index Leaf)

 

 struct kcbh, 20 bytes                      @0      

    ub1 type_kcbh                           @0      

    ub1 frmt_kcbh                           @1      

    ub1 spare1_kcbh                         @2      

    ub1 spare2_kcbh                         @3      

    ub4 rdba_kcbh                           @4      

    ub4 bas_kcbh                            @8      

    ub2 wrp_kcbh                            @12     

    ub1 seq_kcbh                            @14     

    ub1 flg_kcbh                            @15     

    ub2 chkval_kcbh                         @16     

    ub2 spare3_kcbh                         @18     

 

 struct ktbbh, 72 bytes                     @20     

    ub1 ktbbhtyp                            @20     

    union ktbbhsid, 4 bytes                 @24     

    struct ktbbhcsc, 8 bytes                @28     

    b2 ktbbhict                             @36     

    ub1 ktbbhflg                            @38     

    ub1 ktbbhfsl                            @39     

    ub4 ktbbhfnx                            @40     

    struct ktbbhitl[2], 48 bytes            @44     

 

 struct kdxle, 32 bytes                     @92     

    struct kdxlexco, 16 bytes               @92     

    b2 kdxlespl                             @108    

    sb2 kdxlende                            @110    

    ub4 kdxlenxt                            @112    

    ub4 kdxleprv                            @116    

    ub1 kdxledsz                            @120    

    ub1 kdxleunuse                          @121    

 

 b2 kd_off[572]                             @124    

 ub1 freespace[10]                          @1268   

 ub1 rowdata[6842]                          @1278   

 ub4 tailchk                                @8188   

 

现在的ITL2所对应的lock count3

BBED> p ktbbh

struct ktbbh, 72 bytes                      @20     

   ub1 ktbbhtyp                             @20       0x02 (KDDBTINDEX)

   union ktbbhsid, 4 bytes                  @24     

      ub4 ktbbhsg1                          @24       0x00000027

      ub4 ktbbhod1                          @24       0x00000027

   struct ktbbhcsc, 8 bytes                 @28     

      ub4 kscnbas                           @28       0x000226f7

      ub2 kscnwrp                           @32       0x0000

   b2 ktbbhict                              @36       2

   ub1 ktbbhflg                             @38       0x02 (NONE)

   ub1 ktbbhfsl                             @39       0x00

   ub4 ktbbhfnx                             @40       0x00000000

   struct ktbbhitl[0], 24 bytes             @44     

      struct ktbitxid, 8 bytes              @44     

         ub2 kxidusn                        @44       0x000a

         ub2 kxidslt                        @46       0x001e

         ub4 kxidsqn                        @48       0x00000063

      struct ktbituba, 8 bytes              @52     

         ub4 kubadba                        @52       0x008062f8

         ub2 kubaseq                        @56       0x001c

         ub1 kubarec                        @58       0x01

      ub2 ktbitflg                          @60       0xe000 (KTBFUPB, KTBFIBI, KTBFCOM)

      union _ktbitun, 2 bytes               @62     

         b2 _ktbitfsc                       @62       0

         ub2 _ktbitwrp                      @62       0x0000

      ub4 ktbitbas                          @64       0x00021b34

   struct ktbbhitl[1], 24 bytes             @68     

      struct ktbitxid, 8 bytes              @68     

         ub2 kxidusn                        @68       0x0006

         ub2 kxidslt                        @70       0x000e

         ub4 kxidsqn                        @72       0x000000db

      struct ktbituba, 8 bytes              @76     

         ub4 kubadba                        @76       0x0080759b

         ub2 kubaseq                        @80       0x0035

         ub1 kubarec                        @82       0x0f

      ub2 ktbitflg                          @84       0x2003 (KTBFUPB)

      union _ktbitun, 2 bytes               @86     

         b2 _ktbitfsc                       @86       45

         ub2 _ktbitwrp                      @86       0x002d

      ub4 ktbitbas                          @88       0x00022717

 

现在的kdxlende的值也是3

BBED> p kdxle

struct kdxle, 32 bytes                      @92     

   struct kdxlexco, 16 bytes                @92     

      ub1 kdxcolev                          @92       0x00

      ub1 kdxcolok                          @93       0x00

      ub1 kdxcoopc                          @94       0x80

      ub1 kdxconco                          @95       0x01

      ub4 kdxcosdc                          @96       0x00000000

      sb2 kdxconro                          @100      572

      b2 kdxcofbo                           @102      1180

      b2 kdxcofeo                           @104      1190

      b2 kdxcoavs                           @106      10

   b2 kdxlespl                              @108      0

   sb2 kdxlende                             @110      3

   ub4 kdxlenxt                             @112      0x004000f4

   ub4 kdxleprv                             @116      0x00000000

   ub1 kdxledsz                             @120      0x06

   ub1 kdxleunuse                           @121      0x00

我们现在开始来修改,先I_IND1的对应索引行头递增0x01并且修改相应的lock byte使其指向ITL2),这里先改I_DEPENDENCY1所在的行

BBED> set offset 1943

        OFFSET          1943

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets: 1943 to 2454           Dba:0x004000f3

————————————————————————

 00000040 00200005 03c2021c 00000040 00200006 03c2021d 00000040 00200007

 03c2021e 00000040 001f0000 03c2021f 00000040 001f0001 03c20220 00000040

 001f0002 03c20221 00000040 001f0003 03c20222 00000040 001f0004 03c20223

 ……省略显示部分内容

 00240005 03c20261 00000040 00240006 03c20263 00000040 00240007 03c20303

 00000040 00240008 03c20304 00000040 00240009 03c20307 00000040 0024000a

 

 <32 bytes per line>

 

BBED> modify /x 0102

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets: 1943 to 2454           Dba:0x004000f3

————————————————————————

 01020040 00200005 03c2021c 00000040 00200006 03c2021d 00000040 00200007

 03c2021e 00000040 001f0000 03c2021f 00000040 001f0001 03c20220 00000040

 001f0002 03c20221 00000040 001f0003 03c20222 00000040 001f0004 03c20223

 ……省略显示部分内容

 00240005 03c20261 00000040 00240006 03c20263 00000040 00240007 03c20303

 00000040 00240008 03c20304 00000040 00240009 03c20307 00000040 0024000a

 

 <32 bytes per line>

 

再来改I_DEPENDENCY2所在的行:

BBED> set offset 1955

        OFFSET          1955

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets: 1955 to 2466           Dba:0x004000f3

————————————————————————

 00000040 00200006 03c2021d 00000040 00200007 03c2021e 00000040 001f0000

 03c2021f 00000040 001f0001 03c20220 00000040 001f0002 03c20221 00000040

 001f0003 03c20222 00000040 001f0004 03c20223 00000040 001f0005 03c20224

……省略显示部分内容

 00240006 03c20263 00000040 00240007 03c20303 00000040 00240008 03c20304

 00000040 00240009 03c20307 00000040 0024000a 03c20309 00000040 0024000b

 

 <32 bytes per line>

 

BBED> modify /x 0102

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets: 1955 to 2466           Dba:0x004000f3

————————————————————————

 01020040 00200006 03c2021d 00000040 00200007 03c2021e 00000040 001f0000

 03c2021f 00000040 001f0001 03c20220 00000040 001f0002 03c20221 00000040

 001f0003 03c20222 00000040 001f0004 03c20223 00000040 001f0005 03c20224

……省略显示部分内容

 00240006 03c20263 00000040 00240007 03c20303 00000040 00240008 03c20304

 00000040 00240009 03c20307 00000040 0024000a 03c20309 00000040 0024000b

 

 <32 bytes per line>

 

再将kdxlende递增你删掉的索引行数,原来的值是3,这里删掉了两行,所以修改后的值应该是5

BBED> set offset 110

        OFFSET          110

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:  110 to  621           Dba:0x004000f3

————————————————————————

 00030040 00f40000 00000600 00001f60 000004a6 04b104bc 04c704d2 04dd04e8

 04f304fe 05090514 051f052a 05350540 054b0556 0561056c 05770582 058d0598

 05a305ae 05b905c4 05cf05da 05e505f0 05fb0606 0611061c 06270633 063f064b

……省略显示部分内容

 0e960ea2 0eae0eba 0ec60ed2 0ede0eea 0ef60f02 0f0e0f1a 0f260f32 0f3e0f4a

 0f560f62 0f6e0f7a 0f860f92 0f9e0faa 0fb60fc2 0fce0fda 0fe60ff1 0ffd1009

 

 <32 bytes per line>

 

BBED> modify /x 0005

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:  110 to  621           Dba:0x004000f3

————————————————————————

 00050040 00f40000 00000600 00001f60 000004a6 04b104bc 04c704d2 04dd04e8

 04f304fe 05090514 051f052a 05350540 054b0556 0561056c 05770582 058d0598

 05a305ae 05b905c4 05cf05da 05e505f0 05fb0606 0611061c 06270633 063f064b

……省略显示部分内容

 0e960ea2 0eae0eba 0ec60ed2 0ede0eea 0ef60f02 0f0e0f1a 0f260f32 0f3e0f4a

 0f560f62 0f6e0f7a 0f860f92 0f9e0faa 0fb60fc2 0fce0fda 0fe60ff1 0ffd1009

 

 <32 bytes per line>

 

最后将ktbbh中对应itllock count递增你删掉的索引行数原来的值也是0x2003这里删掉了两行所以修改后的值是0x2005

BBED> set offset 84

        OFFSET          84

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:   84 to  595           Dba:0x004000f3

————————————————————————

 2003002d 00022717 00008001 00000000 023c049c 04a6000a 00000005 004000f4

 00000000 06000000 1f600000 04a604b1 04bc04c7 04d204dd 04e804f3 04fe0509

 0514051f 052a0535 0540054b 05560561 056c0577 0582058d 059805a3 05ae05b9

……省略显示部分内容

 0dfa0e06 0e120e1e 0e2a0e36 0e420e4e 0e5a0e66 0e720e7e 0e8a0e96 0ea20eae

 0eba0ec6 0ed20ede 0eea0ef6 0f020f0e 0f1a0f26 0f320f3e 0f4a0f56 0f620f6e

 

 <32 bytes per line>

 

BBED> modify /x 2005

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:   84 to  595           Dba:0x004000f3

————————————————————————

 2005002d 00022717 00008001 00000000 023c049c 04a6000a 00000005 004000f4

 00000000 06000000 1f600000 04a604b1 04bc04c7 04d204dd 04e804f3 04fe0509

 0514051f 052a0535 0540054b 05560561 056c0577 0582058d 059805a3 05ae05b9

……省略显示部分内容

 0dfa0e06 0e120e1e 0e2a0e36 0e420e4e 0e5a0e66 0e720e7e 0e8a0e96 0ea20eae

 0eba0ec6 0ed20ede 0eea0ef6 0f020f0e 0f1a0f26 0f320f3e 0f4a0f56 0f620f6e

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 243:

current = 0xa695, required = 0xa695

 

修改完后我们用dbv来校验一下——注意,当你修改了system表空间下的块时,你应该养成事后用dbv校验的习惯

[P550_04_LA:oracle@]#dbv file=/dras20/testdb/system01.dbf blocksize=8192

DBVERIFY: Release 9.2.0.6.0 – Production on Fri Aug 20 21:09:32 2010

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

DBVERIFY – Verification starting : FILE = /dras20/testdb/system01.dbf

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

**** actual free space credit for itl 2 = 73 != # in trans. hdr = 45

—- end index block validation

Page 243 failed with check code 6401

 

 

DBVERIFY – Verification complete

。。。。。。。。。。

 

这里oracle报错说ITL2中的应该将free space credit的值由45改为73,提示的已经再明确不过了,我们直接去修改好了:

BBED> set file 1 block 243

        FILE#           1

        BLOCK#          243

 

BBED> p ktbbh

struct ktbbh, 72 bytes                      @20     

   ub1 ktbbhtyp                             @20       0x02 (KDDBTINDEX)

   union ktbbhsid, 4 bytes                  @24     

      ub4 ktbbhsg1                          @24       0x00000027

      ub4 ktbbhod1                          @24       0x00000027

   struct ktbbhcsc, 8 bytes                 @28     

      ub4 kscnbas                           @28       0x000226f7

      ub2 kscnwrp                           @32       0x0000

   b2 ktbbhict                              @36       2

   ub1 ktbbhflg                             @38       0x02 (NONE)

   ub1 ktbbhfsl                             @39       0x00

   ub4 ktbbhfnx                             @40       0x00000000

   struct ktbbhitl[0], 24 bytes             @44     

      struct ktbitxid, 8 bytes              @44     

         ub2 kxidusn                        @44       0x000a

         ub2 kxidslt                        @46       0x001e

         ub4 kxidsqn                        @48       0x00000063

      struct ktbituba, 8 bytes              @52     

         ub4 kubadba                        @52       0x008062f8

         ub2 kubaseq                        @56       0x001c

         ub1 kubarec                        @58       0x01

      ub2 ktbitflg                          @60       0xe000 (KTBFUPB, KTBFIBI, KTBFCOM)

      union _ktbitun, 2 bytes               @62     

         b2 _ktbitfsc                       @62       0

         ub2 _ktbitwrp                      @62       0x0000

      ub4 ktbitbas                          @64       0x00021b34

   struct ktbbhitl[1], 24 bytes             @68     

      struct ktbitxid, 8 bytes              @68     

         ub2 kxidusn                        @68       0x0006

         ub2 kxidslt                        @70       0x000e

         ub4 kxidsqn                        @72       0x000000db

      struct ktbituba, 8 bytes              @76     

         ub4 kubadba                        @76       0x0080759b

         ub2 kubaseq                        @80       0x0035

         ub1 kubarec                        @82       0x0f

      ub2 ktbitflg                          @84       0x2005 (KTBFUPB)

      union _ktbitun, 2 bytes               @86     

         b2 _ktbitfsc                       @86       45

         ub2 _ktbitwrp                      @86       0x002d

      ub4 ktbitbas                          @88       0x00022717

 

BBED> set offset 86

        OFFSET          86

 

BBED> dump

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:   86 to  597           Dba:0x004000f3

————————————————————————

 002d0002 27170000 80010000 0000023c 049c04a6 000a0000 00050040 00f40000

 00000600 00001f60 000004a6 04b104bc 04c704d2 04dd04e8 04f304fe 05090514

 051f052a 05350540 054b0556 0561056c 05770582 058d0598 05a305ae 05b905c4

 

SQL> select to_char(73,’XXXX’) from dual;

TO_CHAR(73,’XXXX’)

——————

   49

 

BBED> modify /x 0049

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/testdb/system01.dbf (1)

 Block: 243              Offsets:   86 to  597           Dba:0x004000f3

————————————————————————

 00490002 27170000 80010000 0000023c 049c04a6 000a0000 00050040 00f40000

 00000600 00001f60 000004a6 04b104bc 04c704d2 04dd04e8 04f304fe 05090514

 051f052a 05350540 054b0556 0561056c 05770582 058d0598 05a305ae 05b905c4

 

BBED> sum apply

Check value for File 1, Block 243:

current = 0xa6f1, required = 0xa6f1

 

修改完后再次用dbv校验,现在已经不报错了:

[P550_04_LA:oracle@]#dbv file=/dras20/testdb/system01.dbf blocksize=8192

DBVERIFY: Release 9.2.0.6.0 – Production on Fri Aug 20 21:12:24 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY – Verification an starting : FILE = /dras20/testdb/system01.dbf

 

DBVERIFY – Verification complete

 

Total Pages Examined         : 52480

Total Pages Processed (Data) : 36700

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 4431

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 1750

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 9599

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 702656 (0.702656)

 

我们起库后看看原来的不一致现在到底修正了没有:

SQL>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

Database mounted.

Database opened.

 

SQL>select count(*) from ind$ where obj#=127;

  COUNT(*)

———-

         0

SQL>select count(*) from ind$ where obj#=128;

  COUNT(*)

———-

         0

SQL>select obj#,dataobj#,ts# from ind$ where obj#=127;

no rows selected

 

SQL>select obj#,dataobj#,ts# from ind$ where obj#=128;

no rows selected

 

SQL>select obj#,dataobj#,ts# from ind$ where obj#=30377;

      OBJ#   DATAOBJ#        TS#

———- ———- ———-

     30377      30377          0

 

SQL>select obj#,dataobj#,ts# from ind$ where obj#=30378;

      OBJ#   DATAOBJ#        TS#

———- ———- ———-

     30378      30378          0

从结果里可以看到,原来的不一致已经被我们修正。

至此,这种用BBED来处理Move系统表DEPENDENCY$导致索引失效的恢复已经几无破绽,算是有了一个完美的结局。


9 Comments on “深入解析DEPENDENCY$对象的恢复”

  1. jiq says:

    之前的文章怎么都没了啊~~

  2. li says:

    你好,dbsnake!
    刚测试了一下,对于ind$这个表unload出来怎么没有RDBA这一列呢?
    我的没有0×00400020这列,而且列数只有 29列,ind$表要34列呢,难道是我的ODU版本不对?
    我的ODU版本为odu_308_linux_x86

    • Cui Hua says:

      去oracleodu.com上下载一个最新的试用版,然后在config.txt中新增一个参数record_row_addr,为yes表示在导出为文本文件时,在行的末尾记录行号和RDBA。RDBA以16进制表示,而行号以十进制表示。

  3. xccheese says:

    为什么 6c改为7c 不是把flags改为0吗 7c也不是啊?

    • Cui Hua says:

      我在文章中已经提到:“oracle在index cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc减1”,你都没有认真看我的文章啊。


Leave a Reply

Your email address will not be published. Required fields are marked *