dbms_backup_restore Package

    在数据库恢复的过程中如果有rman备份集，可以使用dbms_backup_restore包从备份集中还原文件，下面的例子说明所有控制文件损坏、某个数据文件丢失，有数据文件备份集的情况下如何恢复数据库：
    DB：Oracle 11g 11.2.0.3.0 X86_64,OS RHEL 6.3 X86_64,未使用Recovery Catalog

1.关闭控制文件自动备份
RMAN> configure controlfile autobackup off;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

2.创建测试表空间
SYS@orcl>create tablespace tstest datafile '/u01/app/oracle/oradata/orcl/tstest01.dbf' size 10m;
Tablespace created.

SYS@orcl>select file#,status,name from v$datafile;
 FILE# STATUS  NAME
---------- ------- ---------------------------------------------
     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
     5 ONLINE  /u01/app/oracle/oradata/orcl/example01.dbf
     6 ONLINE  /u01/app/oracle/oradata/orcl/tstest01.dbf

6 rows selected.

3.备份数据库
RMAN> backup database;

Starting backup at 06-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/tstest01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-JAN-13
channel ORA_DISK_1: finished piece 1 at 06-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_nnndf_TAG20130106T190623_8glpokbm_.bkp tag=TAG20130106T190623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 06-JAN-13
channel ORA_DISK_1: finished piece 1 at 06-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_ncsnf_TAG20130106T190623_8glprm9z_.bkp tag=TAG20130106T190623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-JAN-13

4.删除新建表空间的数据文件和破坏控制文件
[oracle@ora orabak]$ rm /u01/app/oracle/oradata/orcl/tstest01.dbf
[oracle@ora orabak]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control01.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00571965 s, 917 MB/s
[oracle@ora orabak]$ dd if=/dev/zero of=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00548415 s, 956 MB/s

5.启动数据库到nomount状态
SYS@orcl>startup force nomount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size            2228784 bytes
Variable Size          956304848 bytes
Database Buffers      637534208 bytes
Redo Buffers            7344128 bytes

6.执行dbms_backup_restore包，从备份集中还原数据文件
SYS@orcl>declare
devtype varchar2(256);
done boolean;
begin
devtype := dbms_backup_restore.DeviceAllocate (type =>'',ident => 't1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname =>'/u01/app/oracle/oradata/orcl/tstest01.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_nnndf_TAG20130106T190623_8glpokbm_.bkp', params => null);
dbms_backup_restore.DeviceDeallocate;
end;


7.执行'create controlfile'命令创建控制文件
---因为所有归档日志和在线日志都没有损坏，所以使用'NORESETLOGS  ARCHIVELOG'创建控制文件
SYS@orcl>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf',
  '/u01/app/oracle/oradata/orcl/tstest01.dbf'
  CHARACTER SET AL32UTF8;

Control file created.

8.执行'recover database'恢复数据库
RMAN> recover database;

Starting recover at 06-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/06/2013 19:20:11
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 1118760
---提示12号归档日志找不到

---查询视图，12号redo日志是'CURRENT'状态，没有归档
SYS@orcl>select group#,sequence#,a.status,member from v$log a join v$logfile using(group#);

 GROUP#  SEQUENCE# STATUS           MEMBER
---------- ---------- ---------------- ----------------------------------------
     1       10 INACTIVE           /u01/app/oracle/oradata/orcl/redo01.log
     3       12 CURRENT           /u01/app/oracle/oradata/orcl/redo03.log
     2       11 INACTIVE           /u01/app/oracle/oradata/orcl/redo02.log

---在sqlplus下执行'recover database'命令
SYS@orcl>recover database using backup controlfile;
ORA-00279: change 1118760 generated at 01/06/2013 19:06:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_06/o1_mf_1_12_%u_.arc
ORA-00280: change 1118760 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo03.log                   ---输入12号归档日志的路径
Log applied.
Media recovery complete.


9.以resetlogs打开数据库
SYS@orcl>alter database open resetlogs;
Database altered.

SYS@orcl>select file#,status,name from v$datafile;
 FILE# STATUS  NAME
---------- ------- ---------------------------------------------
     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
     5 ONLINE  /u01/app/oracle/oradata/orcl/example01.dbf
     6 ONLINE  /u01/app/oracle/oradata/orcl/tstest01.dbf

6 rows selected.

    dbms_backup_restore包可以用来从备份集中还原数据文件、控制文件、归档日志，而且也可以完成备份还原工作，但是oracle并没有提供官方文档说明，$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql是dbms_backup_restore包的脚本。
    像上面使用的dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname =>'/u01/app/oracle/oradata/orcl/tstest01.dbf')参数分别是数据文件的FILE_ID#和恢复文件的绝对路径，在后面还可以加上恢复数据文件到哪个表空间的参数，例如dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname =>'/u01/app/oracle/oradata/orcl/tstest01.dbf',tsname =>'TSTEST')。
    和还原相关的过程有：
    RestoreArchivedLog              --从备份集中还原归档日志
    RestoreArchivedLogRange       --从备份集中按照scn号的范围还原归档日志
    RestoreControlefileTo            --从备份集中还原控制文件
    RestoreDataFileTo                 --从备份集中还原数据文件

---从备份集中还原归档日志
declare
devtype varchar2(256);
done boolean;
begin
devtype := dbms_backup_restore.DeviceAllocate (type =>'',ident => 't1');
dbms_backup_restore.RestoreSetArchivedLog;    --设置恢复对话
dbms_backup_restore.RestoreArchivedLog(thread =>1 ,sequence =>9);   --日志的线程号和日志号
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_annnn_TAG20130106T200618_8glt5v7f_.bkp', params => null);    --指定使用的备份集
dbms_backup_restore.DeviceDeallocate;
end;


---从备份集中按scn范围还原归档日志
declare
devtype varchar2(256);
done boolean;
begin
devtype := dbms_backup_restore.DeviceAllocate (type =>'',ident => 't1');
dbms_backup_restore.RestoreSetArchivedLog;    --设置恢复对话
dbms_backup_restore.RestoreArchivedLogRange(log_change =>1125933 ,high_change =>1126275);  --日志的线程号和日志号
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_annnn_TAG20130106T200618_8glt5v7f_.bkp', params => null);    --指定使用的备份集
dbms_backup_restore.DeviceDeallocate;
end;


---从备份集中还原控制文件
declare
devtype varchar2(256);
done boolean;
begin
devtype := dbms_backup_restore.DeviceAllocate (type =>'',ident => 't1');
dbms_backup_restore.RestoreSetDatafile;        --设置恢复对话
dbms_backup_restore.RestoreControlfileTo(cfname =>'/home/oracle/contorl03.ctl'); --恢复日志到指定路径
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_06/o1_mf_ncnnf_TAG20130106T201850_8gltxcnk_.bkp', params => null);
dbms_backup_restore.DeviceDeallocate;
end;