如果一个数据文件创建时到当前所有的归档日志都存在的话，那么可以通过offlinedatafile，recoverdatafile，onlinedatafile的方式直接恢复数据文件，此种恢复属于介质恢复。
创新互联专注为客户提供全方位的互联网综合服务，包含不限于网站设计制作、成都网站建设、奈曼网络推广、微信小程序定制开发、奈曼网络营销、奈曼企业策划、奈曼品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联为所有大学生创业者提供奈曼建站搭建服务，24小时服务热线：18982081108，官方网址：www.cdcxhl.com
下面我们做一个实验：
查看当前所有的归档日志的初始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: {=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: {=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: {=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: {=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: {=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: {=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: {=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
网站栏目：无备份有完全归档日志情况下恢复数据文件
URL分享：
http://cdkjz.cn/article/jcshgj.html
