ORACLE表空间删除恢复

注意：在恢复ORACLE表空间的过程中，需要用该表空间仍存在时的备份控制文件对表空间进行恢复。即先还原到前面未删除该表空间的控制文件，再恢复表空间。

一、清除所有RMAN备份集

[oracle@localhost demo]$ rman target /
RMAN> delete noprompt backup;
RMAN> exit

 

二、创建将删除的表空间及数据（具体目录参数等需据你的系统决定）

[oracle@localhost demo]$ sqlplus / as sysdba
SQL>CREATE TABLESPACE test DATAFILE '/opt/oracle/oradata/demo/test01.dbf' SIZE 5M
SQL>create table droptest (id int) tablespace test;
SQL>insert into droptest values(1);
SQL>commit;
SQL>select * from droptest;
        ID
----------
         1

 

三、备份全库

1、设置环境的时间显示格式

[oracle@localhost demo]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
[oracle@localhost demo]$ export NLS_LANG=AMERICAN;

2、备份数据库

[oracle@localhost demo]$ rman target /
RMAN> backup database plus archivelog delete input;

Starting backup at 2010-07-25 16:12:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=148 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/demo/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/demo/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 2010-07-25 16:12:54
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/opt/oracle/oradata/demo/undotbs01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/demo/users01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/demo/test01.dbf
channel ORA_DISK_2: starting piece 1 at 2010-07-25 16:12:54
channel ORA_DISK_2: finished piece 1 at 2010-07-25 16:13:29
piece handle=/opt/oracle/backup/0tljmco6_1_1 tag=TAG20100725T161253 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: finished piece 1 at 2010-07-25 16:13:54
piece handle=/opt/oracle/backup/0sljmco6_1_1 tag=TAG20100725T161253 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:00
Finished backup at 2010-07-25 16:13:54

Starting Control File and SPFILE Autobackup at 2010-07-25 16:13:54
piece handle=/opt/oracle/flash_recovery_area/DEMO/autobackup/2010_07_25/o1_mf_s_725300035_64qwl4st_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2010-07-25 16:13:58

RMAN> exit

 

四、删除表空间

1、设置环境的时间显示格式（可做也可不做）

[oracle@localhost demo]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
[oracle@localhost demo]$ export NLS_LANG=AMERICAN;

2、删除表空间

[oracle@localhost demo]$ sqlplus / as sysdba
SQL>select sysdate from dual;
2010-07-25 16:14:41
SQL>r
2010-07-25 16:14:43
SQL>r
2010-07-25 16:14:45
SQL>r
2010-07-25 16:14:47
SQL>DROP TABLESPACE TEST INCLUDING CONTENTS;

SQL>select * from droptest;
此时会报错找不到该表

 

五、恢复表空间

[oracle@localhost demo]$ rman target /
run{
startup force nomount;//启动到NOMOUNT状态，以恢复控制文件
restore controlfile from '/opt/oracle/flash_recovery_area/DEMO/autobackup/2010_07_25/o1_mf_s_725300035_64qwl4st_.bkp';//据备份集恢复控制文件；备份集参考上面的红色字体
alter database mount;
set until time = '2010-07-25 16:14:41';//该时间为全备和删除该表空间之间，见上面红色字体。
restore database;
recover database;
alter database open resetlogs;//重置RESETLOGS
}

Oracle instance started
Total System Global Area     285212672 bytes
.......
Finished recover at 2010-07-25 16:21:23
database opened

[oracle@localhost demo]$ sqlplus / as sysdba
SQL> select * from droptest;//查看恢复结果
        ID
----------
         1
对于恢复表空间也until到某个SCN，以确保恢复精确。

 

六、此时建议对数据库进行一次全备，包括归档日志。