Oracle誤刪除資料表空間的恢複

來源:互聯網
上載者:User

對於誤刪除資料表空間的恢複,本文通過基於資料庫的時間點恢複和基於資料表空間的時間點恢複分別加以討論

一 通過基於資料庫的時間點恢複被誤刪除的資料表空間

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

  • 1
  • 2
  • 3
  • 4
  • 下一頁

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.