通过BBED修复ORA-01190错误

1、配置BBETOracle11g中缺省不提供BBET库文件，如果需要可以将10g中的文件copy到11g相应目录再执行安装：$ORACLE_HOME/rdbms/lib/ssbbded.o $ORACLE_HOME/rdbms/lib/sbbdpt.o $ORACLE_HOME/rdbms/mesg/bbedus.msb 在第一次使用时会发现有默认的口令，从这里可以看出oracle对bbed工具的限制，默认的密码是blockeditSQL> col name for a50SQL> select file#||" "||name||" "||bytes from v$datafile;FILE#||""||NAME||""||BYTES--------------------------------------------------------------------------------1 /u01/app/oracle/oradata/satdb/system01.dbf 7864320002 /u01/app/oracle/oradata/satdb/sysaux01.dbf 6186598403 /u01/app/oracle/oradata/satdb/undotbs01.dbf 943718404 /u01/app/oracle/oradata/satdb/users01.dbf 52428805 /u01/app/oracle/oradata/satdb/data01.dbf 1048576006 /u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000[oracle@orcl ~]$ vi dbfiles.txt 1 /u01/app/oracle/oradata/satdb/system01.dbf 7864320002 /u01/app/oracle/oradata/satdb/sysaux01.dbf 6186598403 /u01/app/oracle/oradata/satdb/undotbs01.dbf 943718404 /u01/app/oracle/oradata/satdb/users01.dbf 52428805 /u01/app/oracle/oradata/satdb/data01.dbf 1048576006 /u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000[oracle@orcl ~]$ cat parameter.txt blocksize=8192listfile=dbfiles.txtmode=edit2、模拟错误SQL> select file#,status from v$datafile; FILE# STATUS---------- -------------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE6 rows selected.SQL> alter database datafile 5 offline;Database altered.SQL> select file#,status from v$datafile; FILE# STATUS---------- -------------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 RECOVER 6 ONLINE6 rows selected.SQL> select hxfil,fhrba_seq from x$kcvfh; HXFIL FHRBA_SEQ---------- ---------- 1 1 2 1 3 1 4 1 5 1 6 16 rows selected.SQL> select group#,archived,sequence#,status from v$log; GROUP# ARCHIV SEQUENCE# STATUS---------- ------ ---------- -------------------------------- 1 YES 1 ACTIVE 2 YES 2 ACTIVE 3 NO 3 CURRENTSQL> startup force mount;ORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 989858976 bytesDatabase Buffers 603979776 bytesRedo Buffers 7319552 bytesDatabase mounted.SQL> recover database until cancel;ORA-00279: change 1268630 generated at 10/01/2015 11:36:55 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/arch2/1_1_891948516.dbfORA-00280: change 1268630 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: "/u01/app/oracle/oradata/satdb/system01.dbf"ORA-01112: media recovery not startedSQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: "/u01/app/oracle/oradata/satdb/system01.dbf"SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;System altered.SQL> startup force mount;ORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 989858976 bytesDatabase Buffers 603979776 bytesRedo Buffers 7319552 bytesDatabase mounted.注意：这里提示如果以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----------------- -------------------------------------- 1268631 10/01/2015 11:46:40SQL> col fhrlc for a50SQL> set linesize 400 SQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh; HXFIL CHANGE# FHRLC_I TIME---------- -------------------------------- ---------- ---------------------------------------- 1 1268631 891949600 10/01/2015 11:46:40 2 1268631 891949600 10/01/2015 11:46:40 3 1268631 891949600 10/01/2015 11:46:40 4 1268631 891949600 10/01/2015 11:46:40 5 1267919 891948516 10/01/2015 11:28:36 6 1268631 891949600 10/01/2015 11:46:406 rows selected.通过对比5号文件的resetlogs scn及resetlogs count值不难发现触发ora-01190的原因：即数据文件头部的 resetlogs scn 、resetlogs count 和控制文件中的resetlogs信息不匹配造成的。所以，如果要规避ora-01190错误，我们可以通过bbed修改数据文件头部resetlogs相关值3、通过bbed修改数据文件头部规避此错误1 resetlogs count 和resetlogs scn 在数据文件头部的位置resetlogs count 位于数据文件头部偏移量112处resetlogs scn 位于数据文件头部偏移量116处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 RESETLOGSORA-01110: data file 5: "/u01/app/oracle/oradata/satdb/data01.dbf"[oracle@orcl ~]$ bbed parfile=parameter.txt password=blockeditBBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 1 11:52:29 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* ！！！ For Oracle Internal Use only ！！！ ***************BBED> dump /v dba 1,1 offset 112 count 30 File: /u01/app/oracle/oradata/satdb/system01.dbf （1） Block: 1 Offsets: 112 to 141 Dba:0x00400001------------------------------------------------------- 20122a35 975b1300 00000000 00000000 l .*5.[.......... 00000000 00000000 00000420 cf00 l ........... .. <16 bytes per line>BBED> dump /v dba 5,1 offfset 112 count 30BBED-00202: invalid parameter （offfset）BBED> dump /v dba 5,1 offset 112 count 30 File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 112 to 141 Dba:0x01400001------------------------------------------------------- e40d2a35 cf581300 00000000 00000000 l ..*5.X.......... 00000000 00000000 00000400 7f00 l .............. <16 bytes per line>BBED> modify /x 2012Warning: contents of previous BIFILE will be lost. Proceed？（Y/N） y File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 112 to 141 Dba:0x01400001------------------------------------------------------------------------ 20122a35 cf581300 00000000 00000000 00000000 00000000 00000400 7f00 <32 bytes per line>BBED> dump /v dba 1,1 offset 116 count 30 File: /u01/app/oracle/oradata/satdb/system01.dbf （1） Block: 1 Offsets: 116 to 145 Dba:0x00400001------------------------------------------------------- 975b1300 00000000 00000000 00000000 l .[.............. 00000000 00000420 cf000000 8811 l ....... ...... <16 bytes per line>BBED> dump /v dba 5,1 offset 116 count 30 File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 116 to 145 Dba:0x01400001------------------------------------------------------- cf581300 00000000 00000000 00000000 l .X.............. 00000000 00000400 7f000000 780d l ............x. <16 bytes per line>BBED> modify /x 975b File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 116 to 145 Dba:0x01400001------------------------------------------------------------------------ 975b1300 00000000 00000000 00000000 00000000 00000400 7f000000 780d <32 bytes per line>BBED> sum applyCheck value for File 5, Block 1:current = 0xefbf, required = 0xefbfSQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh; HXFIL CHANGE# FHRLC_I TIME---------- -------------------------------- ---------- ---------------------------------------- 1 1268631 891949600 10/01/2015 11:46:40 2 1268631 891949600 10/01/2015 11:46:40 3 1268631 891949600 10/01/2015 11:46:40 4 1268631 891949600 10/01/2015 11:46:40 5 1268631 891949600 10/01/2015 11:46:40 6 1268631 891949600 10/01/2015 11:46:406 rows selected.注意下面，我们上面虽然用bbed调整了数据文件头部的restlogs scn 和resetlogs count 使之和控制文件保持一样，不过我们online 数据文件时会接着报需要介质恢复。如下：SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01113: file 5 needs media recoveryORA-01110: data file 5: "/u01/app/oracle/oradata/satdb/data01.dbf"4、用bbed调整数据文件头部检查点以及scn相关值我们还应改如下偏移量ub4 kcvfhcpc @140 0x00000308------检查点计数ub4 kcvfhccc @148 0x00000307------总是比检查点计算少1ub4 kcvcptim @492 0x2f9af923-----检查点时间ub4 kscnbas @484 0x8013ea80-------- scn的低位ub2 kscnwrp @488 0x0000--------- scn的高位BBED> dump /v dba 1,1 offset 140 count 30 File: /u01/app/oracle/oradata/satdb/system01.dbf （1） Block: 1 Offsets: 140 to 169 Dba:0x00400001------------------------------------------------------- cf000000 88112a35 ce000000 00000000 l ......*5........ 00000000 00000000 00000000 0000 l .............. <16 bytes per line>BBED> dump /v dba 5,1 offset 140 count 30 File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 140 to 169 Dba:0x01400001------------------------------------------------------- 7f000000 780d2a35 7e000000 00000000 l ....x.*5~....... 00000000 00000000 00000000 0000 l .............. <16 bytes per line>BBED> modify /x cfWarning: contents of previous BIFILE will be lost. Proceed？（Y/N） y File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 140 to 169 Dba:0x01400001------------------------------------------------------------------------ cf000000 780d2a35 7e000000 00000000 00000000 00000000 00000000 0000 <32 bytes per line>BBED> dump /v dba 1,1 offset 148 count 30 File: /u01/app/oracle/oradata/satdb/system01.dbf （1） Block: 1 Offsets: 148 to 177 Dba:0x00400001------------------------------------------------------- ce000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 0000 l .............. <16 bytes per line>BBED> dump /v dba 5,1 offset 148 count 30 File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 148 to 177 Dba:0x01400001------------------------------------------------------- 7e000000 00000000 00000000 00000000 l ~............... 00000000 00000000 00000000 0000 l .............. <16 bytes per line>BBED> modify /x ce File: /u01/app/oracle/oradata/satdb/data01.dbf （5） Block: 1 Offsets: 148 to 177 Dba:0x01400001------------------------------------------------------------------------ ce000000 00000000 00000000 00000000 00000000 00000000 00000000 0000 <32 bytes per line>BBED> dump /v dba 1,1 offset 492 count 30 File: /u01/app/oracle/oradata/satdb/system01.dbf （1） Block: 1 Offsets: 492 to 521 Dba:0x00400001------------------------------------------------------- 23122a35 01000000 01000000 02000000 l #.*5............ 10000000 02000000 00000000 0000 l .............. <16 bytes per line>BBED> dump /v dba 5,1 offset 492 count 30更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址