oracle表空间右键删掉,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:54piece 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，以确保恢复精确。

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