# 说明: 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》
