RMAN恢复-丢失整个数据表空间如何恢复

来源：锐游网

整个业务表空间丢失恢复

注意：以下的所有实验，都是基于上面的全库备份来做的恢复。

执行全库备份

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

模拟环境

删除 hfedu001 表空间的所有数据文件

root@hfedudb ~]# cd /oradata/hfzcdb/ 
[root@hfedudb hfedu]# rm hfedu001.dbf

启动数据库，报如下错误。

SQL> conn / as sysdba 
Connected to an idle instance. 
SQL> startup 
ORACLE instance started. 

Total System Global Area 285212672 bytes 
Fixed Size 1218968 bytes 
Variable Size 100664936 bytes 
Database Buffers 176160768 bytes 
Redo Buffers 7168000 bytes 
Database mounted. 
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file 
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu001.dbf'

恢复方法一: 离线恢复

[oracle@hfzcdb91:/oradata/hfzcdb]$cd /oradata/hfzcdb/
[oracle@hfzcdb91:/oradata/hfzcdb]$rm hfedu001.dbf
[oracle@hfzcdb91:/oradata/hfzcdb]$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 10:20:08 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> 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> 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:22:11 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, not open)

RMAN> restore tablespace hfedu001
2> ;

Starting restore at 2023-04-01 10:22:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 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:02
Finished restore at 2023-04-01 10:22:40

RMAN> recover tablespace hfedu001
2> ;

Starting recover at 2023-04-01 10:23:08
using channel ORA_DISK_1

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

Finished recover at 2023-04-01 10:23:09

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:23:19 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

hfeduSQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
hfzcdb           MOUNTED

hfeduSQL> alter database open;

Database altered.

hfeduSQL> select *from hfedu01.hfedu01;

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

hfeduSQL>

恢复方法二：在线恢复

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

hfeduSQL> !rm /oradata/hfzcdb/hfedu001.dbf

hfeduSQL> select * from hfedu01.hfedu01;   【还能查到数据，系统缓存问题】

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

hfeduSQL> !ls /oradata/hfzcdb    【没有文件了】
control01.ctl  hfedu01.dbf  redo02.log    system01.dbf
control02.ctl  hfedu02.dbf  redo03.log    undotbs01.dbf
hfedu002.dbf   redo01.log   sysaux01.dbf  users01.dbf

hfeduSQL> 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:29:50 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 tablespace hfedu001;

Starting restore at 2023-04-01 10:30:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2281 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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/01/2023 10:30:27
ORA-19870: error while restoring backup piece /backup/full/hfedu3_full_hfzcdb_12_1_1132999239
ORA-19573: cannot obtain exclusive enqueue for datafile 7
ORA-19890: data file already in use
ORA-45909: restore, recover or block media recovery may be in progress

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:30:46 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 tablespace hfedu001 offline for recover;

Tablespace 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:31:28 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 tablespace hfedu001;

Starting restore at 2023-04-01 10:31:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3412 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:31:37

RMAN> recover tablespace hfedu001;

Starting recover at 2023-04-01 10:32:03
using channel ORA_DISK_1

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

Finished recover at 2023-04-01 10:32:04

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:32:20 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 tablespace hfedu001 online;

Tablespace altered.

SYS@hfzcdb> select * from hfedu01.hfedu01;

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

SYS@hfzcdb>

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文