在上篇中，我们进行了环境准备。（http://blog.itpub.net/17203031/viewspace-2126665/）下面就可以进行问题修复动作。
3、故障修复
总体修复的思路是：使用BBED，将文件头的SCN等关键信息修改到与控制文件control file相匹配即可。
当前，控制文件各个文件SCN相同，而数据文件上SCN不同。
--控制文件上信息
SQL> select file#, CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1714543
2 1714543
3 1714543
4 1714543
5 1714543
6 1714543
7 1714543
7 rows selected
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1714543
--数据文件头信息
SQL> select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# RECOVER FUZZY CHECKPOINT_CHANGE#
---------- ------- ----- ------------------
1 NO NO 1714543
2 NO NO 1714543
3 NO NO 1714543
4 NO NO 1714543
5 NO NO 1714543
6 NO NO 1714543
7 YES YES 1713752
7 rows selected
一种最简单的策略，是将File 7对应的SCN修改为其他文件相同。首先，我们可以先使用BBED看一下那些正确文件的内容是什么。
[oracle@TESTlife datafile]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Oct 18 22:33:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf' –一号文件
FILENAME /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf
BBED> set block 1
BLOCK# 1
BBED> map
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf (0)
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
针对这个案例，我们通常需要关注四个偏移量offset点，分别为484、492、140和148。
BBED> p kcvfh
struct kcvfh, 860 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
（篇幅原因，有省略……）
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x2000 (NONE)
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001a296f --SCN：1714543与文件一致
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x372b7d00 --最后一次Check Point Time
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000043
ub4 kcrbabno @504 0x0000005b
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub4 kcvfhcpc @140 0x000000c8 --Check Point Count
ub4 kcvfhrts @144 0x372b5d94
ub4 kcvfhccc @148 0x000000c7 --比检查点计数少1
struct kcvfhbcp, 36 bytes @152
struct kcvcpscn, 8 bytes @152
ub4 kscnbas @152 0x00000000
ub2 kscnwrp @156 0x0000
ub4 kcvcptim @160 0x00000000
（篇幅原因，有省略……）
其中，位于484和488偏移量的是数据文件对应的SCN编号。在Oracle内部，SCN是使用wrap*4*1024*1024*1024+base来进行标示的。通常我们看到的数据库wrap都是0。位于492偏移量的是最后一次检查点对应的时间信息。位于140和148偏移量的是检查点次数。这些信息都是会由于时间推动和检查点动作引起变化，我们严格情况下，需要保证文件头块的信息和控制文件信息一致。
另外一点，由于Linux是Little字节系统，要关注写入时候的格式问题。最简单的方式是dump一下偏移量，看看是怎么保存的。
BBED> set offset 484
OFFSET 484
（0x001a296f）
BBED> dump
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
------------------------------------------------------------------------
6f291a00 00000000 007d2b37 01000000 43000000 5b000000 10009e33 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> set offset 492
OFFSET 492
（0x372b7d00）
BBED> dump
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf (0)
Block: 1 Offsets: 492 to 1003 Dba:0x00000000
------------------------------------------------------------------------
007d2b37 01000000 43000000 5b000000 10009e33 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
BBED> set offset 140
OFFSET 140
（0x000000c8）
BBED> dump
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf (0)
Block: 1 Offsets: 140 to 651 Dba:0x00000000
------------------------------------------------------------------------
c8000000 945d2b37 c7000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> set offset 144
OFFSET 144
（0x000000c7)
BBED> dump
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_system_bw773xok_.dbf (0)
Block: 1 Offsets: 144 to 655 Dba:0x00000000
------------------------------------------------------------------------
945d2b37 c7000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
实施修改文件块动作：
BBED> set filename '/u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf'
FILENAME /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf
BBED> set block 1
BLOCK# 1
BBED> set mode edit
MODE Edit
修改对应文件块的位数。
BBED> m /x 6f291a00 offset 484
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
------------------------------------------------------------------------
6f291a00 00000000 79792b37 01005e7d 3c000000 02000000 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
BBED> m /x 007d2b37 offset 492
File: /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf (0)
Block: 1 Offsets: 492 to 1003 Dba:0x00000000
------------------------------------------------------------------------
007d2b37 01005e7d 3c000000 02000000 10000000 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
注意：Oracle数据块中使用冗余校验功能，修改数据块之后，要使用sum apply重新计算校验位。
BBED> sum apply
Check value for File 0, Block 1:
current = 0xc606, required = 0xc606
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
此时在mount状态的数据库中看一下，可以发现文件SCN已经发生变化。
SQL> select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# RECOVER FUZZY CHECKPOINT_CHANGE#
---------- ------- ----- ------------------
1 NO NO 1714543
2 NO NO 1714543
3 NO NO 1714543
4 NO NO 1714543
5 NO NO 1714543
6 NO NO 1714543
7 YES YES 1714543
7 rows selected
启动数据库，尝试open。
[oracle@TESTlife datafile]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 18 23:02:18 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf'
使用recover命令进行还原，此时需要的online redo log就可以支持了。
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database open;
Database altered
启动过程的alert log信息：
Tue Oct 18 23:02:35 2016
alter database open
Errors in file /u01/app/oracle/diag/rdbms/TESTdb/TESTdb/trace/TESTdb_ora_16545.trc:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/TESTDB/datafile/o1_mf_epssite_by19vtnh_.dbf'
ORA-1113 signalled during: alter database open...
Tue Oct 18 23:02:49 2016
ALTER DATABASE RECOVER datafile 7
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 7 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Media Recovery Complete (TESTdb)
Completed: ALTER DATABASE RECOVER datafile 7
alter database open
Oracle在recover的时候发现有一些问题，但是还是让通过了。此时，数据整体正常。
SQL> select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# RECOVER FUZZY CHECKPOINT_CHANGE#
---------- ------- ----- ------------------
（篇幅原因，有省略……）
5 NO YES 1714546
6 NO YES 1714546
7 NO YES 1714546
7 rows selected
4、结论
Oracle启动Open是一个极其复杂的过程。单独通过open过程，我们就可以学习到很多的知识和技能。在解决故障的时候，应用多种途径，找到最适当的策略，是我们需要掌握的工作手段。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-2126807/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-2126807/
扫一扫
2689
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
