eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Mac上安装Oracle 10g的几点注意事项 | Blog首页 | 贵德 归德 - 清水黄河之间隐静谧古城 »

Oracle数据恢复:数据文件头的SCN与时间校验

近日在恢复一个数据库时,用到BBED修改数据文件头信息,更加明确了以下内容,与大家分享。

在数据库加载一个数据文件时,需要对文件头的文件创建SCN进行校验,与数据字典(file$)比较, 如果数据文件信息与数据字典不符,则出现ORA-01203错误:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN

可以从数据字典file$中获得SCN信息:
SQL> select file#,crscnwrp,crscnbas from file$;

     FILE#   CRSCNWRP    CRSCNBAS
---------- ---------- ----------
     1        0           8
     2        0        6448
     3        0        6606
     4        0       10623
     5        0      174768

注意4号文件的SCN是10623,5号是174768

SQL> select to_char('10623','xxxxxx') from dual;

TO_CHAR
-------
   297f

SQL> select to_char('174768','xxxxx') from dual;

TO_CHA
------
 2aab0
然后通过BBED去修改这个创建SCN信息,在文件头上找到这个位置:
BBED> set offset 100
    OFFSET             100

BBED> dump
 File: /oracle/oradata/eygle/users01.dbf (4)
 Block: 1                Offsets:  100 to  131           Dba:0x01000001
------------------------------------------------------------------------
 b0aa0200 00000000 a111ed2c 3a12ed2c 53ab0200 0000e000 00000000 00000000

 <32 bytes per line>

BBED> modify /x 7f290000
 File: /oracle/oradata/eygle/users01.dbf (4)
 Block: 1                Offsets:  100 to  131           Dba:0x01000001
------------------------------------------------------------------------
 7f290000 00000000 a111ed2c 3a12ed2c 53ab0200 0000e000 00000000 00000000

 <32 bytes per line>

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

BBED>

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

然后再次尝试恢复数据文件4,会收到如下错误:
SQL> recover datafile 4;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'
ORA-01202: wrong incarnation of this file - wrong creation time

这里提示的是文件创建时间错误,这是与控制文件的交互校验,如果不通过,需要重建控制文件,这个步骤较为简单:
SQL> @cr
CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01200: actual file size of 640 is smaller than correct size of 1280 blocks
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'


SQL> select to_char('1280','xxx') from dual;

TO_C
----
 500

SQL> select to_char('640','xxx') from dual;

TO_C
----
 280

这里遇到的错误指文件的实际大小和文件头记录的信息不一致,由于这个头块是我复制而来,还需要修改数据块的数量:
BBED> set offset 44
    OFFSET             44

BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes                   @20     
   ub4 kccfhswv                             @20       0x00000000
   ub4 kccfhcvn                             @24       0x0a200300
   ub4 kccfhdbi                             @28       0x5dbc6478
   text kccfhdbn[0]                         @32      E
   text kccfhdbn[1]                         @33      Y
   text kccfhdbn[2]                         @34      G
   text kccfhdbn[3]                         @35      L
   text kccfhdbn[4]                         @36      E
   text kccfhdbn[5]                         @37      
   text kccfhdbn[6]                         @38      
   text kccfhdbn[7]                         @39      
   ub4 kccfhcsq                             @40       0x0000008b
   ub4 kccfhfsz                             @44       0x00000500
   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      
   text kccfhtag[4]                         @68      
   text kccfhtag[5]                         @69      
   text kccfhtag[6]                         @70      
   text kccfhtag[7]                         @71      
   text kccfhtag[8]                         @72      
   text kccfhtag[9]                         @73      
   text kccfhtag[10]                        @74      
   text kccfhtag[11]                        @75      
   text kccfhtag[12]                        @76      
   text kccfhtag[13]                        @77      
   text kccfhtag[14]                        @78      
   text kccfhtag[15]                        @79      
   text kccfhtag[16]                        @80      
   text kccfhtag[17]                        @81      
   text kccfhtag[18]                        @82      
   text kccfhtag[19]                        @83      
   text kccfhtag[20]                        @84      
   text kccfhtag[21]                        @85      
   text kccfhtag[22]                        @86      
   text kccfhtag[23]                        @87      
   text kccfhtag[24]                        @88      
   text kccfhtag[25]                        @89      
   text kccfhtag[26]                        @90      
   text kccfhtag[27]                        @91      
   text kccfhtag[28]                        @92      
   text kccfhtag[29]                        @93      
   text kccfhtag[30]                        @94      
   text kccfhtag[31]                        @95      

BBED> set offset 44
    OFFSET             44

BBED> dump         
 File: /oracle/oradata/eygle/users01.dbf (4)
 Block: 1                Offsets:   44 to   75           Dba:0x01000001
------------------------------------------------------------------------
 00050000 00200000 04000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> modify /x 8002
 File: /oracle/oradata/eygle/users01.dbf (4)
 Block: 1                Offsets:   44 to   75           Dba:0x01000001
------------------------------------------------------------------------
 80020000 00200000 04000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes                   @20     
   ub4 kccfhswv                             @20       0x00000000
   ub4 kccfhcvn                             @24       0x0a200300
   ub4 kccfhdbi                             @28       0x5dbc6478
   text kccfhdbn[0]                         @32      E
   text kccfhdbn[1]                         @33      Y
   text kccfhdbn[2]                         @34      G
   text kccfhdbn[3]                         @35      L
   text kccfhdbn[4]                         @36      E
   text kccfhdbn[5]                         @37      
   text kccfhdbn[6]                         @38      
   text kccfhdbn[7]                         @39      
   ub4 kccfhcsq                             @40       0x0000008b
   ub4 kccfhfsz                             @44       0x00000280
   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      
   text kccfhtag[4]                         @68      
   text kccfhtag[5]                         @69      
   text kccfhtag[6]                         @70      
   text kccfhtag[7]                         @71      
   text kccfhtag[8]                         @72      
   text kccfhtag[9]                         @73      
   text kccfhtag[10]                        @74      
   text kccfhtag[11]                        @75      
   text kccfhtag[12]                        @76      
   text kccfhtag[13]                        @77      
   text kccfhtag[14]                        @78      
   text kccfhtag[15]                        @79      
   text kccfhtag[16]                        @80      
   text kccfhtag[17]                        @81      
   text kccfhtag[18]                        @82      
   text kccfhtag[19]                        @83      
   text kccfhtag[20]                        @84      
   text kccfhtag[21]                        @85      
   text kccfhtag[22]                        @86      
   text kccfhtag[23]                        @87      
   text kccfhtag[24]                        @88      
   text kccfhtag[25]                        @89      
   text kccfhtag[26]                        @90      
   text kccfhtag[27]                        @91      
   text kccfhtag[28]                        @92      
   text kccfhtag[29]                        @93      
   text kccfhtag[30]                        @94      
   text kccfhtag[31]                        @95      

BBED>
应用修改:
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/eygle/users01.dbf
BLOCK = 1

Block 1 is corrupt
Corrupt block relative dba: 0x01000001 (file 0, block 1)
Bad check value found during verification
Data in bad block:
 type: 11 format: 2 rdba: 0x01000001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x9f43
 computed block checksum: 0x780


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

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

BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/eygle/users01.dbf
BLOCK = 1


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   : 0
Total Blocks Influx           : 0

然后可以重建控制文件,恢复一下,Online加载数据文件,将原本Offline丢失的数据文件,加载回数据库中:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size            2085872 bytes
Variable Size          167775248 bytes
Database Buffers      436207616 bytes
Redo Buffers            6299648 bytes
SQL> @cr
SQL> CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETLOGS  NOARCHIVELOG 
  2       MAXLOGFILES 16
  3       MAXLOGMEMBERS 3
  4       MAXDATAFILES 100
  5       MAXINSTANCES 8
  6       MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oracle/oradata/eygle/redo01.log'  SIZE 50M,
  9    GROUP 2 '/oracle/oradata/eygle/redo02.log'  SIZE 50M,
 10    GROUP 3 '/oracle/oradata/eygle/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oracle/oradata/eygle/system01.dbf',
 14    '/oracle/oradata/eygle/undotbs01.dbf',
 15    '/oracle/oradata/eygle/sysaux01.dbf',
 16    '/oracle/oradata/eygle/users01.dbf',
 17    '/oracle/oradata/eygle/user02.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;

Control file created.

SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'


SQL> select file#,name,status from v$datafile;

     FILE# NAME                     STATUS
---------- ---------------------------------------- -------
     1 /oracle/oradata/eygle/system01.dbf        SYSTEM
     2 /oracle/oradata/eygle/undotbs01.dbf        ONLINE
     3 /oracle/oradata/eygle/sysaux01.dbf        ONLINE
     4 /oracle/oradata/eygle/users01.dbf        RECOVER
     5 /oracle/oradata/eygle/user02.dbf        ONLINE

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                     STATUS
---------- ---------------------------------------- -------
     1 /oracle/oradata/eygle/system01.dbf        SYSTEM
     2 /oracle/oradata/eygle/undotbs01.dbf        ONLINE
     3 /oracle/oradata/eygle/sysaux01.dbf        ONLINE
     4 /oracle/oradata/eygle/users01.dbf        ONLINE
     5 /oracle/oradata/eygle/user02.dbf        ONLINE
此处遇到的错误还有:
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oracle/oradata/eygle/users01.dbf'
ORA-01251: Unknown File Header Version read for file number 4

这是因为文件号错误导致的,修改之后即可。



历史上的今天...
    >> 2008-06-15文章:
    >> 2006-06-15文章:
    >> 2004-06-15文章:
           关于ASSM HWM的研究

无觅

By eygle on 2011-06-15 08:39 | Comments (4) | Backup&Recovery | 2815 |

4 Comments

select to_char('10623','xxxxxx') from dual;
select to_char('174768','xxxxx') from dual;

这2个一直不明白,怎么10623是5位数,但是6个x,1747686个x反而是5个x,请教了!
我使用select to_char('10623','xxxxx') from dual;
select to_char('174768','xxxxxx') from dual;也是一样的结果,不知道到底该如何使用了。


SQL> select file#,crscnwrp,crscnbas from file$;
select file#,crscnwrp,crscnbas from file$
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
在数据库没有打开的情况下,无法执行这个查询啊


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com