使用dbms_backup_restore包恢复数据库

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:使用dbms_backup_restore包恢复数据库

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Oracle提供了一个包:DBMS_BACKUP_RESTORE包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.
由此可见,我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的
1、启动数据库到nomount状态

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 13:34:22 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes

2、恢复controlfile

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.restoreControlfileTo(cfname=>'/opt/oracle/oradata/ora11g/control01.ctl');
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_02mu1avd_1_1.rman', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /

PL/SQL procedure successfully completed.

3、恢复数据文件

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=>1,toname=>'/opt/oracle/oradata/ora11g/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/ora11g/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/ora11g/undotbs01.dbf');
  8    9   10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/ora11g/users01.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/ora11g/example01.dbf');
 12  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_01mu1aqq_1_1.rman', params=>null);
 13  sys.dbms_backup_restore.deviceDeallocate;
 14  END;
 15  /

PL/SQL procedure successfully completed.

4、启动数据库

SQL> alter database mount;
SQL> alter database recover until cancel using backup controlfile;
SQL> alter database open resetlogs;

5、特别说明
在oracle 10g及其以后版本中,因为rman中有catalog with start命令,可以实现rman备份的加载,所以不需要使用dbms_backup_restore包处理,在oracle 9i及其以前版本中,可能因为没有catalog库,控制文件中又没有了备份集信息,需要采用这种方法处理数据文件还原,然后根据实际情况,使用ALTER DATABASE REGISTER LOGFILE 添加日志,进行恢复

此条目发表在 rman备份/恢复 分类目录。将固定链接加入收藏夹。

使用dbms_backup_restore包恢复数据库》有 2 条评论

  1. 惜分飞 说:

    生成还原数据文件sql

    set pagesize 10000
    set lines 150
    select 'sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>' || file# ||
    ',toname=>' ||chr(39)|| name ||chr(39) || ');',
    'sys.dbms_backup_restore.applySetDatafile(dfnumber=>' || file# ||
    ',toname=>' ||chr(39)|| name ||chr(39) || ');'
    from v$datafile;
    
  2. 惜分飞 说:

    恢复归档日志方法

     declare
     devtype varchar2(256);
     done boolean;
     begin
     devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
     sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/oradata/rmanbackup/archive');
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>22);
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>23);
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>24);
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>25);
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>26);
     sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>27);
     sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/oradata/HB130000_12_588180557.bak',params=>null);
     sys.dbms_backup_restore.devicedeallocate;
     end;
     /