在数据库启动的时如遇核心对象(特别是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 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_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.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_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) |
change bootstrap$ table with bbed to skip corrupt block on i_undo1：等您坐沙发呢！