当前位置: 365bet亚洲官方投注 > MySQL数据库 > 正文

BBED模拟并修复ORA

时间:2019-12-22 11:02来源:MySQL数据库
如何是块损坏: 【BBED】BBED模拟并修复ORA-08102乖谬 索引 所谓损坏的数据块,是指块未有使用可识其他 Oracle格式,或然其剧情在中间不相像。经常状态下,损坏是由硬件故障或操作系统

如何是块损坏:

【BBED】BBED模拟并修复ORA-08102乖谬

索引

所谓损坏的数据块,是指块未有使用可识其他 Oracle 格式,或然其剧情在中间不相像。经常状态下,损坏是由硬件故障或操作系统难题引起的。Oracle 数据库将损坏的块标记为“逻辑损坏”或“媒质破坏”。假使是逻辑损坏,则是 Oracle 内部错误。Oracle 数据库检查测验到不相同等之后,就将逻辑损坏的块标志为损坏。如若是介质媒质破坏,则是块格式不得法;从磁盘读取的块不包涵有意义的消息。实验:有个别分区数据块损坏,不完全复苏此分区表数据。

 

目录与表相似,也归属段的豆蔻梢头种。里面贮存了客商的数据,跟表雷同要求占用磁盘空间。索引是大器晚成种允许直接访问数据表中某生龙活虎数据行的树型布局,为了增加查询效能而引进,是三个独自于表的对象,能够存放在与表差异的表空间中。索引记录中存有索引关键字和指向表中数据的指针。对索引举办的I/O操作比对表举办操作要少超级多。索引大器晚成旦被确立就将被Oracle系统自动敬性格很顽强在山高水险或巨大压力面前不屈,查询语句中不用钦赐使用哪个索引.

背景:数据库未有可行备份,有个别分区中有数量块损坏。

1.1  BLOG文书档案布局图

图片 1 

 

从物理上说,索引经常能够分成:分区和非分区索引、常规B树引得、位图索引等。个中,B树索引归属最广泛的目录。

要求:最大限度恢复生机此分区数据。

1.2  前言部分

引言

环境:RHEL 6.4 + Oracle 11.2.0.4

1.2.1  导读和注意事项

诸君技巧爱好者,看完本文后,你能够调整如下的本事,也得以学到一些任何你所不清楚的知识,~O(∩_∩)O~:

① **使用BBED修复ORA-08102错误**(重点)

② BBED的使用

③ 数据块格式的dump文件表明

④ ORA-08102不当的trace文件表明

⑤ 从rdba获取ROWID信息

⑥ 此外实用本事

 

Tips:

① 本文在itpub(

② 小说中用到的持有代码、相关软件、相关资料及本文的pdf版本都请前往玉茭苗的云盘下载,大麦苗的云盘地址见:

③ 若网页作品代码格式有错乱,请下载pdf格式的文书档案来阅读。

④ 在本篇BLOG中,代码输出部分平常坐落于后生可畏行一列的表格中。

⑤ 本文相符于Oracle初个中职员阅读,Oracle大师请略过本文。

⑥ 不喜勿喷。

本文****有错误或不周到的地点请大家多多指正,您的商议指就是自家撰文的最大重力。

正文重要给大家介绍了关于ORACLE检查找损坏索引的有关内容,分享出去供我们参照他事他说加以考查学习,上边话非常少说了,来八只拜会详细的牵线吧

下边那篇小说首要给大家介绍了关于Oracle数据块损坏之10231里头事件的相关内容,分享出去供我们仿效学习,下边来拜见详细的牵线:

1.3  正文简单介绍

最近三个恋人问小编有关ORA-08102的荒唐,何况是有关OBJ$表上的I_OBJ4索引。这几个系统对象的目录,不可能动用重新建立或设置事件的办法来修补该错误。文文莫莫的记得很早早先看过使用BBED的主意来修补该错误,只是已经忘记了。适逢其时,趁此机缘把该错误再模拟的复现一下,也把bbed再领会一下吧。

相爱的人发给自个儿的参谋小说也是大师惜分飞的博客地址,大概看了须臾间进度,首若是找到索引块的有关地方,然后利用bbed把键值更正的和表中蕴藏的均等即可。依然那句话,“不痛不痒,绝知那一件事要躬行。”,温馨模拟实验,这几个进度是必须的。

废话相当的少说,初阶尝试吧。

在Oracle数据库中如何搜索损坏索引呢? 上面大家人为构造多个案例,将索引块损坏。如下案例所示:

1. 早先化实验意况

1.3.1  有关小说链接

翻阅本篇作品,请先阅读以下内容:

1. Oracle 中 Object_iD 和 Data_Object_ID 的区别:

2. Oracle的dump函数:

3. BBED的几篇文章:

① 【BBED】编写翻译及宗旨命令(1卡塔尔国:

② 【BBED】遗失归档文件境况下的卷土而来:

③ 【BBED】 sys.bootstrap$ 对象的还原:

④ 【BBED】 SYSTEM文件头损坏的回涨(4卡塔尔(قطر‎:

⑤ 【BBED】bbed常用命令:

 

 

SQL> create tablespace test_data 2 datafile '/u01/app/oracle/oradata/gsp/test_data_01.dbf' 3 size 200M autoextend off4 logging5 segment space management auto6 extent management local;Tablespace created.SQL> create tablespace test_index 2 datafile '/u01/app/oracle/oradata/gsp/test_idx_01.dbf' 3 size 200M autoextend off4 logging5 segment space management auto6 extent management local;Tablespace created.SQL> create user kerry2 identified by 1234563 default tablespace test_data;User created.SQL> grant connect to kerry;SQL> grant resource to kerry;

开始化创制模拟实验景况用到的表空间、业务顾客、表,并导入测量试验数据。

1.4  注意事项

1、bbed毕竟是未公开的东山再起措施,所以不纯熟的爱人要慎用。

2、startup force慎用

3、操作bbed在此以前最棒先把数据库关闭

 

上述脚本是创建表空间,成立顾客kerry并授权,然后使用kerry账号登入数据库,布局测量试验数据,在TEST表上成立索引IX_TEST

这一次试验用到表空间DBS_D_JINGYU, 业务客商JINGYU, 分区表T_PART。

1.5  连锁知识点扫除文盲

An ORA-08102 indicates that there is a mismatch between the key(s) stored in the index and the values stored in the table.What typically happens in the index is built and at some future time,some type of corruption occurs,either in the table or index,to cause the mismatch.

ORA-08102周围于索引键值与表上存的值不相似。

[oracle@rhel6lhr ~]$ oerr ora 8102

08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"

// *Cause:  Internal error: possible inconsistency in index

// *Action:  Send trace file to your customer support representative, along

//           with information on reproducing the error

 

 

ora-08102这种不当表明索引或表现身了数额不相似,索引上记录的键值和表里的多寡不相符,引起访问败北,平日重建下索引就能够解决。两侧不意气风发致改表和目录都能达到规定的标准目标,只要生龙活虎致就能够,但有二个标准便是索引键值始终要保险按梯次依次增加。常常常有两种状态:

1.假如破坏为索引,则删除索引人己一视建索引,但对于index的obj#低于56的状态,由于是中央的bootstrap$对象,index是在DB运营时由DB自动成立,此种情形下通过安装event 38003或startup migrate格局都不可能化解,但obj#>56的则能够。

2.假诺破坏为块品级,则使用坏块的管理办法

3.如若破坏的为表的记录级其他则运用bbed或别的工具

 

I_OBJ1、I_OBJ2、I_OBJ3、I_OBJ4、I_OBJ5那多少个皆以OBJ$基表的目录,假诺破坏会万分辛劳,因为ORACLE 对那几个指标的DDL做了严谨限制,未有艺术轻易修补它们。

图片 2 

 

SYS@ora11g > drop index i_obj4;

drop index i_obj5

           *

ERROR at line 1:

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

 

 


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

SQL> show user;USER is "KERRY"SQL> SQL> CREATE TABLE TEST, NAME VARCHAR2;Table created.SQL> DECLARE I NUMBER;2 BEGIN3 FOR I IN 1..1000 LOOP4 INSERT INTO TEST VALUES; 5 END LOOP;6 COMMIT;7 END;8 /PL/SQL procedure successfully completed.SQL> CREATE INDEX IX_TEST ON KERRY.TEST TABLESPACE TEST_INDEX;Index created.
-- 数据表空间create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf' size 30M autoextend off;-- 临时表空间create temporary tablespace temp_jingyu tempfile '/u02/oradata/jingyu/temp_jingyu01.tmp' size 30M autoextend off;-- 索引表空间create tablespace dbs_i_jingyu datafile '/u02/oradata/jingyu/dbs_i_jingyu01.dbf' size 30M autoextend off;-- 假设创建用户 jingyu 密码 jingyu,默认临时表空间 temp_jingyu, 默认数据表空间 dbs_d_jingyu。CREATE USER jingyu IDENTIFIED BY jingyu TEMPORARY TABLESPACE temp_jingyu DEFAULT TABLESPACE dbs_d_jingyu QUOTA UNLIMITED ON dbs_d_jingyu;-- 赋予普通业务用户权限grant resource, connect to jingyu;-- 赋予DBA用户权限grant dba to jingyu;-- 业务用户登录conn jingyu/jingyu -- 1.1 创建分区表create table t_part(id number, name varchar2, start_time date, content varchar2partition by range( partition P20150101 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150102 values less than (TO_DATE(' 2015-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150103 values less than (TO_DATE(' 2015-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu);-- 1.2 插入测试数据--分区P20150102插入10000行数据begin for i in 1..10000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-01','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/--分区P20150103插入20000行数据begin for i in 10001..30000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-02','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/-- 1.3查询表数据量和大小select count from t_part; --result: 30000select count from t_part partition; --result: 10000select count from t_part partition; --result: 20000--普通表/分区表的每个分区大约__G大小set linesize 160col segment_name for a30select  "MB", t.owner, t.segment_name, t.partition_name, t.tablespace_name from dba_segments t where segment_name = 'T_PART'; MB OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 8 JINGYU T_PART P20150102 DBS_D_JINGYU 8 JINGYU T_PART P20150103 DBS_D_JINGYU

第二章 尝试部分

下一场使用上边脚本找到索引段数据库文件ID,甚至索引段的率先个块的块号。

2. 模仿分区中有多少块损坏情景

2.1  尝试情况介绍

项目

source db

db 类型

单机

db version

11.2.0.3.0

db 存储

FS

OS版本及kernel版本

RHEL 6.5

 

SQL> show user;USER is "SYS"SQL> col segment_name for a32; SQL> col header_file for 9999; SQL> col header_block for 9999; SQL> select segment_name 2 ,header_file 3 ,header_block 4 ,blocks 5 from dba_segments ds 6 where ds.owner='KERRY' and ds.segment_name='IX_TEST'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS-------------------------------- ----------- ------------ ----------IX_TEST 8 130 16SQL>

本人这里运用BBED创建坏块,矫正t_part分区表的分区P二零一四0103中的有个别块内容,模拟真实情况中有多少块损坏的场所。

2.2  实行对象

尝试对象:使用BBED模拟并修复ORA-08102错误。

仿照错误进程:通过bbed修改OBJ$表中DATAOBJ#列最大的行所在的块,让DATAOBJ#的值增大,进而和目录中著录的值不豆蔻梢头致。重启数据库并创设表让数据库报出ORA-08102荒诞。

修复错误进度:经过bbed把表中或索引中的不相符的数目改正成意气风发致的,从而修复ORA-08102八花九裂。

构造坏块的格局有好多,这里大家运用RMAN下边包车型客车命令clear,能够标识数据块为corrupt,标记数据文件第88中学130号数据块为坏块。

--查询分区P20150103的HEADER_BLOCKselect header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';SQL> select header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';HEADER_FILE HEADER_BLOCK----------- ------------ 5 1169--查询某一行记录所在的块select rowid, dbms_rowid.rowid_relative_fnorel_fno, dbms_rowid.rowid_block_numberblockno, dbms_rowid.rowid_row_number rowno from t_part where id = 20000; SQL> select 2 rowid, 3 dbms_rowid.rowid_relative_fnorel_fno, 4 dbms_rowid.rowid_block_numberblockno, 5 dbms_rowid.rowid_row_number rowno 6 from t_part where id = 20000;ROWID REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAAVveAAFAAAATBABX 5 1217 87

2.3  试验进度

[oracle@DB-Server ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 13 17:41:05 2018Copyright  1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: GSP RMAN> recover datafile 8 block 130 clear;Starting recover at 13-SEP-18using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=12 device type=DISKFinished recover at 13-SEP-18RMAN> 

动用bbed工具破坏5号文件1217块内容,

2.3.1  做全备

[oracle@rhel6lhr ~]$ more rman_full.sh

rman target / nocatalog <<eof< span="">

run{

backup database format '/home/oracle/bak/%d_%U.full';

sql 'alter system archive log current';

backup archivelog all format '/home/oracle/bak/%d_%U.arc';

backup current controlfile format '/home/oracle/bak/%d_%U.ctl';

}

EOF

 

[oracle@rhel6lhr ~]$ sh rman_full.sh

 

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 20 13:56:41 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORA11G (DBID=4270446895)

using target database control file instead of recovery catalog

 

RMAN> 2> 3> 4> 5> 6>

Starting backup at 2017-09-20 13:56:41

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf

input datafile file number=00022 name=/u01/app/oracle/oradata/ora11g/ts_ogg01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf

input datafile file number=00013 name=/u01/app/oracle/oradata/ora11g/DW_USER.dbf

input datafile file number=00014 name=/u01/app/oracle/oradata/ora11g/SQCHECK.dbf

input datafile file number=00015 name=/u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf

input datafile file number=00016 name=/u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf

input datafile file number=00017 name=/u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf

input datafile file number=00018 name=/u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf

input datafile file number=00019 name=/u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf

input datafile file number=00020 name=/u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf

input datafile file number=00021 name=/u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/ora11g/aa.dbf

input datafile file number=00023 name=/u01/app/oracle/oradata/ora11g/test01.dbf

channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:56:42

channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:17

piece handle=/home/oracle/bak/ORA11G_27seuekq_1_1.full tag=TAG20170920T135642 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35

Finished backup at 2017-09-20 13:59:17

 

Starting Control File and SPFILE Autobackup at 2017-09-20 13:59:17

piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/autobackup/2017_09_20/o1_mf_s_955202357_dw40xohn_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2017-09-20 13:59:18

 

sql statement: alter system archive log current

 

Starting backup at 2017-09-20 13:59:18

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=302 RECID=304 STAMP=955202163

input archived log thread=1 sequence=303 RECID=305 STAMP=955202358

input archived log thread=1 sequence=304 RECID=306 STAMP=955202358

channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:59:18

channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:19

piece handle=/home/oracle/bak/ORA11G_29seuepm_1_1.arc tag=TAG20170920T135918 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2017-09-20 13:59:19

 

Starting backup at 2017-09-20 13:59:20

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:59:21

channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:22

piece handle=/home/oracle/bak/ORA11G_2aseuepo_1_1.ctl tag=TAG20170920T135920 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2017-09-20 13:59:22

 

Starting Control File and SPFILE Autobackup at 2017-09-20 13:59:22

piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/autobackup/2017_09_20/o1_mf_s_955202362_dw40xtf0_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2017-09-20 13:59:23

 

RMAN>

 

Recovery Manager complete.

 

 

 

那就是说大家先来探视使用那个方法验证索引损坏了,测量试验证喜宝向下探底访是还是不是行得通。

BBED工具://www.jb51.net/article/118349.htm

2.3.2  模拟一:BBED模拟ORA-08102错误

通过BBED修改OBJ$中DATAOBJ$重现I_OBJ4索引报ORA-08102错误。定位须要破坏的OBJ$上DATAOBJ$列最大的笔录,使之和索引I_OBJ4中著录不生机勃勃致,进而达成ORA-8102错误。

[oracle@rhel6lhr ~]$ ORACLE_SID=ora11g

[oracle@rhel6lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 21 09:24:08 2017

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ora11g > select object_id,object_type from dba_objects where object_name='I_OBJ4';

 

OBJECT_ID OBJECT_TYPE

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

        39 INDEX

 

SYS@ora11g > select max(DATAOBJ#) from obj$;

 

MAX(DATAOBJ#)

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

        94098

 

SYS@ora11g > select dump(94098,16) from dual;

 

DUMP(94098,16)

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

Typ=2 Len=4: c3,a,29,63 ===>>>>> Typ=2表示NUMBER,96表示CHAR。Len=4表示4位长度,所以,94098在数据库内部的存储格式为04c30a2963

 

 

SYS@ora11g > SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,

  2         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,

  3         DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW#

  4    FROM OBJ$

  5   WHERE DATAOBJ# = 94098;

 

     FILE#     BLOCK#       ROW#

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

         1        241         27

 

SYS@ora11g > SELECT COUNT(*) COUNTS,

  2         MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MAX_ROWNUM,

  3         MIN(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MIN_ROWNUM

  4    FROM SYS.OBJ$ D

  5   WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 1

  6     AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 241;

 

    COUNTS MAX_ROWNUM MIN_ROWNUM

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

       105        104          0

 

 

基于以上的SQL能够拿走下表的内容:

项目

OBJ$上DATAOBJ#列的最大值

94098

OBJ$上DATAOBJ#列的最大值dump值

Typ=2 Len=4: c3,a,29,63  即:04c30a2963

该行所在数据块的地址

     FILE#     BLOCK#       ROW#

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

         1        241         27

该行的存储情况

    COUNTS MAX_ROWNUM MIN_ROWNUM

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

       105        104          0

即:OBJ$表上DATAOBJ#列的最大值为94098,该值在Oracle数据库中的存款和储蓄格式为04c30a2963,该行数据所在的块为1号文件,241号块,第27行,该块上共有105行数据,最大值的行号为104,最小值的行号为0

 

1:使用ANALYZE分析验证索引布局

[oracle@JY-DB01 ~]$ bbed parfile=/tmp/bbed.parPassword:BBED: Release 2.0.0.0.0 - Limited Production on Tue Jan 19 11:37:59 2016Copyright  1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set dba 5,1217 DBA 0x014004c1 BBED> map File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf  Block: 1217 Dba:0x014004c1------------------------------------------------------------ KTB Data Block  struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[177] @118 ub1 freespace[815] @472 ub1 rowdata[6901] @1287 ub4 tailchk @8188 BBED> d /v offset 0 count 128 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf  Block: 1217 Offsets: 0 to 127 Dba:0x014004c1------------------------------------------------------- 06a20000 c1044001 52733100 00000106 l ......@.Rs1..... a18b0000 01000c00 de5b0100 4d733100 l .........[..Ms1. 0000e81f 021f3200 81044001 02001b00 l ......2...@..... 5d0b0000 fc0fc000 df030600 b1200000 l ]............ .. 52733100 00000000 00000000 00000000 l Rs1............. 00000000 00000000 00000000 00000000 l ................ 00000000 0001b100 ffff7401 a3042f03 l ..........t.../. 2f030000 b100711f 4a1f231f fc1ed51e l /.....q.J.#..... <16 bytes per line>BBED> modify /x 19901010 offset 0 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf  Block: 1217 Offsets: 0 to 127 Dba:0x014004c1------------------------------------------------------------------------ 19901010 c1044001 52733100 00000106 a18b0000 01000c00 de5b0100 4d733100 0000e81f 021f3200 81044001 02001b00 5d0b0000 fc0fc000 df030600 b1200000 52733100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0001b100 ffff7401 a3042f03 2f030000 b100711f 4a1f231f fc1ed51e <32 bytes per line>BBED> sum applyCheck value for File 5, Block 1217:current = 0xa9ae, required = 0xa9aeBBED>

2.3.2.1  dump文件剖析

先对1号文件,241号块做dump:

SYS@ora11g >  conn / as sysdba

Connected.

SYS@ora11g >  alter system dump datafile 1  block 241;

 

System altered.

 

SYS@ora11g > select value from v$diag_info where name='Default Trace File';

 

VALUE

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

/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28221.trc

 

 

 

图片 3

关于块格式的详实介绍请参谋:

Start dump data blocks tsn: 0 file#:1 minblk 241 maxblk 241  ====>>>>> SYSTEM是0号表空间1号文件,当前块是241号

Block dump from cache:====>>>>> 从内存中dump出来的

Dump of buffer cache at level 4 for tsn=0 rdba=4194545====>>>>> cache中的位置

BH (0x6cfe2658) file#: 1 rdba: 0x004000f1 (1/241) class: 1 ba: 0x6cd1a000====>>>>> 参考链接地址

  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25

  dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1 hint: f

  hash: [0x773fca58,0x773fca58] lru: [0x6dbedcf0,0x6bbf1e40]

  lru-flags: hot_buffer

  ckptq: [NULL] fileq: [NULL] objq: [0x6e7d8388,0x6d7e82d8] objaq: [0x6d3f0588,0x6d7e82e8]

  st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 11

  flags:

  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

Block dump from disk: ====>>>>> 参考链接地址

buffer tsn: 0 rdba: 0x004000f1 (1/241)

scn: 0x0000.038f7e74 seq: 0x01 flg: 0x06 tail: 0x7e740601

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

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00007F1583F05A00 to 0x00007F1583F07A00

7F1583F05A00 0000A206 004000F1 038F7E74 06010000  [......@.t~......]

7F1583F05A10 0000C3CC 00000001 00000012 038F7E73  [............s~..]

7F1583F05A20 00000000 0002F801 00000000 00190006  [................]

7F1583F05A30 00000BF4 00C002D5 00030203 00002001  [............. ..]

7F1583F05A40 038F7E74 00690100 00E4FFFF 035F013B  [t~....i.....;._.]

...........省略部分输出................

7F1583F079C0 0215C102 800103C1 4F434905 C102244C  [.........ICOL$..]

7F1583F079D0 C102FF02 6F780703 2F0A1109 6F78070E  [......xo.../..xo]

7F1583F079E0 3B0A1109 6F780701 2F0A1109 02C1020E  [...;..xo.../....]

7F1583F079F0 8001FFFF 028001FF 800102C1 7E740601  [..............t~]

Block header dump:  0x004000f1 ====>>>>> 参考链接地址

Object id on Block? Y

seg/obj: 0x12  csc: 0x00.38f7e73  itc: 1  flg: -  typ: 1 - DATA

     fsl: 0  fnx: 0x0 ver: 0x01

 

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0006.019.00000bf4  0x00c002d5.0203.03  --U-    1  fsc 0x0000.038f7e74

bdba: 0x004000f1

data_block_dump,data header at 0x7f1583f05a44

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

tsiz: 0x1fb8

hsiz: 0xe4

pbl: 0x7f1583f05a44

     76543210

flag=--------

ntab=1

nrow=105 ====>>>>> 共105行数据

frre=-1

fsbo=0xe4

fseo=0x13b

avsp=0x35f

tosp=0x35f

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

0x12:pri[0]offs=0x1f79

0x14:pri[1]offs=0x1f36

...........省略部分输出................

0x44:pri[25]offs=0x18ff

0x46:pri[26]offs=0x18c1

0x48:pri[27]offs=0x13b====>>>>> 第27行数据的指针偏移位置是13b,转换为10进制是315,selet to_number('13b','xxx') from dual;

...........省略部分输出................

0xde:pri[102]offs=0x515

0xe0:pri[103]offs=0x4ce

0xe2:pri[104]offs=0x48b

block_row_dump:

tab 0, row 0, @0x1f79

tl: 63 fb: --H-FL-- lb: 0x0  cc: 18

col  0: [ 2]  c1 15

col  1: [ 2]  c1 03

col  2: [ 1]  80

col  3: [ 5]  49 43 4f 4c 24

col  4: [ 2]  c1 02

col  5: *NULL*

col  6: [ 2]  c1 03

col  7: [ 7]  78 6f 09 11 0a 2f 0e

col  8: [ 7]  78 6f 09 11 0a 3b 01

col  9: [ 7]  78 6f 09 11 0a 2f 0e

col 10: [ 2]  c1 02

col 11: *NULL*

col 12: *NULL*

col 13: [ 1]  80

col 14: *NULL*

col 15: [ 1]  80

col 16: [ 2]  c1 02

col 17: [ 1]  80

...........省略部分输出................

tab 0, row 27, @0x13b ====>>>>> 该块中第一个表第27行的指针位置,转换为10进制是315

tl: 72 fb: --H-FL-- lb: 0x1  cc: 18

col  0: [ 2]  c1 02

col  1: [ 4]  c3 0a 29 63

col  2: [ 1]  80

col  3: [12]  5f 4e 45 58 54 5f 4f 42 4a 45 43 54

col  4: [ 2]  c1 02

col  5: *NULL*

col  6: [ 1]  80

col  7: [ 7]  78 6f 09 11 0a 2f 0e

col  8: [ 7]  78 75 09 14 13 1d 25

col  9: [ 7]  78 6f 09 11 0a 2f 0e

col 10: [ 1]  80

col 11: *NULL*

col 12: *NULL*

col 13: [ 1]  80

col 14: *NULL*

col 15: [ 1]  80

col 16: [ 4]  c3 07 38 24

col 17: [ 1]  80

...........省略部分输出................

tab 0, row 104, @0x48b

tl: 67 fb: --H-FL-- lb: 0x0  cc: 18

col  0: [ 3]  c2 02 06

col  1: [ 3]  c2 02 06

col  2: [ 1]  80

col  3: [ 7]  41 43 43 45 53 53 24

col  4: [ 2]  c1 02

col  5: *NULL*

col  6: [ 2]  c1 03

col  7: [ 7]  78 6f 09 11 0a 2f 10

col  8: [ 7]  78 6f 09 11 0a 2f 10

col  9: [ 7]  78 6f 09 11 0a 2f 10

col 10: [ 2]  c1 02

col 11: *NULL*

col 12: *NULL*

col 13: [ 1]  80

col 14: *NULL*

col 15: [ 1]  80

col 16: [ 2]  c1 02

col 17: [ 1]  80

end_of_block_dump

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

 

 

 

出于SYS.OBJ$表共21列,可是最终3列都为空,所以,dump文件里就一向不出示出来。将该行数据以16进制dump出来看看:

SELECT DUMP(OBJ#, 16),

       DUMP(DATAOBJ#, 16),

       DUMP(OWNER#, 16),

       DUMP(NAME, 16),

       DUMP(NAMESPACE, 16),

       DUMP(SUBNAME, 16),

       DUMP(TYPE#, 16),

       DUMP(CTIME, 16),

       DUMP(MTIME, 16),

       DUMP(STIME, 16),

       DUMP(STATUS, 16),

       DUMP(REMOTEOWNER, 16),

       DUMP(LINKNAME, 16),

       DUMP(FLAGS, 16),

       DUMP(OID$, 16),

       DUMP(SPARE1, 16),

       DUMP(SPARE2, 16),

       DUMP(SPARE3, 16),

       DUMP(SPARE4, 16),

       DUMP(SPARE5, 16),

       DUMP(SPARE6, 16)

  FROM SYS.OBJ$ D

WHERE DATAOBJ# = 94098;

 

图片 4 

结果和dump文件中的内容肖似。

列名

10进制值

16进制值

dump文件的存储

OBJ#

1

Typ=2 Len=2: c1,2

col  0: [ 2]  c1 02

DATAOBJ#

94098

Typ=2 Len=4: c3,a,29,63

col  1: [ 4]  c3 0a 29 63

OWNER#

0

Typ=2 Len=1: 80

col  2: [ 1]  80

NAME

_NEXT_OBJECT

Typ=1 Len=12: 5f,4e,45,58,54,5f,4f,42,4a,45,43,54

col  3: [12]  5f 4e 45 58 54 5f 4f 42 4a 45 43 54

NAMESPACE

1

Typ=2 Len=2: c1,2

col  4: [ 2]  c1 02

SUBNAME

 

NULL

col  5: *NULL*

TYPE#

0

Typ=2 Len=1: 80

col  6: [ 1]  80

CTIME

2011-09-17 09:46:13

Typ=12 Len=7: 78,6f,9,11,a,2f,e

col  7: [ 7]  78 6f 09 11 0a 2f 0e

MTIME

2017-09-20 18:28:36

Typ=12 Len=7: 78,75,9,14,13,1d,25

col  8: [ 7]  78 75 09 14 13 1d 25

STIME

2011-09-17 09:46:13

Typ=12 Len=7: 78,6f,9,11,a,2f,e

col  9: [ 7]  78 6f 09 11 0a 2f 0e

STATUS

0

Typ=2 Len=1: 80

col 10: [ 1]  80

REMOTEOWNER

 

NULL

col 11: *NULL*

LINKNAME

 

NULL

col 12: *NULL*

FLAGS

0

Typ=2 Len=1: 80

col 13: [ 1]  80

OID$

 

NULL

col 14: *NULL*

SPARE1

0

Typ=2 Len=1: 80

col 15: [ 1]  80

SPARE2

65535

Typ=2 Len=4: c3,7,38,24

col 16: [ 4]  c3 07 38 24

SPARE3

0

Typ=2 Len=1: 80

col 17: [ 1]  80

 

据此,从dump文件中还足以博得第27行数据的指针偏移地方是13b,转变为10进制是315。

 

[oracle@DB-Server ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 13 17:42:03 2018Copyright  1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> analyze index kerry.ix_test validate structure;analyze index kerry.ix_test validate structure*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 8, block # 130)ORA-01110: data file 8: '/u01/app/oracle/oradata/gsp/test_idx_01.dbf'

从那之后破坏了5号文件,1217块。

2.3.2.2  应用bbed破坏该块中的第27行数据

运用bbed破坏记录,纠正dataobj#中的值,使得obj$.dataobj#和i_obj4中的dataobj#不匹配。

SYS@ora11g > select name from v$datafile where file#=1;

 

NAME

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

/u01/app/oracle/oradata/ora11g/system01.dbf

 

最好是干净的关闭数据库

 

 

 

[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'

 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 10:40:47 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> show all

        FILE#           0

        BLOCK#          1

        OFFSET          0

        DBA             0x00000000 (0 0,1)

        FILENAME        /u01/app/oracle/oradata/ora11g/system01.dbf

        BIFILE          bifile.bbd

        LISTFILE      

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         ./log.bbd

        SPOOL           No

 

BBED> set block 241

        BLOCK#          241

 

BBED> map

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241                                   Dba:0x00000000

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

KTB Data Block (Table/Cluster) ====>>>>> 表明是数据块

 

struct kcbh, 20 bytes                      @0      

 

struct ktbbh, 48 bytes                     @20     

 

struct kdbh, 14 bytes                      @68     

 

struct kdbt[1], 4 bytes                    @82     

 

sb2 kdbr[105]                              @86     

 

ub1 freespace[87]                          @296    

 

ub1 rowdata[7805]                          @383    

 

ub4 tailchk                                @8188    ====>>>>> 块校验位

 

BBED> p kdbr===>>>>> 块中数据指针位置

sb2 kdbr[0]                                 @86       8057

sb2 kdbr[1]                                 @88       7990

sb2 kdbr[2]                                 @90       7928

。。。。。。。。。。。省略。。。。。。。。。。。

sb2 kdbr[24]                                @134      6466

sb2 kdbr[25]                                @136      6399

sb2 kdbr[26]                                @138      6337

sb2 kdbr[27]                                @140      315===>>>>> 第27行数据指针位置为315,和dump出来的信息一致

sb2 kdbr[28]                                @142      6268

sb2 kdbr[29]                                @144      6201

。。。。。。。。。。。省略。。。。。。。。。。。

sb2 kdbr[103]                               @292      1230

sb2 kdbr[104]                               @294      1163===>>>>> 表明该块共有105行数据

 

BBED> p *kdbr[27]

rowdata[0]

----------

ub1 rowdata[0]                              @383      0x2c===>>>>> 第27行偏移位置为383

 

BBED> show offset

        OFFSET          383

 

BBED> x /rnnncncntttnccncnnn===>>>>> 打印第27行的数据内容,n代表number,c代表char,t代表date

rowdata[0]                                  @383    

----------

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

lock@384:  0x01

cols@385:    18===>>>>> 共18列

 

col    0[2] @386: 1

col    1[4] @389: 94098 ==>>>>> 需要修改该列的值

col    2[1] @394: 0

col   3[12] @396: _NEXT_OBJECT

col    4[2] @409: 1

col    5[0] @412: *NULL*

col    6[1] @413: 0

col    7[7] @415: 2011-09-17 09:46:13

col    8[7] @423: 2017-09-20 18:28:36

col    9[7] @431: 2011-09-17 09:46:13

col   10[1] @439: 0

col   11[0] @441: *NULL*

col   12[0] @442: *NULL*

col   13[1] @443: 0

col   14[0] @445: *NULL*

col   15[1] @446: 0

col   16[4] @448: 65535

col   17[1] @453: 0

 

BBED> set offset 389

        OFFSET          389

 

BBED> d /v count 32

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241     Offsets:  389 to  420  Dba:0x00000000

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

 04c30a29 6301800c 5f4e4558 545f4f42 l ...)c..._NEXT_OB

4a454354 02c102ff 01800778 6f09110a l JECT.......xo...

 

<16 bytes="" per="" line="">

 

BBED>

 

 

 

 

本来,也得以选取find来平素询问:

BBED> f /x c30a29

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241              Offsets:  390 to  421           Dba:0x00000000

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

 c30a2963 01800c5f 4e455854 5f4f424a 45435402 c102ff01 8007786f 09110a2f

 

<32 bytes="" per="" line="">

 

BBED> f

BBED-00212: search string not found

 

 

 

94098和94099对应的囤积格式:

SYS@ora11g > select dump(94098,16),dump(94099,16) from dual;

 

DUMP(94098,16)          DUMP(94099,16)

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

Typ=2 Len=4: c3,a,29,63 Typ=2 Len=4: c3,a,29,64

 

 

 

运用bbed 纠正04c30a2963为04c30a2964,即把94098改变为94099,如下所示:

BBED> set offset +2

        OFFSET          392

 

BBED> d

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241              Offsets:  392 to  423           Dba:0x00000000

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

 29630180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786f0911 0a2f0e07

 

<32 bytes="" per="" line="">

 

BBED>  m /x 2964

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

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241              Offsets:  392 to  423           Dba:0x00000000

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

 29640180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786f0911 0a2f0e07

 

<32 bytes="" per="" line="">

 

BBED> d /v

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 241     Offsets:  392 to  423  Dba:0x00000000

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

29640180 0c5f4e45 58545f4f 424a4543 l )d..._NEXT_OBJEC

5402c102 ff018007 786f0911 0a2f0e07 l T.......xo.../..

 

<16 bytes="" per="" line="">

 

BBED> sum

Check value for File 0, Block 241:

current = 0xc3cc, required = 0xc4cc

 

BBED> sum apply

Check value for File 0, Block 241:

current = 0xc4cc, required = 0xc4cc

 

BBED> v

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ora11g/system01.dbf

BLOCK = 241

 

 

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

 

 

修改后翻看该行记录的开始和结果:

BBED> set offset 383

        OFFSET          383

 

BBED> x /rnnncncntttnccncnnn

rowdata[0]                                  @383    

----------

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

lock@384:  0x01

cols@385:    18

 

col    0[2] @386: 1

col    1[4] @389: 94099 ==>>>>> 已修改

col    2[1] @394: 0

col   3[12] @396: _NEXT_OBJECT

col    4[2] @409: 1

col    5[0] @412: *NULL*

col    6[1] @413: 0

col    7[7] @415: 2011-09-17 09:46:13

col    8[7] @423: 2017-09-20 18:28:36

col    9[7] @431: 2011-09-17 09:46:13

col   10[1] @439: 0

col   11[0] @441: *NULL*

col   12[0] @442: *NULL*

col   13[1] @443: 0

col   14[0] @445: *NULL*

col   15[1] @446: 0

col   16[4] @448: 65535

col   17[1] @453: 0

 

可以见见成功的将94098改换为94099。

 

如上截图所示,纵然索引损坏,那么使用analyze index validate structure就能够报错。要检查整个数据库全体的毁损索引的话,就足以依据下边脚本:

查询v$database_block_corruption

2.3.2.3  重启数据库

重现在obj$的I_OBJ4 index上报ORA-8102错误,並且无法创设新目标。

SYS@ora11g > create table test_8102_lhr as select * from dba_users;

 

Table created.==>>>>> 需要重启数据库

 

SYS@ora11g > startup force

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             306187648 bytes

Database Buffers           92274688 bytes

Redo Buffers                8503296 bytes

Database mounted.

Database opened.

SYS@ora11g >

SYS@ora11g > create table test_8102_lhr_01 as select * from dba_users;

create table test_8102_lhr_01 as select * from dba_users

                                               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)

 

SYS@ora11g > col OBJECT_NAME for a30

SYS@ora11g > select object_name,object_type from dba_objects where object_id=39;

 

OBJECT_NAME                    OBJECT_TYPE

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

I_OBJ4                         INDEX

 

==>>>>>39号为I_OBJ4这个索引,在块94093上没有找到键值。

 

 

 

spool analy_index.sqlSET PAGESIZE 50000;SELECT'ANALYZE INDEX ' || OWNER || '.' || INDEX_NAME|| ' VALIDATE STRUCTURE;' FROM DBA_INDEXES;spool off;@analy_index.sql
select * from v$database_block_corruption;SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ --------- 5 1217 1 0 CORRUPT--此时查询分区表T_PARTalter system flush buffer_cache;select count from t_part;--查询报错ORA-01578select count from t_part partition;--查询正常,即分区P20150102未受影响select count from t_part partition;--查询报错ORA-01578--尝试逻辑导出表数据失败[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 11:52:21 2016Copyright  1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table T_PART. . exporting partition P20150101 0 rows exported. . exporting partition P20150102 10000 rows exported. . exporting partition P20150103EXP-00056: ORACLE error 1578 encounteredORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'Export terminated successfully with warnings.[oracle@JY-DB01 ~]$

2.3.2.4  errorstack的trace文件剖析

报告警察方日志会生成errorstack:

图片 5

Thu Sep 21 10:57:39 2017

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_46780.trc:

Thu Sep 21 10:57:41 2017

Dumping diagnostic data in directory=[cdmp_20170921105741], requested by (instance=1, osid=46780), summary=[abnormal process termination].

 

 

从日记文件中可以看看:

*** 2017-09-21 10:57:39.849

oer 8102.2 - obj# 39, rdba: 0x00416f83(afn 1, blk# 94083)<<<<<<===发生错误的对象为39号对象,1号文件,94083号块,rdba(relative data block address)表示相对数据块地址,计算方式参考:http://blog.itpub.net/26736162/viewspace-2141499/

kdk key 8102.2:

  ncol: 4, len: 16<<<<<<===共4列,长度为16字节

  key: (16):  04 c3 0a 29 64 01 80 01 80 06 00 40 00 f1 00 1b <<<<<<===在索引中找不到键值04 c3 0a 29 64(转换为10进制即94099),其中04代表行的长度,后面则是列的内容。由于该索引共有3列(DATAOBJ#, TYPE#, OWNER#),所以,04 c3 0a 29 64代表DATAOBJ#,“01 80”代表TYPE#,接下来的“01 80”代表OWNER#,剩下的“06 00 40 00 f1 00 1b”代表该行的ROWID,06代表长度。索引中的ROWID=文件号(2进制前10位)+块号(2进制22位)+行号(2进制16位)

00 40 00 f1 00 1b(16进制)= 000000000100000000000000111100010000000000011011(2进制)

文件号=(0000000001)取前10位的二进制=第1号文件

块号=(0000000000000011110001)取前2进制22位==>第241号

行号=(0000000000011011)取2进制16位==>第27行

这个结果和之前查询出来的结果一致。

  mask: (4096):

。。。。。。。。省略。。。。。。。。

*** 2017-09-21 10:57:39.850

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

----- Error Stack Dump -----

----- Current SQL Statement for this session (sql_id=4yyb4104skrwj) -----

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

 

 

。。。。。。。。省略。。。。。。。。

 

Block header dump:  0x00416f83

Object id on Block? Y

 seg/obj: 0x27  csc: 0x00.38f9bc9  itc: 3  flg: O  typ: 2 - INDEX<<<<<<===注意:块中可能有多个索引,所以需要使用“seg/obj: 0x27”来进行判断,这里0x27表示39号对象。

     fsl: 0  fnx: 0x416f84 ver: 0x01

 

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.01f.00000f09  0x00c001f6.02d1.01  CB--    0  scn 0x0000.038326d7

0x02   0x000a.009.0000098a  0x00c0030c.01ff.14  ----    1  fsc 0x0000.00000000

0x03   0x0006.008.00000bf2  0x00c002df.0203.16  --U-    1  fsc 0x0000.038f9bdc

Leaf block dump

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

header address 1857765492=0x6ebb4074

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

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

kdxconco 4

kdxcosdc 1

kdxconro 297

kdxcofbo 630=0x276

kdxcofeo 1722=0x6ba

kdxcoavs 1398

kdxlespl 0

kdxlende 0

kdxlenxt 4213578=0x404b4a

kdxleprv 4288386=0x416f82

kdxledsz 0

kdxlebksz 8008

row#0[7987] flag: ------, lock: 0, len=21

col 0; len 4; (4):  c3 0a 20 26

col 1; len 2; (2):  c1 23

col 2; len 3; (3):  c2 02 26

col 3; len 6; (6):  00 41 6a b1 00 18

。。。。。。。。省略。。。。。。。。

row#284[1861] flag: ------, lock: 0, len=18

col 0; len 4; (4):  c3 0a 29 63<<<<<<===索引中存储的是c3 0a 29 63

col 1; len 1; (1):  80

col 2; len 1; (1):  80

col 3; len 6; (6):  00 40 00 f1 00 1b<<<<<<===索引中的ROWID

。。。。。。。。省略。。。。。。。。

row#296[2302] flag: ------, lock: 0, len=15

col 0; NULL

col 1; len 2; (2):  c1 02

col 2; len 1; (1):  80

col 3; len 6; (6):  00 40 2e 9f 00 50

----- end of leaf block dump -----

。。。。。。。。省略。。。。。。。。

 

此外通过有关trace开采,在成立表操作中会调用update obj$的三个递归操作,而该操作会更新dataobj#,不过由于该值在表和index中不相配,因而条件成熟自然发生ORA-08102导致成立表不成功。也足以因而如下的SQL生成errorstack:

ALTER SESSION SET EVENTS '8102 trace name errorstack level 3';

create table t1 as select * from dual;          

select value from v$diag_info where name='Default Trace File';

 

在trace中,要求从索引块中读取c30a2964:

LHR@ora11g > select utl_raw.cast_to_number('c30a2964') from dual;

 

UTL_RAW.CAST_TO_NUMBER('C30A2964')

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

                             94099

 

 

 

2:使用系统视图v$database_block_corruption查看损坏索引

3. 尝试采用Oracle内部事件10231举办不完全苏醒

2.3.3  模拟一:BBED修复ORA-08102错误

正如所示,大家接受那一个脚本来查看出现坏块的目录,发掘那几个剧本不恐怕搜索坏块索引。

行使Oracle 10231内部事件能够跳过坏块

2.3.3.1  使用全表扫和全索引扫找到键值不周边的地点

SYS@ora11g > create table test_8102_lhr_01 as select * from dba_users;

create table test_8102_lhr_01 as select * from dba_users

                                               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)

 

SYS@ora11g > col OBJECT_NAME for a30

SYS@ora11g > select object_name,object_type from dba_objects where object_id=39;

 

OBJECT_NAME                    OBJECT_TYPE

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

I_OBJ4                         INDEX

 

SYS@ora11g > set autot on

SYS@ora11g > set line 9999

SYS@ora11g >

SYS@ora11g > select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$  t 

  2  minus

  3  select /*+ full(t1) */ DATAOBJ# from sys.obj$  t1;

 

  DATAOBJ#

----------

     94098

 

 

Execution Plan

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

Plan hash value: 1107706645

 

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

| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |        | 85519 |   334K|       |  1116  (47)| 00:00:14 |

|   1 |  MINUS              |        |       |       |       |            |          |

|   2 |   SORT UNIQUE NOSORT|        | 85519 |   167K|       |   603   (1)| 00:00:08 |

|   3 |    INDEX FULL SCAN  | I_OBJ4 | 85519 |   167K|       |   386   (0)| 00:00:05 |

|   4 |   SORT UNIQUE       |        | 85519 |   167K|   680K|   512   (1)| 00:00:07 |

|   5 |    TABLE ACCESS FULL| OBJ$   | 85519 |   167K|       |   295   (1)| 00:00:04 |

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

 

 

Statistics

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

          0  recursive calls

          0  db block gets

       1475  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SYS@ora11g > select /*+ full(t1) */ DATAOBJ# from sys.obj$  t1

  2   minus

  3    select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$  t 

  4   ;

 

 

  DATAOBJ#

----------

     94099

 

 

Execution Plan

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

Plan hash value: 321946325

 

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

| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |        | 85519 |   334K|       |  1116  (55)| 00:00:14 |

|   1 |  MINUS              |        |       |       |       |            |          |

|   2 |   SORT UNIQUE       |        | 85519 |   167K|   680K|   512   (1)| 00:00:07 |

|   3 |    TABLE ACCESS FULL| OBJ$   | 85519 |   167K|       |   295   (1)| 00:00:04 |

|   4 |   SORT UNIQUE NOSORT|        | 85519 |   167K|       |   603   (1)| 00:00:08 |

|   5 |    INDEX FULL SCAN  | I_OBJ4 | 85519 |   167K|       |   386   (0)| 00:00:05 |

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

 

 

Statistics

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

          1  recursive calls

          0  db block gets

       1474  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

即表中记录的是94099,而索引中记录的是94098:

SYS@ora11g > set autot off

SYS@ora11g >

SYS@ora11g > select /*+ full(t) */  DATAOBJ#,type#,owner# from sys.obj$  t  WHERE t.dataobj# IN (94098,94099);

 

  DATAOBJ#      TYPE#     OWNER#

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

     94099          0          0

 

SYS@ora11g > select /*+ full(t i_obj4) */  DATAOBJ#,type#,owner# from sys.obj$  t  WHERE t.dataobj# IN (94098,94099);

 

  DATAOBJ#      TYPE#     OWNER#

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

     94098          0          0

 

 

 

 

 

set pagesize 50 linesize 170col segment_name format a30col partition_name format a30SELECT DISTINCT file#, segment_name, segment_type, tablespace_name, partition_name FROM dba_extents a, v$database_block_corruption b WHERE a.file_id = b.file# AND a.block_id <= b.block# AND a.block_id + a.blocks >= b.block#; 
--启用10231内部事件alter system set events='10231 trace name context forever,level 10';--关闭10231内部事件alter system set events='10231 trace name context off';

2.3.3.2  使用bbed 修复ORA-8102

SYS@ora11g > select dump(94098,16),dump(94099,16) from dual;

 

DUMP(94098,16)          DUMP(94099,16)

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

Typ=2 Len=4: c3,a,29,63 Typ=2 Len=4: c3,a,29,64

 

 

 

[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'

 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 11:16:34 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set block 94083

        BLOCK#          94083

BBED> map

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083                                 Dba:0x00000000

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

KTB Data Block (Index Leaf)<<<<===索引块

 

struct kcbh, 20 bytes                      @0      

 

struct ktbbh, 96 bytes                     @20     

 

struct kdxle, 32 bytes                     @116    

 

sb2 kd_off[297]                            @148    

 

ub1 freespace[1092]                        @742    

 

ub1 rowdata[6286]                          @1834   

 

ub4 tailchk                                @8188   

 

BBED> f /x 0a2963

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083            Offsets: 1981 to 2492           Dba:0x00000000

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

0a296301 80018006 004000f1 001b0000 04c30a29 5b02c103 02c15b06 00416abb

00110000 04c30a29 5c02c102 02c15b06 00416abb 00100100 04c30a29 5c02c102

02c15b06 00416abb 000d0100 04c30a29 5b02c103 02c15b06 00416abb 000f0000

04c30a29 5a02c103 02c15b06 00416abb 000e0100 04c30a29 5a02c103 02c15b06

00416abb 000d0000 04c30a28 6202c103 02c15b06 00416abb 000c0000 04c30a28

6302c102 02c15b06 00416abb 000b0000 04c30a29 5902c103 01800600 416abb00

0a010004 c30a295e 01800180 06004000 f1001b00 0004c30a 283102c1 15018006

00416abb 00090000 04c30a29 5102c115 01800600 416abb00 08000004 c30a294f

02c11401 80060041 6abb0007 010004c3 0a295901 80018006 004000f1 001b0000

04c30a28 2d02c115 01800600 416abb00 06000004 c30a294d 02c11501 80060041

6abb0005 000004c3 0a294b02 c1140180 0600416a bb000400 0004c30a 282902c1

15018006 00416abb 00030000 04c30a29 4902c115 01800600 416abb00 02010004

c30a294e 01800180 06004000 f1001b00 0004c30a 294702c1 14018006 00416abb

00010000 04c30a28 2502c115 01800600 416abb00 000000ff 02c10201 80060040

2e9f0050 0000ff02 c1020180 0600402e 9f004800 00ff02c1 02018006 00402e9f

003e0000 ff02c102 01800600 402e9f00 380000ff 02c10201 80060040 2e9f0032

 

<32 bytes="" per="" line="">

 

BBED> f

BBED-00212: search string not found <<<<===只找到1个,说明位置就是这里

 

 

BBED> set offset -2

        OFFSET          1979

BBED> d /v count 32

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083   Offsets: 1979 to 2010  Dba:0x00000000

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

 04c30a29 63018001 80060040 00f1001b l ...)c......@....

000004c3 0a295b02 c10302c1 5b060041 l .....)[.....[..A

 

<16 bytes="" per="" line="">

BBED> set offset +4

        OFFSET          1983

 

BBED> d /v

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083   Offsets: 1983 to 2014  Dba:0x00000000

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

63018001 80060040 00f1001b 000004c3 l c......@........

0a295b02 c10302c1 5b060041 6abb0011 l .)[.....[..Aj...

 

<16 bytes="" per="" line="">

 

BBED> m /x 64

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

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083            Offsets: 1983 to 2014           Dba:0x00000000

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

64018001 80060040 00f1001b 000004c3 0a295b02 c10302c1 5b060041 6abb0011

 

<32 bytes="" per="" line="">

BBED> sum

Check value for File 0, Block 94083:

current = 0xb4e3, required = 0xb3e3

 

BBED> sum apply

Check value for File 0, Block 94083:

current = 0xb3e3, required = 0xb3e3

 

BBED> set offset 0

        OFFSET          0

 

BBED> f /x 0a2964

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083            Offsets: 1981 to 2012           Dba:0x00000000

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

 0a296401 80018006 004000f1 001b0000 04c30a29 5b02c103 02c15b06 00416abb

 

<<<<===已修改成和表中的数据一致

<32 bytes="" per="" line="">

BBED> f

BBED-00212: search string not found

BBED> v

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ora11g/system01.dbf

BLOCK = 94083

 

 

DBVERIFY - Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 1

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 0 ====>>>>> 若该块被标识为坏块,则需要修改offset为8188处为01

Total Blocks Influx           : 0

Message 531 not found;  product=RDBMS; facility=BBED

BBED> set offset 8188

        OFFSET          8188

 

BBED> d

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 94083            Offsets: 8188 to 8191           Dba:0x00000000

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

 0106aa4c

 

<32 bytes="" per="" line="">

 

 

 

案由解析如下,视图v$database_block_corruption中有坏块记录,但是我们将索引段的第二个块标识为坏块后,在dba_extents中从未该索引段的笔录了。所以这种景色下的目录损坏,那几个SQL语句根本不可能找寻坏块索引。

测量试验设置10231平地风波后是还是不是能够逻辑导出:

2.3.3.3  验证

SYS@ora11g > create table t1 as select * from dual;

create table t1 as select * from dual

                                 *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)

 

SYS@ora11g >  startup force;===>>>>> 慎用

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             306187648 bytes

Database Buffers           92274688 bytes

Redo Buffers                8503296 bytes

Database mounted.

Database opened.

SYS@ora11g >  create table t1 as select * from dual;

 

Table created.

 

SYS@ora11g > insert into t1 select * from dual;

 

1 row created.

 

SYS@ora11g > commit;

 

Commit complete.

 

SYS@ora11g > select * from t1;

 

D

-

X

X

 

 

OK,搞定。

 

SQL> SELECT file_id, 2 segment_name, 3 segment_type 4 FROM dba_extents 5 WHERE file_id = 8 ;no rows selectedSQL> SELECT file_id, 2 segment_name, 3 segment_type 4 FROM dba_extents 5 WHERE owner = 'KERRY';FILE_ID SEGMENT_NAME SEGMENT_TYPE---------- -------------------------------- ------------------7 TEST TABLE7 TEST TABLESQL> 
[oracle@JY-DB01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016Copyright  1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 14:01:57 2016Copyright  1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table T_PART. . exporting partition P20150101 0 rows exported. . exporting partition P20150102 10000 rows exported. . exporting partition P20150103 19823 rows exportedExport terminated successfully without warnings.--成功导出后记得要关闭10231内部事件alter system set events='10231 trace name context off';20000 - 19823 = 177行,也就是说该数据块损坏直接导致了177行数据丢失。不过还好,保住了大部分数据。

2.3.4  模拟二:利用con$做模拟

看了郭大器晚成军政大学师的博客,也可以使用con$表来效仿ORA-08102不当,所以,水稻苗也尝尝了生机勃勃把。那些模拟实验就不详细说明了。

 

con$表是Oracle数据库中限定的基表,

SELECT * FROM Dba_Dependencies d WHERE d.name='DBA_CONSTRAINTS' AND d.owner='SYS' AND D.referenced_type='TABLE' ;

图片 6 

参照文档是郭豆蔻梢头军先生的:

DROP TABLE T_ORA8102_LHR;

CREATE TABLE T_ORA8102_LHR AS SELECT * FROM USER_OBJECTS;

CREATE UNIQUE INDEX IDX_ORA8102_LHR ON T_ORA8102_LHR(OBJECT_ID);

ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID);

LHR@ora11g > SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='T_ORA8102_LHR';

 

CONSTRAINT_NAME

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

SYS_C0018168

 

LHR@ora11g > SELECT MAX(CON#) FROM SYS.CON$;

 

MAX(CON#)

----------

     18169

 

LHR@ora11g > SELECT D.NAME,

  2         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,

  3         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,

  4         DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW#

  5    FROM SYS.CON$ D

  6   WHERE D.CON# = 18169;

 

NAME                                FILE#     BLOCK#       ROW#

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

_NEXT_CONSTRAINT                        1        289         12

 

LHR@ora11g >  SELECT COUNT(*) COUNTS,

  2         MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MAX_ROWNUM,

  3         MIN(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MIN_ROWNUM

  4    FROM SYS.CON$ D

  5   WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 1

  6     AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 289;  

 

    COUNTS MAX_ROWNUM MIN_ROWNUM

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

       311        310          0

 

 

 

SELECT D.* FROM SYS.CON$ D WHERE D.CON# = 18169**;**

图片 7 

[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'

 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 13:31:03 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set block 289

        BLOCK#          289

 

BBED> map

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 289                                   Dba:0x00000000

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

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[311]                              @110    

 

ub1 freespace[675]                         @732    

 

ub1 rowdata[6781]                          @1407   

 

ub4 tailchk                                @8188   

 

 

BBED> p kdbr

sb2 kdbr[0]                                 @110      8077

sb2 kdbr[1]                                 @112      8057

sb2 kdbr[2]                                 @114      8038

sb2 kdbr[3]                                 @116      8016

sb2 kdbr[4]                                 @118      7994

sb2 kdbr[5]                                 @120      7972

sb2 kdbr[6]                                 @122      7952

sb2 kdbr[7]                                 @124      7932

sb2 kdbr[8]                                 @126      7910

sb2 kdbr[9]                                 @128      7888

sb2 kdbr[10]                                @130      7868

sb2 kdbr[11]                                @132      7848

sb2 kdbr[12]                                @134      1315

sb2 kdbr[13]                                @136      7826

。。。。。。。省略部分内容。。。。。。。。。

sb2 kdbr[303]                               @716      1640

sb2 kdbr[304]                               @718      1618

sb2 kdbr[305]                               @720      1596

sb2 kdbr[306]                               @722      1574

sb2 kdbr[307]                               @724      1552

sb2 kdbr[308]                               @726      1530

sb2 kdbr[309]                               @728      1508

sb2 kdbr[310]                               @730      1486

 

BBED> p *kdbr[12]

rowdata[0]

----------

ub1 rowdata[0]                              @1407     0x2c

 

BBED> show offset

        OFFSET          1407

 

BBED> x /rncnn

rowdata[0]                                  @1407   

----------

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

lock@1408: 0x00

cols@1409:    4

 

col    0[1] @1410: 0

col   1[16] @1412: _NEXT_CONSTRAINT

col    2[4] @1429: 18169 

col    3[1] @1434: 0

 

BBED> d /v offset 1429 count 16

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 289     Offsets: 1429 to 1444  Dba:0x00000000

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

 04c30252 4601802c 00040180 105f4e45 l ...RE..,....._NE

 

<16 bytes="" per="" line="">

 

 

 

 

LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;

 

DUMP(18169,16)          DUMP(18170,16)

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

Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47

 

 

BBED> set offset +4

        OFFSET          1433

 

BBED> d

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 289              Offsets: 1433 to 1448           Dba:0x00000000

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

4601802c 00040180 105f4e45 58545f43

 

<32 bytes="" per="" line="">

 

BBED> m /x 47

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

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 289              Offsets: 1433 to 1448           Dba:0x00000000

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

4701802c 00040180 105f4e45 58545f43

 

<32 bytes="" per="" line="">

 

BBED> sum

Check value for File 0, Block 289:

current = 0xeeee, required = 0xecee

 

BBED> sum apply

Check value for File 0, Block 289:

current = 0xecee, required = 0xecee

 

BBED> v

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ora11g/system01.dbf

BLOCK = 289

 

 

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

 

BBED> p *kdbr[12]

rowdata[0]

----------

ub1 rowdata[0]                              @1407     0x2c

 

BBED> x /rccnn

rowdata[0]                                  @1407   

----------

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

lock@1408: 0x00

cols@1409:    4

 

col    0[1] @1410: .

col   1[16] @1412: _NEXT_CONSTRAINT

col    2[4] @1429: 18170

col    3[1] @1434: 0

 

 

 

 

SYS@ora11g >  startup force

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             314576256 bytes

Database Buffers           83886080 bytes

Redo Buffers                8503296 bytes

Database mounted.

Database opened.

SYS@ora11g > conn lhr/lhr

Connected.

LHR@ora11g > ALTER TABLE T_ORA8102_LHR DROP PRIMARY KEY;

 

Table altered.

 

LHR@ora11g > ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID);

ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-08102: index key not found, obj# 52, file 1, block 93040 (2)

 

LHR@ora11g > SELECT d.OBJECT_NAME,d.OBJECT_TYPE FROM Dba_Objects d WHERE d.OBJECT_ID=52;

 

OBJECT_NAME                    OBJECT_TYPE

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

I_CON2                         INDEX

 

LHR@ora11g > SELECT * FROM SYS.BOOTSTRAP$ D WHERE D.OBJ#=52;

 

     LINE#       OBJ# SQL_TEXT

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

        52         52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))

 

LHR@ora11g > set long 9999

LHR@ora11g > select dbms_metadata.get_ddl('INDEX','I_CON2','SYS') from dual;

 

DBMS_METADATA.GET_DDL('INDEX','I_CON2','SYS')

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

 

  CREATE UNIQUE INDEX "SYS"."I_CON2" ON "SYS"."CON$" ("CON#")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

 

 

图片 8 

 

报告急方日志:

图片 9

Thu Sep 21 14:18:32 2017

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_31964.trc:

Thu Sep 21 14:18:33 2017

Dumping diagnostic data in directory=[cdmp_20170921141833], requested by (instance=1, osid=31964), summary=[abnormal process termination].

 

 

 

找到:

oer 8102.2 - obj# 52, rdba: 0x00416b70(afn 1, blk# 93040)

kdk key 8102.2:

  ncol: 1, len: 5

  key: (5):  04 c3 02 52 47<<<<===在索引中找不到键值04 c3 02 52 47(转换为10进制即18170)

 

  mask: (4096):

。。。。。。。。。省略。。。。。。。。

 

*** 2017-09-21 14:18:32.488

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

----- Error Stack Dump -----

----- Current SQL Statement for this session (sql_id=bajr90ryjd2w8) -----

update con$ set con#=:3,spare1=:4 where owner#=:1 and name=:2

。。。。。。。。。省略。。。。。。。。

Block header dump:  0x00416b70<<<<===搜Block header dump

Object id on Block? Y

seg/obj: 0x34  csc: 0x00.39125d6  itc: 3  flg: O  typ: 2 - INDEX<<<<===找到52号对象

     fsl: 0  fnx: 0x416b71 ver: 0x01

。。。。。。。。。省略。。。。。。。。

row#447[966] flag: ------, lock: 0, len=13, data:(6):  00 41 69 a4 00 d9

col 0; len 4; (4):  c3 02 52 44

row#448[940] flag: ---D--, lock: 3, len=13, data:(6):  00 41 69 a4 00 da

col 0; len 4; (4):  c3 02 52 45

row#449[953] flag: ------, lock: 0, len=13, data:(6):  00 40 01 21 00 0c====>>>>> ROWID

col 0; len 4; (4):  c3 02 52 46====>>>>> 索引中存储的最大键值是18169

----- end of leaf block dump -----

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

      SO: 0x7624b240, type: 56, owner: 0x77896d88, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

       proc=0x77c98660, name=transaction, file=ktccts.h LINE:410, pg=0

 

 

瞩目索引中的ROWID(2进制共49人)=文件号(2进制前拾人)+块号(2进制二十二位)+行号(2进制15人)

00 40 01 21 00 0c(16进制)= 000000000100000000000001001000010000000000001100(2进制)

文件号=(0000000001)取前十一人的二进制=第1号文件

块号=(0000000000000100100001)取前2进制22位==>第289号

行号=(0000000000001100)取2进制16位==>第12行

LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;

 

DUMP(18169,16)          DUMP(18170,16)

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

Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47

 

LHR@ora11g > select utl_raw.cast_to_number('c3025247'),utl_raw.cast_to_number('c3025246') from dual;

 

UTL_RAW.CAST_TO_NUMBER('C3025247') UTL_RAW.CAST_TO_NUMBER('C3025246')

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

                             18170                              18169

 

 

ORA-8102周围于索引键值与表上存的值不平等。(Corruption related to Index 索引卡塔尔国

ORA- 8102即恐怕是ORACLE的bug,也恐怕是由于硬件I/O错误所引起。硬件依然I/O子系统由于错过写 Lost Write造成块的逻辑上错误,当叁个Lost Io发生,满含对key的更改也许尚未写入到ORACLE数据文件上,那即恐怕爆发在表块上也可以有可能暴发在索引块上。

查阅这一个指标号为52的指标,发掘是SYS.CON$表中I_CON2索引,那是一个BOOTSTRAP$对象,何况那是叁个中坚BOOTSTRP$对象,是不可能因此startup migrate和event 38003重新建立的,所以最终只得通过BBED去修改这几个块中不平日的地点。

[root@rhel6lhr ~]# oerr ora 38003

38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL"

// *Cause:

// *Action:

[root@rhel6lhr ~]#

 

先经过上边那些SQL,查找表和目录之间终归相差什么

 

LHR@ora11g > set autot on

LHR@ora11g >

LHR@ora11g > select /*+ index(t I_CON2) */ T.CON# from sys.CON$  t 

  2  minus

  3  select /*+ full(t1) */ T1.CON# from sys.CON$  t1;

 

      CON#

----------

     18169

 

 

Execution Plan

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

Plan hash value: 2332423480

 

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

| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |        | 16963 |   165K|       |   163  (48)| 00:00:02 |

|   1 |  MINUS              |        |       |       |       |            |          |

|   2 |   SORT UNIQUE NOSORT|        | 16963 | 84815 |       |    88   (3)| 00:00:02 |

|   3 |    INDEX FULL SCAN  | I_CON2 | 16963 | 84815 |       |    32   (0)| 00:00:01 |

|   4 |   SORT UNIQUE       |        | 16963 | 84815 |   208K|    76   (3)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| CON$   | 16963 | 84815 |       |    20   (0)| 00:00:01 |

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

 

 

Statistics

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

          1  recursive calls

          0  db block gets

        102  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

LHR@ora11g > select /*+ full(t1) */ T1.CON# from sys.CON$  t1

  2   minus

  3    select /*+ index(t I_CON2) */ T.CON# from sys.CON$  t 

  4   ;

 

      CON#

----------

     18170

 

 

Execution Plan

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

Plan hash value: 2017867816

 

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

| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |        | 16963 |   165K|       |   163  (55)| 00:00:02 |

|   1 |  MINUS              |        |       |       |       |            |          |

|   2 |   SORT UNIQUE       |        | 16963 | 84815 |   208K|    76   (3)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| CON$   | 16963 | 84815 |       |    20   (0)| 00:00:01 |

|   4 |   SORT UNIQUE NOSORT|        | 16963 | 84815 |       |    88   (3)| 00:00:02 |

|   5 |    INDEX FULL SCAN  | I_CON2 | 16963 | 84815 |       |    32   (0)| 00:00:01 |

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

 

 

Statistics

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

          1  recursive calls

          0  db block gets

        102  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

LHR@ora11g > select /*+ full(t) */  T.CON# from sys.CON$  t  WHERE t.CON# IN (18169,18170);

 

      CON#

----------

     18170

 

 

Execution Plan

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

Plan hash value: 3767504726

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     2 |    10 |    20   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CON$ |     2 |    10 |    20   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("T"."CON#"=18169 OR "T"."CON#"=18170)

 

 

Statistics

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

          5  recursive calls

          0  db block gets

         77  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

LHR@ora11g > select /*+ full(t I_CON2) */  T.CON# from sys.CON$  t  WHERE t.CON# IN (18169,18170);

 

      CON#

----------

     18169

 

 

Execution Plan

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

Plan hash value: 1461913314

 

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

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |        |     2 |    10 |     3   (0)| 00:00:01 |

|   1 |  INLIST ITERATOR   |        |       |       |            |          |

|*  2 |   INDEX UNIQUE SCAN| I_CON2 |     2 |    10 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("T"."CON#"=18169 OR "T"."CON#"=18170)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

行使那样的艺术来查询表和目录之间的不均等,通过询问结果真的能够看看表和索引存在不形似的,表里是18170,而索引里储存的是18169,表达索引键值与表上存的值不一致样

 

那正是说怎么做呢?如哪儿理这么些难点?大家向来对索引举办rebuild,看可以还是不可以?

SYS@ora11g > alter index sys.I_CON2 rebuild;

alter index sys.I_CON2 rebuild

*

ERROR at line 1:

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

 

LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;

 

DUMP(18169,16)          DUMP(18170,16)

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

Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47

 

LHR@ora11g > select utl_raw.cast_to_number('c3025247'),utl_raw.cast_to_number('c3025246') from dual;

 

UTL_RAW.CAST_TO_NUMBER('C3025247') UTL_RAW.CAST_TO_NUMBER('C3025246')

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

                             18170                              18169

 

 

[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'

 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 16:08:05 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set block 93040

        BLOCK#          93040

 

BBED> map

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 93040                                 Dba:0x00000000

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

KTB Data Block (Index Leaf)

 

struct kcbh, 20 bytes                      @0      

 

struct ktbbh, 96 bytes                     @20     

 

struct kdxle, 32 bytes                     @116    

 

sb2 kd_off[450]                            @148    

 

ub1 freespace[4]                           @1048   

 

ub1 rowdata[7068]                          @1052   

 

ub4 tailchk                                @8188   

 

BBED> f /x 025246

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 93040            Offsets: 1079 to 1590           Dba:0x00000000

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

 02524600 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000

4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704

c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100

00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c

04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000

00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400

d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c

01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121

000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252

3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169

a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302

52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040

0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3

02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000

4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04

c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100

 

<32 bytes="" per="" line="">

 

BBED> f

BBED-00212: search string not found

 

BBED> d

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 93040            Offsets: 1079 to 1590           Dba:0x00000000

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

02524600 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000

4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704

c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100

00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c

04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000

00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400

d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c

01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121

000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252

3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169

a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302

52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040

0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3

02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000

4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04

c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100

 

<32 bytes="" per="" line="">

 

BBED> m /x 025247

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

File: /u01/app/oracle/oradata/ora11g/system01.dbf (0)

Block: 93040            Offsets: 1079 to 1590           Dba:0x00000000

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

02524700 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000

4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704

c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100

00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c

04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000

00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400

d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c

01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121

000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252

3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169

a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302

52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040

0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3

02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000

4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04

c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100

 

<32 bytes="" per="" line="">

 

BBED> sum

Check value for File 0, Block 93040:

current = 0xde85, required = 0xdf85

 

BBED> sum apply

Check value for File 0, Block 93040:

current = 0xdf85, required = 0xdf85

 

 

 

以此操作甘休后,能够重启数据库,然后测一下建主键。

SYS@ora11g > conn lhr/lhr

Connected.

LHR@ora11g > ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID);

ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-08102: index key not found, obj# 52, file 1, block 93040 (2)

 

 

LHR@ora11g > conn / as sysdba

Connected.

SYS@ora11g > startup force

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             314576256 bytes

Database Buffers           83886080 bytes

Redo Buffers                8503296 bytes

Database mounted.

Database opened.

 

SYS@ora11g > conn lhr/lhr

Connected.

LHR@ora11g >  ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID);

 

Table altered.

 

 

因为贰个段的首先个区的首先个块是FI景逸SUVST LEVEL BITMAP BLOCK,第4个块是SECOND LEVEL BITMAP BLOCK,那四个块是用来管理free block的,第多个块是PAGETABLE SE欧霉素ENT HEADEXC60,那个块才是segment里的HEADER_BLOCK,再前面包车型客车块就是用来记录数据的。关于这么些知识,能够参照他事他说加以考察笔者博客ORACLE关于段的HEADE本田CR-V_BLOCK的一点浅析。而小编辈最上边的例子,是将率先个块布局为坏块,所以造成地点SQL无法得悉。 大家再一次组织案例,如大家将索引段的多少块布局为坏块,比方上边,将块号1肆十五个人为布局坏块。那么当时这几个剧本就能够找寻坏块索引了。所以综上述试验能够观望,那几个剧本查找坏块索引是有原则的,要看索引段损坏的块是如何本种

实际上设置10231内部事件后,假若地点逻辑导出没难点,这种气象当然还是能把数量直接导出到一时表,尤其便于。

2.4  实验总括

1、ORA-08102错误发生时会生成生机勃勃份errorstack的trace文件,从该公文中得以拿走不雷同的详细音讯。该trace文件指的细读四回。

2、实验中的修复进度都以将索引中的键值修改为表中的值,其实,只要保障那2者风姿洒脱致就能够。也足以依赖报错消息找到有关的表的ROWID消息,然后使用bbed矫正表中的值和目录中的值保持少年老成致就可以。--大麦苗已推行通过

SQL> SELECT FILE_ID, 2 BLOCK_ID, 3 BLOCKS FROM DBA_EXTENTS 4 5 WHERE OWNER ='&OWNER'6 AND SEGMENT_NAME = '&TABLE_NAME'; Enter value for owner: KERRYold 5: WHERE OWNER ='&OWNER'new 5: WHERE OWNER ='KERRY'Enter value for table_name: IX_TESTold 6: AND SEGMENT_NAME = '&TABLE_NAME'new 6: AND SEGMENT_NAME = 'IX_TEST'FILE_ID BLOCK_ID BLOCKS---------- ---------- ----------8 144 88 152 8SQL> SELECT HEADER_FILE2 , HEADER_BLOCK3 , BYTES4 , BLOCKS5 , EXTENTS FROM DBA_SEGMENTS 6 7 WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME';Enter value for owner: KERRYEnter value for segment_name: IX_TESTold 7: WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME'new 7: WHERE OWNER='KERRY' AND SEGMENT_NAME='IX_TEST'HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS----------- ------------ ---------- ---------- ----------8 146 131072 16 2SQL> 

RMAN> recover datafile 8 block 148 clear;
SQL> select count from t_part;select count from t_part*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> select count from t_part; COUNT---------- 29823SQL> create table temp_t_part_20150103 as select * from t_part partition;Table created.SQL> alter system set events='10231 trace name context off';System altered.SQL> select count from t_part partition;select count from t_part partition*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> select count from temp_t_part_20150103; COUNT---------- 19823

2.4.1  一成不改变后生可畏的实验数据展现

使用BBED改良前取得到的数量:

项目

OBJ$上DATAOBJ#列的最大值

94098

OBJ$上DATAOBJ#列的最大值dump值

Typ=2 Len=4: c3,a,29,63  即:04c30a2963

该行所在数据块的地址

     FILE#     BLOCK#       ROW#

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

         1        241         27

该行的存储情况

    COUNTS MAX_ROWNUM MIN_ROWNUM

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

       105        104          0

 

列名

10进制值

16进制值

dump文件的存储

bbed中的存储

 

 

 

tab 0, row 27, @0x13b ====>>>>> 该块中第一个表第27行的指针位置,转换为10进制是315

tl: 72 fb: --H-FL-- lb: 0x1  cc: 18

rowdata[0]                                  @383    

----------

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

lock@384:  0x01

cols@385:    18===>>>>> 共18

OBJ#

1

Typ=2 Len=2: c1,2

col  0: [ 2]  c1 02

col    0[2] @386: 1

DATAOBJ#

94098

Typ=2 Len=4: c3,a,29,63

col  1: [ 4]  c3 0a 29 63

col    1[4] @389: 94098 ==>>>>> 需要修改该列的值

OWNER#

0

Typ=2 Len=1: 80

col  2: [ 1]  80

col    2[1] @394: 0

NAME

_NEXT_OBJECT

Typ=1 Len=12: 5f,4e,45,58,54,5f,4f,42,4a,45,43,54

col  3: [12]  5f 4e 45 58 54 5f 4f 42 4a 45 43 54

col   3[12] @396: _NEXT_OBJECT

NAMESPACE

1

Typ=2 Len=2: c1,2

col  4: [ 2]  c1 02

col    4[2] @409: 1

SUBNAME

 

NULL

col  5: *NULL*

col    5[0] @412: *NULL*

TYPE#

0

Typ=2 Len=1: 80

col  6: [ 1]  80

col    6[1] @413: 0

CTIME

2011/9/17 9:46

Typ=12 Len=7: 78,6f,9,11,a,2f,e

col  7: [ 7]  78 6f 09 11 0a 2f 0e

col    7[7] @415: 2011-09-17 09:46:13

MTIME

2017/9/20 18:28

Typ=12 Len=7: 78,75,9,14,13,1d,25

col  8: [ 7]  78 75 09 14 13 1d 25

col    8[7] @423: 2017-09-20 18:28:36

STIME

2011/9/17 9:46

Typ=12 Len=7: 78,6f,9,11,a,2f,e

col  9: [ 7]  78 6f 09 11 0a 2f 0e

col    9[7] @431: 2011-09-17 09:46:13

STATUS

0

Typ=2 Len=1: 80

col 10: [ 1]  80

col   10[1] @439: 0

REMOTEOWNER

 

NULL

col 11: *NULL*

col   11[0] @441: *NULL*

LINKNAME

 

NULL

col 12: *NULL*

col   12[0] @442: *NULL*

FLAGS

0

Typ=2 Len=1: 80

col 13: [ 1]  80

col   13[1] @443: 0

OID$

 

NULL

col 14: *NULL*

col   14[0] @445: *NULL*

SPARE1

0

Typ=2 Len=1: 80

col 15: [ 1]  80

col   15[1] @446: 0

SPARE2

65535

Typ=2 Len=4: c3,7,38,24

col 16: [ 4]  c3 07 38 24

col   16[4] @448: 65535

SPARE3

0

Typ=2 Len=1: 80

col 17: [ 1]  80

col   17[1] @453: 0

 

 

总结

Reference

2.4.2  校勘块的标识位

通过bbed修改i_obj4中的dataobj#值使之和obj$中对应值生机勃勃致。

BED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf

BLOCK = 97266

 

Block 97266 is corrupt

Corrupt block relative dba: 0x00417bf2 (file 0, block 97266)

Fractured block found during verification

Data in bad block:

type: 6 format: 2 rdba: 0x00417bf2

last change scn: 0x0000.00102ed8 seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x2ed80602

check value in block header: 0x7955

computed block checksum: 0x0

 

 

DBVERIFY - Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 1

Total Blocks Influx           : 2

Message 531 not found;  product=RDBMS; facility=BBED

 

 

BBED> set offset 8188

        OFFSET          8188

 

BBED> d

File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)

Block: 97266            Offsets: 8188 to 8191           Dba:0x00000000

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

0206d82e

 

<32 bytes="" per="" line="">

 

BBED> m /x 01

File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)

Block: 97266            Offsets: 8188 to 8191           Dba:0x00000000

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

0106d82e

 

<32 bytes="" per="" line="">

 

BBED> sum

Check value for File 0, Block 97266:

current = 0x7955, required = 0x7956

 

BBED> sum apply

Check value for File 0, Block 97266:

current = 0x7956, required = 0x7956

 

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf

BLOCK = 97266

 

 

DBVERIFY - Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 1

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

 

 

 

如上正是这篇文章的全体内容了,希望本文的原委对我们的上学大概专门的职业有所一定的参照学习价值,假如有疑难大家能够留言调换,多谢我们对台本之家的支撑。

第三章 参照小说

3.1  博客

4. ORA-08102: index key not found:

5. 通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误:

6. 使用bbed 修复I_OBJ4 index 报ORA-8102错误:

7. 利用bbed来修复ora-08102错误 :

8. 跳过obj$坏块方法:

9. bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等卡塔尔国格外苏醒—ORA-00701漏洞超多解决:

10. 分享I_OBJ4 ORA-8102故障恢复案例:

11. How to recreate Bootstrap Index(I_OBJ1,I_USER1,I_FILE#_BLOCK#) to fix ORA-00701 ?:

12. bootstrap$基本指标数据不雷同招致ORA-08102:

 

 

 

 

 

 

第四章 试验中用到的SQL总括

SELECT COUNT(*) COUNTS,

       MAX(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MAX_ROWNUM,

       MIN(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)) MIN_ROWNUM

  FROM SYS.OBJ$ D

WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 1

   AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 241;

 

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,

       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,

       DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW#

  FROM SYS.OBJ$

WHERE DATAOBJ# = 94109;

 

SELECT DUMP(94098, 16) FROM DUAL;

 

SELECT DUMP(OBJ#, 16),

       DUMP(DATAOBJ#, 16),

       DUMP(OWNER#, 16),

       DUMP(NAME, 16),

       DUMP(NAMESPACE, 16),

       DUMP(SUBNAME, 16),

       DUMP(TYPE#, 16),

       DUMP(CTIME, 16),

       DUMP(MTIME, 16),

       DUMP(STIME, 16),

       DUMP(STATUS, 16),

       DUMP(REMOTEOWNER, 16),

       DUMP(LINKNAME, 16),

       DUMP(FLAGS, 16),

       DUMP(OID$, 16),

       DUMP(SPARE1, 16),

       DUMP(SPARE2, 16),

       DUMP(SPARE3, 16),

       DUMP(SPARE4, 16),

       DUMP(SPARE5, 16),

       DUMP(SPARE6, 16)

  FROM SYS.OBJ$ D

WHERE DATAOBJ# = 94098;

 

SELECT OBJ#,

       DATAOBJ#,

       OWNER#,

       NAME,

       NAMESPACE,

       SUBNAME,

       TYPE#,

       CTIME,

       MTIME,

       STIME,

       STATUS,

       REMOTEOWNER,

       LINKNAME,

       FLAGS,

       OID$,

       SPARE1,

       SPARE2,

       SPARE3

  FROM SYS.OBJ$ D

WHERE DATAOBJ# = 94098;

 

SELECT * FROM SYS.OBJ$ D WHERE D.NAME = 'OBJ$';

 

SELECT 'DUMP(' || D.COLUMN_NAME || ',16),'

  FROM DBA_TAB_COLS D

WHERE D.TABLE_NAME = 'OBJ$'

ORDER BY D.COLUMN_ID;

SELECT D.COLUMN_NAME || ','

  FROM DBA_TAB_COLS D

WHERE D.TABLE_NAME = 'OBJ$'

ORDER BY D.COLUMN_ID;

 

 

SELECT * FROM sys.Bootstrap$ ;

 

ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)

 

select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$  t 

minus

select /*+ full(t1) */ DATAOBJ# from sys.obj$  t1;

 

 

select /*+ full(t1) */ DATAOBJ# from sys.obj$  t1

minus

  select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$  t 

;

select /*+ full(t) */  DATAOBJ#,type#,owner# from sys.obj$  t  WHERE t.dataobj# IN (94098,94099);

select /*+ full(t i_obj4) */  DATAOBJ#,type#,owner# from sys.obj$  t  WHERE t.dataobj# IN (94098,94099);

 

 

--16进制转换为10进制

select utl_raw.cast_to_number('c30a2964') from dual;

 

 

show all

map

p kdbr

p *kdbr[27]

x /rnnncncntttnccncnnn

d /v count 32

f /x c30a29

sum

sum apply

v

 

 



style="color: #000001; font-family: courier new; font-size: 12pt; line-height: 1.6;">About Me

.............................................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2145368/

● 本文博客园地址:http://www.cnblogs.com/lhrbest/p/7576416.html

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-09-20 09:00 ~ 2017-09-23 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

● 小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店

.............................................................................................................................................




总结

如上便是那篇小说的全体内容了,希望本文的原委对我们的读书只怕专门的学问能拉动一定的扶植,假设失常大家能够留言调换,感谢我们对剧本之家的帮衬。

编辑:MySQL数据库 本文来源:BBED模拟并修复ORA

关键词: