Oracle误删除表空间的恢复

文章由LinuxBoy分享于2019-03-30 03:03:12热评（234）

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

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

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;

GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
1 NO 1 CURRENT
2 YES 0 UNUSED
3 YES 0 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create tablespace jxc datafile '/oracle/CRM/jxc.dbf' size 10m;

Tablespace created.

SQL> create user zx identified by dhhzdhhz default tablespace jxc;

User created.

SQL> grant connect ,resource to zx;

Grant succeeded.

SQL> exit

推荐文章：

Oracle误删除表空间的恢复