通過bbed修復ora-01190錯誤

1、配置BBET

Oracle11g中缺省不提供BBET庫文件，如果需要可以將10g中的文件copy到11g相應目錄再執(zhí)行安裝：

$ORACLE_HOME/rdbms/lib/ssbbded.o 

$ORACLE_HOME/rdbms/lib/sbbdpt.o 

$ORACLE_HOME/rdbms/mesg/bbedus.msb

 在第一次使用時會發(fā)現(xiàn)有默認的口令，從這里可以看出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: {<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打開數(shù)據(jù)庫，則13號文件會丟失。所以我們用如下命令增加關鍵字for drop 意思就是告訴數(shù)據(jù)庫，這個數(shù)據(jù)文件我后面可能會丟棄，不會在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值不難發(fā)現(xiàn)觸發(fā)ora-01190的原因：即數(shù)據(jù)文件頭部的 resetlogs scn 、resetlogs count 和控制文件中的resetlogs信息不匹配造成的。所以，如果要規(guī)避ora-01190錯誤，我們可以通過bbed修改數(shù)據(jù)文件頭部resetlogs相關值

3、通過bbed修改數(shù)據(jù)文件頭部規(guī)避此錯誤

1  resetlogs count 和resetlogs scn 在數(shù)據(jù)文件頭部的位置

resetlogs count 位于數(shù)據(jù)文件頭部偏移量112處

resetlogs scn 位于數(shù)據(jù)文件頭部偏移量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調(diào)整了數(shù)據(jù)文件頭部的restlogs scn 和resetlogs count 使之和控制文件保持一樣，不過我們online 數(shù)據(jù)文件時會接著報需要介質(zhì)恢復。如下：

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調(diào)整數(shù)據(jù)文件頭部檢查點以及scn相關值

我們還應改如下偏移量

ub4 kcvfhcpc @140 0x00000308------檢查點計數(shù)

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、檢查數(shù)據(jù)及狀態(tài)

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