Oracle误删除表空间的恢复

误删除表空间，当数据库有之前可用于恢复的全库备份和相关归档，如果对数据库执行不完全恢复，恢复该数据库到删除表空间之前的状

对于误删除表空间的恢复，本文通过基于数据库的时间点恢复和基于表空间的时间点恢复分别加以讨论

一 通过基于数据库的时间点恢复被误删除的表空间

1 需要注意的事项

a 基于数据库的时间点恢复将会回退整个数据库。

b 误删除表空间，当数据库有之前可用于恢复的全库备份和相关归档，如果对数据库执行不完全恢复，恢复该数据库到删除表空间之前的状态，便可恢复误删除的表空间。但实际上当我们删除表空间，数据库备份中将不存在关于该表空间的的信息,直接进行恢复将会出现问题。如下所示：

RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================

 

BS Key  Type LV Size      Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
34      Incr 0  2.04G      DISK        00:02:22    2014-02-09 19:13:39
        BP Key: 34  Status: AVAILABLE  Compressed: NO  Tag: TAG20140209T191116
        Piece Name: /backup/crm/full-20140209-14p0792l_1_1.bak
  List of Datafiles in backup set 34
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 3892854    2014-02-09 19:11:17 /Oracle/CRM/CRM/system01.dbf
  2    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/sysaux01.dbf
  3    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/undotbs01.dbf
  4    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/users01.dbf
  5    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/jxc.dbf
6    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/pos.dbf
  7    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/user01.dbf
  8    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/erp.dbf
  9    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/undotbs03.dbf
  12  0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/crm.dbf

RMAN> host;

[oracle@dest bak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:16:40 2014

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>drop tablespace pos  including contents and datafiles;

Tablespace dropped.

RMAN> list backup of database;
List of Backup Sets
===================

 

BS Key  Type LV Size      Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
34      Incr 0  2.04G      DISK        00:02:22    2014-02-09 19:13:39
        BP Key: 34  Status: AVAILABLE  Compressed: NO  Tag: TAG20140209T191116
        Piece Name: /backup/crm/full-20140209-14p0792l_1_1.bak
  List of Datafiles in backup set 34
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/system01.dbf
  2    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/sysaux01.dbf
  3    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/undotbs01.dbf
  4    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/CRM/users01.dbf
  5    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/jxc.dbf
6    0  Incr 3892854    2014-02-09 19:11:17
  7    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/user01.dbf
  8    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/erp.dbf
  9    0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/undotbs03.dbf
  12  0  Incr 3892854    2014-02-09 19:11:17 /oracle/CRM/crm.dbf
所以，在恢复前应该先用上一次全备份时刻控制文件备份恢复当前控制文件，之后再对整个数据库执行基于时间点的不完全恢复

2  创建测试表空间及相应的用户
[oracle@dest bak]$ sqlplus / as  sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:18:28 2014

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#,archived,sequence#,status from v$log;