利用dbms_backup_restore函数来恢复数据文件

本实验对数据文件丢失，控制文件丢失，但是RMAN的备份信息和备份日志存在的时候使用。

下面是数据文件的丢失的恢复。

下面是RMAN的备份脚本。

[oracle@huang ~]$ cat /tmp/11.sql

run

{

  allocate channel c1 type disk;

  backup database include current controlfile;

}

下面是执行RMAN备份整个数据库。

[oracle@huang ~]$ rman target / @/tmp/11.sql log=/tmp/111.txt >>/dev/null;

下面是查看RMAN备份的日志。

[oracle@huang ~]$ vim /tmp/111.txt

Starting backup at 12-APR-10

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/opt/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00002 name=/opt/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00004 name=/opt/oracle/oradata/orcl/users01.dbf

channel c1: starting piece 1 at 12-APR-10

channel c1: finished piece 1 at 12-APR-10

piece handle=/opt/oracle/flash_recovery_area/ORCL/backupset/2010_04_12/o1_mf_nnndf_TAG20100412T225557_5w6f3xjm_.bkp tag=TAG20100412T225557 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:55

including current control file in backupset

including current SPFILE in backupset

piece handle=/opt/oracle/flash_recovery_area/ORCL/backupset/2010_04_12/o1_mf_ncsnf_TAG20100412T225557_5w6f5pyl_.bkp tag=TAG20100412T225557 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:05

Finished backup at 12-APR-10

released channel: c1

Recovery Manager complete.

根据上面的日志来下面这个脚本。前提是要RMAN备份的日志存在或你记得自己的数据文件和编号。

[oracle@huang ~]$ cat /tmp/data.sql

DECLARE

devtype varchar2(256);

done boolean;

BEGIN

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

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/opt/oracle/system01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/opt/oracle/sysaux01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/opt/oracle/undotbs01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/opt/oracle/users01.dbf');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/flash_recovery_area/ORCL/backupset/2010_04_12/o1_mf_nnndf_TAG20100412T225557_5w6f3xjm_.bkp', params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

下面是具体的执行。

 SQL> get /tmp/data.sql

  1  DECLARE

  2  devtype varchar2(256);

  3  done boolean;

  4  BEGIN

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

  6  sys.dbms_backup_restore.restoreSetDatafile;

  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/opt/oracle/system01.dbf');

  8  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/opt/oracle/sysaux01.dbf');

  9  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/opt/oracle/undotbs01.dbf');

 10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/opt/oracle/users01.dbf');

 11  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/flash_recovery_area/ORCL/backupset/2010_04_12/o1_mf_nnndf_TAG20100412T225557_5w6f3xjm_.bkp', params=>null);

 12  sys.dbms_backup_restore.deviceDeallocate;

 13* END;

SQL> /

PL/SQL procedure successfully completed.

查看是否生成了我们要的数据文件。

SQL> !ls /opt/oracle/system01.dbf;

/opt/oracle/system01.dbf

注意可能在上面的时候会出先下面的问题，退出当前的sqlplus会话，重新进入就可以解决问题。

*

ERROR at line 1:

ORA-19568: a device is already allocated to this session

ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 170

ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 145

ORA-06512: at line 5

  本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/294182，如需转载请自行联系原作者