通过bbed修复ora-01190错误

jgfhrt

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/arch2/1_1_891948516.dbf
ORA-00280: change 1268630 for thread 1 is in sequence #1



Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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 hh24: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