当前位置: 首页 > BACKUP & RESTORE > 正文

change bootstrap$ table with bbed to skip corrupt block on i_undo1

         在数据库启动的时如遇核心对象(特别是bootstrap$中的对象)有坏块,会抛出ORA-00604ORA-01578导致数据库启动失败。下面是模拟在数据库启动时遇到i_undo1索引块完全被损坏的情况下,通过跳过i_undo1来正常启动数据库。如果块只是部分损坏,可以考虑通过bbed来手动修复块。

         下面是测试11.2.0.3环境,请不要在生产环境操作。

         一般索引(特别是对象小于59)引坏块时,我个人习惯使用下面2种解决方案:1,修改oracle二进制文件,通过修改SQL,不走索引,不过此方案需要注意:在二进制文件中的sql,都有长度记录,如果修改sql语句后,需要修改相应SQL的长度。2,修改boostrap$表删除创建索引的行记录。下面是采用方法2来处理

1 环境介绍

下面的实验只能在相同的环境操作,不过的环境请注意需要变化的部分内容

www.htz.pw > !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

 

www.htz.pw > select * from v$version where rownum=1;

 

BANNER

——————————————————————————–

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

2 查询i_undo1索引信息

查询i_undo1区的信息,主要用于下面dd命令操作

www.htz.pw > @extent.sql

Enter value for owner: sys

Enter value for segment_name: i_undo1

Enter value for tablespace_name:

                    

                      FILE                  BLOCK

OWNER:SEGMENT_NAME      ID   FNO  EXTENT_ID BEGIN_END   

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

SYS.I_UNDO1              1     1          0 320~327     

 

查询创建i_undo1bootstrap$中的位置,也可以通过其它数据库查询

www.htz.pw > select * from bootstrap$ where sql_text like ‘%I_UNDO1%’;

 

     LINE#       OBJ# SQL_TEXT

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

        34         34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTI

                      NCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))

查询i_undo1对象存放的物理位置                     

www.htz.pw > select obj#,

       dbms_rowid.rowid_relative_fno(rowid) file_id,

       dbms_rowid.rowid_block_number(rowid) block_id,

       dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id

  from bootstrap$

 where obj# = 34order by obj#;

 

 

            FILE

      OBJ#    ID   BLOCK_ID     ROW_ID

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

        34     1        521          8

       

查询在obj$表中存放的位置

www.htz.pw > select obj#,

       dbms_rowid.rowid_relative_fno(rowid) file_id,

       dbms_rowid.rowid_block_number(rowid) block_id,

       dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id

  from ind$

 where obj# = 34

 order by obj#;

 

            FILE

      OBJ#    ID   BLOCK_ID     ROW_ID

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

        34     1        145          3

 

1 row selected.

查询在ind$表中存放的位置

www.htz.pw > select obj#,

       dbms_rowid.rowid_relative_fno(rowid) file_id,

       dbms_rowid.rowid_block_number(rowid) block_id,

       dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id

  from obj$

 where obj# = 34

 order by obj#;

 

            FILE

      OBJ#    ID   BLOCK_ID     ROW_ID

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

        34     1        241         39

       

查询数据文件的位置

www.htz.pw > select name from v$dbfile;

 

NAME

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

/oracle/app/oracle/oradata/orcl1123/users01.dbf

/oracle/app/oracle/oradata/orcl1123/undotbs01.dbf

/oracle/app/oracle/oradata/orcl1123/sysaux01.dbf

/oracle/app/oracle/oradata/orcl1123/system01.dbf

3 dd清空i_undo1索引

在清空的时候,注意增加上conv=notrunc

www.htz.pw > !dd if=/dev/zero of=/oracle/app/oracle/oradata/orcl1123/system01.dbf bs=8192 count=8 seek=320 conv=notrunc

8+0 records in

8+0 records out

4 出现坏块

直接查询undo$表报坏块

www.htz.pw > select * from undo$ where us#=1;

select * from undo$ where us#=1

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 1, block # 321)

ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’

 

www.htz.pw > startup force;

ORACLE instance started.

 

Total System Global Area  592920576 bytes

Fixed Size                  2230632 bytes

Variable Size             176162456 bytes

Database Buffers          411041792 bytes

Redo Buffers                3485696 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-01578: ORACLE data block corrupted (file # 1, block # 321)

ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’

Process ID: 14665

Session ID: 1 Serial number: 5

5 bbed修改bootstrap$

BBED> set filename ‘/oracle/app/oracle/oradata/orcl1123/system01.dbf’;

        FILENAME        /oracle/app/oracle/oradata/orcl1123/system01.dbf

块号是上面通过rowid得到的,其实在11Gbootstrap$对象存放位置是520,在10G中的位置不一样,请注意版本。

BBED> set block 521

        BLOCK#          521

行记录数也是在之前通过rowid转换得到的

BBED> x /rnnc *kdbr[8]

rowdata[4533]                               @5823   

————-

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

lock@5824: 0x01

cols@5825:    3

 

col    0[2] @5826: 34

col    1[2] @5829: 34

col  2[196] @5832: CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 IN

ITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXT

ENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))

修改行标识符,其实就是在行标识符增加D的标识符

BBED> set mode edit

        MODE            Edit

 

BBED> set count 10

        COUNT           10

 

BBED> modify /x 3c offset 5823

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

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

 Block: 521              Offsets: 5823 to 5832           Dba:0x00000000

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

 3c010302 c12302c1 23c4

 

 <32 bytes per line>

 

BBED> dump offset 5823

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

 Block: 521              Offsets: 5823 to 5832           Dba:0x00000000

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

 3c010302 c12302c1 23c4

 

 <32 bytes per line>

 

BBED> x /rnnc *kdbr[8]

rowdata[4533]                               @5823   

————-

flag@5823: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@5824: 0x01

cols@5825:    0

下面是修改块的剩余空间信息

BBED> sum apply

Check value for File 0, Block 521:

current = 0x41fc, required = 0x41fc

 

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1123/system01.dbf

BLOCK = 521

 

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

data header at 0x2a98b87244

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

        used=6760 fsc=0 avsp=1156 dtl=8120

Block 521 failed with check code 6110

关于这部分的说明可以见BLOG:6110

DBVERIFY – Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing   (Data) : 1

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 0

Total Blocks Influx           : 0

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

 

www.htz.pw > select 8120-6760 from dual;

 

 8120-6760

———-

      1360

www.htz.pw > select to_char(1360,’xxxx’) from dual;

 

TO_CH

—–

  550

 

BBED> p kdbh

struct kdbh, 14 bytes                       @68     

   ub1 kdbhflag                             @68       0x00 (NONE)

   sb1 kdbhntab                             @69       1

   sb2 kdbhnrow                             @70       24

   sb2 kdbhfrre                             @72      -1

   sb2 kdbhfsbo                             @74       66

   sb2 kdbhfseo                             @76       1222

   sb2 kdbhavsp                             @78       1156

   sb2 kdbhtosp                             @80       1156

 

BBED> modify /x 5005 offset 80

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

 Block: 521              Offsets:   80 to   89           Dba:0x00000000

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

 50050000 1800a31f 1a1f

 

 <32 bytes per line>

 

 

BBED> modify /x 5005 offset 78

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

 Block: 521              Offsets:   78 to   87           Dba:0x00000000

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

 50055005 00001800 a31f

 

 <32 bytes per line>

 

BBED> p kdbh

struct kdbh, 14 bytes                       @68     

   ub1 kdbhflag                             @68       0x00 (NONE)

   sb1 kdbhntab                             @69       1

   sb2 kdbhnrow                             @70       24

   sb2 kdbhfrre                             @72      -1

   sb2 kdbhfsbo                             @74       66

   sb2 kdbhfseo                             @76       1222

   sb2 kdbhavsp                             @78       1360

   sb2 kdbhtosp                             @80       1360

 

BBED> sum apply

Check value for File 0, Block 521:

current = 0x41fc, required = 0x41fc

 

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1123/system01.dbf

BLOCK = 521

 

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

data header at 0x2a98b87244

kdbchk: space available on commit is incorrect

        tosp=1360 fsc=0 stb=2 avsp=1360

Block 521 failed with check code 6111

 

DBVERIFY – Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing   (Data) : 1

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 0

Total Blocks Influx           : 0

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

 

 

www.htz.pw > select  to_char(1362,’xxxx’) from dual;

 

TO_CH

—–

  552

 

BBED> modify /x 5205 offset 80

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

 Block: 521              Offsets:   80 to   89           Dba:0x00000000

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

 52050000 1800a31f 1a1f

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 0, Block 521:

current = 0x41fe, required = 0x41fe

 

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1123/system01.dbf

BLOCK = 521

 

 

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

这里看到verify已经没有报错了

6 数据库正常启动

[oracle@www.htz.pw sql]$sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 21 13:49:04 2014

 

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

 

Connected to an idle instance.

 

www.htz.pw > startup

ORACLE instance started.

 

Total System Global Area  592920576 bytes

Fixed Size                  2230632 bytes

Variable Size             176162456 bytes

Database Buffers          411041792 bytes

Redo Buffers                3485696 bytes

Database mounted.

Database opened.

这里看到数据库已经正常启动,其实我们还可以通过10046去跟踪数据库启动过程,可以发现没有CREATE I_UNDO1DDL语句执行了

www.htz.pw > select * from undo$ where us#=1;

Execution Plan

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

Plan hash value: 3995376916

 

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

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

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

|   0 | SELECT STATEMENT  |       |     1 |    52 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| UNDO$ |     1 |    52 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 – filter(“US#”=1)

 

可以看到已经走全表扫描了,

下面是创建一个UNDO表空间,操作undo$表,看是否报错。

www.htz.pw > create undo tablespace undo1 datafile ‘/oracle/app/oracle/oradata/orcl1123/undo1.dbf’ size 10m autoextend on;

 

Tablespace created.

 

www.htz.pw > show parameter undo

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

www.htz.pw > alter system set undo_tablespace=undo1;

 

System altered.

 

www.htz.pw > startup force;

ORA-01031: insufficient privileges

www.htz.pw > conn / as sysdba

Connected.

www.htz.pw > startup force;

ORACLE instance started.

 

Total System Global Area  592920576 bytes

Fixed Size                  2230632 bytes

Variable Size             176162456 bytes

Database Buffers          411041792 bytes

Redo Buffers                3485696 bytes

Database mounted.

Database opened.

已经没有报错了。

7 dbv验证数据文件

通过dbv验证,可以发现原来i_undo1的块都是坏块,也说明了我们上面已经成功的跳过了对i_undo1索引的访问。

www.htz.pw > !dbv file=/oracle/app/oracle/oradata/orcl1123/system01.dbf

 

DBVERIFY: Release 11.2.0.3.0 – Production on Fri Nov 21 15:09:46 2014

 

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

 

DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/orcl1123/system01.dbf

Page 320 is marked corrupt

Corrupt block relative dba: 0x00400140 (file 1, block 320)

Completely zero block found during dbv:

 

Page 321 is marked corrupt

Corrupt block relative dba: 0x00400141 (file 1, block 321)

Completely zero block found during dbv:

 

Page 322 is marked corrupt

Corrupt block relative dba: 0x00400142 (file 1, block 322)

Completely zero block found during dbv:

 

Page 323 is marked corrupt

Corrupt block relative dba: 0x00400143 (file 1, block 323)

Completely zero block found during dbv:

 

Page 324 is marked corrupt

Corrupt block relative dba: 0x00400144 (file 1, block 324)

Completely zero block found during dbv:

 

Page 325 is marked corrupt

Corrupt block relative dba: 0x00400145 (file 1, block 325)

Completely zero block found during dbv:

 

Page 326 is marked corrupt

Corrupt block relative dba: 0x00400146 (file 1, block 326)

Completely zero block found during dbv:

 

Page 327 is marked corrupt

Corrupt block relative dba: 0x00400147 (file 1, block 327)

Completely zero block found during dbv:

 

 

 

DBVERIFY – Verification complete

 

Total Pages Examined         : 90880

Total Pages Processed (Data) : 59549

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 12467

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 3306

Total Pages Processed (Seg)  : 1

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 15550

Total Pages Marked Corrupt   : 8

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1116286 (0.1116286)

本文固定链接: http://www.htz.pw/2014/11/21/change-bootstrap-table-with-bbed-to-skip-corrupt-block-on-i_undo1.html | 认真就输

该日志由 huangtingzhong 于2014年11月21日发表在 BACKUP & RESTORE 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: change bootstrap$ table with bbed to skip corrupt block on i_undo1 | 认真就输
关键字: , ,