通过bbed修复ora-01190错误
1、配置BBET
Oracle11g中缺省不提供BBET库文件，如果需要可以将10g中的文件copy到11g相应目录再执行安装：
$ORACLE_HOME/rdbms/lib/ssbbded.o
$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb
在第一次使用时会发现有默认的口令，从这里可以看出oracle对bbed工具的限制，默认的密码是blockedit
SQL> col name for a50
SQL> select file#||' '||name||' '||bytes from v$datafile;
FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/satdb/system01.dbf 786432000
2 /u01/app/oracle/oradata/satdb/sysaux01.dbf 618659840
3 /u01/app/oracle/oradata/satdb/undotbs01.dbf 94371840
4 /u01/app/oracle/oradata/satdb/users01.dbf 5242880
5 /u01/app/oracle/oradata/satdb/data01.dbf 104857600
6 /u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000
[oracle@orcl ~]$ vi dbfiles.txt
1 /u01/app/oracle/oradata/satdb/system01.dbf 786432000
2 /u01/app/oracle/oradata/satdb/sysaux01.dbf 618659840
3 /u01/app/oracle/oradata/satdb/undotbs01.dbf 94371840
4 /u01/app/oracle/oradata/satdb/users01.dbf 5242880
5 /u01/app/oracle/oradata/satdb/data01.dbf 104857600
6 /u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000
[oracle@orcl ~]$ cat parameter.txt
blocksize=8192
listfile=dbfiles.txt
mode=edit
2、模拟错误
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- --------------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
6 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 ONLINE
6 rows selected.
SQL> select hxfil,fhrba_seq from x$kcvfh;
HXFIL FHRBA_SEQ
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
6 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 CURRENT
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 1268630 generated at 10/01/2015 11:36:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch3/1_1_891948516.dbf
ORA-00280: change 1268630 for thread 1 is in sequence #1
Specify log: {
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/satdb/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-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 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database 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 hh34:mi:ss') time from v$database;
RESETLOGS_CHANGE# TIME
----------------- --------------------------------------
1268631 10/01/2015 11:46:40
SQL> col fhrlc for a50
SQL> 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:40
6 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 RESETLOGS
ORA-01110: data file 5: '/u01/app/oracle/oradata/satdb/data01.dbf'
[oracle@orcl ~]$ bbed parfile=parameter.txt password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 1 11:52:29 2015
Copyright (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 30
BBED-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 2012
Warning: 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 apply
Check value for File 5, Block 1:
current = 0xefbf, required = 0xefbf
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 1268631 891949600 10/01/2015 11:46:40
6 1268631 891949600 10/01/2015 11:46:40
6 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 recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/satdb/data01.dbf'
4、用bbed调整数据文件头部检查点以及scn相关值
我们还应改如下偏移量
ub4 kcvfhcpc @140 0x00000308------检查点计数
ub4 kcvfhccc @148 0x00000307------总是比检查点计算少1
ub4 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 cf
Warning: 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
File: /u01/app/oracle/oradata/satdb/data01.dbf (5)
Block: 1 Offsets: 492 to 521 Dba:0x01400001
-------------------------------------------------------
d70f2a35 01000000 01000000 2b020000 l ..*5........+...
100085a6 02000000 00000000 0000 l ..............
<16 bytes per line>
BBED> modify /x 2312
File: /u01/app/oracle/oradata/satdb/data01.dbf (5)
Block: 1 Offsets: 492 to 521 Dba:0x01400001
------------------------------------------------------------------------
23122a35 01000000 01000000 2b020000 100085a6 02000000 00000000 0000
<32 bytes per line>
BBED> dump /v dba 1,1 offset 484 count 30
File: /u01/app/oracle/oradata/satdb/system01.dbf (1)
Block: 1 Offsets: 484 to 513 Dba:0x00400001
-------------------------------------------------------
9a5b1300 00000000 23122a35 01000000 l .[......#.*5....
01000000 02000000 10000000 0200 l ..............
<16 bytes per line>
BBED> dump /v dba 5,1 offset 484 count 30
File: /u01/app/oracle/oradata/satdb/data01.dbf (5)
Block: 1 Offsets: 484 to 513 Dba:0x01400001
-------------------------------------------------------
975b1300 00000000 23122a35 01000000 l .[......#.*5....
01000000 2b020000 100085a6 0200 l ....+.........
<16 bytes per line>
BBED> modify /x 9a5b
File: /u01/app/oracle/oradata/satdb/data01.dbf (5)
Block: 1 Offsets: 484 to 513 Dba:0x01400001
------------------------------------------------------------------------
9a5b1300 00000000 23122a35 01000000 01000000 2b020000 100085a6 0200
<32 bytes per line>
BBED> dump /v dba 1,1 offset 488 count 30
File: /u01/app/oracle/oradata/satdb/system01.dbf (1)
Block: 1 Offsets: 488 to 517 Dba:0x00400001
-------------------------------------------------------
00000000 23122a35 01000000 01000000 l ....#.*5........
02000000 10000000 02000000 0000 l ..............
<16 bytes per line>
BBED> dump /v dba 5,1 offset 488 count 30
File: /u01/app/oracle/oradata/satdb/data01.dbf (5)
Block: 1 Offsets: 488 to 517 Dba:0x01400001
-------------------------------------------------------
00000000 23122a35 01000000 01000000 l ....#.*5........
2b020000 100085a6 02000000 0000 l +.............
<16 bytes per line>
BBED> sum apply
Check value for File 5, Block 1:
current = 0xf246, required = 0xf246
BBED> exit
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/satdb/data01.dbf'
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
5、检查数据及状态
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- --------------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
6 rows selected.
SQL> conn lineqi/lineqi
Connected.
SQL> select table_name,tablespace_name from user_tables;
SQL> col tablespace_name for 50
SQL> set linesize 400
SQL> /
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------ --------------------------------------------------
EMP1 DATA
TEMP2 DATA
TEST DATA
SYS_FBA_DDL_COLMAP_87367 FDA_TBS1
SYS_FBA_TCRV_87367 FDA_TBS1
SYS_FBA_HIST_87367
SYS_TEMP_FBT
TTT DATA
8 rows selected.
SQL> select * from ttt;
ID NAME
---------- ----------------------------------------
1 aa
6、参考资料：
http://jiujian.blog.51cto.com/444665/1127404
http://blog.chinaunix.net/uid-20124596-id-1734425.html
