今天来模拟一个非归档模式下恢复offline数据文件的场景，主要有2种情况： 一种是在线日志没有被覆盖，另一种是在线日志被覆盖。 第一种情况比较简单，数据库自身就能处理，而第二种情况稍显复杂，但也并不难，下面开始整个实验过程： 一、在线日志没有被覆盖
今天来模拟一个非归档模式下恢复offline数据文件的场景，主要有2种情况：一种是在线日志没有被覆盖，另一种是在线日志被覆盖。
第一种情况比较简单，数据库自身就能处理，而第二种情况稍显复杂，但也并不难，下面开始整个实验过程：
一、在线日志没有被覆盖的场景
--切换数据库到非归档模式
sql> archive log list
database log mode archive mode
automatic archival enabled
archive destination use_db_recovery_file_dest
oldest online log sequence 6
next log sequence to archive 8
current log sequence 8
sql> shutdown immediate
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount
oracle instance started.
total system global area 285212672 bytes
fixed size 1218992 bytes
variable size 125830736 bytes
database buffers 155189248 bytes
redo buffers 2973696 bytes
database mounted.
sql> alter database noarchivelog;
database altered.
sql> archive log list
database log mode no archive mode
automatic archival disabled
archive destination use_db_recovery_file_dest
oldest online log sequence 6
current log sequence 8
sql>
--创建测试环境(创建表空间，创建用户，创建测试表，插入数据)
sql> create tablespace zlm_test datafile '/u01/app/oracle/oradata/ora10g/zlm_test01.dbf' size 50m;
tablespace created.
sql> create user zlm1 identified by oracle;
user created.
sql> grant connect,resource to zlm1;
grant succeeded.
sql> alter user zlm1 default tablespace zlm_test;
user altered.
sql> conn zlm1/oracle
connected.
sql> create table offline_test(id int,name varchar2(10));
table created.
sql> insert into offline_test values(1,'aaron8219');
1 row created.
sql> commit;
commit complete.
--查看数据文件检查点scn
sql> set line 130
sql> col name for a45
sql> select file#,name,status,checkpoint_change# from v$datafile order by 1;
file# name status checkpoint_change#
---------- --------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ora10g/system01.dbfsystem 551520
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf online 551520
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbfonline 551520
4 /u01/app/oracle/oradata/ora10g/users01.dbfonline 551520
5 /u01/app/oracle/oradata/ora10g/example01.dbf online 551520
6 /u01/app/oracle/oradata/ora10g/zlm01.dbfonline 551520
7 /u01/app/oracle/oradata/ora10g/zlm02.dbfonline 551520
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbfonline 551753
此时8号数据文件的checkpoint scn是551753，比其他文件都biger，或者说newer
尽管刚才插入一行数据后已经commit过了，但db buffer cache并不一定会立即刷到磁盘文件，需要手动执行检查点
sql> alter system checkpoint;
system altered.
sql> select file#,name,status,checkpoint_change# from v$datafile order by 1;
file# name status checkpoint_change#
---------- --------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ora10g/system01.dbfsystem 552134
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf online 552134
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbfonline 552134
4 /u01/app/oracle/oradata/ora10g/users01.dbfonline 552134
5 /u01/app/oracle/oradata/ora10g/example01.dbf online 552134
6 /u01/app/oracle/oradata/ora10g/zlm01.dbfonline 552134
7 /u01/app/oracle/oradata/ora10g/zlm02.dbfonline 552134
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf online 552134
--查看数据文件头的检查点scn
sql> select file#,name,status,checkpoint_change# from v$datafile_header order by 1;
file# name status checkpoint_change#
---------- --------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ora10g/system01.dbfonline 552134
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf online 552134
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbfonline 552134
4 /u01/app/oracle/oradata/ora10g/users01.dbfonline 552134
5 /u01/app/oracle/oradata/ora10g/example01.dbf online 552134
6 /u01/app/oracle/oradata/ora10g/zlm01.dbfonline 552134
7 /u01/app/oracle/oradata/ora10g/zlm02.dbfonline 552134
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf online 552134
执行了检查点以后，脏数据刷到磁盘数据文件，数据库全部数据文件的检查点scn都会保持一致
--当前日志情况
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 yes inactive 551038 05-9? -14
2 1 8 52428800 1no current 551097 05-9? -14
3 1 6 52428800 1 yes inactive 550653 05-9? -14
--把8号数据文件offline
sql> alter database datafile 8 offline; --只有归档模式可以用
alter database datafile 8 offline
*
error at line 1:
ora-01145: offline immediate disallowed unless media recovery enabled
sql> alter database datafile 8 offline drop; --非归档要用offline drop，注意，不是真正的删除物理数据文件
database altered.
sql> alter database datafile 8 online; --offline drop以后，需要recover以后才能online
alter database datafile 8 online
*
error at line 1:
ora-01113: file 8 needs media recovery
ora-01110: data file 8: '/u01/app/oracle/oradata/ora10g/zlm_test01.dbf'
sql> recover datafile 8
media recovery complete.
sql> alter database datafile 8 online;
database altered.
由于此时在线日志并没有被覆盖，可以进行recover操作，然后对8号数据文件进行online二、在线日志被覆盖的场景
--把8号数据文件重新offline
sql> alter database datafile 8 offline drop;
database altered.
--查看在线日志当前状态
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 yes inactive 551038 05-9? -14
2 1 8 52428800 1no current 551097 05-9? -14
3 1 6 52428800 1 yes inactive 550653 05-9? -14
--切换3次日志，把当前日志内容覆盖
sql> alter system switch logfile;
system altered.
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 yes inactive 551038 05-9? -14
2 1 8 52428800 1 no active 551097 05-9? -14
3 1 9 52428800 1no current 552891 05-9? -14
sql> alter system switch logfile;
system altered.
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 10 52428800 1no current 552899 05-9? -14
2 1 8 52428800 1 no active 551097 05-9? -14
3 1 9 52428800 1 no active 552891 05-9? -14
sql> alter system switch logfile;
system altered.
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 10 52428800 1 no active 552899 05-9? -14
2 1 11 52428800 1no current 552911 05-9? -14
3 1 9 52428800 1 no active 552891 05-9? -14
当前日志的检查点scn从551097变为552911，也就是说，第3次切换日志以后，current日志被覆盖了
--对8号数据文件online
sql> alter database datafile 8 online;
alter database datafile 8 online
*
error at line 1:
ora-01113: file 8 needs media recovery
ora-01110: data file 8: '/u01/app/oracle/oradata/ora10g/zlm_test01.dbf'
可以看到，同样是需要media recovery的，从以下视图可以获得同样地结果：
sql> select file#,online_status from v$recover_file;
file# online_
---------- -------
8 offline
sql> recover datafile 8
ora-00279: change 552554 generated at 09/05/2014 12:16:56 needed for thread 1
ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_05/o1_mf_1_8_%u_.arc
ora-00280: change 552554 for thread 1 is in sequence #8
specify log: {=suggested | filename | auto | cancel}
auto
ora-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_05/o1_mf_1_8_%u_.arc'
ora-27037: unable to obtain file status
linux error: 2: no such file or directory
additional information: 3
ora-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_05/o1_mf_1_8_%u_.arc'
ora-27037: unable to obtain file status
linux error: 2: no such file or directory
additional information: 3
sql>
自动recover失败，原因是在线日志已经被覆盖了，而且目前是非归档模式，也没有可用的归档日志进行recover，
这个时候，数据库自身无法重新让该文件再online了，必须借助一个特殊工具——bbed(block browse editor)bbed是oracle仅供内部使用的一个数据块浏览编辑工具，通过命令行方式，对数据块所在磁盘文件的十六进制物理地址进行修改，主要用来处理一些特殊恢复的场景(无备份恢复方式)。
bbed在10g数据库软件中是自带的，但是需要对其进行编译安装，需要用到3个包，分别是sbbdpt.o、ssbbded.o、bbedus.msb，其中sbbdpt.o、ssbbded.o位于?/rdbms/lib下，而bbedus.msb位于?/rdbms/mesg下。据说由于在dblink场景中使用bbed修改数据块的scn，会引起scn headroom，因此在11g中，默认没有放入这几个文件(oracle也许并不愿意让客户使用bbed )，因此如果11g中要使用bbed，需要去10g数据库中手工拷贝这3个文件到相应位置，然后再编译安装，要注意的是，bbed的32bit和64bit文件不能互用。似乎只能用于unix/linux系统中。
--检查bbed所需的3个文件sbbdpt.o、ssbbded.o、bbedus.msb是否存在
sql> !
[oracle@ora10g ~]$ cd $oracle_home/rdbms/lib
[oracle@ora10g lib]$ ll
...
-rwxr-xr-x 1 oracle oinstall 3043 jun 28 2005 sbbdpt.o
-rwxr-xr-x 1 oracle oinstall 2721 jun 28 2005 ssbbded.o
...
[oracle@ora10g lib]$ cd $oracle_home/rdbms/mesg/
[oracle@ora10g mesg]$ ll
-rwxr-xr-x 1 oracle oinstall 8704 jun 28 2005 bbedus.msb
...
--编译安装bbed
[oracle@ora10g mesg]$ make -f $oracle_home/rdbms/lib/ins_rdbms.mk bbed=$oracle_home/bin/bbed $oracle_home/bin/bbed
linking bbed utility (bbed)
rm -f /u01/app/oracle/product/10.2.0/db_1/bin/bbed
gcc -o /u01/app/oracle/product/10.2.0/db_1/bin/bbed -l/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ -l/u01/app/oracle/product/10.2.0/db_1/lib/ -l/u01/app/oracle/product/10.2.0/db_1/lib/stubs/ -l/usr/lib -lirc /u01/app/oracle/product/10.2.0/db_1/lib/s0main.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/defopt.o -ldbtools10 -lclntsh `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -wl,-rpath,/u01/app/oracle/product/10.2.0/db_1/lib -lm `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm -l/u01/app/oracle/product/10.2.0/db_1/lib
--首先用下面的sql语句获得数据文件列表
sql> select file#||' '||name||' '||bytes from v$datafile ;
file#||''||name||''||bytes
--------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/ora10g/system01.dbf 524288000
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf 31457280
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf 251658240
4 /u01/app/oracle/oradata/ora10g/users01.dbf 104857600
5 /u01/app/oracle/oradata/ora10g/example01.dbf 104857600
6 /u01/app/oracle/oradata/ora10g/zlm01.dbf 104857600
7 /u01/app/oracle/oradata/ora10g/zlm02.dbf 104857600
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf 52428800
--创建listfile
sql> !
[oracle@ora10g ~]$ touch bbedlistfile.log --扩展名任意，能被parfile识别到就可以
[oracle@ora10g ~]$ cat >> bbedlistfile.log > 1 /u01/app/oracle/oradata/ora10g/system01.dbf 524288000
> 2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf 31457280
> 3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf 251658240
> 4 /u01/app/oracle/oradata/ora10g/users01.dbf 104857600
> 5 /u01/app/oracle/oradata/ora10g/example01.dbf 104857600
> 6 /u01/app/oracle/oradata/ora10g/zlm01.dbf 104857600
> 7 /u01/app/oracle/oradata/ora10g/zlm02.dbf 104857600
> 8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf 52428800
> eof
--创建parfile
[oracle@ora10g ~]$ touch parfile.bbd --注意扩展名为bbd，否则识别不了
[oracle@ora10g ~]$ cat >> parfile.bbd > blocksize=8192
> listfile=bbedlistfile.log
> mode=edit
> eof
--查看装载到bbed中的文件信息
bbed> info
file# name size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/ora10g/system01.dbf 64000
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf 3840
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf 30720
4 /u01/app/oracle/oradata/ora10g/users01.dbf 12800
5 /u01/app/oracle/oradata/ora10g/example01.dbf 12800
6 /u01/app/oracle/oradata/ora10g/zlm01.dbf 12800
7 /u01/app/oracle/oradata/ora10g/zlm02.dbf 12800
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf 6400
--进入bbed命令行模式(默认密码：blockedit)
[oracle@ora10g ~]$ bbed parfile=parfile.bbd
password:
bbed: release 2.0.0.0.0 - limited production on fri sep 5 13:09:55 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
************* !!! for oracle internal use only !!! ***************
bbed>
注意：这里使用了指定了参数文件和文件列表方式登陆bbed，否则，所有参数都需要在bbed的cml界面中输入
如果数据库做过resetlogs，那么需要关注以下两项：
kcvfhrls
kcvfhrlc
由于这里并没有用resetlogs打开数据库，所以只需关注以下几项值：
kscnbas
kcvcptim
kcvfhcpc
kcvfhccc
--查看datafile 8的信息
bbed> set file 8
file# 8
bbed> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00086e6a --数据文件头的scn
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x331c12b8
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000008 --数据文件当前写的redolog sequence
ub4 kcrbabno @504 0x00000ab9 --数据文件当前写的redolog block number
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
bbed> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000005
bbed> p kcvfhccc
ub4 kcvfhccc @148 0x00000004
--和datafile 7做比较
bbed> set file 7
file# 7
bbed> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00086fcf
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x331c159d
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000b
ub4 kcrbabno @504 0x00000002
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
bbed> p kcvfhcpc
ub4 kcvfhcpc @140 0x0000001b
bbed> p kcvfhccc
ub4 kcvfhccc @148 0x0000001a
其实这里kcvfhcpc和kcvfhccc可以不必理会，要修改的就2个地方
只需要把datafile 8中offset 484和500的值改成和datafile 7一致，就ok了
--用dump命令查看datafile 7的存储值
bbed> dump /v offset 484 count 16
file: /u01/app/oracle/oradata/ora10g/zlm02.dbf (7)
block: 1 offsets: 484 to 499 dba:0x01c00001
-------------------------------------------------------
cf6f0800 0000f1b7 9d151c33 01000000 l ?....穹...3....
注意，这里存储的格式与之前通过p kcvfhckp查看到的值是相反的
--通过以下视图可以获得endian值：
sql> set lin 130
sql> col platform_name for a40
sql> select platform_id,platform_name,endian_format from v$db_transportable_platform;
platform_id platform_name endian_format
----------- ---------------------------------------- --------------
7 microsoft windows ia (32-bit) little
10 linux ia (32-bit) little
5 hp tru64 unix little
11 linux ia (64-bit) little
15 hp open vms little
8 microsoft windows ia (64-bit) little
13 linux 64-bit for amd little
12 microsoft windows 64-bit for amd little
17 solaris operating system (x86) little
big-endian和little-endian的定义如下：
a) little-endian 低位字节排放在内存的低地址端，高位字节排放在内存的高地址端。
b) big-endian 高位字节排放在内存的低地址端，低位字节排放在内存的高地址端。
bbed> dump /v offset 500 count 16
file: /u01/app/oracle/oradata/ora10g/zlm02.dbf (7)
block: 1 offsets: 500 to 515 dba:0x01c00001
-------------------------------------------------------
0b000000 02000000 1000e1bf 02000000 l ..........峥....
bbed> modify /x cf6f0800 dba 8,1 offset 484
bbed-00209: invalid number (cf6f0800)
要修改的值为8位4字节十六进制，一起写会提示非法值，可以先修改前3个字节
bbed> modify /x cf6f08 dba 8,1 offset 484
warning: contents of previous bifile will be lost. proceed? (y/n) y
file: /u01/app/oracle/oradata/ora10g/zlm_test01.dbf (8)
block: 1 offsets: 484 to 499 dba:0x02000001
------------------------------------------------------------------------
cf6f0800 00000000 b8121c33 01000000
--再次验证一下
bbed> d /v dba 8,1 offset 484 count 16 --d就是dump，m就是modify，bbed的命令可以用首字母缩写
file: /u01/app/oracle/oradata/ora10g/zlm_test01.dbf (8)
block: 1 offsets: 484 to 499 dba:0x02000001
-------------------------------------------------------
cf6f0800 00000000 b8121c33 01000000 l ?......?.3....
bbed> set file 7 block 1 --注意，这里必须再次指定一下file和block，刚才修改完以后默认file是8号
file# 7
block# 1
bbed> d /v offset 500 count 16
file: /u01/app/oracle/oradata/ora10g/zlm02.dbf (7)
block: 1 offsets: 500 to 515 dba:0x01c00001
-------------------------------------------------------
0b000000 02000000 1000e1bf 02000000 l ..........峥....
bbed> m /x 0b dba 8,1 offset 500
file: /u01/app/oracle/oradata/ora10g/zlm_test01.dbf (8)
block: 1 offsets: 500 to 515 dba:0x02000001
------------------------------------------------------------------------
0b000000 b90a0000 1000bf0e 02000000
bbed> sum
check value for file 8, block 1:
current = 0xf66e, required = 0xf7c8
bbed> sum apply -执行该命令才算是真正的修改完成
check value for file 8, block 1:
current = 0xf7c8, required = 0xf7c8
bbed> exit
[oracle@ora10g ~]$ sqlplus / as sysdba
sql*plus: release 10.2.0.1.0 - production on fri sep 5 13:49:58 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
connected to:
oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
sql> select file#,checkpoint_change#,last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 552911
2 552911
3 552911
4 552911
5 552911
6 552911
7 552911
8 552554 552828
sql> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
file# checkpoint_change# resetlogs_change#
---------- ------------------ -----------------
1 552911547304
2 552911547304
3 552911547304
4 552911547304
5 552911547304
6 552911547304
7 552911547304
8 552911547304
虽然8号数据文件的checkpoint_change#，last_change#与其他文件还是不一致的，但是它的数据文件头中的信息已经一致了，也就是我们刚才改的内容
--recover数据文件并使其online
sql> recover datafile 8
media recovery complete.
ql> alter database datafile 8 online;
database altered.
--再次查看
sql> select file#,checkpoint_change#,last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 552911
2 552911
3 552911
4 552911
5 552911
6 552911
7 552911
8 555977
sql> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
file# checkpoint_change# resetlogs_change#
---------- ------------------ -----------------
1 552911 547304
2 552911 547304
3 552911 547304
4 552911 547304
5 552911 547304
6 552911 547304
7 552911 547304
8 555977 547304
sql> select * from zlm1.offline_test;
id name
---------- ----------
1 aaron8219
此时可以看到，虽然8号数据文件的checkpoint_change#仍然是不一致的，但是已经比其他文件newer了，说明是当前更改的文件，其实之前不能直接online，就是因为8号数据文件的checkpoint_change#=552554要比其他文件的checkpoint_change#552911要older所致。online的时候，控制文件中记录的信息过旧(因为之前文件是offline的，它的checkpoint信息自那一刻开始就不会再变化)，需要用recover把它推送到其他文件的checkpoint之后的值(即比它们都要biger or newer)。经过recover以后，我们获得了8号文件新的checkpoint_change#=555977>552911，因此才能够重新将它online，即通过bbed，手工修改数据文件头中的checkpoint scn，使其能够不依赖于online redo logfile和archive logfile，就能实现recover操作。
sql> alter system checkpoint;
system altered.
sql> select file#,checkpoint_change#,last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 556048
2 556048
3 556048
4 556048
5 556048
6 556048
7 556048
8 556048
8 rows selected.
sql> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
file# checkpoint_change# resetlogs_change#
---------- ------------------ -----------------
1 556048 547304
2 556048 547304
3 556048 547304
4 556048 547304
5 556048 547304
6 556048 547304
7 556048 547304
8 556048 547304
8 rows selected.
sql>
当我们再次手工执行检查点以后，就会触发dbwr进程把db buffer cache中的脏数据写到磁盘文件，此时8号数据文件和数据文件头中的checkpoint scn信息，已经与其他数据文件都一致了。至此，整个恢复过程顺利结束。
总结
本例是通过oracle bbed工具，在非归档模式下对offline数据文件进行恢复的过程。注意，由于current日志非常重要，它仅保留在内存中(sga的db buffer cache)，所以一般重要的系统，都是需要开启归档的，这样可以保证current日志被覆盖后依然可以对数据文件进行前滚(recover)。当我们既没有current日志，又没有归档日志，那就只能通过特殊手段来进行恢复了。如果offline的数据文件中有比较重要的数据内容，就能够使其重新online并取出数据了。当然，bbed还有更多比较复杂的运用，如当某个数据文件块出现损坏，又没有可以用的备份时，通过bbed，可以对数据文件块直接dump并替换内容等，这里就暂且不讨论了，大家可以自己参考手册进行研究。总得来说，bbed还是比较inernal的东东，想要掌握周全，实属不易。