无备份有完全归档日志情况下恢复数据文件
如果一个数据文件创建时到当前所有的归档日志都存在的话，那么可以通过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: {
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: {
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: {
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: {
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: {
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: {
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: {
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
下一篇：Oracle中的sign函数