oracle数据库offline,案例:Oracle数据库启动报错ORA-01190 数据文件offline无法online

天萃荷净

Oracle数据库无法启动 文件无法online 通过Oracle bbed工具解决ORA-01190

当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。

一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline

2.rman备份数据库

3.关闭原数据库，删除数据文件/当前日志和部分归档日志

4.执行不完全恢复，resetlogs打开数据库(如下面操作)

[oracle@xifenfei ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database until cancel;

ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf

ORA-00280: change 868870 for thread 1 is in sequence #29

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999

---------- -------------- --------------------------

5 OFFLINE 868810

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/oracle/oradata/ora11g/xifenfei01.dbf'

SQL> select file#,to_char(checkpoint_change#,'999999999999'),

2 to_char(last_change#,'999999999999') from v$datafile;

FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999

---------- -------------------------- --------------------------

1 868874

2 868874

3 868874

4 868874

5 868810 868874

--可以看到offline的数据文件，没有因为resetlogs操作而改变

--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息

SQL> select file#,to_char(checkpoint_change#,'999999999999'),

2 to_char(RESETLOGS_CHANGE#,'999999999999')

3 from v$datafile_header;

FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,

---------- -------------------------- --------------------------

1 868874 868871

2 868874 868871

3 868874 868871

4 868874 868871

5 868810 787897

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

二、bbed修改相关项

下面两项与resetlogs相关

kcvfhrlc表示 reset logs count

kcvfhrls表示 resetlogs scn

下面四项与数据库文件scn相关

kscnbas (at offset 140) – SCN of last change to the datafile.

kcvcptim (at offset 148) - Time of the last change to the datafile.

kcvfhcpc (at offset 176) – Checkpoint count.

kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.

BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'

FILENAME /u01/oracle/oradata/ora11g/system01.dbf

BBED> p kcvfhrlc

ub4 kcvfhrlc @112 0x2e5eed37

BBED> p kcvfhrls

struct kcvfhrls, 8 bytes @116

ub4 kscnbas @116 0x000d4207

ub2 kscnwrp @120 0x0000

BBED> p kcvcpscn

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x000d4495

ub2 kscnwrp @488 0x0000

BBED> p kcvfhcpc

ub4 kcvfhcpc @140 0x00000078

BBED> p kcvfhccc

ub4 kcvfhccc @148 0x00000077

BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'

FILENAME /u01/oracle/oradata/ora11g/xifenfei01.dbf

BBED> p kcvfhrlc

ub4 kcvfhrlc @112 0x2e5bc6e5

BBED> set mode edit

MODE Edit

BBED> m /x 37ed5e2e

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

BBED> p kcvfhrlc

ub4 kcvfhrlc @112 0x2e5eed37

BBED> p kcvfhrls

struct kcvfhrls, 8 bytes @116

ub4 kscnbas @116 0x000c05b9

ub2 kscnwrp @120 0x0000

BBED> m /x 07420d00

BBED> p kcvfhrls

struct kcvfhrls, 8 bytes @116

ub4 kscnbas @116 0x000d4207

ub2 kscnwrp @120 0x0000

BBED> p kcvcpscn

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x000d41ca

ub2 kscnwrp @488 0x0000

BBED> set offset 484

OFFSET 484

BBED> m /x 95440d00

BBED-00209: invalid number (95440d00)

BBED> m /x 9544

BBED> set offset +2

OFFSET 486

BBED> m /x 0d00

BBED> p kcvfhcpc

ub4 kcvfhcpc @140 0x00000003

BBED> m /x 78000000

BBED> p kcvfhcpc

ub4 kcvfhcpc @140 0x00000078

BBED> p kcvfhccc

ub4 kcvfhccc @148 0x00000002

BBED> m /x 77000000

BBED> p kcvfhccc

ub4 kcvfhccc @148 0x00000077

BBED> sum

Check value for File 0, Block 1:

current = 0xe079, required = 0x5940

BBED> sum apply

Check value for File 0, Block 1:

current = 0x5940, required = 0x5940

1

三、数据文件online

[oracle@xifenfei ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 368263168 bytes

Fixed Size 1345016 bytes

Variable Size 301992456 bytes

Database Buffers 58720256 bytes

Redo Buffers 6205440 bytes

Database mounted.

Database opened.

SQL> select file#,to_char(checkpoint_change#,'999999999999'),

2 to_char(RESETLOGS_CHANGE#,'999999999999')

3 from v$datafile_header;

FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,

---------- -------------------------- --------------------------

1 869528 868871

2 869528 868871

3 869528 868871

4 869528 868871

5 869525 868871

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle数据库启动报错ORA-01190 数据文件offline无法online