转：Oracle Rman の dbms_backup_restore
之前看文章，说建议数据库用rman备份的话，最好采用catalog数据库，将备份信息保留在catalog库中。对于没有catalog的数据库rman备份，备份信息将保留在控制文件中。
因此如果控制文件丢失，还原起来就比较麻烦，需要用dbms_backup_restore来进行恢复（恢复方式见文章的附录），其实想想，dbms_backup_restore其实也没那么常用了。因为：
1、控制文件如果有自动备份，那么可以从自动备份还原。
2、如果有单独备份控制文件，那么可以从这个单独备份的backupset处还原。
3、如果之前有做数据库全备，全备中就包含了控制文件和spfile的备份，可以从全备中还原。
4、另外还可以从控制文件的snapshot还原。
5、从上面的四种方法都无法还原控制文件，那么只能从备份集restore数据文件以及arch，然后重建控制文件，再resetlogs打开数据库。
====================================
附录（在控制文件都无法恢复的情况，用dbms_backup_restore restore数据文件，再重建控制文件，在open resetlogs）：
SQL> l
1* select count(*) from t1
SQL> /
COUNT(*)
----------
1024
SQL>
SQL>
SQL>
SQL> insert into t1 select * from t1;
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> select count(*) from t1;
COUNT(*)
----------
8192
SQL>
SQL>--commit前数据为1024行，insert到8192行，但是未commit
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:/Documents and Settings/Administrator>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 9 16:53:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4045875789)
RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> incremental level=0
5> filesperset 5
6> format 'df_%s_%p_%t'
7> (database);
8> sql 'alter system archive log current';
9> release channel c1;
10> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=146 devtype=DISK
Starting backup at 09-SEP-10
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF
input datafile fno=00002 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF
input datafile fno=00003 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF
input datafile fno=00004 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/DF_19_1_729276821 tag=TAG20100909T165341 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:50
channel c1: starting incremental level 0 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 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/DF_20_1_729276871 tag=TAG20100909T165341 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-SEP-10
sql statement: alter system archive log current
released channel: c1
RMAN>
RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> filesperset 20
5> format 'arch_%s_%p_%t'
6> (archivelog all delete input);
7> }
allocated channel: c1
channel c1: sid=146 devtype=DISK
Starting backup at 09-SEP-10
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=71 recid=42 stamp=729276793
input archive log thread=1 sequence=72 recid=43 stamp=729276794
input archive log thread=1 sequence=73 recid=44 stamp=729276795
input archive log thread=1 sequence=74 recid=45 stamp=729276878
input archive log thread=1 sequence=75 recid=46 stamp=729276880
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/ARCH_21_1_729276880 tag=TAG20100909T165440 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archive log(s)
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_71_68K83SGM_.ARC recid=42 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_72_68K83TF4_.ARC recid=43 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_73_68K83VG3_.ARC recid=44 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_74_68K86GD3_.ARC recid=45 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_75_68K86HW7_.ARC recid=46 stamp=7292
Finished backup at 09-SEP-10
released channel: c1
RMAN>
RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> filesperset 20
5> format 'ctrl_%s_%p_%t'
6> current controlfile;
7> }
allocated channel: c1
channel c1: sid=146 devtype=DISK
Starting backup at 09-SEP-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/CTRL_22_1_729276884 tag=TAG20100909T165444 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-SEP-10
released channel: c1
RMAN>
RMAN>--备份全库，arch文件，控制文件。其实后面我们可以从全库备份或者控制文件备份还原的，但是这里我们不演示用这种方式还原，我们用dbms_backup_restore包来做还原
RMAN> exit
Recovery Manager complete.
C:/Documents and Settings/Administrator>exit
SQL>
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 9 17:20:29 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 79692940 bytes
Database Buffers 121634816 bytes
Redo Buffers 7139328 bytes
SQL>
SQL>--开始用dbms_backup_restore包来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=>01,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF');
10 sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF');
11 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/DF_19_1_729276821',params=>null);
12 sys.dbms_backup_restore.devicedeallocate;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>--重建控制文件：
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO03.LOG' SIZE 50M
11 DATAFILE
12 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF',
13 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF',
14 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF',
15 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF'
16 CHARACTER SET ZHS16GBK
17 ;
Control file created.
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 1239559 generated at 09/09/2010 16:53:41 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_74_%U_.ARC
ORA-00280: change 1239559 for thread 1 is in sequence #74
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
SQL> --需要74号arch来做recover
SQL> --restore 74号arch
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.restoresetarchivedlog(destination=>'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09');
7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>74);
8 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/ARCH_21_1_729276880',params=>null);
9 sys.dbms_backup_restore.devicedeallocate;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
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.restoresetarchivedlog(destination=>'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09');
7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>75);
8 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/ARCH_21_1_729276880',params=>null);
9 sys.dbms_backup_restore.devicedeallocate;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> recover database using backup controlfile;
ORA-00279: change 1239644 generated at 09/09/2010 16:54:38 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_75_%U_.ARC
ORA-00280: change 1239644 for thread 1 is in sequence #75
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:/oracle/product/10.2.0/flash_recovery_area/ORA10G/ARCHIVELOG/2010_09_09/0727003661_001_00075.ARC
ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_76_%U_.ARC
ORA-00280: change 1239656 for thread 1 is in sequence #76
ORA-00278: log file
'D:/oracle/product/10.2.0/flash_recovery_area/ORA10G/ARCHIVELOG/2010_09_09/07270
03661_001_00075.ARC' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF
_1_76_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>
SQL>
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF'
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_76_%U_.ARC
ORA-00280: change 1239656 for thread 1 is in sequence #76
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: cannot open archived log 'cancel;'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) The system cannot find the file specified.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from test.t1;
COUNT(*)
----------
1024
SQL>--我们看到还原到crash前未commit的状态。
