联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
当我们使用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: {<RET>=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.
加我QQ
你需要指定到特定的block上面才能够使用p(print)，有些block是bbed不支持的
为什么我下载的bbed工具，使用 p 命令时，报“bbed-00400: ivalid blocktype (00)”错误。
BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
你好，请问这里的 37ed5e2e表示什么意思？如何得到该数值?
跟踪datafile header