无备份有完全归档日志情况下恢复数据文件

发布时间：2020-06-21 12:34:55 来源：网络阅读：1743 作者：striong 栏目：关系型数据库

如果一个数据文件创建时到当前所有的归档日志都存在的话，那么可以通过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