使用bbed修复ORA-01190 control file or data file 6 is from before the

minebmw7

于 2024-11-28 07:44:15 发布

阅读量175

点赞数 1

文章标签：程序人生

本文链接：https://blog.youkuaiyun.com/minebmw7/article/details/144101212

版权

1.模拟出ora1190

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

    FILE# NAME           STATUS
---------- ---------------------------------------------------------------------- -------
  1 /u01/app/oracle/oradata/11gdb/system01.dbf      SYSTEM
  2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf      ONLINE
  3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf      ONLINE
  4 /u01/app/oracle/oradata/11gdb/users01.dbf      ONLINE
  5 /u01/app/oracle/oradata/11gdb/test01.dbf      ONLINE


SQL> create table t0804(a date) tablespace test1190;

Table created.

SQL> insert into t0804 values(sysdate);

1 row created.

SQL> 
SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t0804;

A
---------
03-MAR-23
03-MAR-23
03-MAR-23
03-MAR-23

SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug close_trace
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/11gdb/udump/11gdb_ora_20222.trc
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

删除新增数据文件重建控制文件

startup nomount
CREATE CONTROLFILE REUSE DATABASE "11gDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/11gdb/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/11gdb/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/11gdb/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/11gdb/system01.dbf',
  '/u01/app/oracle/oradata/11gdb/undotbs01.dbf',
  '/u01/app/oracle/oradata/11gdb/sysaux01.dbf',
  '/u01/app/oracle/oradata/11gdb/users01.dbf',
  '/u01/app/oracle/oradata/11gdb/test01.dbf'
CHARACTER SET ZHS16GBK
;

类似错误还有ora01189

SQL> startup nomount
ORACLE instance started.

Total System Global Area  759169024 bytes
Fixed Size      2023848 bytes
Variable Size    260050520 bytes
Database Buffers   494927872 bytes
Redo Buffers      2166784 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "11gDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/11gdb/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/11gdb/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/11gdb/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/11gdb/system01.dbf',
  '/u01/app/oracle/oradata/11gdb/undotbs01.dbf',
  '/u01/app/oracle/or 10   11   12   13   14   15  adata/11gdb/sysaux01.dbf',
  '/u01/app/oracle/oradata/11gdb/users01.dbf',
  '/u01/app/oracle/oradata/11gdb/test01.dbf',
  '/u01/app/oracle/oradata/11gdb/test1190.dbf'
CHARACTER SET ZHS16GBK
; 16   17   18   19   20  
CREATE CONTROLFILE REUSE DATABASE "11gDB" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 6: '/u01/app/oracle/oradata/11gdb/test1190.dbf'


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

     FILE# NAME           STATUS
---------- ---------------------------------------------------------------------- -------
  1 /u01/app/oracle/oradata/11gdb/system01.dbf      SYSTEM
  2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf      RECOVER
  3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf      RECOVER
  4 /u01/app/oracle/oradata/11gdb/users01.dbf      RECOVER
  5 /u01/app/oracle/oradata/11gdb/test01.dbf      RECOVER

SQL> 

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1613283 generated at 03/03/2023 09:43:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_30_1111587742.dbf
ORA-00280: change 1613283 for thread 1 is in sequence #30


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_30_1111587742.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_30_1111587742.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter database open resetlogs;

Database altered.

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

     FILE# NAME           STATUS
---------- ---------------------------------------------------------------------- -------
  1 /u01/app/oracle/oradata/11gdb/system01.dbf      SYSTEM
  2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf      ONLINE
  3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf      ONLINE
  4 /u01/app/oracle/oradata/11gdb/users01.dbf      ONLINE
  5 /u01/app/oracle/oradata/11gdb/test01.dbf      ONLINE
  6 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006     RECOVER

此时创建控制文件时漏掉的datafile 现在变成了missing

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/u01/app/oracle/oradata/11gdb/test1190.dbf';          
Database altered.

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/u01/app/oracle/oradata/11gdb/test1190.dbf'

ora1189也类似，之所以报这个错误，是因为datafile的某些信息跟其他的datafile的resetlog信息不同。想解决这个问题，就需要对datafile header的结构比较了解。

方法1 利用bbed修复

BBED> info 
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/app/oracle/oradata/11gdb/system01.dbf                       61440
     2  /u01/app/oracle/oradata/11gdb/undotbs01.dbf                       3200
     3  /u01/app/oracle/oradata/11gdb/sysaux01.dbf                       33280
     4  /u01/app/oracle/oradata/11gdb/users01.dbf                          640
     5  /u01/app/oracle/oradata/11gdb/test01.dbf                          1280
     6  /u01/app/oracle/oradata/11gdb/test1190.dbf                         640

BBED> set file 5 block 1
 FILE#           5
 BLOCK#          1

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x43624547

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x0018c907
   ub2 kscnwrp                              @120      0x0000

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0018c908
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x4362454a
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000001
         ub4 kcrbabno                       @504      0x00000002
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

BBED      
BBED> set file 6 block 1
 FILE#           6
 BLOCK#          1

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x4361f41d

BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x00189de4
   ub2 kscnwrp                              @120      0x0000

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0018c906
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x436244d3
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000008
         ub4 kcrbabno                       @504      0x000000f5
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00

BBED> d /v offset 112 count 6
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1       Offsets:  112 to  117  Dba:0x01800001
-------------------------------------------------------
 1df46143 e49d                       l .Ĥ.

 <16 bytes per line>

BBED> d /v offset 112 count 6
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1       Offsets:  112 to  117  Dba:0x01800001
-------------------------------------------------------
 1df46143 e49d                       l .Ĥ.

 <16 bytes per line>

BBED> modify /x 47456243 offset 112
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1                Offsets:  112 to  117           Dba:0x01800001
------------------------------------------------------------------------
 47456243 e49d 

 <32 bytes per line>

BBED> 
BBED> modify /x 07c918 offset 116
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1                Offsets:  116 to  121           Dba:0x01800001
------------------------------------------------------------------------
 07c91800 0000 

 <32 bytes per line>

BBED> modify /x 08c918 offset 484
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1                Offsets:  484 to  489           Dba:0x01800001
------------------------------------------------------------------------
 08c91800 0000 

 <32 bytes per line>

BBED> modify /x 4a45 offset 492
 File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
 Block: 1                Offsets:  492 to  497           Dba:0x01800001
------------------------------------------------------------------------
 4a456243 0100 

 <32 bytes per line>

BBED> sum apply
Check value for File 6, Block 1:
current = 0x6f6e, required = 0x6f6e

数据库

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

     FILE# NAME           STATUS
---------- ---------------------------------------------------------------------- -------
  1 /u01/app/oracle/oradata/11gdb/system01.dbf      SYSTEM
  2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf      ONLINE
  3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf      ONLINE
  4 /u01/app/oracle/oradata/11gdb/users01.dbf      ONLINE
  5 /u01/app/oracle/oradata/11gdb/test01.dbf          ONLINE
  6 /u01/app/oracle/oradata/11gdb/test1190.dbf      RECOVER

6 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

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


     FILE# NAME           STATUS
---------- ---------------------------------------------------------------------- -------
  1 /u01/app/oracle/oradata/11gdb/system01.dbf      SYSTEM
  2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf      ONLINE
  3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf      ONLINE
  4 /u01/app/oracle/oradata/11gdb/users01.dbf      ONLINE
  5 /u01/app/oracle/oradata/11gdb/test01.dbf          ONLINE
  6 /u01/app/oracle/oradata/11gdb/test1190.dbf      ONLINE

6 rows selected.

SQL> SQL>

验证：

SQL> show user;
USER is "SYS"
SQL> select * from test.t0804;

A
---------
03-MAR-23
03-MAR-23
03-MAR-23
03-MAR-23

方法2 使用 events 10015

首先启动到mount状态，执行如下命令：

alter session set events '10015 trace name adjust_scn level 10';
alter database datafile 5 online;
alter database datafile 6 online;
recover database;
alter database open;

本文由 mdnice 多平台发布