bbed恢复ORA-01190

我们在做recover的时候，如果没有注意存在offline的数据文件，然后就resetlogs打开了数据库，当你再想online这个数据文件的时候，就会报ORA-01190错误

我们模拟一下这种环境：
SQL> col name for a55
SQL> set lines 120
SQL> select file#,status,name from v$datafile;
     FILE# STATUS  NAME
---------- ------- -------------------------------------------------------
         1 SYSTEM  /opt/app/oracle/oradata/goolen/system01.dbf
         2 ONLINE  /opt/app/oracle/oradata/goolen/sysaux01.dbf
         3 ONLINE  /opt/app/oracle/oradata/goolen/undotbs01.dbf
         4 ONLINE  /opt/app/oracle/oradata/goolen/users01.dbf
--首先offline 4号文件
SQL> alter database datafile 4 offline;
Database altered.
--做一个全库备份
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 24 14:10:23 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: GOOLEN (DBID=863382018)
RMAN> backup database;
Starting backup at 24-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/app/oracle/oradata/goolen/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/goolen/sysaux01.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/goolen/users01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/goolen/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 24-OCT-13
channel ORA_DISK_1: finished piece 1 at 24-OCT-13
piece handle=/opt/app/oracle/product/11.2.0/dbs/01on7al5_1_1 tag=TAG20131024T141149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-OCT-13
channel ORA_DISK_1: finished piece 1 at 24-OCT-13
piece handle=/opt/app/oracle/product/11.2.0/dbs/02on7amj_1_1 tag=TAG20131024T141149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-OCT-13
RMAN> exit
Recovery Manager complete.
--关闭数据库，删除数据文件和redo文件，归档文件
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ rm -rf /opt/app/oracle/oradata/goolen/system01.dbf
[oracle@localhost ~]$ rm -rf /opt/app/oracle/oradata/goolen/sysaux01.dbf 
[oracle@localhost ~]$ rm -rf /opt/app/oracle/oradata/goolen/users01.dbf 
[oracle@localhost ~]$ rm -rf /opt/app/oracle/oradata/goolen/undotbs01.dbf 
[oracle@localhost arch]$ rm -rf /opt/app/oracle/oradata/goolen/redo01.log 
[oracle@localhost arch]$ rm -rf /opt/app/oracle/oradata/goolen/redo02.log  
[oracle@localhost arch]$ rm -rf /opt/app/oracle/oradata/goolen/redo03.log 
--数据库启动到mount
SQL> startup mount
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2214936 bytes
Variable Size            1207960552 bytes
Database Buffers          872415232 bytes
Redo Buffers                5189632 bytes
Database mounted.
SQL> 
--还原数据库
RMAN> restore database;
Starting restore at 24-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/goolen/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/goolen/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/goolen/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/goolen/users01.dbf
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/product/11.2.0/dbs/01on7al5_1_1
channel ORA_DISK_1: piece handle=/opt/app/oracle/product/11.2.0/dbs/01on7al5_1_1 tag=TAG20131024T141149
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 24-OCT-13
RMAN> exit
--数据库需要recover，但是由于缺失归档和redo，需要resetlog打开库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/app/oracle/oradata/goolen/system01.dbf'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/goolen/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 1738293 generated at 10/24/2013 14:11:49 needed for thread 1
ORA-00289: suggestion : /opt/arch/1_64_828544261.dbf
ORA-00280: change 1738293 for thread 1 is in sequence #64
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,change# from v$recover_file;
     FILE# ONLINE_    CHANGE#
---------- ------- ----------
         4 OFFLINE    1738174
--online的时候提示4号文件
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1738297
         2            1738297
         3            1738297
         4            1738174      1738297
SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
         1            1738297           1738294
         2            1738297           1738294
         3            1738297           1738294
         4            1738174            945184
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--使用bbed来修改文件头信息
有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作
kscnbas (at offset 484) - SCN of last change to the datafile. 
kcvcptim (at offset 492) -Time of the last change to the datafile. 
kcvfhcpc (at offset 140) - Checkpoint count. 
kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count. 
还有2个跟resetlog相关的属性：
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
[oracle@localhost bbed]$ bbed parfile=bbed.par 
Password: 
BBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 24 14:40:13 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /opt/app/oracle/oradata/goolen/system01.dbf                      87040
     2  /opt/app/oracle/oradata/goolen/sysaux01.dbf                      71680
     3  /opt/app/oracle/oradata/goolen/undotbs01.dbf                      3840
     4  /opt/app/oracle/oradata/goolen/users01.dbf                        7040
BBED> p kcvfhrlc dba 1,1
ub4 kcvfhrlc                                @112      0x3173af1a
BBED> d /v dba 1,1 offset 112 count 23
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  112 to  134  Dba:0x00400001
-------------------------------------------------------
 1aaf7331 36861a00 00000000 00000000 l .ˉs16...........
 00000000 000000                     l .......
 <16 bytes per line>
BBED> p kcvfhrlc dba 4,1   
ub4 kcvfhrlc                                @112      0x31629505
BBED> m /x 1aaf7331 dba 4,1 offset 112
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  112 to  134           Dba:0x01000001
------------------------------------------------------------------------
 1aaf7331 206c0e00 00000000 00000000 00000000 000000 
 <32 bytes per line>
BBED> p kcvfhrls dba 1,1
struct kcvfhrls, 8 bytes                    @116     
   ub4 kscnbas                              @116      0x001a8636
   ub2 kscnwrp                              @120      0x0000
BBED> d /v dba 1,1 offset 116
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  116 to  138  Dba:0x00400001
-------------------------------------------------------
 36861a00 00000000 00000000 00000000 l 6...............
 00000000 000000                     l .......
 <16 bytes per line>
BBED> m /x 36861a00 dba 4,1 offset 116
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  116 to  138           Dba:0x01000001
------------------------------------------------------------------------
 36861a00 00000000 00000000 00000000 00000000 000000 
 <32 bytes per line>
BBED> p kcvfhckp dba 1,1
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x001a8886
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x3173b0aa
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000001
         ub4 kcrbabno                       @504      0x000001d0
         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> p kcvfhcpc dba 1,1
ub4 kcvfhcpc                                @140      0x000000b7
BBED> p kcvfhccc dba 1,1
ub4 kcvfhccc                                @148      0x000000b6
BBED> d /v dba 1,1 offset 484
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  484 to  506  Dba:0x00400001
-------------------------------------------------------
 86881a00 00000000 aab07331 01000000 l ........a°s1....
 01000000 d00100                     l ....Ю.
 <16 bytes per line>
BBED> d /v dba 1,1 offset 492
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  492 to  514  Dba:0x00400001
-------------------------------------------------------
 aab07331 01000000 01000000 d0010000 l a°s1........Ю..
 10004ddc 020000                     l ..M?..
 <16 bytes per line>
BBED> d /v dba 1,1 offset 140
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  140 to  162  Dba:0x00400001
-------------------------------------------------------
 b7000000 05af7331 b6000000 00000000 l ·....ˉs1?.......
 00000000 000000                     l .......
 <16 bytes per line>
BBED> d /v dba 1,1 offset 148
 File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
 Block: 1       Offsets:  148 to  170  Dba:0x00400001
-------------------------------------------------------
 b6000000 00000000 00000000 00000000 l ?...............
 00000000 000000                     l .......
 <16 bytes per line>
BBED> m /x 86881a00 dba 4,1 offset 484
BBED-00209: invalid number (86881a00)
BBED> m /x 8688 dba 4,1 offset 484
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  484 to  506           Dba:0x01000001
------------------------------------------------------------------------
 86881a00 00000000 8ca97331 01000000 40000000 020000 
 <32 bytes per line>
BBED> m /x aab07331 dba 4,1 offset 492
BBED-00209: invalid number (aab07331)
BBED> m /x aab0 dba 4,1 offset 492
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  492 to  514           Dba:0x01000001
------------------------------------------------------------------------
 aab07331 01000000 40000000 02000000 10000000 020000 
 <32 bytes per line>
BBED> m /x b700 dba 4,1 offset 140
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  140 to  162           Dba:0x01000001
------------------------------------------------------------------------
 b7000000 c7ab7331 b1000000 00000000 00000000 000000 
 <32 bytes per line>
BBED> m /x b600 dba 4,1 offset 148
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 1                Offsets:  148 to  170           Dba:0x01000001
------------------------------------------------------------------------
 b6000000 00000000 00000000 00000000 00000000 000000 
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 1:
current = 0x658a, required = 0x658a

BBED> 

SQL> startup
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2214936 bytes
Variable Size            1207960552 bytes
Database Buffers          872415232 bytes
Redo Buffers                5189632 bytes
Database mounted.
Database opened.
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1738889
         2            1738889
         3            1738889
         4            1738174      1738297
SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
         1            1738889           1738294
         2            1738889           1738294
         3            1738889           1738294
         4            1738886           1738294
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
SQL> recover database datafile 4 ;
ORA-00274: illegal recovery option DATAFILE
SQL> recover datafile 4 ;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL>