故障处理：change bootstrap$ table with bbed to skip corrupt block on i_undo1
在数据库启动的时如遇核心对象(特别是bootstrap$中的对象)有坏块，会抛出ORA-00604，ORA-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 Production2 查询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_undo1在bootstrap$中的位置，也可以通过其它数据库查询
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.dbf3 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 out4 出现坏块
直接查询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_UNDO1的DDL语句执行了
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)------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
