oracle scn与数据恢复,Oracle数据恢复：数据文件头的SCN与时间校验

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

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

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

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;也是一样的结果，不知道到底该如何使用了。

这是掩码的位数，只要结果不超过这个位数，几个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$的内容查看到，或者，从历史版本的文件头上看到。很容易得到这个信息。