使用bbed解决ORA-01189错误

ORA-01189错误常见于使用Create Controlfile命令时发现OFFLINE的数据文件或者备份文件生成于上一次的RESETLOGS之前,一般来说如果是在RESETLOGS前offline的数据文件,可以在CREATE CONTROLFILE命令中省略该offline datafile,在打开数据库后使用rename file命令修复其丢失的数据文件名,之后再将数据文件online;若不是offline datafile所造成的ORA-01189错误,就必须要找出与其他数据文件版本一致的数据文件或其备份才能解决该问题。

设想存在这样一个场景:数据库处于非归档无备份的状态,在周日使用shutdown immediate命令干净地关闭了数据库,到周一发现因为磁盘故障丢失了所有的控制文件,不得不使用Create Controlfile RESETLOGS重建控制文件,而在重建过程中又漏输了某条数据文件的记录,之后又使用alter database open resetlogs重置日志文件并打开数据库,此时发现丢失了一个数据文件,尝试使用针对offline datafile的处理方法将丢失的数据文件重命名(rename),并尝试online该数据文件,毫无疑问我们会遇到ORA-01190 “control file or data file %s is from before the last RESETLOGS”错误,这时虽然我们手上有该”干净”的数据文件,却也没有办法使之online了。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

 

诗檀软件专业数据库修复团队

 

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

这里我介绍一种使用bbed修改数据文件头的方法来解决该ORA-01189错误:

 

 

[oracle@rh2 controlfile]$ oerr ora 1189
01189, 00000, "file is from a different RESETLOGS than previous files"
// *Cause:  In a CREATE CONTROLFILE command either this file or all previous
//          files were backups from before the last RESETLOGS. This may also
//          occur if this is a file that is offline and has been offline since
//          before the last RESETLOGS.
// *Action: If the file was taken offline normal before the last RESETLOGS,
//          and is still offline, omit it from the CREATE CONTROLFILE command.
//          Rename and online the file after the database is open. Otherwise
//          find the version of the mentioned file consistent with the rest
//          of the datafiles and resubmit the command.

[oracle@rh2 controlfile]$ oerr ora 1190
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.

/* 在我们的场景中wwli.dbf充当这样一个被"忽视"的数据文件 */

SQL> create tablespace we_will_lost_it datafile '/flashcard/wwli.dbf' size 20M;
Tablespace created.

SQL> create table lost_data tablespace we_will_lost_it as select * from obj$;
Table created.

SQL> alter database backup controlfile to trace ;
Database altered.

SQL> shutdown immediate;

/* 删除所有的控制文件,切勿用于生产环境! */

[oracle@rh2 ~]$ rm /flashcard/oradata/G10R2/controlfile/1.ctl 

[oracle@rh2 ~]$ sqlplus / as sysdba                           

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 27 20:42:31 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2083560 bytes
Variable Size             318768408 bytes
Database Buffers          889192448 bytes
Redo Buffers               14692352 bytes
ORA-00205: error in identifying control file, check alert log for more info

/* 使用create controlfile resetlogs重建控制文件,这里我们"无意"中丢失了一个数据文件  */

SQL> CREATE CONTROLFILE REUSE DATABASE "G10R2" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log',
 10      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_1_6v34jnst_.log'
 11    ) SIZE 50M,
 12    GROUP 2 (
 13      '/flashcard/oradata/G10R2/onlinelog/o1_mf_2_6v34jokt_.log',
 14      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.log'
 15    ) SIZE 50M,
 16    GROUP 3 (
 17      '/flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log',
 18      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_3_6v34jpyn_.log'
 19    ) SIZE 50M
 20  -- STANDBY LOGFILE
 21  DATAFILE
 22    '/flashcard/oradata/G10R2/datafile/o1_mf_system_6v34hhyw_.dbf',
 23    '/flashcard/oradata/G10R2/datafile/o1_mf_undotbs1_6v34hj0l_.dbf',
 24    '/flashcard/oradata/G10R2/datafile/o1_mf_sysaux_6v34hhyx_.dbf',
 25    '/flashcard/oradata/G10R2/datafile/o1_mf_users_6v34hj0p_.dbf',
 26    '/flashcard/oradata/G10R2/datafile/o1_mf_example_6v34jthj_.dbf',
 27    '/flashcard/oradata/G10R2/datafile/o1_mf_test_6vhlm3mv_.dbf',
 28    '/flashcard/oradata/G10R2/datafile/o1_mf_lostfile_6vhtgo7w_.dbf'
 29    -- we lost datafile here!!! '/flashcard/wwli.dbf'
 30  CHARACTER SET UTF8
 31  ;

Control file created.

SQL> recover database using backup controlfile  ;
ORA-00279: change 690423 generated at 04/27/2011 20:40:36 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R2/archivelog/2011_04_27/o1_mf_1_1_%u_.arc
ORA-00280: change 690423 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         9 OFFLINE OFFLINE FILE MISSING                                                               0

SQL> select name from v$datafile where file#=9;

NAME
--------------------------------------------------
/s01/db_1/dbs/MISSING00009

SQL> alter database rename file '/s01/db_1/dbs/MISSING00009' to '/flashcard/wwli.dbf';

Database altered.

SQL> alter database datafile 9 online;
alter database datafile 9 online
*
ERROR at line 1:
ORA-01190: control file or data file 9 is from
before the last RESETLOGS
ORA-01110: data file 9: '/flashcard/wwli.dbf'

/* 此时遭遇了我们之前描述的问题!  */

SQL> oradebug setmypid;
Statement processed.

/* 使用dump file_hdrs命令转储数据文件头,我们转储数据文件头的目的是为了获取resetlogs count,resetlogs scn */

SQL> oradebug dump file_hdrs 8;
Statement processed.

SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_20029.trc

Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000005 03/12/2008 00:39:08
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 04/27/2011 20:45:34
 status:0x2004 root dba:0x00400179 chkpt cnt: 85 ctl cnt:84
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88fa 04/27/2011 20:46:00

Tablespace #1 - UNDOTBS1  rel_fn:2
Creation   at   scn: 0x0000.00092a47 03/12/2008 01:19:05
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 04/27/2011 20:45:34
 status:0x4 root dba:0x00000000 chkpt cnt: 45 ctl cnt:44
begin-hot-backup file size: 0

Tablespace #10 - WE_WILL_LOST_IT  rel_fn:9
Creation   at   scn: 0x0000.000a8849 04/27/2011 20:39:27
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2cadcee3 scn: 0x0000.000a2af7 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/01/1988 00:00:00
 status:0x0 root dba:0x00000000 chkpt cnt: 3 ctl cnt:2
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88f7 04/27/2011 20:40:36
 thread:1 rba:(0x1.c6f.10)

以上可以看到数据文件1和2的resetlogs count(0x2cade887),scn(000a88f9)都是一样的值,而我们的问题数据文件头中的对应信息则与之不同!

接下来我们使用bbed命令去修改问题数据文件头中的resetlogs count,scn信息,关于bbed的编译和使用可以参考这里。

[oracle@rh2 ~]$ bbed filename=/flashcard/wwli.dbf blocksize=8192 password=blockedit mode=edit

BBED: Release 2.0.0.0.0 - Limited Production on Wed Apr 27 20:59:14 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 1
        BLOCK#          1

BBED> map
 File: /flashcard/wwli.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 676 bytes                    @0       

 ub4 tailchk                                @8188    

BBED> p kcvfh
struct kcvfh, 676 bytes                     @0
   struct kcvfhbfh, 20 bytes                @0
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0xa2                     format 10.2
      ub1 spare1_kcbh                       @2        0x00
      ub1 spare2_kcbh                       @3        0x00
      ub4 rdba_kcbh                         @4        0x02400001           RDBA
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0xafa1                checksum
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20
      ub4 kccfhswv                          @20       0x00000000
      ub4 kccfhcvn                          @24       0x0a200300          compatible
      ub4 kccfhdbi                          @28       0xac859d12
      text kccfhdbn[0]                      @32      G                         DB_NAME
      text kccfhdbn[1]                      @33      1
      text kccfhdbn[2]                      @34      0
      text kccfhdbn[3]                      @35      R
      text kccfhdbn[4]                      @36      2
      text kccfhdbn[5]                      @37
      text kccfhdbn[6]                      @38
      text kccfhdbn[7]                      @39
      ub4 kccfhcsq                          @40       0x00000245
      ub4 kccfhfsz                          @44       0x00000a00
      s_blkz kccfhbsz                       @48       0x00
      ub2 kccfhfno                          @52       0x0009                   FILE NUM
      ub2 kccfhtyp                          @54       0x0003                   FILE TYPE
      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
   ub4 kcvfhrdb                             @96       0x00000000
   struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
      ub4 kscnbas                           @100      0x000a8849
      ub2 kscnwrp                           @104      0x0000
   ub4 kcvfhcrt                             @108      0x2cade6ff           
   ub4 kcvfhrlc                             @112      0x2cadd4e7               resetlogs count
   struct kcvfhrls, 8 bytes                 @116
      ub4 kscnbas                           @116      0x000a7f86              resetlogs scn
      ub2 kscnwrp                           @120      0x0000
   ub4 kcvfhbti                             @124      0x00000000
   struct kcvfhbsc, 8 bytes                 @128
      ub4 kscnbas                           @128      0x00000000
      ub2 kscnwrp                           @132      0x0000
   ub2 kcvfhbth                             @136      0x0000
   ub2 kcvfhsta                             @138      0x0000 (NONE)
   struct kcvfhckp, 36 bytes                @484
      struct kcvcpscn, 8 bytes              @484                                  Checkpoint scn
         ub4 kscnbas                        @484      0x000a88f7
         ub2 kscnwrp                        @488      0x0000
      ub4 kcvcptim                          @492      0x2cade744
      ub2 kcvcpthr                          @496      0x0001
      union u, 12 bytes                     @500
         struct kcvcprba, 12 bytes          @500
            ub4 kcrbaseq                    @500      0x00000001
            ub4 kcrbabno                    @504      0x00000c6f
            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
   ub4 kcvfhcpc                             @140      0x00000003
   ub4 kcvfhrts                             @144      0x00000000
   ub4 kcvfhccc                             @148      0x00000002
   struct kcvfhbcp, 36 bytes                @152
      struct kcvcpscn, 8 bytes              @152
         ub4 kscnbas                        @152      0x00000000
         ub2 kscnwrp                        @156      0x0000
      ub4 kcvcptim                          @160      0x00000000
      ub2 kcvcpthr                          @164      0x0000
      union u, 12 bytes                     @168
         struct kcvcprba, 12 bytes          @168
            ub4 kcrbaseq                    @168      0x00000000
            ub4 kcrbabno                    @172      0x00000000
            ub2 kcrbabof                    @176      0x0000
      ub1 kcvcpetb[0]                       @180      0x00
      ub1 kcvcpetb[1]                       @181      0x00
      ub1 kcvcpetb[2]                       @182      0x00
      ub1 kcvcpetb[3]                       @183      0x00
      ub1 kcvcpetb[4]                       @184      0x00
      ub1 kcvcpetb[5]                       @185      0x00
      ub1 kcvcpetb[6]                       @186      0x00
      ub1 kcvcpetb[7]                       @187      0x00
   ub4 kcvfhbhz                             @312      0x00000000
   struct kcvfhxcd, 16 bytes                @316
      ub4 space_kcvmxcd[0]                  @316      0x00000000
      ub4 space_kcvmxcd[1]                  @320      0x00000000
      ub4 space_kcvmxcd[2]                  @324      0x00000000
      ub4 space_kcvmxcd[3]                  @328      0x00000000
   word kcvfhtsn                            @332      10                         Tablespace#
   ub2 kcvfhtln                             @336      0x000f
   text kcvfhtnm[0]                         @338     W                          TABLESPACE_NAME
   text kcvfhtnm[1]                         @339     E
   text kcvfhtnm[2]                         @340     _
   text kcvfhtnm[3]                         @341     W
   text kcvfhtnm[4]                         @342     I
   text kcvfhtnm[5]                         @343     L
   text kcvfhtnm[6]                         @344     L
   text kcvfhtnm[7]                         @345     _
   text kcvfhtnm[8]                         @346     L
   text kcvfhtnm[9]                         @347     O
   text kcvfhtnm[10]                        @348     S
   text kcvfhtnm[11]                        @349     T
   text kcvfhtnm[12]                        @350     _
   text kcvfhtnm[13]                        @351     I
   text kcvfhtnm[14]                        @352     T
   text kcvfhtnm[15]                        @353
   text kcvfhtnm[16]                        @354
   text kcvfhtnm[17]                        @355
   text kcvfhtnm[18]                        @356
   text kcvfhtnm[19]                        @357
   text kcvfhtnm[20]                        @358
   text kcvfhtnm[21]                        @359
   text kcvfhtnm[22]                        @360
   text kcvfhtnm[23]                        @361
   text kcvfhtnm[24]                        @362
   text kcvfhtnm[25]                        @363
   text kcvfhtnm[26]                        @364
   text kcvfhtnm[27]                        @365
   text kcvfhtnm[28]                        @366
   text kcvfhtnm[29]                        @367
   ub4 kcvfhrfn                             @368      0x00000009
   struct kcvfhrfs, 8 bytes                 @372
      ub4 kscnbas                           @372      0x00000000
      ub2 kscnwrp                           @376      0x0000
   ub4 kcvfhrft                             @380      0x00000000
   struct kcvfhafs, 8 bytes                 @384
      ub4 kscnbas                           @384      0x00000000
      ub2 kscnwrp                           @388      0x0000
   ub4 kcvfhbbc                             @392      0x00000000
   ub4 kcvfhncb                             @396      0x00000000
   ub4 kcvfhmcb                             @400      0x00000000
   ub4 kcvfhlcb                             @404      0x00000000
   ub4 kcvfhbcs                             @408      0x00000000
   ub2 kcvfhofb                             @412      0x0000
   ub2 kcvfhnfb                             @414      0x0000
   ub4 kcvfhprc                             @416      0x2cadcee3     prev reset logs count
   struct kcvfhprs, 8 bytes                 @420                        prev reset scn
      ub4 kscnbas                           @420      0x000a2af7
      ub2 kscnwrp                           @424      0x0000
   struct kcvfhprfs, 8 bytes                @428
      ub4 kscnbas                           @428      0x00000000
      ub2 kscnwrp                           @432      0x0000
   ub4 kcvfhtrt                             @444      0x00000000

/* 以上kcvfh结构是数据文件头的主要信息,其中ub4 kcvfhrlc(offset 112)记录了resetlogs count,
    而 struct kcvfhrls 8 bytes(offset 116)记录了resetlogs scn */

/* 接下来将问题数据文件头上的kcvfhrlc和kcvfhrls信息修改成和1号文件中的一样,
    以欺骗Oracle让其以为该数据文件参与了上一次的RESETLOGS                 */

BBED> set offset 112
        OFFSET          112

/* 在使用bbed modify的时候需要注意所在平台的endian,Linux上使用Little Endian */

BBED> modify /x 87e8
 File: /flashcard/wwli.dbf (0)
 Block: 1                Offsets:  112 to  623           Dba:0x00000000
------------------------------------------------------------------------
 87e8ad2c 867f0a00 00000000 00000000 00000000 00000000 00000000 03000000
 00000000 02000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000000
 0f005745 5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000
 09000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c
 01000160 01000000 6f0c0000 10000000 02000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2cade887

BBED> set offset 116
        OFFSET          116

BBED> modify /x 0xf988
 File: /flashcard/wwli.dbf (0)
 Block: 1                Offsets:  116 to  627           Dba:0x00000000
------------------------------------------------------------------------
 f9880a00 00000000 00000000 00000000 00000000 00000000 03000000 00000000
 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 0a000000 0f005745
 5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000 09000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c 01000160
 01000000 6f0c0000 10000000 02000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

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

/* 还需要将kcvfhckp结构中的kcvcpscn也修改成与1号文件一样的resetlogs scn ,
    否则在recover数据文件的时候可能出现
    ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []错误  */

BBED> set offset 484
        OFFSET          484

BBED> modify /x 0xf9
 File: /flashcard/wwli.dbf (0)
 Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
 f9880a00 00000000 44e7ad2c 01000160 01000000 6f0c0000 10000000 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 0d000d00 0d000100 00000000 00000000 00000000 02004002 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x000a88f9
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2cade744
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x00000001
         ub4 kcrbabno                       @504      0x00000c6f
         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> sum
Check value for File 0, Block 1:
current = 0xb897, required = 0xb899

/* 使用sum apply命令修改该数据块的checksum值  */

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

SQL>  alter database datafile '/flashcard/wwli.dbf' online;
 alter database datafile '/flashcard/wwli.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/flashcard/wwli.dbf'

/* 完美地recover datafile 9,因为该数据文件中的数据本身就是"干净"的,
    仅仅是datafile header中的resetlogs信息与其他datafile不一致而已  */

SQL> recover datafile 9;
Media recovery complete.

SQL> select * from v$recover_file where file#=9;
no rows selected

SQL> select count(*) from lost_data ;

  COUNT(*)
----------
     51791

/* That's great!

虽然我们也可以利用adjust_scn和设置隐藏参数”_allow_resetlogs_corruption”来尝试解决ORA-01189错误,但使用bbed可以避免用到”_allow_resetlogs_corruption”这个危险参数,且在之后更完美地recover恢复数据文件。

需要注意的是使用bbed修复问题数据文件并不能保证必然都成功,在使用bbed之前必须执行必要的备份,并在专业人士的指导下操作!

Comments

  1. renny says

    请问数据文件中的数据本身就是”干净”的,这个干净的是指什么意思?
    我曾做数据文件移植发生了:internal error code, arguments这个错误。

  2. 这里的干净首先指该数据文件物理和逻辑上都没有任何corrupted,但因为数据库发生了resetlogs操作,所以即便该数据文件是”干净的”,我们也无法在已经resetlogs的DB中再加入该数据文件。这时源于controlfile和datafile header中的resetlogs scn不一致了。

  3. renny says

    谢谢解答!
    你这个例子所设置的场景发生概率很低,而且重建controlfile一般都会非常谨慎,而我认为这个解决方案更适用于下面这个应用场景(实际上是我遇到的一个需求):
    所有的应用数据严格按日期分区,但由于生产上磁盘扩充限制,现要以一种类似备机(和容灾有区别)的机制做一个相同的实例以保留历史数据,业务需要历史数据的时候可以直接从这个实例获取。显然,这个库需要定期地做数据同步(比如半年一次),这样生产上的库也可以定期得到清理。
    我用这个方法修改SCN号确实成功了,600 error我也解决了,可以查询出新的数据,没有任何问题,请问这是否有什么隐患?
    最后感谢你提供的这个案例。

    • renny ,

      你的用法也不错,但危险系数太高了,有种每天走钢丝的感觉………..
      因为我写这个例子的目的是为了把数据找回,所以只要能读取,将之转移就OK!
      隐患是BBED使用不便,虽为利器,难免自伤。

  4. wailon.lu says

    我的环境是开启归档的,是不是在恢复文件前不能open resetlogs,否则就会遇到ORA-01113: file 6 needs media recovery if it was restored from backup, or END BACKUP if it was not。求解

    • wailon.lu says

      SQL> alter database datafile 6 online;alter database datafile 6 online*ERROR at line 1:ORA-01113: file 6 needs media recovery if it was restored from backup, or END BACKUP if it was notORA-01110: data file 6: ‘/u01/lost_it.dbf’SQL> recover datafile 6;Media recovery complete.再测试一次,又正常了。

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号