修改Datafile Header規避ORA-01190

一觸發ORA-01190錯誤的原因

1 先抛出一個ora-01190錯誤，此錯誤用bbed工具構造

SQL> startup

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 96471936 bytes

Database Buffers 218103808 bytes

Redo Buffers 6365184 bytes

Database mounted.

ORA-01190: control file or data file 11 is from before the last RESETLOGS

ORA-01110: data file 11: '/oracle/test/jiujian1.dbf'

2 oerr ora 01190 給出這個錯誤的解釋

[oracle@oracle ~]$ oerr ora 01190

// *Cause: Attempting to use a data file when the log reset information in

// the file does not match the control file. Either the data file

// or the control file is a backup that was made before the most

// recent ALTER DATABASE OPEN RESETLOGS.

// *Action: Restore file from a more recent backup.

以上英文大體意思是

調用資料檔案時發現資料檔案的resetlogs資訊和控制檔案中resetlogs資訊不比對。

3 查詢資料檔案頭部和控制檔案中關于resetlogs的資訊

控制檔案中關于resetlogs的相關資訊如下：

SQL> select resetlogs_change#, to_char(resetlogs_time,'mm/dd/yyyy hh24:mi:ss') time from v$database;

RESETLOGS_CHANGE# TIME

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

2781464667 01/15/2013 21:51:45

各資料檔案頭中resetlogs的資訊如下：

Fhrls：resetlogs scn值

Fhrlc：resetlogs count值

fhrlc_i：resetlogs count 轉換成10進制的值

對于resetlogs count 的描述如下：

reset logs count and scn: The counter with the SCN is called the Reset Log Stamp,

and is a unique identification. The counter is in fact a timestamp

HXFIL CHANGE# FHRLC_I TIME

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

1 2781464667 804808305 01/15/2013 21:51:45

2 2781464667 804808305 01/15/2013 21:51:45

3 2781464667 804808305 01/15/2013 21:51:45

4 2781464667 804808305 01/15/2013 21:51:45

5 2781464667 804808305 01/15/2013 21:51:45

6 2781464667 804808305 01/15/2013 21:51:45

7 2781464667 804808305 01/15/2013 21:51:45

8 2781464667 804808305 01/15/2013 21:51:45

11 2781455194 804803925 01/15/2013 20:38:45

12 2781464667 804808305 01/15/2013 21:51:45

13 2781464667 804808305 01/15/2013 21:51:45

11 rows selected.

通過對比11号檔案的resetlogs scn及resetlogs count值不難發現觸發ora-01190的原因：即資料檔案頭部的 resetlogs scn 、resetlogs count 和控制檔案中的resetlogs資訊不比對造成的。是以，如果要規避ora-01190錯誤，我們可以通過bbed修改資料檔案頭部resetlogs相關值

二通過bbed修改資料檔案頭部規避此錯誤

resetlogs count 位于資料檔案頭部偏移量112處

resetlogs scn 位于資料檔案頭部偏移量116處

BBED> p offset 112

kcvfh.kcvfhrlc

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

ub4 kcvfhrlc @112 0x2ff85555 resetlogs count

BBED> p offset 116

kcvfh.kcvfhrls.kscnbas

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

ub4 kscnbas @116 0xa5c9a35a resetlogs scn

2 用bbed 調整這兩處的值

調整resetlogs count如下

BBED> dump /v dba 1,1 offset 112 count 30

File: /oracle/test/system1.dbf (1)

Block: 1 Offsets: 112 to 141 Dba:0x00400001

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

7166f82f 5bc8c9a5 00000000 00000000 l qf?[壬?.......

00000000 00000000 00000420 dc07 l ........... ?

<16 bytes per line>

BBED> dump /v dba 11,1 offset 112 count 30

File: /oracle/test/jiujian1.dbf (11)

Block: 1 Offsets: 112 to 141 Dba:0x02c00001

5555f82f 5aa3c9a5 00000000 00000000 l UU?ZＩ?.......

00000000 00000000 00000400 9b02 l ..............

BBED> modify /x 7166

Block: 1 Offsets: 112 to 141 Dba:0x02c00001

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

7166f82f 5aa3c9a5 00000000 00000000 00000000 00000000 00000400 9b02

<32 bytes per line>

調整 resetlogs scn值如下

BBED> dump /v dba 1,1 offset 116 count 30

Block: 1 Offsets: 116 to 145 Dba:0x00400001

5bc8c9a5 00000000 00000000 00000000 l [壬?...........

00000000 00000420 dc070000 df70 l ....... ?..遬

BBED> dump /v dba 11,1 offset 116 count 30

Block: 1 Offsets: 116 to 145 Dba:0x02c00001

5aa3c9a5 00000000 00000000 00000000 l ZＩ?...........

00000000 00000400 9b020000 b155 l ............盪

BBED> modify /x 5bc8

Block: 1 Offsets: 116 to 145 Dba:0x02c00001

5bc8c9a5 00000000 00000000 00000000 00000000 00000400 9b020000 b155

最後sum apply 使更改生效

BBED> sum apply

Check value for File 11, Block 1:

current = 0x77a2, required = 0x77a2

三舉例如下：

最上面的錯誤是由bbed模拟的，下面構造一個比較接近實際的例子。

既然我們已經知道，ora-01190錯誤是由于資料檔案頭部resetlogs資訊和控制檔案中resetlogs資訊不比對造成，那麼當我們alter database open resetlogs 打開一個資料庫時，如果某個檔案頭部沒有被alter database open resetlogs動作更新，比如open resetlogs 打開資料庫前有一個offline的資料檔案，那麼open resetlogs 之後此資料檔案要online時就會報ora-01190錯誤。

1 構造一個ora-01190錯誤

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

FILE# NAME STATUS

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

1 /oracle/test/system1.dbf SYSTEM

2 /oracle/test/zxb.dbf ONLINE

3 /oracle/test/sysaux01.dbf ONLINE

4 /oracle/test/users01.dbf ONLINE

5 /oracle/test/zxa.dbf ONLINE

6 /oracle/test/test1.dbf ONLINE

7 /oracle/test/zxc.dbf ONLINE

8 /oracle/test/undotbs1.dbf ONLINE

9 /oracle/test/undotbs3.dbf ONLINE

11 /oracle/test/jiujian1.dbf ONLINE

13 /oracle/test/test2.dbf ONLINE

SQL> alter database datafile 13 offline;（注意open resetlogs資料庫前需要offline drop 資料檔案這裡先offline觸發報錯時在改成offline drop）

Database altered.

FILE# NAME STATUS

3 /oracle/test/sysaux01.dbf ONLINE

8 /oracle/test/undotbs1.dbf ONLINE

13 /oracle/test/test2.dbf RECOVER

SQL> select hxfil,fhrba_seq from x$kcvfh;

HXFIL FHRBA_SEQ

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

1 5

2 5

3 5

4 5

5 5

6 5

7 5

8 5

9 5

11 5

13 5

SQL> select group#,archived,sequence#,status from v$log;

GROUP# ARC SEQUENCE# STATUS

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

1 YES 7 ACTIVE

2 NO 8 CURRENT

6 YES 6 ACTIVE

4 YES 4 INACTIVE

5 YES 5 ACTIVE

3 YES 3 INACTIVE

6 rows selected.

SQL> startup force mount;

Variable Size 96471936 bytes

SQL> recover database until cancel;

ORA-00279: change 2781702590 generated at 01/21/2013 22:22:06 needed for thread

ORA-00289: suggestion : /oracle/archive/1_5_805259716.dbf

ORA-00280: change 2781702590 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/test/system1.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

注意這裡由于一緻性原因不能open resetlogs我們加下面的隐含參數規避該錯誤

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> startup force mount;

Redo Buffers 6365184 bytes

ORA-01245: offline file 13 will be lost if RESETLOGS is done

ORA-01110: data file 13: '/oracle/test/test2.dbf'

注意：這裡提示如果以resetlogs打開資料庫，則13号檔案會丢失。是以我們用如下指令增加關鍵字for drop 意思就是告訴資料庫，這個資料檔案我後面可能會丢棄，不會在online了。

SQL> alter database datafile 13 offline for drop;

RESETLOGS_CHANGE# TIME

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

2781863029 01/21/2013 23:15:55

SQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh;

HXFIL CHANGE# FHRLC_I TIME

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

1 2781863029 805331755 01/21/2013 23:15:55

2 2781863029 805331755 01/21/2013 23:15:55

3 2781863029 805331755 01/21/2013 23:15:55

4 2781863029 805331755 01/21/2013 23:15:55

5 2781863029 805331755 01/21/2013 23:15:55

6 2781863029 805331755 01/21/2013 23:15:55

7 2781863029 805331755 01/21/2013 23:15:55

8 2781863029 805331755 01/21/2013 23:15:55

9 2781863029 805331755 01/21/2013 23:15:55

11 2781863029 805331755 01/21/2013 23:15:55

13 2781842874 805330675 01/21/2013 22:57:55

SQL> alter database datafile 13 online;

alter database datafile 13 online

ORA-01190: control file or data file 13 is from before the last RESETLOGS

2 用bbed更改資料檔案頭部規避ora-01190錯誤

2b630030 75dccfa5 00000000 00000000 l +c.0u芟?.......

00000000 00000000 00000420 3408 l ........... 4.

BBED> dump /v dba 13,1 offset 112 count 30

File: /oracle/test/test2.dbf (13)

Block: 1 Offsets: 112 to 141 Dba:0x03400001

f35e0030 ba8dcfa5 00000000 00000000 l 骬.0?膝........

00000000 00000000 00000400 6c02 l ............l.

BBED> modify /x 2b63

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

Block: 1 Offsets: 112 to 141 Dba:0x03400001

2b630030 ba8dcfa5 00000000 00000000 00000000 00000000 00000400 6c02

75dccfa5 00000000 00000000 00000000 l u芟?...........

00000000 00000420 34080000 2a61 l ....... 4...*a

BBED> dump /v dba 13,1 offset 116 count 30

Block: 1 Offsets: 116 to 145 Dba:0x03400001

ba8dcfa5 00000000 00000000 00000000 l ?膝............

00000000 00000400 6c020000 785f l ........l...x_

BBED> modify /x 75dc

Block: 1 Offsets: 116 to 145 Dba:0x03400001

75dccfa5 00000000 00000000 00000000 00000000 00000400 6c020000 785f

Check value for File 13, Block 1:

current = 0xf50b, required = 0xf50b

13 2781863029 805331755 01/21/2013 23:15:55

注意下面，我們上面雖然用bbed調整了資料檔案頭部的restlogs scn 和resetlogs count 使之和控制檔案保持一樣，不過我們online 資料檔案時會接着報需要媒體恢複。如下：

SQL> alter database datafile 13 online;

alter database datafile 13 online

ORA-01113: file 13 needs media recovery

3 用bbed調整資料檔案頭部檢查點以及scn相關值

我們還應改如下偏移量

ub4 kcvfhcpc @140 0x00000308------檢查點計數

ub4 kcvfhccc @148 0x00000307------總是比檢查點計算少1

ub4 kcvcptim @492 0x2f9af923-----檢查點時間

ub4 kscnbas @484 0x8013ea80-------- scn的低位

ub2 kscnwrp @488 0x0000--------- scn的高位

BBED> dump /v dba 1,1 offset 140 count 30

Block: 1 Offsets: 140 to 169 Dba:0x00400001

34080000 2a610030 33080000 00000000 l 4...*a.03.......

00000000 00000000 00000000 0000 l ..............

BBED> dump /v dba 13,1 offset 140 count 30

Block: 1 Offsets: 140 to 169 Dba:0x03400001

6c020000 05690030 6b020000 00000000 l l....i.0k.......

BBED> modify /x 3408

Block: 1 Offsets: 140 to 169 Dba:0x03400001

34080000 05690030 6b020000 00000000 00000000 00000000 00000000 0000

BBED> dump /v dba 1,1 offset 148 count 30

Block: 1 Offsets: 148 to 177 Dba:0x00400001

33080000 00000000 00000000 00000000 l 3...............

BBED> dump /v dba 13,1 offset 148

Block: 1 Offsets: 148 to 177 Dba:0x03400001

6b020000 00000000 00000000 00000000 l k...............

BBED> modify /x 3308

Block: 1 Offsets: 148 to 177 Dba:0x03400001

33080000 00000000 00000000 00000000 00000000 00000000 00000000 0000

BBED> dump /v dba 1,1 offset 492 count 30

Block: 1 Offsets: 492 to 521 Dba:0x00400001

7f630030 01000000 01000000 02000000 l .c.0............

10000000 02000000 00000000 0000 l ..............

BBED> dump /v dba 13,1 offset 492 count 30

Block: 1 Offsets: 492 to 521 Dba:0x03400001

4f600030 01000000 02000000 25000000 l O`.0........%...

100068be 02000000 00000000 0000 l ..h?.........

BBED> modify /x 7f63

Block: 1 Offsets: 492 to 521 Dba:0x03400001

7f630030 01000000 02000000 25000000 100068be 02000000 00000000 0000

BBED> dump /v dba 1,1 offset 484 count 30

Block: 1 Offsets: 484 to 513 Dba:0x00400001

76dccfa5 00000000 7f630030 01000000 l v芟?....c.0....

01000000 02000000 10000000 0200 l ..............

BBED> dump /v dba 13,1 offset 484 count 30

Block: 1 Offsets: 484 to 513 Dba:0x03400001

49dccfa5 00000000 7f630030 01000000 l I芟?....c.0....

02000000 25000000 100068be 0200 l ....%.....h?.

BBED> modify /x 76dc

Block: 1 Offsets: 484 to 513 Dba:0x03400001

76dccfa5 00000000 7f630030 01000000 02000000 25000000 100068be 0200

current = 0xc02f, required = 0xc02f

SQL> recover datafile 13;

Media recovery complete.

--------------------------------------------------------完------------------------------------------------

總結

對于ora-01190，往往是ora-01190和資料檔案的不一緻共同出現。是以用bbed解決了ora-01190錯誤後，還需要處理資料檔案的不一緻問題。

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1127404，如需轉載請自行聯系原作者

修改Datafile Header規避ORA-01190

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List