bbed解决ORA-01190

当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline 2.rman备份数据库 3.关闭原数据库，删除数据文件/当前日志和部分归档日志 4.执行不完全恢复，resetlogs打开数据库(如下面操作) [oracle@xifenfei ora11g]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> recover database until cancel; ORA-00279: change 868870 generated at 03 /15/2012 03:32:11 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_29_777766629 .dbf ORA-00280: change 868870 for thread 1 is in sequence #29   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs;   Database altered.   SQL> select file #,online_status,to_char(change#,'999999999999') from v$recover_file;         FILE # ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999 ---------- -------------- --------------------------           5 OFFLINE               868810   SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01190: control file or data file 5 is from before the last RESETLOGS ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'   SQL> select file #,to_char(checkpoint_change#,'999999999999'),    2  to_char(last_change #,'999999999999') from v$datafile;         FILE # TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999 ---------- -------------------------- --------------------------           1        868874           2        868874           3        868874           4        868874           5        868810                     868874   --可以看到offline的数据文件，没有因为resetlogs操作而改变 --CHECKPOINT_CHANGE #和RESETLOGS_CHANGE#信息 SQL> select file #,to_char(checkpoint_change#,'999999999999'),    2  to_char(RESETLOGS_CHANGE #,'999999999999')    3  from v $datafile_header;         FILE # TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#, ---------- -------------------------- --------------------------           1        868874                     868871           2        868874                     868871           3        868874                     868871           4        868874                     868871           5        868810                     787897   SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

二、bbed修改相关项

下面两项与resetlogs相关 kcvfhrlc表示 reset logs count kcvfhrls表示 resetlogs scn 下面四项与数据库文件scn相关 kscnbas (at offset 140) – SCN of last change to the datafile. kcvcptim (at offset 148) - Time of the last change to the datafile. kcvfhcpc (at offset 176) – Checkpoint count. kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.   BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'          FILENAME        /u01/oracle/oradata/ora11g/system01 .dbf   BBED> p kcvfhrlc ub4 kcvfhrlc                                @112      0x2e5eed37   BBED> p kcvfhrls struct kcvfhrls, 8 bytes                    @116     ub4 kscnbas                              @116      0x000d4207     ub2 kscnwrp                              @120      0x0000   BBED>  p kcvcpscn struct kcvcpscn, 8 bytes                 @484     ub4 kscnbas                           @484      0x000d4495     ub2 kscnwrp                           @488      0x0000   BBED> p kcvfhcpc ub4 kcvfhcpc                                @140      0x00000078   BBED> p kcvfhccc ub4 kcvfhccc                                @148      0x00000077   BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'          FILENAME        /u01/oracle/oradata/ora11g/xifenfei01 .dbf   BBED> p kcvfhrlc ub4 kcvfhrlc                                @112      0x2e5bc6e5   BBED> set mode edit          MODE            Edit   BBED> m /x 37ed5e2e Warning: contents of previous BIFILE will be lost. Proceed? (Y /N ) y   BBED> p kcvfhrlc ub4 kcvfhrlc                                @112      0x2e5eed37   BBED> p kcvfhrls struct kcvfhrls, 8 bytes                    @116     ub4 kscnbas                              @116      0x000c05b9     ub2 kscnwrp                              @120      0x0000   BBED> m /x 07420d00   BBED> p kcvfhrls struct kcvfhrls, 8 bytes                    @116     ub4 kscnbas                              @116      0x000d4207     ub2 kscnwrp                              @120      0x0000   BBED>  p kcvcpscn struct kcvcpscn, 8 bytes                 @484     ub4 kscnbas                           @484      0x000d41ca     ub2 kscnwrp                           @488      0x0000   BBED> set offset 484          OFFSET          484   BBED> m /x 95440d00 BBED-00209: invalid number (95440d00)   BBED> m /x 9544   BBED> set offset +2          OFFSET          486   BBED> m /x 0d00   BBED> p kcvfhcpc ub4 kcvfhcpc                                @140      0x00000003   BBED> m /x 78000000   BBED> p kcvfhcpc ub4 kcvfhcpc                                @140      0x00000078   BBED> p kcvfhccc ub4 kcvfhccc                                @148      0x00000002   BBED> m /x 77000000   BBED> p kcvfhccc ub4 kcvfhccc                                @148      0x00000077   BBED> sum Check value for File 0, Block 1: current = 0xe079, required = 0x5940   BBED> sum apply Check value for File 0, Block 1: current = 0x5940, required = 0x5940 1

三、数据文件online

[oracle@xifenfei ora11g]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup ORACLE instance started.   Total System Global Area  368263168 bytes Fixed Size                  1345016 bytes Variable Size             301992456 bytes Database Buffers           58720256 bytes Redo Buffers                6205440 bytes Database mounted. Database opened. SQL> select file#,to_char(checkpoint_change#, '999999999999' ),    2  to_char(RESETLOGS_CHANGE#, '999999999999' )    3  from v$datafile_header;         FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#, ---------- -------------------------- --------------------------           1        869528                     868871           2        869528                     868871           3        869528                     868871           4        869528                     868871           5        869525                     868871   SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online;   Database altered.

转载： http://www.xifenfei.com/2666.html