非归档,open 模式下
一 将表空间users设置为只读
SQL> show user
USER is "SYS"
SQL> alter tablespace users read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
CHEN_TEMP ONLINE
CHEN01 ONLINE
TEST ONLINE
9 rows selected.
二 open,非归档状态下,备份只读表空间
RMAN> run{
2> allocate channel c1 type disk;
3> backup tablespace users;
4> }
allocated channel: c1
channel c1: SID=141 device type=DISK
Starting backup at 07-JUL-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/ogg1/users01.dbf
channel c1: starting piece 1 at 07-JUL-15
channel c1: finished piece 1 at 07-JUL-15
piece handle=/u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp tag=TAG20150707T162539 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-15
Starting Control File and SPFILE Autobackup at 07-JUL-15
piece handle=/u01/app/oracle/flash_recovery_area/OGG1/autobackup/2015_07_07/o1_mf_s_884449540_bsq384q6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-JUL-15
released channel: c1
三 剪切只读表空间到其他目录,模拟故障
[oracle@ogg1 ogg1]$ mv users01.dbf /home/oracle/
SQL> conn chen/chen
Connected.
SQL> select * from t1;
ID
10000
10000
四 清空数据库缓冲区和共享池,查询表报错(表所在表空间为users)
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ogg1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
五 通过备份恢复表空间users,报错,不能在open状态下进行恢复
RMAN> restore tablespace users;
Starting restore at 07-JUL-15
using channel ORA_DISK_1
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 00004 to /u01/app/oracle/oradata/ogg1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2015 17:26:43
ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 4
'/u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp'
六 挂载数据库,进行恢复
[oracle@ogg1 2015_07_07]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 7 17:52:57 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
RMAN> restore tablespace users;
Starting restore at 07-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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 00004 to /u01/app/oracle/oradata/ogg1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/OGG1/backupset/2015_07_07/o1_mf_nnndf_TAG20150707T162539_bsq383fm_.bkp tag=TAG20150707T162539
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-JUL-15
RMAN> recover tablespace users;
Starting recover at 07-JUL-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-JUL-15
RMAN> alter database open;
database opened
七 恢复成功,read write表空间
SQL> conn chen/chen
Connected.
SQL> select * from t1;
ID
10000
10000
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
CHEN_TEMP ONLINE
CHEN01 ONLINE
TEST ONLINE
9 rows selected.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
CHEN_TEMP ONLINE
CHEN01 ONLINE
TEST ONLINE
9 rows selected.
转载自chenoracle