Oracle数据库，误删除数据文件，无任何备份情况下，完全恢复只需3步？

说明:

Oracle数据库，误删除数据文件，无任何备份情况下，仍然有希望完全恢复数据。
本次恢复方式需要满足一个条件:需要完整的从数据文件创建到当前时间点的重做日志。

恢复过程如下:

Oracle 10以前版本恢复方法如下:

SQL> alter database create datafile 'missing name' as 'misisng name';
SQL> recover datafile 'missing name';
SQL> alter database datafile '<missing name>' online;

Oracle 10G以后版本恢复方法如下:
注意:没有任何rman备份的情况下，只要满足条件，仍可以执行下面操作

RMAN> restore datafile <missing file id>;
RMAN> recover datafile <missing file id>;
RMAN> sql 'alter database datafile <missing file id> online';

详细测试过程如下:

数据库版本:Oracle 19.22

1.新增表空间 和 测试数据

SQL> create tablespace cjc datafile '/oracle/oradata/CHEN/cjctbs.dbf' size 10M;
SQL> create user cjc identified by "a" default tablespace cjc;
SQL> grant connect,resource to cjc;
SQL> create table cjc.t1(id int);
SQL> insert into cjc.t1 values(1);
SQL> commit;

2.查询file_id

set line 150
col tablespace_name for a15
col file_name for a50
select file_id,tablespace_name,file_name from dba_data_files order by 1;
   FILE_ID TABLESPACE_NAME FILE_NAME
---------- --------------- --------------------------------------------------
	 1 SYSTEM	   /oracle/oradata/CHEN/system01.dbf
	 3 SYSAUX	   /oracle/oradata/CHEN/sysaux01.dbf
	 4 UNDOTBS1	   /oracle/oradata/CHEN/undotbs01.dbf
	 5 CJC		   /oracle/oradata/CHEN/cjctbs.dbf
	 7 USERS	   /oracle/oradata/CHEN/users01.dbf

SQL> select * from cjc.t1;
	ID
----------
	 1

3.模拟误删除

停库:

SQL> shutdown immediate;

重命名数据文件，模拟误删除

mv /oracle/oradata/CHEN/cjctbs.dbf /oracle/oradata/CHEN/cjctbs.dbf.bak

4.启动数据库，报错:

SQL> startup;
ORACLE instance started.
Total System Global Area 1560279512 bytes
Fixed Size		    8939992 bytes
Variable Size		  905969664 bytes
Database Buffers	  637534208 bytes
Redo Buffers		    7835648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/oradata/CHEN/cjctbs.dbf'

告警日志报错:

2025-03-02T14:23:44.156026+08:00
ALTER DATABASE OPEN
2025-03-02T14:23:44.276937+08:00
Errors in file /oracle/db/diag/rdbms/chen/chen/trace/chen_dbw0_787.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/oradata/CHEN/cjctbs.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2025-03-02T14:23:44.277719+08:00
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.

5.rman恢复?

说明:数据库安装以后，没有做过任何的rman备份，但是只要该数据文件创建以来的redo日志没有覆盖，仍然可以恢复。

[oracle@cjc-db-02 CHEN]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 2 14:25:47 2025
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CHEN (DBID=1574800662, not open)

检查，没有 datafile 5 的备份

RMAN> list copy of datafile 5;
using target database control file instead of recovery catalog
specification does not match any datafile copy in the repository

RMAN> list backup of datafile 5;
specification does not match any backup in the repository

仍然可以成功执行restore

RMAN> restore datafile 5;
Starting restore at 02-MAR-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
creating datafile file number=5 name=/oracle/oradata/CHEN/cjctbs.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 02-MAR-25

检查新restore的文件

[oracle@cjc-db-02 CHEN]$ ls -lrth cjctbs.dbf*
-rw-r----- 1 oracle asmadmin 11M Mar  2 14:22 cjctbs.dbf.bak
-rw-r----- 1 oracle asmadmin 11M Mar  2 14:26 cjctbs.dbf

[oracle@cjc-db-02 CHEN]$ md5sum cjctbs.dbf*
866fc473e7c668e47bd75a700682c736  cjctbs.dbf
f67077d86b29c1e5e959eeaf42906bce  cjctbs.dbf.bak

继续进行recover

RMAN> recover datafile 5;
Starting recover at 02-MAR-25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-MAR-25

查看 recover 时，后台的告警日志:
可以看到，是自动使用 Online Redo Log Thread 1 Group 2进行恢复的。

2025-03-02T14:27:35.129644+08:00

alter database recover datafile list clear
Completed: alter database recover datafile list clear

alter database recover
 if needed datafile 5
2025-03-02T14:27:35.131946+08:00

Media Recovery Start
2025-03-02T14:27:35.132289+08:00

Serial Media Recovery started
2025-03-02T14:27:35.173906+08:00

Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
  Mem# 0: /oracle/oradata/CHEN/redo02.log
2025-03-02T14:27:35.264340+08:00

Media Recovery Complete (chen)
Completed: alter database recover

 if needed datafile 5

执行online

RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online

启动数据库

RMAN> sql 'alter database open';
sql statement: alter database open

恢复成功:
验证数据:

SQL> select status,instance_name from v$instance;
STATUS	     INSTANCE_NAME
------------ ----------------
OPEN	     chen

SQL> select * from cjc.t1;
	ID
----------
	 1

SQL> insert into cjc.t1 values(2);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from cjc.t1;
	ID
----------
	 1
	 2

参考:

Recreating a missing datafile with no backups (Doc ID 1149946.1)

###chenjuchao 20250302###
欢迎关注我的公众号《IT小Chen》