【翻譯自mos文章】當控制檔案的備份丟失時，怎麼restore database，mosrestore

【翻譯自mos文章】當控制檔案的備份丟失時，怎麼restore database，mosrestore

當控制檔案的備份丟失時，怎麼restore database？

來源於：
How to restore database when controlfile backup missing (文檔 ID 1438776.1)

適用於：

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-Sep-2013***

目標：
本文對如下的情況有協助：除了控制檔案備份不存在，其他的備份都存在，該情況下的database restore.

解決方案：
給出一個例子。
1. 查看當前資料庫結構：

RMAN> report schema;using target database control file instead of recovery catalogReport of database schemaList of Permanent Datafiles===========================File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 510 SYSTEM *** +DATA/ora102/datafile/system.257.7751266032 595 UNDOTBS1 *** +DATA/ora102/datafile/undotbs1.256.7751265613 250 SYSAUX *** +DATA/ora102/datafile/sysaux.258.7751266374 28 USERS *** +DATA/ora102/datafile/users.259.7751266535 50 USERS *** +DATA/ora102/datafile/users.262.776000421List of Temporary Files=======================File Size(MB) Tablespace Maxsize(MB) Tempfile Name---- -------- -------------------- ----------- --------------------1 29 TEMP 32767 /u01/app/oracle/oradata/ora102/ORA102/datafile/o1_mf_temp_7lqq1qko_.tmpRMAN> exitRecovery Manager complete

 

2.Create a dummy instance/ can use existing database to extract datafile 1 from backup piece and restore datafile 1 from backup piece

SQL> DECLARE  devtype varchar2(256);  done boolean;  BEGIN  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');  dbms_backup_restore.RestoreSetDatafile;  dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);  dbms_backup_restore.DeviceDeallocate; END;/

 

3.在包括datafile 1的情況下建立控制檔案：

SQL>!cat /u03/1.ctlCREATE CONTROLFILE REUSE DATABASE "ORA102" NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,GROUP 2 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,GROUP 3 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50MDATAFILE'/u03/datafile1.dbf'CHARACTER SET WE8ISO8859P1;SQL> @/u03/1.ctlControl file created.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u03/datafile1.dbfSQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

 

4.catalog 所有的backuppiece

[oracle@oel57 ~]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Thu Mar 8 11:55:58 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: ORA102 (DBID=396070408, not open)RMAN> catalog start with '/u03/backup/' noprompt;using target database control file instead of recovery catalogsearching for all files that match the pattern /u03/backup/List of Files Unknown to the Database=====================================File Name: /u03/backup/28n5bki6_1_1File Name: /u03/backup/ORA1122-backup-080312.logFile Name: /u03/backup/2dn5blsq_1_1File Name: /u03/backup/2cn5blrn_1_1File Name: /u03/backup/ora102-080312.logFile Name: /u03/backup/27n5bkd0_1_1cataloging files...cataloging doneList of Cataloged Files=======================File Name: /u03/backup/2dn5blsq_1_1File Name: /u03/backup/2cn5blrn_1_1List of Files Which Where Not Cataloged=======================================File Name: /u03/backup/28n5bki6_1_1RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122File Name: /u03/backup/ORA1122-backup-080312.logRMAN-07517: Reason: The file header is corruptedFile Name: /u03/backup/ora102-080312.logRMAN-07517: Reason: The file header is corruptedFile Name: /u03/backup/27n5bkd0_1_1RMAN-07518: Reason: Foreign database file DBID: 2353175563 Database Name: ORA1122RMAN> list backup;using target database control file instead of recovery catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------1 Full 1.05M DISK 00:00:00 08-MAR-12BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415Piece Name: /u03/backup/2dn5blsq_1_1Control File Included: Ckp SCN: 1320981 Ckp time: 08-MAR-12SPFILE Included: Modification time: 08-MAR-12BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------2 Full 107.40M DISK 00:00:00 08-MAR-12BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20120308T101415Piece Name: /u03/backup/2cn5blrn_1_1List of Datafiles in backup set 2File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 1320981 08-MAR-12 +DATA/ora102/datafile/system.257.7751266032 Full 1320981 08-MAR-123 Full 1320981 08-MAR-124 Full 1320981 08-MAR-125 Full 1320981 08-MAR-12RMAN>

5. 使用dbms_backup_restore package 來restore datafile。請注意：datafile的名字不重要，只要對於要恢複的datafile，是唯一的名字即可。

SQL>  -- use scriptDECLARE  devtype varchar2(256);  done boolean;BEGIN  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');  dbms_backup_restore.RestoreSetDatafile;  dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/u03/datafile4.dbf');  dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/u03/datafile2.dbf');  dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/u03/datafile3.dbf');  dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/u03/datafile5.dbf');  dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf');  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);  dbms_backup_restore.DeviceDeallocate;END;/PL/SQL procedure successfully completed.

 

中繼資料顯示只有datafile 1，我麼需要重建控制檔案以便帶上所有需要恢複的datafiles

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DATA/ora102/datafile/system.257.775126603SQL> shutdown immediate; <ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 209715200 bytesFixed Size 1272864 bytesVariable Size 142607328 bytesDatabase Buffers 58720256 bytesRedo Buffers 7114752 bytesSQL>!cat /u03/1.ctlCREATE CONTROLFILE REUSE DATABASE "ORA102" NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,GROUP 2 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,GROUP 3 '/u01/app/oracle/oradata/ora102/ORA102/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50MDATAFILE'/u03/datafile1.dbf','/u03/datafile4.dbf','/u03/datafile2.dbf','/u03/datafile3.dbf','/u03/datafile5.dbf'CHARACTER SET WE8ISO8859P1;SQL> @/u03/1.ctlControl file created.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u03/datafile1.dbf/u03/datafile2.dbf/u03/datafile3.dbf/u03/datafile4.dbf/u03/datafile5.dbf

6.若是該備份不是冷備份，那麼我們需要recover database，我們需要catalog 包括archivelog的backuppiece，然後restore archivelog，然後在recover

SQL> recover database until cancel;Media recovery complete.SQL> alter database open resetlogs;Database altered.SQL>

7. 一旦資料庫處於一致性狀態，我們可以用resetlogs的方式 open database

SQL>alter database open resetlogs;Database altered.SQL>

注意：
在multisection backup 的情況下，我們需要考慮所有的backuppiece（也就是所有的section）,使用initmsr函數來restore datafile

DECLAREdevtype varchar2(256);done boolean;BEGINdevtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');   dbms_backup_restore.RestoreSetDatafile;dbms_backup_restore.initmsr(1,'/ud1001/PROD/oradata/system01-test.dbf');dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ud1001/PROD/oradata/system01-test.dbf');dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_1_1.rman', params => null);dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_2_1.rman', params => null);END;/