如果一个数据文件创建时到当前所有的归档日志都存在的话，那么可以通过offline datafile，recover datafile，online datafile的方式直接恢复数据文件，此种恢复属于介质恢复。
下面我们做一个实验：
查看当前所有的归档日志的初始scn以及终止scn：（status 为 A 表示available，D表示已删除）
SQL> col name
for a100
SQL> select
sequence#,name,first_change#,next_change#,status from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# S
----------
---------------------------------------------------------------------------------------------------- ------------- ------------ -----
5
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_5_cqztt6no_.arc 990220 1001021 A
6
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc 1001021 1006241 A
7
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc 1006241 1006650 A
8
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc 1006650 1009513 A
9
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc 1009513 1009663 A
10
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc 1009663 1009860 A
11
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc 1009860 1010001 A
12
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc 1010001 1010136 A
13
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_13_cqzycyyl_.arc 1010136 1010268 A
9 rows selected.
查看各数据文件的创建SCN，以及checkpoint SCN：
SQL> select
file#,name,status,fuzzy,creation_change#,checkpoint_change# from
v$datafile_header;
FILE# NAME
STATUS FUZ CREATION_CHANGE#
CHECKPOINT_CHANGE#
----------
----------------------------------------------------------------------------------------------------
------- --- ---------------- ------------------
1
/u01/app/oracle/oradata/ora11g/system01.dbf
ONLINE YES 7 1010268
2
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
ONLINE YES 1834 1010268
3
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE YES 923328 1010268
4
/u01/app/oracle/oradata/ora11g/users01.dbf
ONLINE YES 16143 1010268
5
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE YES 952919 1010268
6
/u01/app/oracle/oradata/ora11g/users02.dbf
ONLINE YES 1005138 1010268
6 rows selected.
可以发现当前只有datafile
6是在第一个归档日志后创建的，因此，如果这个文件corruption的话，可以直接通过介质恢复的方式来恢复该数据文件，而不需要使用备份。
SQL> select
file#,name,status,fuzzy,creation_change#,checkpoint_change# from
v$datafile_header where creation_change# > 990220;
FILE# NAME
STATUS FUZ CREATION_CHANGE#
CHECKPOINT_CHANGE#
----------
----------------------------------------------------------------------------------------------------
------- --- ---------------- ------------------
6
/u01/app/oracle/oradata/ora11g/users02.dbf
ONLINE YES 1005138 1010268
删除datafile
6文件
[oracle@ora11g
scripts]$ rm -f /u01/app/oracle/oradata/ora11g/users02.dbf
重启数据库暴露数据文件丢失问题（生产环境切勿操作）
SQL>
startup force
ORACLE
instance started.
Total
System Global Area 730714112 bytes
Fixed
Size 2256832 bytes
Variable
Size 486539328 bytes
Database
Buffers 239075328 bytes
Redo
Buffers 2842624 bytes
Database
mounted.
ORA-01157:
cannot identify/lock data file 6 - see DBWR trace file
ORA-01110:
data file 6: '/u01/app/oracle/oradata/ora11g/users02.dbf'
查看数据文件状态，可以发现数据库无法识别数据文件6了
SQL> select
file#,name,status,fuzzy,creation_change#,checkpoint_change# from
v$datafile_header;
FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#
----------
----------------------------------------------------------------------------------------------------
------- --- ---------------- ------------------
1
/u01/app/oracle/oradata/ora11g/system01.dbf
ONLINE YES 7
1013668
2
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1013668
3
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE YES 923328 1013668
4
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
YES 16143 1013668
5
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE YES 952919 1013668
6 ONLINE 0 0
先offline
datafile 6来打开数据库
SQL> alter
database datafile 6 offline;
Database
altered.
SQL> alter
database open;
Database
altered.
创建数据文件6
SQL> alter
database create datafile '/u01/app/oracle/oradata/ora11g/users02.dbf';
Database
altered.
恢复数据库文件6
SQL>
recover datafile 6;
ORA-00279:
change 1005138 generated at 07/09/2016 02:33:49 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc
ORA-00280:
change 1005138 for thread 1 is in sequence #6
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1006241 generated at 07/09/2016 02:36:15 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc
ORA-00280:
change 1006241 for thread 1 is in sequence #7
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1006650 generated at 07/09/2016 02:41:36 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc
ORA-00280:
change 1006650 for thread 1 is in sequence #8
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1009513 generated at 07/09/2016 03:04:58 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc
ORA-00280:
change 1009513 for thread 1 is in sequence #9
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1009663 generated at 07/09/2016 03:04:59 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc
ORA-00280:
change 1009663 for thread 1 is in sequence #10
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1009860 generated at 07/09/2016 03:05:28 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc
ORA-00280:
change 1009860 for thread 1 is in sequence #11
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279:
change 1010001 generated at 07/09/2016 03:05:32 needed for thread 1
ORA-00289:
suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc
ORA-00280:
change 1010001 for thread 1 is in sequence #12
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
Log
applied.
Media
recovery complete.
SQL>
SQL>
恢复完成
查看数据文件状态
SQL>
select file#,name,status,fuzzy,creation_change#,checkpoint_change# from
v$datafile_header;
FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#
----------
----------------------------------------------------------------------------------------------------
------- --- ---------------- ------------------
1
/u01/app/oracle/oradata/ora11g/system01.dbf
ONLINE YES 7 1034000
2
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1034000
3
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE YES 923328 1034000
4
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1034000
5
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE YES 952919 1034000
6
/u01/app/oracle/oradata/ora11g/users02.dbf OFFLINE NO 1005138 1034000
online数据文件6
SQL>
alter database datafile 6 online;
Database
altered.
恢复完成，确认数据文件状态
SQL> select
file#,name,status,fuzzy,creation_change#,checkpoint_change# from
v$datafile_header;
FILE# NAME
STATUS FUZ CREATION_CHANGE#
CHECKPOINT_CHANGE#
----------
----------------------------------------------------------------------------------------------------
------- --- ---------------- ------------------
1
/u01/app/oracle/oradata/ora11g/system01.dbf
ONLINE YES 7 1034000
2
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1034000
3
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE YES 923328 1034000
4
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1034000
5
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE YES 952919 1034000
6
/u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES
1005138 1034717