Control File中備份資訊被覆蓋情況下的資料庫恢復案例 (zt)

tolywang發表於2007-08-01

http://blog.csdn.net/kamus/archive/2005/01/17/257094.aspx

現場人員報告說誤刪除了使用者(drop user),按照操作文件作RMAN的不完全恢復,但是報錯,錯誤資訊顯示:

RMAN-03002: failure during compilation of command

RMAN-03013: command type: restore


RMAN-03002: failure during compilation of command

RMAN-03013: command type: IRESTORE

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 13 found to restore

RMAN-06023: no backup or copy of datafile 12 found to restore

RMAN-06023: no backup or copy of datafile 11 found to restore

RMAN-06023: no backup or copy of datafile 10 found to restore

RMAN-06023: no backup or copy of datafile 9 found to restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN-06023: no backup or copy of datafile 7 found to restore

RMAN-06023: no backup or copy of datafile 6 found to restore

RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

restore database的時候報沒有可用的資料檔案備份?於是作list backup,檢查結果,發現居然沒有真的沒有顯示可用的0級備份。

先說明一下該客戶使用的備份策略:

at 5:00 /every:Friday cmd /c E:oracleoradata.bakRMAN_db_inc0.bat

at 5:00 /every:Saturday cmd /c E:oracleoradata.bakRMAN_db_inc2.bat

at 5:00 /every:Sunday cmd /c E:oracleoradata.bakRMAN_db_inc2.bat

at 5:00 /every:Monday cmd /c E:oracleoradata.bakRMAN_db_inc2.bat

at 5:00 /every:Tuesday cmd /c E:oracleoradata.bakRMAN_db_inc1.bat

at 5:00 /every:Wednesday cmd /c E:oracleoradata.bakRMAN_db_inc2.bat

at 5:00 /every:Thursday cmd /c E:oracleoradata.bakRMAN_db_inc2.bat

at 6:00 /every:Friday cmd /c E:oracleoradata.bakRMAN_del_archive.bat

每週五臨晨5點作0級備份,週六至週一作2級備份,週二作1級備份,週三、週四作2級備份。

檢視備份路徑中生成的檔案:

2004-12-31 05:04 2,147,475,968 DB0_QFIIDB_159_1_546325203

2004-12-31 05:08 2,147,475,968 DB0_QFIIDB_159_2_546325203

2004-12-31 05:09 425,230,848 DB0_QFIIDB_159_3_546325203

2005-01-01 05:04 149,479,936 DB2_QFIIDB_160_1_546411603

2005-01-02 05:04 179,380,736 DB2_QFIIDB_161_1_546498003

2005-01-03 05:04 179,945,984 DB2_QFIIDB_162_1_546584403

2005-01-04 05:04 298,500,608 DB1_QFIIDB_163_1_546670803

2005-01-05 05:04 281,788,928 DB2_QFIIDB_164_1_546757203

2005-01-06 05:04 116,072,960 DB2_QFIIDB_165_1_546843603

2005-01-08 05:04 300,474,880 DB2_QFIIDB_167_1_547016403

2005-01-09 05:04 3,596,800 DB2_QFIIDB_168_1_547102803

2005-01-10 05:04 3,662,336 DB2_QFIIDB_169_1_547189205

2005-01-11 05:04 519,537,152 DB1_QFIIDB_170_1_547275603

2005-01-12 05:04 112,280,064 DB2_QFIIDB_171_1_547362003

2005-01-13 05:04 94,863,872 DB2_QFIIDB_172_1_547448404

2005-01-15 05:04 199,836,160 DB2_QFIIDB_174_1_547621203

2005-01-16 05:04 72,442,368 DB2_QFIIDB_175_1_547707603

2005-01-17 05:04 10,600,960 DB2_QFIIDB_176_1_547794003

可以看到2004年12月31日確實作了0級備份,但是2005年1月7日1月14日0級備份卻都沒有生成,檢視RMAN備份的log檔案發現這兩天的0級備份都報告了“磁碟空間不足”的錯誤,也就是空閒的磁碟空間不足夠作一次0級備份,所以這兩天的備份全部沒有成功。而恰巧的是這個專案中並沒有使用catalog,而是隻使用了資料庫的控制檔案來儲存備份資訊的。而資料庫的control_file_record_keep_time是預設的7天,所以很明顯控制檔案中的備份資訊已經超過了記錄的儲存期限而被後續的備份資訊覆蓋了。

問題於是就簡化為如果控制檔案中不包含可用的備份資訊(甚或是控制檔案完全損壞),那麼如何恢復RMAN備份的資料庫?

這就需要使用Oracle線上文件中並沒有介紹的dbms_backup_restore包。

關於該包的安裝和介紹以及一些使用方法可以參看寫的一文。

本文則是一次完整地在真實環境中的恢復例項,並且使用了Fenng那篇文章中沒有提到的一些儲存過程。

1. 關閉資料庫

SQL> shutdown immediate;

2. 啟動資料庫到nomount狀態

SQL> startup nomount;

3. restore 0級備份檔案

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'E:ORACLEORADATAQFIIDBSYSTEM01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'E:ORACLEORADATAQFIIDBRBS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'E:ORACLEORADATAQFIIDBUSERS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'E:ORACLEORADATAQFIIDBSTK_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'E:ORACLEORADATAQFIIDBTOOLS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'E:ORACLEORADATAQFIIDBSTK_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>12,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>13,toname=>'E:ORACLEORADATAQFIIDBSTK_TS03.DBF');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB0_QFIIDB_159_1_546325203', params=>null);

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB0_QFIIDB_159_2_546325203', params=>null);

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB0_QFIIDB_159_3_546325203', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

4. restore 1級備份檔案

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.applySetDatafile;

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>01,toname=>'E:ORACLEORADATAQFIIDBSYSTEM01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>02,toname=>'E:ORACLEORADATAQFIIDBRBS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>03,toname=>'E:ORACLEORADATAQFIIDBUSERS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>04,toname=>'E:ORACLEORADATAQFIIDBSTK_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'E:ORACLEORADATAQFIIDBTOOLS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'E:ORACLEORADATAQFIIDBSTK_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'E:ORACLEORADATAQFIIDBSTK_TS03.DBF');

sys.dbms_backup_restore.applyBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB1_QFIIDB_170_1_547275603', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

5. restore 第一份2級備份檔案

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.applySetDatafile;

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>01,toname=>'E:ORACLEORADATAQFIIDBSYSTEM01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>02,toname=>'E:ORACLEORADATAQFIIDBRBS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>03,toname=>'E:ORACLEORADATAQFIIDBUSERS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>04,toname=>'E:ORACLEORADATAQFIIDBSTK_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'E:ORACLEORADATAQFIIDBTOOLS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'E:ORACLEORADATAQFIIDBSTK_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'E:ORACLEORADATAQFIIDBSTK_TS03.DBF');

sys.dbms_backup_restore.applyBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB2_QFIIDB_171_1_547362003', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

6. restore 第二份2級備份檔案

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.applySetDatafile;

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>01,toname=>'E:ORACLEORADATAQFIIDBSYSTEM01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>02,toname=>'E:ORACLEORADATAQFIIDBRBS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>03,toname=>'E:ORACLEORADATAQFIIDBUSERS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>04,toname=>'E:ORACLEORADATAQFIIDBSTK_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'E:ORACLEORADATAQFIIDBTOOLS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS01.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'E:ORACLEORADATAQFIIDBSTK_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'E:ORACLEORADATAQFIIDBSTK_INX_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'E:ORACLEORADATAQFIIDBSTK_HIS_IND_TS02.DBF');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'E:ORACLEORADATAQFIIDBSTK_TS03.DBF');

sys.dbms_backup_restore.applyBackupPiece(done=>done,handle=>'E:ORACLEORADATA.BAKDB2_QFIIDB_172_1_547448404', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

7. mount資料庫

SQL> alter database mount;

8. 恢復資料庫到drop user前的某一時間點

SQL> >recover database until time '2005-1-14 16:00:00';

9. 啟動資料庫

SQL> alter database open resetlogs;

10. 立刻作一次full備份

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=257094

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84835/,如需轉載,請註明出處,否則將追究法律責任。

相關文章