熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

表的許多分區被意外drop用rman不完全恢復

2013-11-13 15:32:02  來源: Oracle 

  如果表的很多分區被意外drop需要利用帶庫上的備份集在測試機上做不完全恢復具體日期需要達到一個特定的時間那麼我們可以參考下文

  ◆首先需要用logminer查出刪除語句

  

  uncompress /oracle/oradata/xxx/xxxARCZ begin sysdbms_logmnradd_logfile(/oracle/oradata/xxx/xxxARC); end; begin sysdbms_logmnrstart_logmnr(Options => sysdbms_logmnrDICT_FROM_ONLINE
_CATALOG); end; create table xsb_logminer_ as select timestamp seg_nameoperation sql_redo from V$LOGMNR_CONTENTS where seg_owner=XXX and operation=DDL and sql_redo like ALTER TABLE XXX
DROP PARTITION %; BEGIN sysdbms_logmnrend_logmnr();END;

  ◆然後在生產庫上創建pfile

   create pfile= from spfile;

  需要從帶庫上恢復rman備份集至生產機上將此備份集FTP至測試機上連同pfile文件

  在測試機上創建與生產機上相同目錄admin ……

  在測試機上創建新實例

  

  orapwd file= password=xxx

  然後修改pfile文件內容改變control_files內容

  ◆啟動新實例

  

  export ORACLE_SID=xxx sqlplus / as sysdba startup nomount pfile=/home/oracle/init_xxxora; create spfile from pfile=/home/oracle/init_xxxora; exit RMAN target sys/xxx restore controlfile from ; startup mount crosscheck backup; list backup; run { set newname for datafile to /oracle/oradata/xxx/systemdbf; set newname for datafile to /oracle/oradata/xxx/undodbf; set newname for datafile to /oracle/oradata/xxx/sysauxdbf; set newname for datafile to /oracle/oradata/xxx/pay_tsdbf; restore datafile ; restore datafile ; restore datafile ; restore datafile ; } (注發現單個datafile恢復不如整庫恢復快!) sql alter database backup controlfile to trace; shutdown immediate; exit

  然後修改controlfile文件內容去掉不用的文件名

  ◆不完全恢復數據庫

  

  sqlplus / as sysdba startup nomount;

  ◆重建控制文件

  

  CREATE CONTROLFILE REUSE DATABASE XXX RESETLOGS ARCHIVELOG MAXLOGFILES MAXLOGMEMBERS MAXDATAFILES MAXINSTANCES MAXLOGHISTORY LOGFILE GROUP ( /oracle/oradata/xxx/rdb_redoa ) SIZE M GROUP ( /oracle/oradata/xxx/rdb_redoa ) SIZE M DATAFILE /oracle/oradata/xxx/systemdbf /oracle/oradata/xxx/undodbf /oracle/oradata/xxx/sysauxdbf /oracle/oradata/xxx/pay_tsdbf CHARACTER SET ZHSGBK ; recover database until cancel using backup controlfile; alter database open resetlogs;

  ◆做數據恢復操作

  

  create table xxx as select xxx from xxx where xxx; expftp dmpimp

  也可以如下所示這樣可以更簡單一點

  

  restore database; recover database using backup controlfile until cancel; alter database open resetlogs; END


From:http://tw.wingwit.com/Article/program/Oracle/201311/16867.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.