某客户误删除数据文件后down机恢复
下面来模拟下用户的操作和恢复
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/db/oradata/QDDS/datafile/o1_mf_system_hw8svjfp_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_sysaux_hw8svjgc_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_undotbs1_hw8svjgl_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_users_hw8svjh0_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_oth_ogg_hw8v3kwj_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkg3v9_.dbf
/u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf
7 rows selected.
SQL> ! rm /u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf
SQL> ! ls -lrt /u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf
ls: cannot access /u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf: No such file or directory
切换归档，触发ckpt进程
SQL> alter system switch logfile;
System altered.
SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12781
Session ID: 14 Serial number: 331
观察alert日志如下：
Mon Dec 21 05:53:41 2020 Errors in file /u01/app/db/diag/rdbms/qdds/qdds/trace/qdds_ckpt_3557.trc: ORA-63999: data file suffered media failure ORA-01116: error in opening database file 7 ORA-01110: data file 7: ‘/u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf‘ ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/db/diag/rdbms/qdds/qdds/trace/qdds_ckpt_3557.trc: ORA-63999: data file suffered media failure ORA-01116: error in opening database file 7 ORA-01110: data file 7: ‘/u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf‘ ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Mon Dec 21 05:53:41 2020 System state dump requested by (instance=1, osid=3557 (CKPT)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/db/diag/rdbms/qdds/qdds/trace/qdds_diag_3545_20201221055341.trc CKPT (ospid: 3557): terminating the instance due to error 63999 Dumping diagnostic data in directory=[cdmp_20201221055341], requested by (instance=1, osid=3557 (CKPT)), summary=[abnormal instance termination]. Instance terminated by CKPT, pid = 3557
ckpt检查点进程发现7号数据库文件不能正常打开，数据库实例被检查点进程终止
启动数据库是报ORA-01157错误，不能锁住7号数据文件
SQL> startup ORACLE instance started. Total System Global Area 4375998464 bytes Fixed Size 2260328 bytes Variable Size 1342177944 bytes Database Buffers 3019898880 bytes Redo Buffers 11661312 bytes Database mounted. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: ‘/u01/app/db/oradata/QDDS/datafile/o1_mf_tt_hxzkty0b_.dbf‘
使用alter datbase create datefile 命令，把数据文件创建出来，从重做日志或者归档日志进行恢复
alter datbase create datefile 语法如下：
SQL> alter database create datafile 7;
Database altered.
恢复数据文件
SQL> recover datafile 7;
Media recovery complete.
观察alert日志如下：
Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 2 Seq 20 Reading mem 0 Mem# 0: /u01/app/db/oradata/QDDS/onlinelog/o1_mf_2_hw8sxbfj_.log Mem# 1: /u01/app/db/fast_recovery_area/QDDS/onlinelog/o1_mf_2_hw8sxbh4_.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 21 Reading mem 0 Mem# 0: /u01/app/db/oradata/QDDS/onlinelog/o1_mf_3_hw8sxcy1_.log Mem# 1: /u01/app/db/fast_recovery_area/QDDS/onlinelog/o1_mf_3_hw8sxd0l_.log Media Recovery Complete (qdds) Completed: ALTER DATABASE RECOVER datafile 7
从重做日志 Group 2 Seq 20、Group 3 Seq 21 恢7号数据文件
打开数据库开始做crash recovery
SQL> alter database open;
Database altered.
观察alert日志如下：
Beginning crash recovery of 1 threads parallel recovery started with 3 processes Started redo scan Completed redo scan read 149 KB redo, 45 data blocks need recovery Started redo application at Thread 1: logseq 20, block 2 Recovery of Online Redo Log: Thread 1 Group 2 Seq 20 Reading mem 0 Mem# 0: /u01/app/db/oradata/QDDS/onlinelog/o1_mf_2_hw8sxbfj_.log Mem# 1: /u01/app/db/fast_recovery_area/QDDS/onlinelog/o1_mf_2_hw8sxbh4_.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 21 Reading mem 0 Mem# 0: /u01/app/db/oradata/QDDS/onlinelog/o1_mf_3_hw8sxcy1_.log Mem# 1: /u01/app/db/fast_recovery_area/QDDS/onlinelog/o1_mf_3_hw8sxd0l_.log Completed redo application of 0.04MB Completed crash recovery at Thread 1: logseq 21, block 2, scn 1136464 45 data blocks read, 44 data blocks written, 149 redo k-bytes read
到此 恢复完毕数据库正常打开。