RMAN恢复-丢失单个数据文件如何恢复

1. 准备环境

1.1. 业务数据

create tablespace hfedu001 datafile '/oradata/hfzcdb/hfedu001.dbf' size 50m; 
create tablespace hfedu002 datafile '/oradata/hfzcdb/hfedu002.dbf' size 50m; 
create user hfedu01 identified by hfedu01 default tablespace hfedu001; 
create user hfedu02 identified by hfedu02 default tablespace hfedu002; 
grant dba to hfedu01; 
grant dba to hfedu02; 
conn hfedu01/hfedu01; 
create table hfedu01 (id number(10),name varchar2(10)); 
insert into hfedu01 values(1,'hfedu01'); 
insert into hfedu01 values(2,'hfedu02'); 
insert into hfedu01 values(3,'hfedu03'); 
insert into hfedu01 values(4,'hfedu04'); 
insert into hfedu01 values(5,'hfedu05'); 
commit; 
create table hfedu02 as select * from hfedu01; 
select * from hfedu01; 
select * from hfedu02;
conn /as sysdba; 
alter system switch logfile; 
alter system checkpoint; 

1.2. 执行全库备份

-- 打开控制文件备份
CONFIGURE CONTROLFILE AUTOBACKUP ON;     
backup tag hfedu3full format '/backup/full/hfedu3_full_hfzcdb_%s_%p_%t' (database); 

1.3. 模拟数据丢失，手动删除数据文件 hfedu001.dbf

[oracle@hfedudb hfzcdb]$ rm -rf hfedu001.dbf 

1.4. 再次启动数据库，无法启动并报错不能锁定数据文件 6，查看 dbwr 的跟踪文件。

SQL>startup force; 
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file 
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu001.dbf' 

1.5. 检查跟踪文件，报如下错误，非常的清楚的告诉了找不到的文件：

Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_mz00_11969.trc: 
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu001.dbf' 
ORA-01565: error in identifying file '/oradata/hfzcdb/hfedu001.dbf' 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file or directory 
Additional information: 7 

2. 数据恢复

2.1. 恢复方法一

零停机，在线恢复 【启动数据库报7号文件丢失，恢复前提：数据库归档模式下，rman全备份数据库

，更改丢失的文件offlie ，启动数据，进入rman， restore datafile 7;再recover datafile 7;在进入数据库把7号丢失文件online，启动数据库】

SYS@hfzcdb> startup force;
ORACLE instance started.

Total System Global Area 2466249672 bytes
Fixed Size                  8899528 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata/hfzcdb/hfedu001.dbf'
SYS@hfzcdb> alter database datafile '/oradata/hfzcdb/hfedu001.dbf' offline;

Database altered.

SYS@hfzcdb> alter database open;

Database altered.

SYS@hfzcdb> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oradata/hfzcdb]$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 1 10:04:26 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HFZCDB (DBID=797496974)

RMAN> restore datafile 7;

Starting restore at 2023-04-01 10:04:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2283 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oradata/hfzcdb/hfedu001.dbf
channel ORA_DISK_1: reading from backup piece /backup/full/hfedu3_full_hfzcdb_12_1_1132999239
channel ORA_DISK_1: piece handle=/backup/full/hfedu3_full_hfzcdb_12_1_1132999239 tag=hfedu3FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-04-01 10:04:52

RMAN> recover datafile 7;

Starting recover at 2023-04-01 10:05:18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2023-04-01 10:05:19

RMAN> exit


Recovery Manager complete.
[oracle@hfzcdb91:/oradata/hfzcdb]$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 10:05:35 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@hfzcdb> alter database datafile 7 online;

Database altered.

SYS@hfzcdb> select * from hfedu01.hfedu01;

ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu05

SYS@hfzcdb> select * from hfedu01.hfedu02;

ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu05

SYS@hfzcdb>

2.2. 恢复方法二

离线恢复 【离线恢复就是没有把丢失文件offline，直接进入rman， restore datafile 7;再recover datafile 7，启动数据库】

-- 数据库打开的时候删除表空间文件，表空间丢失，使用
alter tablespace hfedu001 offline for recover;
-- 将文件至于恢复状态，再进rman恢复， 
restore tablespace hfedu001; 
recover tablespace hfedu001;
-- 再进数据库online丢失的文件

-- 如果是丢失system 表空间，是不可以采用脱机的方式实现在线的的恢复。