Heim  >  Artikel  >  Datenbank  >  Oracle误删除表空间的恢复

Oracle误删除表空间的恢复

WBOY
WBOYOriginal
2016-06-07 17:35:21937Durchsuche

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

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

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

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;

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn