對於誤刪除資料表空間的恢複,本文通過基於資料庫的時間點恢複和基於資料表空間的時間點恢複分別加以討論
一 通過基於資料庫的時間點恢複被誤刪除的資料表空間
1 需要注意的事項
a 基於資料庫的時間點恢複將會回退整個資料庫。
b 誤刪除資料表空間,當資料庫有之前可用於恢複的全庫備份和相關歸檔,如果對資料庫執行不完全恢複,恢複該資料庫到刪除資料表空間之前的狀態,便可恢複誤刪除的資料表空間。但實際上當我們刪除資料表空間,Database Backup中將不存在關於該資料表空間的的資訊,直接進行恢複將會出現問題。如下所示:
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;
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