Oracle数据库，误删除数据文件，无任何备份情况下，完全恢复只需3步？
青
chenjuchao约 5 分钟
chenjuchao约 5 分钟说明:
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>createtablespace cjc datafile '/oracle/oradata/CHEN/cjctbs.dbf' size 10M;SQL>createuser cjc identified by "a" default tablespace cjc;SQL>grantconnect,resource to cjc;SQL>createtable cjc.t1(id int);SQL>insertinto cjc.t1 values(1);SQL>commit;
2.查询file_id
set line 150col tablespace_name for a15col file_name for a50select 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.dbf3 SYSAUX /oracle/oradata/CHEN/sysaux01.dbf4 UNDOTBS1 /oracle/oradata/CHEN/undotbs01.dbf5 CJC /oracle/oradata/CHEN/cjctbs.dbf7 USERS /oracle/oradata/CHEN/users01.dbfSQL> select * from cjc.t1;ID----------1
3.模拟误删除
停库:
SQL> shutdown immediate;重命名数据文件，模拟误删除
mv /oracle/oradata/CHEN/cjctbs.dbf /oracle/oradata/CHEN/cjctbs.dbf.bak4.启动数据库，报错:
SQL> startup;ORACLE instance started.Total System Global Area 1560279512bytesFixed Size 8939992bytesVariable Size 905969664bytesDatabase Buffers 637534208bytesRedo Buffers 7835648bytesDatabase mounted.ORA-01157: cannot identify/lock data file 5 - see DBWR trace fileORA-01110: data file 5: '/oracle/oradata/CHEN/cjctbs.dbf'
告警日志报错:
2025-03-02T14:23:44.156026+08:00ALTERDATABASEOPEN2025-03-02T14:23:44.276937+08:00Errorsin file /oracle/db/diag/rdbms/chen/chen/trace/chen_dbw0_787.trc:ORA-01157: cannot identify/lock data file 5- see DBWR trace fileORA-01110: data file 5: '/oracle/oradata/CHEN/cjctbs.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 72025-03-02T14:23:44.277719+08:00Smart fusion block transfer is disabled:instance mounted in exclusive mode.
5.rman恢复?
说明:数据库安装以后，没有做过任何的rman备份，但是只要该数据文件创建以来的redo日志没有覆盖，仍然可以恢复。
[oracle@cjc-db-02 CHEN]$ rman target /Recovery Manager: Release19.0.0.0.0- Production on Sun Mar 214:25:472025Version 19.22.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to target database: CHEN (DBID=1574800662, notopen)
检查，没有 datafile 5 的备份
RMAN> list copyof datafile 5;using target database control file instead of recovery catalogspecification does notmatchany datafile copyin the repositoryRMAN> list backup of datafile 5;specification does notmatchany backup in the repository
仍然可以成功执行restore
RMAN> restore datafile 5;Starting restore at 02-MAR-25allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=46 device type=DISKcreating datafile file number=5 name=/oracle/oradata/CHEN/cjctbs.dbfrestore not done; all files read only, offline, excluded, or already restoredFinished restore at 02-MAR-25
检查新restore的文件
[oracle@cjc-db-02CHEN]$ ls -lrth cjctbs.dbf*-rw-r----- 1 oracle asmadmin 11M Mar214:22 cjctbs.dbf.bak-rw-r----- 1 oracle asmadmin 11M Mar214:26 cjctbs.dbf[oracle@cjc-db-02CHEN]$ md5sum cjctbs.dbf*866fc473e7c668e47bd75a700682c736 cjctbs.dbff67077d86b29c1e5e959eeaf42906bce cjctbs.dbf.bak
继续进行recover
RMAN> recover datafile 5;Starting recover at 02-MAR-25using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 02-MAR-25
查看 recover 时，后台的告警日志:
可以看到，是自动使用 Online Redo Log Thread 1 Group 2进行恢复的。
2025-03-02T14:27:35.129644+08:00alter database recover datafile list clearCompleted: alter database recover datafile list clearalter database recoverif needed datafile 52025-03-02T14:27:35.131946+08:00Media Recovery Start2025-03-02T14:27:35.132289+08:00Serial Media Recovery started2025-03-02T14:27:35.173906+08:00Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0Mem# 0: /oracle/oradata/CHEN/redo02.log2025-03-02T14:27:35.264340+08:00Media Recovery Complete (chen)Completed: alter database recoverif 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 chenSQL>select*from cjc.t1;ID----------1SQL>insertinto cjc.t1 values(2);1row created.SQL>commit;Commit complete.SQL>select*from cjc.t1;ID----------12
参考:
Recreating a missing datafile with no backups (DocID1149946.1)###chenjuchao 20250302###
欢迎关注我的公众号《IT小Chen》