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

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

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

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;
复制

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
复制

SQL> shutdown immediate;
复制

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

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.
复制

[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)
复制

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
复制

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
复制

[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
复制

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
复制

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
复制

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)
复制