误删除数据文件，无任何备份情况下，完全恢复

mahan

说明:

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)