• 利用DBMS_BACKUP_RESETORE从备份中恢复控制文件数据文件和归档日志


    在Oracle 816 以后的版本中,Oracle提供了一个包: DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE,由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建,
    catproc.sql 脚本运行后会调用这两个包。该包是Oracle服务器和操作系统之间IO操作的接口,由恢复管理器直接调用。


    在catalog的情况下,采用RMAN备份,如果丢失了控制文件,可以直接用RMAN恢复:
       SQL>startup nomount;
       ORACLE instance started.
       Total System Global Area  101784276 bytes
       Fixed Size                   453332 bytes
       Variable Size              75497472 bytes
       Database Buffers           25165824 bytes
       Redo Buffers                 667648 bytes
       SQL>

       c:\>rman target xxx catalog xxx
       Recovery Manager: Release 9.2.0.1.0 - Production
       Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
       connected to target database: zhs16gbk (not mounted)
       connected to recovery catalog database

       RMAN>restore controlfile from 'C:\SOFTWARE\ORACLE\ORA92\DATABASE\C-165830944-20070412-01';


    在nocatalog的情况下,采用RMAN备份,因为控制文件中包含了rman的备份信息,所以如果丢失了控制文件,不能用平常的方法来恢复。我们可以利用DBMS_BACKUP_RESTORE的包来恢复。该包在nomount下即可运行,也就是说,只需要启动到nomount下就可以利用它来恢复控制文件或者数据文件以及归档日志。

       FUNCTION DeviceAllocate(
          type     IN VARCHAR2 default NULL,
          name     IN VARCHAR2 default NULL,
          ident    IN VARCHAR2 default NULL,
          noio     IN BOOLEAN  default FALSE,
          params   IN VARCHAR2 default NULL ) RETURN VARCHAR2;

       PROCEDURE RestoreControlFileTo(cfname IN VARCHAR2);
       PROCEDURE RestoreDataFileTo(dfnumber IN binary_integer, toname IN VARCHAR2 default NULL);

    1. 从备份中恢复控制文件
       DECLARE
          devtype VARCHAR2(256);
          done BOOLEAN;
       BEGIN
          --分配一个device channel,如果使用的操作系统文件,type就为空,如果是从磁带上恢复要用 "sbt_tape";
          devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'t1');
          --指明开始restore
          SYS.DBMS_BACKUP_RESTORE.restoreSetDatafile;
          --指出待恢复文件目标存储位置;
          SYS.DBMS_BACKUP_RESTORE.RestoreControlFileTo(cfname=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL01.CTL');
          --SYS.DBMS_BACKUP_RESTORE.RestoreControlFileTo(cfname=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL02.CTL');
          --SYS.DBMS_BACKUP_RESTORE.RestoreControlFileTo(cfname=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL03.CTL');
          --指定备份集的位置
          SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'D:\DBBAK\CK_52_1_623326577', params=>null);
          --释放通道
          SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
       END;

        DECLARE
              devtype VARCHAR2(256);
              done BOOLEAN;
           BEGIN
              devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'t1'); 
              SYS.DBMS_BACKUP_RESTORE.restoreSetDatafile;
              SYS.DBMS_BACKUP_RESTORE.RestoreSPFileTo(sfname=>'D:\CONTROL01.CTL');
              SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'h:\rmanbackup\CNTR_20080401_2699_1_650869237', params=>null);
              SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
           END;

    2. 从备份中恢复数据文件(数据文件可以从v$datafile中获得)
       A. 恢复0级备份中的数据文件
          DECLARE
             devtype VARCHAR2(256);
             done BOOLEAN;
          BEGIN
             devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate (type=>'',ident=>'t1');
             SYS.DBMS_BACKUP_RESTORE.restoreSetDatafile;
             SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>1,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF');
             SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>2,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF');
             SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>3,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF');
             SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>4,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF');
             SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>5,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF');
             SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\BK_1_1_623326719', params=>null);
             SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
          END;
       B. 恢复增量备份中的数据文件
          DECLARE
             devtype VARCHAR2(256);
             done BOOLEAN;
          BEGIN
             devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate (type=>'',ident=>'t1');
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile;
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>1,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF');        
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>2,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF');       
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>3,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF');          
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>4,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF');         
             SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>5,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF');         
             SYS.DBMS_BACKUP_RESTORE.applyBackupPiece(done=>done,handle=>'D:\DBBAK\BK_1_1_623326719', params=>null);
             SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
          END;

          (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; )

    3. 从备份中恢复归档日志
       DECLARE
          devtype VARCHAR2(256);
          done BOOLEAN;
       begin
          devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'t1');
          SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>42);
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>43);
          SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_3_1_623326755',params=>null);
          SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
       end;
      
       DECLARE
          devtype VARCHAR2(256);
          done BOOLEAN;
       begin
          devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'t1');
          SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>44);
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>45);
          SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_4_1_623326982',params=>null);
          SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
       end;

       OR (以下是将这两个备份文件中归档日志全部恢复出来)
       DECLARE
          devtype VARCHAR2(256);
          done BOOLEAN;
       BEGIN
          devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'T1');
          SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLogRange;
          SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_3_1_623326755',params=>null);
          SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
       END;

       DECLARE
          devtype VARCHAR2(256);
          done BOOLEAN;
       BEGIN
          devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'T1');
          SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
          SYS.DBMS_BACKUP_RESTORE.restoreArchivedLogRange;
          SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_4_1_623326982',params=>null);
          SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
       END;

  • 相关阅读:
    SKAHThe Secret Of Excellence
    【SQLite备忘】sqlite设置自增字段
    实现自定义字段的几种方式(转载)
    7个促进Vue3开发更友好的插件(转载)
    《无垠的太空(9).利维坦陨落》第二十六章:吉姆
    《无垠的太空(9).利维坦陨落》翻译过半,求P电子书封面
    《无垠的太空(9).利维坦陨落》第二十五章:田中
    一读《浮生六记》
    《无垠的太空(9).利维坦陨落》第二十七章:艾薇
    《无垠的太空(9).利维坦陨落》第二十四章:灯塔和守护者
  • 原文地址:https://www.cnblogs.com/jasonsfu/p/1152813.html
Copyright © 2020-2023  润新知