sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle无法启动报错ORA-00604 ORA-01578官方解决办法

时间:2016-11-03 10:03   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库无法启动并报错ORA-00604 ORA-01578,分析原因为遇核心对象bootstrap$有坏块的解决办法。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 环境介绍

下面的实验只能在相同的环境操作,不过的环境请注意需要变化的部分内容
oracleplus.net>!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
oracleplus.net>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命令操作
oracleplus.net>@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$中的位置,也可以通过其它数据库查询
oracleplus.net>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对象存放的物理位置
oracleplus.net>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$表中存放的位置
oracleplus.net>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$表中存放的位置
oracleplus.net>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

查询数据文件的位置
oracleplus.net>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
oracleplus.net>!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$表报坏块
oracleplus.net>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’


oracleplus.net>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 Emptyhttp://www.oracleplus.net : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED

oracleplus.net>select 8120-6760 from dual;

8120-6760
———-
1360
oracleplus.net>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

oracleplus.net>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@oracleplus.net 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.

oracleplus.net>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语句执行了
oracleplus.net>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$表,看是否报错。
oracleplus.net>create undo tablespace undo1 datafile ‘/oracle/app/oracle/oradata/orcl1123/undo1.dbf’ size 10m autoextend on;

Tablespace created.

oracleplus.net>show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
oracleplus.net>alter system set undo_tablespace=undo1;

System altered.

oracleplus.net>startup force;
ORA-01031: insufficient privileges
oracleplus.net>conn / as sysdba
Connected.
oracleplus.net>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索引的访问。
oracleplus.net>!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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle无法启动报错ORA-00604 ORA-01578官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1151.html

Oracle研究中心

关键词:

ORA-01578

ORA-00604

Oracle bbed修复Oracle坏块

Oracle坏块导致无法启动