oracle可以放在文件的第一号,【案例】Oracle报错ORA-01210 datafile header block损坏的修复方法...

最新推荐文章于 2022-03-11 18:18:30 发布

晴朗的树

最新推荐文章于 2022-03-11 18:18:30 发布

阅读量820

点赞数

文章标签： oracle可以放在文件的第一号

当Oracle数据库遭遇ORA-01210错误，表明数据文件头块损坏。本文通过模拟实验，详细介绍了使用BBED工具修复数据文件头损坏的步骤，包括复制健康文件头、验证和修改文件头信息，以及在必要时重建控制文件，最终成功恢复数据库。

摘要由CSDN通过智能技术生成

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库遇到报错ORA-01210分析原因为数据文件头块损坏块导致，使用BBED修复损坏文件。

本站文章除注明转载外，均为本站原创：转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: Archivelog 模式下，datafile header损坏，如何恢复？

客户数据库出现故障，现象的有2个datafile的文件头彻底损坏，有可能是硬件问题导致。

当时由于是windows环境，通过远程桌面操作，太卡，加上接手之前已经有人经过一系列的操作，导致恢复相对麻烦，我采取的方式是利用他们之前create 的datafile，借助bbed修改ckpt信息，然后将库先open后。最后再借助数据抽取软件将备份的损坏datafile数据抽取出来，然后直接加载到数据库中。

这里我来使用vm模拟下如果仅仅是datafile header block损坏的情况下，如何去手工恢复？当然，如果还有

其他block损坏，比如datafile header前面的bitmap block，那么恢复就相当复杂了。

—先模拟文件头损坏的情况

BBED> info

File# Name Size(blks)

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

1 /home/ora10g/oradata/roger/system01.dbf 0

2 /home/ora10g/oradata/roger/roger01.dbf 0

3 /home/ora10g/oradata/roger/sysaux01.dbf 0

4 /home/ora10g/oradata/roger/users01.dbf 0

6 /home/ora10g/oradata/roger/undotbs2_01.dbf 0

8 /home/ora10g/oradata/roger/sqlt_01.dbf 0

BBED> copy file 4 block 5 to file 2 block 1

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

File: /home/ora10g/oradata/roger/roger01.dbf (2)

Block: 1 Offsets: 0 to 511 Dba:0x00800001

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

1ea20000 05000001 b7220000 00000104 19c10000 04000000 09800f00 00000000

00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 2, Block 1:

current = 0xc119, required = 0xc119

BBED> set file 2 block 1

FILE# 2

BLOCK# 1

BBED> verify

DBVERIFY - Verification starting

FILE = /home/ora10g/oradata/roger/roger01.dbf

BLOCK = 1

Block 1 is corrupt

Corrupt block relative dba: 0x01000001 (file 0, block 1)

Bad header found during verification

Data in bad block:

type: 30 format: 2 rdba: 0x01000005

last change scn: 0x0000.000022b7 seq: 0x1 flg: 0x04

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

consistency value in tail: 0x22b71e01

check value in block header: 0xc119

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 : 0

此时启动数据库，你会遇到类似如下错误：

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed SIZE 1272600 bytes

Variable SIZE 146801896 bytes

DATABASE Buffers 16777216 bytes

Redo Buffers 2920448 bytes

DATABASE mounted.

ORA-01122: DATABASE file 2 failed verification CHECK

ORA-01110: DATA file 2: '/home/ora10g/oradata/roger/roger01.dbf'

ORA-01210: DATA file header IS media corrupt

SQL> recover datafile 2;

ORA-00283: recovery SESSION canceled due TO errors

ORA-01110: DATA file 2: '/home/ora10g/oradata/roger/roger01.dbf'

ORA-01122: DATABASE file 2 failed verification CHECK

ORA-01110: DATA file 2: '/home/ora10g/oradata/roger/roger01.dbf'

ORA-01210: DATA file header IS media corrupt

当然我这里模拟有点绝对了，部分情况下，这种情况下是可以recover的。如果说文件头block彻底损坏完了。是不能直接进行修复的。如果你有备份，那么你可以从备份里面将该datafile restore 出来，然后再去进行recover，这样是的。

上次遇到的情况下，在我接手之前，已经有人重建过datafile，且重建过controlfile(原始文件没有备份).

类似的操作:alter database create datafile ‘/home/ora10g/oradata/roger/roger01.dbf’. 当然，这个步骤也需要你先重建controlfile或者使用较旧的controlfile来替换当前的controlfile，不然你会遇到这样的错误：

SQL> alter database create datafile 2 as '/home/ora10g/oradata/roger/roger01.dbf';

alter database create datafile 2 as '/home/ora10g/oradata/roger/roger01.dbf'

ERROR at line 1:

ORA-01178: file 2 created before last CREATE CONTROLFILE, cannot recreate

ORA-01110: data file 2: '/home/ora10g/oradata/roger/roger01.dbf'

SQL> !oerr ora 1178

01178, 00000, "file %s created before last CREATE CONTROLFILE, cannot recreate"

// *Cause: Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a

// datafile that existed at the last CREATE CONTROLFILE command.

// The information needed to recreate the file was lost with the

// control file that existed when the file was added to the database.

// *Action: Find a backup of the file, and recover it. Do incomplete recovery