oracle误删除数据文件后恢复

某客户误删除数据文件后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

　　到此 恢复完毕数据库正常打开。