通过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/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

************* !!! 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

通过BBED修复ORA-01190错误

相关推荐