無備份有完全歸檔日志情況下恢復數據文件

發布時間：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