归档模式下，物理删除数据文件的完全的恢复
1、实验环境
环境归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/archivelog
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
数据文件信息
SQL> col name for a60
SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
4 /oradata/cndb/users01.dbf
3 /oradata/cndb/undotbs01.dbf
2 /oradata/cndb/sysaux01.dbf
1 /oradata/cndb/system01.dbf
5 /oradata/cndb/DALE_01.dbf
6 /oradata/cndb/undotbs2_01.dbf
2、物理删除
物理删除数据文件DALE_01.dbf
SQL> host rm -rf /oradata/cndb/DALE_01.dbf
往表插入数据，显示数据文件不存在
SQL> select table_name from all_tables where owner='DALE';
TABLE_NAME
------------------------------
DALE01
JUNJIE001
SQL> desc dale.JUNJIE001
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> insert into dale.junjie001 values(2);
insert into dale.junjie001 values(2)
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/oradata/cndb/DALE_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
显示五号文件不存在
3、恢复过程
offline datafile 5，创建datafile 5
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database create datafile 5;
Database altered.
因为控制文件里还有记录，我们可以更具控制文件里的信息，从新创建datafile 5.大小跟原来是一样的
查看恢复视图
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
5 OFFLINE OFFLINE
UNKNOWN ERROR 1150131
29-MAR-24
利用归档恢复
SQL> recover datafile 5;
ORA-00279: change 1150131 generated at 03/29/2024 23:31:03 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_1_1164928856.dbf
ORA-00280: change 1150131 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1150490 generated at 03/29/2024 23:31:04 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_2_1164928856.dbf
ORA-00280: change 1150490 for thread 1 is in sequence #2
ORA-00279: change 1150636 generated at 03/29/2024 23:32:15 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_3_1164928856.dbf
ORA-00280: change 1150636 for thread 1 is in sequence #3
ORA-00279: change 1150644 generated at 03/29/2024 23:32:15 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_4_1164928856.dbf
ORA-00280: change 1150644 for thread 1 is in sequence #4
ORA-00279: change 1150694 generated at 03/29/2024 23:33:56 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_1_1164929636.dbf
ORA-00280: change 1150694 for thread 1 is in sequence #1
ORA-00279: change 1234125 generated at 03/31/2024 18:10:50 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_2_1164929636.dbf
ORA-00280: change 1234125 for thread 1 is in sequence #2
ORA-00279: change 1290312 generated at 04/01/2024 21:03:45 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_3_1164929636.dbf
ORA-00280: change 1290312 for thread 1 is in sequence #3
ORA-00279: change 1325430 generated at 04/02/2024 04:27:30 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_4_1164929636.dbf
ORA-00280: change 1325430 for thread 1 is in sequence #4
ORA-00279: change 1429946 generated at 04/04/2024 19:00:38 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_5_1164929636.dbf
ORA-00280: change 1429946 for thread 1 is in sequence #5
ORA-00279: change 1519822 generated at 04/06/2024 22:05:55 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_6_1164929636.dbf
ORA-00280: change 1519822 for thread 1 is in sequence #6
ORA-00279: change 1562762 generated at 04/07/2024 22:01:44 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_7_1164929636.dbf
ORA-00280: change 1562762 for thread 1 is in sequence #7
ORA-00279: change 1601162 generated at 04/08/2024 21:22:29 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_8_1164929636.dbf
ORA-00280: change 1601162 for thread 1 is in sequence #8
ORA-00279: change 1601296 generated at 04/08/2024 21:24:02 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_9_1164929636.dbf
ORA-00280: change 1601296 for thread 1 is in sequence #9
ORA-00279: change 1601534 generated at 04/08/2024 21:26:03 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_10_1164929636.dbf
ORA-00280: change 1601534 for thread 1 is in sequence #10
ORA-00279: change 1601794 generated at 04/08/2024 21:28:50 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_11_1164929636.dbf
ORA-00280: change 1601794 for thread 1 is in sequence #11
ORA-00279: change 1622254 generated at 04/08/2024 21:45:18 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_12_1164929636.dbf
ORA-00280: change 1622254 for thread 1 is in sequence #12
ORA-00279: change 1642733 generated at 04/08/2024 21:55:58 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_13_1164929636.dbf
ORA-00280: change 1642733 for thread 1 is in sequence #13
ORA-00279: change 1646926 generated at 04/08/2024 22:01:47 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_14_1164929636.dbf
ORA-00280: change 1646926 for thread 1 is in sequence #14
ORA-00279: change 1646994 generated at 04/08/2024 22:02:22 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_15_1164929636.dbf
ORA-00280: change 1646994 for thread 1 is in sequence #15
ORA-00279: change 1647002 generated at 04/08/2024 22:02:22 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_16_1164929636.dbf
ORA-00280: change 1647002 for thread 1 is in sequence #16
ORA-00279: change 1647327 generated at 04/08/2024 22:07:00 needed for thread 1
ORA-00289: suggestion : /arch/archivelog/arch_cndb_1_17_1164929636.dbf
ORA-00280: change 1647327 for thread 1 is in sequence #17
online datafile
SQL> alter database datafile 6 online;
Database altered.
SQL> select table_name from all_tables where owner ='DALE';
TABLE_NAME
------------------------------
DALE01
JUNJIE001
可以看到表已恢复