控制檔案丟失的RMAN恢復

1. 做一次RMAN的資料庫全備
C:>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期一 8月 27 13:58:37 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ALEX (DBID=1906837469)

RMAN> run{
2> allocate channel c1 type disk;
3> backup full tag 'FullBackup' format 'd:ora_bakALEX_%U.dbf' database include current controlfile;
4> sql 'alter system archive log current';
5> release channel c1;
6> }

[@more@]

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=142 devtype=DISK

Starting backup at 27-8月 -07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:ORACLEPRODUCT10.2.0ORADATAALEXSYSTEM01.DBF
input datafile fno=00003 name=D:ORACLEPRODUCT10.2.0ORADATAALEXSYSAUX01.DBF
input datafile fno=00002 name=D:ORACLEPRODUCT10.2.0ORADATAALEXUNDOTBS01.DBF
input datafile fno=00004 name=D:ORACLEPRODUCT10.2.0ORADATAALEXUSERS01.DBF
channel c1: starting piece 1 at 27-8月 -07
channel c1: finished piece 1 at 27-8月 -07
piece handle=D:ORA_BAKALEX_03IQEJ1A_1_1.DBF tag=FULLBACKUP comment=NONE
channel c1: backup set complete, elapsed time: 00:00:56
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 27-8月 -07
channel c1: finished piece 1 at 27-8月 -07
piece handle=D:ORA_BAKALEX_04IQEJ32_1_1.DBF tag=FULLBACKUP comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 27-8月 -07

sql statement: alter system archive log current

released channel: c1

2. 關閉資料庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3. 刪除所有的資料檔案和控制檔案

4. unmount資料庫
SQL> startup force nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes

5. 呼叫dbms_backup_restore包恢復控制檔案（RMAN直接呼叫該包）
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 --分配一個device channel ，因為使用的作業系統檔案，所以這裡為空，如果是從磁帶上恢復要用"sbt_tape"
6 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
7 --開始restore
8 sys.dbms_backup_restore.restoreSetDatafile;
9 --指出待恢復檔案目標儲存位置
10 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'D:oracleproduct10.2.0oradataalexControl01.ctl');
11 --從哪個備份片中恢復
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:ora_bakALEX_04IQEJ32_1_1.DBF', params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /

PL/SQL procedure successfully completed.

6. 呼叫dbms_backup_restore包恢復資料檔案
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>00001,toname=>'D:oracleproduct10.2.0oradataalexSYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>00002,toname=>'D:oracleproduct10.2.0oradataalexUNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>00003,toname=>'D:oracleproduct10.2.0oradataalexSYSAUX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>00004,toname=>'D:oracleproduct10.2.0oradataalexUSERS01.DBF');
11 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'d:ora_bakALEX_03IQEJ1A_1_1.DBF', params=>null);
12 sys.dbms_backup_restore.deviceDeallocate;
13 END;
14 /

PL/SQL procedure successfully completed.

7. 介質恢復資料庫，要求resetlogs
SQL> startup mount force
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 547460 generated at 08/27/2007 14:03:23 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0ORADATAALEXARCHIVEARC00003_0631720033.001
ORA-00280: change 547460 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
alex OPEN

8. 做一次全備

9. 小結：控制檔案必須分開備份，RMAN備份的日誌也很重要