修改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
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
 
 
 
   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?ZI?.......
 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 ZI?...........
 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
1
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,如需转载请自行联系原作者
上一篇:Mysql有无group by 对max函数查询结果的影响 ResultSet中有无记录受到影响


下一篇:详细解析MyBatis Plus框架的核心功能!MyBatis Plus框架经典使用场景的分析说明