« Mac上安装Oracle 10g的几点注意事项 | Blog首页 | 贵德 归德 - 清水黄河之间隐静谧古城 »
Oracle数据恢复：数据文件头的SCN与时间校验
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2011/06/oracle_fileheader_scn.html
近日在恢复一个数据库时，用到BBED修改数据文件头信息，更加明确了以下内容，与大家分享。链接：https://www.eygle.com/archives/2011/06/oracle_fileheader_scn.html
在数据库加载一个数据文件时，需要对文件头的文件创建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;然后通过BBED去修改这个创建SCN信息，在文件头上找到这个位置：
TO_CHAR
-------
297f
SQL> select to_char('174768','xxxxx') from dual;
TO_CHA
------
2aab0
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>
应用修改：
然后可以重建控制文件，恢复一下，Online加载数据文件，将原本Offline丢失的数据文件，加载回数据库中：
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文章:
By eygle on 2011-06-15 08:39 | Comments (4) | Backup&Recovery | 2815 |
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;也是一样的结果，不知道到底该如何使用了。
这是掩码的位数，只要结果不超过这个位数，几个x无所谓的，我是随便写的几位。
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
在数据库没有打开的情况下，无法执行这个查询啊
可以用工具卸载file$的内容查看到，或者，从历史版本的文件头上看到。很容易得到这个信息。