sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

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

【案例】Oracle报错ORA-01210 datafile header block损坏的修复方法

时间:2016-12-19 10:17   来源:Oracle研究中心   作者:网络   点击:

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
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 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
//          to time before file was originally created.

针对这种情况下,创建datafile后,其scn是非常老的,如果你需要进行recover,那么需要从该scn开始至今的所有archivelog。

在这样的情况下起手就可以手工去修复数据文件头,如果是损坏很严重,那么你可以偷懒,从其他datfile copy过去,然后再修改。

+++++++++++ 如下是整个修复的过程,我这里来简单演示一下。

BBED> copy file 4 block 1 to file 2 block 1
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:    0 to  511           Dba:0x00800001
------------------------------------------------------------------------
0ba20000 01000001 00000000 00000104 b2590000 00000000 0005200a 294d0a93
524f4745 52000000 db210000 60950000 00200000 04000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 b0220000 00000000 cb3eb32a 8ebafc2f d4a75c00 00000000 00000000
00000000 00000000 00000000 8b040000 d7b8fc2f 8a040000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000
00000000 00000000 00000000 00000000 04000000 00000000 00000000 1d41672f
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
e8b8fc2f f1a65c00 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 c6bc5d00 00000000 24aa0830 01000000 03000000 29610000 10000000

<32 bytes per line>

BBED> set file 2 block 1
        FILE#           2
        BLOCK#          1

BBED> map /v
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                                     Dba:0x00800001
------------------------------------------------------------
Data File Header

struct kcvfh, 676 bytes                    @0      
    struct kcvfhbfh, 20 bytes               @0      
    struct kcvfhhdr, 76 bytes               @20     
    ub4 kcvfhrdb                            @96     
    struct kcvfhcrs, 8 bytes                @100    
    ub4 kcvfhcrt                            @108    
    ub4 kcvfhrlc                            @112    
    struct kcvfhrls, 8 bytes                @116    
    ub4 kcvfhbti                            @124    
    struct kcvfhbsc, 8 bytes                @128    
    ub2 kcvfhbth                            @136    
    ub2 kcvfhsta                            @138    
    struct kcvfhckp, 36 bytes               @484    
    ub4 kcvfhcpc                            @140    
    ub4 kcvfhrts                            @144    
    ub4 kcvfhccc                            @148    
    struct kcvfhbcp, 36 bytes               @152    
    ub4 kcvfhbhz                            @312    
    struct kcvfhxcd, 16 bytes               @316    
    word kcvfhtsn                           @332    
    ub2 kcvfhtln                            @336    
    text kcvfhtnm[30]                       @338    
    ub4 kcvfhrfn                            @368    
    struct kcvfhrfs, 8 bytes                @372    
    ub4 kcvfhrft                            @380    
    struct kcvfhafs, 8 bytes                @384    
    ub4 kcvfhbbc                            @392    
    ub4 kcvfhncb                            @396    
    ub4 kcvfhmcb                            @400    
    ub4 kcvfhlcb                            @404    
    ub4 kcvfhbcs                            @408    
    ub2 kcvfhofb                            @412    
    ub2 kcvfhnfb                            @414    
    ub4 kcvfhprc                            @416    
    struct kcvfhprs, 8 bytes                @420    
    struct kcvfhprfs, 8 bytes               @428    
    ub4 kcvfhtrt                            @444    

ub4 tailchk                                @8188

这里有个关键性的问题是,我们需要修改哪些地方? 这就需要你对datafile header的结构相对熟悉了。 dbsanke很早之前写过一篇,大家可以参考下。

++++++ 第一修改的地方:rdba_kcbh

BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes                   @0      
   ub1 type_kcbh                            @0        0x0b
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01000001
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x59b2
   ub2 spare3_kcbh                          @18       0x0000

这里是0×0100001,表示file 4 block 1,我们要将其改成正确的值:file 2 block 1 。 后面block号不变,即应该是0×00800001

BBED> modify /x 01008000 offset 4
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:    4 to  515           Dba:0x00800001
------------------------------------------------------------------------
01008000 00000000 00000104 b2590000 00000000 0005200a 294d0a93 524f4745
52000000 db210000 60950000 00200000 04000300 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
b0220000 00000000 cb3eb32a 8ebafc2f d4a75c00 00000000 00000000 00000000
00000000 00000000 8b040000 d7b8fc2f 8a040000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 04000000 05005553 45525300 00000000 00000000 00000000
00000000 00000000 00000000 04000000 00000000 00000000 1d41672f 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 e8b8fc2f
f1a65c00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
c6bc5d00 00000000 24aa0830 01000000 03000000 29610000 10000000 02000000

<32 bytes per line>

++++++ 第2个修改的地方:kccfhfsz 即文件大小

BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes                   @20     
   ub4 kccfhswv                             @20       0x00000000
   ub4 kccfhcvn                             @24       0x0a200500
   ub4 kccfhdbi                             @28       0x930a4d29
   text kccfhdbn[0]                         @32      R
   text kccfhdbn[1]                         @33      O
   text kccfhdbn[2]                         @34      G
   text kccfhdbn[3]                         @35      E
   text kccfhdbn[4]                         @36      R
   text kccfhdbn[5]                         @37      
   text kccfhdbn[6]                         @38      
   text kccfhdbn[7]                         @39      
   ub4 kccfhcsq                             @40       0x000021db
   ub4 kccfhfsz                             @44       0x00009560
   s_blkz kccfhbsz                          @48       0x00
   ub2 kccfhfno                             @52       0x0004
   ub2 kccfhtyp                             @54       0x0003
   ub4 kccfhacid                            @56       0x00000000
   ub4 kccfhcks                             @60       0x00000000
   text kccfhtag[0]                         @64      
   text kccfhtag[1]                         @65      
   text kccfhtag[2]                         @66      
   text kccfhtag[3]                         @67      
.......

首先你可以从os上去查看数据文件的实际大小,如下:
[ora10g@killdb roger]$ ls -ltr roger01.dbf
-rw-r—– 1 ora10g oinstall 10493952 Jan 28 06:31 roger01.dbf

不过需要注意一点的是,dbfile的大小应该你操作系统上看到的实际大小减去一个db block_size,因为还存在一个os block header。

BBED>  modify /x 00050000 offset 44
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:   44 to  555           Dba:0x00800001
------------------------------------------------------------------------
00050000 00200000 04000300 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 b0220000 00000000
cb3eb32a 8ebafc2f d4a75c00 00000000 00000000 00000000 00000000 00000000
8b040000 d7b8fc2f 8a040000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000
00000000 04000000 00000000 00000000 1d41672f 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 e8b8fc2f f1a65c00 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 c6bc5d00 00000000
24aa0830 01000000 03000000 29610000 10000000 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

++++++ 第3个修改的地方:kccfhfno datafile文件号

BBED> modify /x 02 offset 52
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:   52 to  563           Dba:0x00800001
------------------------------------------------------------------------
02000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 b0220000 00000000 cb3eb32a 8ebafc2f
d4a75c00 00000000 00000000 00000000 00000000 00000000 8b040000 d7b8fc2f
8a040000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553
45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000
00000000 00000000 1d41672f 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 e8b8fc2f f1a65c00 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 c6bc5d00 00000000 24aa0830 01000000
03000000 29610000 10000000 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

++++++ 第4个修改的地方:kscnbas v$datafile.create_change#

BBED> p kcvfhcrs
struct kcvfhcrs, 8 bytes                    @100    
   ub4 kscnbas                              @100      0x000022b0
   ub2 kscnwrp                              @104      0x0000

BBED> modify /x 0def59 offset 100
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  100 to  109           Dba:0x00800001
------------------------------------------------------------------------
0def5900 00000000 cb3e

<32 bytes per line>

++++++++ 第5个修改的地方:kcvfhcrt 表示v$datafile.create_time

BBED> p kcvfhcrt
ub4 kcvfhcrt                                @108      0x2ab33ecb

此时你将数据库mount,可以通过v$datafile直接查询得知,如下:
SQL> set lines 120
SQL> col creation_time_file for 999999999999999
SQL> col creation_name_scn for 999999999999999
SQL> select file#,to_char(creation_time,'yyyy-mm-dd hh24:mi:ss') creation_time_file,
  2    (to_char(creation_time,'yyyy')-1988)*12*31*24*3600+
  3    (to_char(creation_time,'mm')-1)*31*24*3600
  4    +(to_char(creation_time,'dd')-1)*24*3600
  5    +to_char(creation_time,'hh24')*3600
  6    +to_char(creation_time,'mi')*60
  7    +to_char(creation_time,'ss') creation_name_scn
  8    from v$datafile order by 1;

     FILE# CREATION_TIME_FILE  CREATION_NAME_SCN
---------- ------------------- -----------------
         1 2010-04-15 13:14:44         716390084
         2 2012-12-21 05:28:14         802589294
         3 2010-04-15 13:14:49         716390089
         4 2010-04-15 13:14:51         716390091
         6 2012-11-20 08:32:57         799835577
         8 2012-02-13 06:11:26         775116686

6 rows selected.
或者你可以直接dump controlfile,然后从trace中获得该信息。

修改为如下:
BBED> modify /x 6e8ad62f offset 108
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1       Offsets:  108 to  127  Dba:0x00800001
-------------------------------------------------------
6e8ad62f 8ebafc2f d4a75c00 00000000 l n..狐/鸳\.....
00000000                            l ....

<16 bytes per line>

++++++++ 第6个修改的地方:kcvfhtsn 表示表空间号v$datafile.ts#

BBED>  p kcvfhtsn
word kcvfhtsn                               @332      4

通过v$datafile查询结果:
SQL> select file#,ts# from v$datafile order by 1;

     FILE#        TS#
---------- ----------
         1          0
         2          6
         3          2
         4          4
         6          5
         8          9

6 rows selected.

SQL>

修改为:
BBED> modify /x 06 offset 332
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  332 to  341           Dba:0x00800001
------------------------------------------------------------------------
06000000 05005553 4552

<32 bytes per line>

++++++++ 第7个修改的地方: kcvfhrfn v$datafile.rfile# 即相对文件号

BBED> p kcvfhrfn
ub4 kcvfhrfn                                @368      0x00000004

BBED> modify /x 02 offset 368
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  368 to  377           Dba:0x00800001
------------------------------------------------------------------------
02000000 00000000 0000

<32 bytes per line>

+++++++++ 第8个修改的地方:kcvfhtnm 表空间名称即v$tablespace.name

BBED> p kcvfhtnm
text kcvfhtnm[0]                            @338     U
text kcvfhtnm[1]                            @339     S
text kcvfhtnm[2]                            @340     E
text kcvfhtnm[3]                            @341     R
text kcvfhtnm[4]                            @342     S
text kcvfhtnm[5]                            @343     
text kcvfhtnm[6]                            @344     
text kcvfhtnm[7]                            @345     
text kcvfhtnm[8]                            @346     
text kcvfhtnm[9]                            @347     
text kcvfhtnm[10]                           @348     
text kcvfhtnm[11]                           @349     
text kcvfhtnm[12]                           @350     
text kcvfhtnm[13]                           @351     
text kcvfhtnm[14]                           @352     
text kcvfhtnm[15]                           @353     
text kcvfhtnm[16]                           @354     
text kcvfhtnm[17]                           @355     
text kcvfhtnm[18]                           @356     
text kcvfhtnm[19]                           @357     
text kcvfhtnm[20]                           @358     
text kcvfhtnm[21]                           @359     
text kcvfhtnm[22]                           @360     
text kcvfhtnm[23]                           @361     
text kcvfhtnm[24]                           @362     
text kcvfhtnm[25]                           @363     
text kcvfhtnm[26]                           @364     
text kcvfhtnm[27]                           @365     
text kcvfhtnm[28]                           @366     
text kcvfhtnm[29]                           @367     

BBED> d /v offset 338
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1       Offsets:  338 to  347  Dba:0x00800001
-------------------------------------------------------
55534552 53000000 0000              l USERS.....

<16 bytes per line>

BBED>

我们只要知道表空间名称,转换一下即可:
SQL> select name from v$tablespace where ts#=6;

NAME
------------------------------
ROGER
SQL> select dump('ROGER',16) from dual;

DUMP('ROGER',16)
----------------------------
Typ=96 Len=5: 52,4f,47,45,52

SQL> select dump('USERS',16) from dual;

DUMP('USERS',16)
----------------------------
Typ=96 Len=5: 55,53,45,52,53

修改为:

BBED> modify /x 524f4745
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  338 to  347           Dba:0x00800001
------------------------------------------------------------------------
524f4745 53000000 0000

<32 bytes per line>

BBED> modify /x 52 342
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  342 to  351           Dba:0x00800001
------------------------------------------------------------------------
52000000 00000000 0000

<32 bytes per line>
BBED>
BBED> sum apply
Check value for File 2, Block 1:
current = 0x15bb, required = 0x15bb

这里补充一下,如果你的表空间名称跟实际不符,可能还需要修改kcvfhtln,表示长度。我这里是ROGER即为5.

最后我们来尝试下:

SQL> startup mount
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.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
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

看来还有地方不对,我们来查询下v$datafile看下信息:

SQL> SELECT file#,rfile#,CREATION_CHANGE#,CHECKPOINT_CHANGE# ,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#
  2  FROM v$datafile ORDER BY 1;

     FILE#     RFILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ---------- ---------------- ------------------ --------------------- ------------ ---------------
         1          1                8            6143174                     0      6143174         6072275
         2          2          5893901            6142083                     0      6142083         6072275
         3          3             5796            6143174                     0      6143174         6072275
         4          4             8880            6143174                     0      6143174         6072275
         6          6          5608840            6143174                     0      6143174         6072275
         8          8          2387501            6143174                     0      6143174         6072275

6 ROWS selected.
可以看到检查点信息不一致,这是因为开始我为了演示模拟中途open过了一次。

而目前datafile 2的实际scn跟实际不符:

BBED> set file 2 block 1
        FILE#           2
        BLOCK#          1

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484    
   struct kcvcpscn, 8 bytes                 @484    
      ub4 kscnbas                           @484      0x005dbcc6 ---checkpoint Scn转换后为6143174
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x3008aa24 -- checkpoint Time 转换后为805874212
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500    
      struct kcvcprba, 12 bytes             @500    
         ub4 kcrbaseq                       @500      0x00000003
         ub4 kcrbabno                       @504      0x00006129
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

然而我们datafile 2的实际情况如何呢? 来查询v$datafile确认一下:

SQL> select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
  2    (to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
  3    (to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
  4    +(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
  5    +to_char(CHECKPOINT_TIME,'hh24')*3600
  6    +to_char(CHECKPOINT_TIME,'mi')*60
  7    +to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
  8    from v$datafile order by 1;

     FILE# CHECKPOINT_TIME_FIL CHECKPOINT_TIME_SCN
---------- ------------------- -------------------
         1 2013-01-28 05:56:52           805874212
         2 2013-01-28 05:22:07           805872127
         3 2013-01-28 05:56:52           805874212
         4 2013-01-28 05:56:52           805874212
         6 2013-01-28 05:56:52           805874212
         8 2013-01-28 05:56:52           805874212

6 rows selected.
我们可以看到,checkpoint scn和time都不对,所以还需要修改下:

BBED> modify /x 83b85d offset 484
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  484 to  493           Dba:0x00800001
------------------------------------------------------------------------
83b85d00 00000000 24aa

<32 bytes per line>

BED> d /v offset 492
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1       Offsets:  492 to  501  Dba:0x00800001
-------------------------------------------------------
24aa0830 01000000 0300              l $0......

<16 bytes per line>

BBED> set file 2 block 1 offset 492
        FILE#           2
        BLOCK#          1
        OFFSET          492

BBED>  modify /x ffa1
File: /home/ora10g/oradata/roger/roger01.dbf (2)
Block: 1                Offsets:  492 to  501           Dba:0x00800001
------------------------------------------------------------------------
ffa10830 01000000 0300

<32 bytes per line>

BBED> sum apply
Check value for File 2, Block 1:
current = 0xb0ee, required = 0xb0ee

修改完成之后我们可以通过dbv来检查下,看看文件头修改是否都正确,如下:

[ora10g@killdb roger]$ dbv file=/home/ora10g/oradata/roger/roger01.dbf start=1 end=2

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Jan 28 08:45:51 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
DBVERIFY - Verification complete

Total Pages Examined         : 2
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 6051499 (0.6051499)

最后我们再来将数据库open,如下:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/home/ora10g/oradata/roger/roger01.dbf'
ORA-01207: file is more recent than control file - old control file

SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 2 failed verification check

ORA-01110: data file 2: '/home/ora10g/oradata/roger/roger01.dbf'
ORA-01207: file is more recent than control file - old control file

遇到过程了,先不要着急,我们来看下这个错误是什么意思? 从字面上的含义来看,意思是说改datafile的信息可能比controlfile中的部分信息还要新,这个有可能,因为最开始我offline 文件后,open过,然后接着修改文件头的时候的信息又是参考其他datafile header block来的,所以file 2的信息可能比以前的信息要新。

既然如此,那么我们重建下controlfhttp://www.oracleplus.netile即可,注意要用noresetlogs方式创建:


SQL> ALTER DATABASE backup controlfile TO trace;
DATABASE altered.

SQL> shutdown immediate
ORA-01109: DATABASE NOT OPEN

DATABASE dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ROGER" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 2 '/home/ora10g/oradata/roger/redo02.log'  SIZE 50M,
  GROUP 3 '/home/ora10g/oradata/roger/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/ora10g/oradata/roger/system01.dbf',
  '/home/ora10g/oradata/roger/roger01.dbf',
  '/home/ora10g/oradata/roger/sysaux01.dbf',
  '/home/ora10g/oradata/roger/users01.dbf',
  '/home/ora10g/oradata/roger/undotbs2_01.dbf',
  '/home/ora10g/oradata/roger/sqlt_01.dbf'
CHARACTER SET ZHS16GBK
;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

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19 
Control file created.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery IF it was restored FROM backup, OR END BACKUP IF it was NOT
ORA-01110: DATA file 2: '/home/ora10g/oradata/roger/roger01.dbf'

从上面的提示来看,正常了,至少能够正常认到datafile 2了,提示需要进行介质恢复。

SQL> recover datafile 2;
Media recovery complete.

SQL> SELECT MAX(checkpoint_change#) FROM v$datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
                6143174

SQL>  SELECT MAX(checkpoint_change#) FROM v$datafile_header;

MAX(CHECKPOINT_CHANGE#)
-----------------------
                6143174

SQL> ALTER DATABASE OPEN;
DATABASE altered.

SQL>  -----最后顺利打开数据库。

到这里就结束了,我认为本身这个没有任何难度,可能只是要理解文件头的结构以及了解相关的信息的含义,最后来个简单的小总结,针对文件头损坏的情况下,在恢复的情况下可能需要修复如下内容:

1. rdba_kcbh (offset 4)即使文件头block的rdba地址
2. kccfhfsz (offset 44) 即文件大小
3. kccfhfno (offset 52) 即datafile文件号
4. kscnbas (offset 100) 即v$datafile.create_change#
5. kcvfhcrt (offset 108) 即v$datafile.create_time
6. kcvfhtsn (offset 332) 即v$datafile.ts#,表示表空间号
7. kcvfhrfn (offset 368) 即v$datafile.rfile#, 表示相对文件号
8. kcvfhtnm (offset 338) 即v$tablespace.name,表示表空间名称(根据实际情况,可能还会需要修改kcvfhtln,表示表空间名称字符长度)
9. kscnbas (offset 484) 即checkpoint scn
10.kcvcptim (offset 492) 即last checkpoint time.


当然,这个总结并不全面,可能很多情况还需要修改一些其他的地方,另外如果是system datafile,那么也会有所不同,比如oracle数据库中的bootstrap$对象是存储在file 1 block 337中.而system 表空间的第一个datafile header中会有个root rdba地址是指向bootstrap地址的,在11gR2版本中该地址变为file 1 block 521(不同版本可能有所差异).

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

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-01210 datafile header block损坏的修复方法

原文唯一网址:http://www.oracleplus.net/arch/1414.html

Oracle研究中心

关键词:

ORA-01210

ORA-01178

Oracle bbed使用案例

datafile header block损坏的情况下,如何去手工恢复