修改Datafile Header规避ORA-01190

2021-07-15 00:00:04

一触发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

01190, 00000, "control file or data file %s is from before the last RESETLOGS"

// *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

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

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修改数据文件头部规避此错误

1 resetlogs count 和resetlogs scn 在数据文件头部的位置

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 ..............

<16 bytes per line>

BBED> modify /x 7166

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

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

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

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

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

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

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

<16 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 5bc8

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

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

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

5bc8c9a5 00000000 00000000 00000000 00000000 00000400 9b020000 b155

<32 bytes per line>

最后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.

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 RECOVER

11 rows selected.

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

11 rows selected.

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;

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.

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

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

注意这里由于一致性原因不能open resetlogs我们加下面的隐含参数规避该错误

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

System altered.

SQL> startup force mount;

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.

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

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;

Database altered.

SQL> alter database open resetlogs;

Database altered.

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

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

11 rows selected.

SQL> alter database datafile 13 online;

alter database datafile 13 online

ERROR at line 1:

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

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

2 用bbed更改数据文件头部规避ora-01190错误

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

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

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

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

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

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

<16 bytes per line>

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.

<16 bytes per line>

BBED> modify /x 2b63

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

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

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

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

2b630030 ba8dcfa5 00000000 00000000 00000000 00000000 00000400 6c02

<32 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 75dc

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

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

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

75dccfa5 00000000 00000000 00000000 00000000 00000400 6c020000 785f

<32 bytes per line>

BBED> sum apply

Check value for File 13, Block 1:

current = 0xf50b, required = 0xf50b

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 2781863029 805331755 01/21/2013 23:15:55

11 rows selected.

注意下面，我们上面虽然用bbed调整了数据文件头部的restlogs scn 和resetlogs count 使之和控制文件保持一样，不过我们online 数据文件时会接着报需要介质恢复。如下：

SQL> alter database datafile 13 online;

alter database datafile 13 online

ERROR at line 1:

ORA-01113: file 13 needs media recovery

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

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

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

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

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

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

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

<16 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 3408

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

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

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

34080000 05690030 6b020000 00000000 00000000 00000000 00000000 0000

<32 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> dump /v dba 13,1 offset 148

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 3308

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

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

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

33080000 00000000 00000000 00000000 00000000 00000000 00000000 0000

<32 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 7f63

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

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

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

7f630030 01000000 02000000 25000000 100068be 02000000 00000000 0000

<32 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

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

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

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

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

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

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

<16 bytes per line>

BBED> modify /x 76dc

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

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

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

76dccfa5 00000000 7f630030 01000000 02000000 25000000 100068be 0200

<32 bytes per line>

BBED> sum apply

Check value for File 13, Block 1:

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，如需转载请自行联系原作者

码农公寓

相关文章