ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 移除迁移遗留的无法访问的RMAN过期备份

移除迁移遗留的无法访问的RMAN过期备份

原创 Linux操作系统 作者:junsansi 时间:2009-02-01 15:39:18 1 删除 编辑


  这里提供一种并不常见的NBU+RMAN备份问题的解决方案。这是最近一项迁移计划的实施带来的问题,迁移非常成功,但是迁移后创建备份计划时遇到问题,由于数据库迁移前采用带库备份,迁移后考虑新机硬盘空间充足,改为本地硬盘备份,但是迁移之前的备份信息并没有删除,迁移后的服务器未配置连接带库,因此迁移前的备份就无法直接删除了。

    如下:

    RMAN> report obsolete device type sbt;


    using target database controlfile instead of recovery catalog
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 7 days
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           11301  22-12月-08       
      Backup Piece       11301  22-12月-08         c-1984618042-20081222-00
    Backup Set           11304  22-12月-08       
      Backup Piece       11304  22-12月-08         c-1984618042-20081222-01
    Backup Set           11306  22-12月-08       
      Backup Piece       11306  22-12月-08         c-1984618042-20081222-02
    Backup Set           11312  23-12月-08       
      Backup Piece       11312  23-12月-08         c-1984618042-20081223-00
    Backup Set           11315  23-12月-08       
      Backup Piece       11315  23-12月-08         c-1984618042-20081223-01
    Backup Set           11317  23-12月-08       
      Backup Piece       11317  23-12月-08         c-1984618042-20081223-02
    Backup Set           11323  24-12月-08       
      Backup Piece       11323  24-12月-08         c-1984618042-20081224-00
    Backup Set           11326  24-12月-08       
      Backup Piece       11326  24-12月-08         c-1984618042-20081224-01
    Backup Set           11328  24-12月-08       
      Backup Piece       11328  24-12月-08         c-1984618042-20081224-02
    Backup Set           11334  25-12月-08       
      Backup Piece       11334  25-12月-08         c-1984618042-20081225-00
    Backup Set           11335  25-12月-08       
      Backup Piece       11335  25-12月-08         al_11445_1_674426605
    Backup Set           11336  25-12月-08       
      Backup Piece       11336  25-12月-08         al_11446_1_674426911
    Backup Set           11337  25-12月-08       
      Backup Piece       11337  25-12月-08         c-1984618042-20081225-01
    Backup Set           11339  25-12月-08       
      Backup Piece       11339  25-12月-08         c-1984618042-20081225-02
    ............
    ..............
    ................

    直接delete会报这个错误:

    RMAN> delete obsolete device type sbt;

    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 7 days
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of delete command at 02/01/2009 13:42:24
    ORA-19554: error allocating device, device type: SBT_TAPE, device name:
    ORA-27211: Failed to load Media Management Library
    Additional information: 25

    你想说crosscheck一下?

    RMAN> crosscheck backup;

    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=33 devtype=DISK
    released channel: ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of crosscheck command at 02/01/2009 14:32:18
    ORA-19554: error allocating device, device type: SBT_TAPE, device name:
    ORA-27211: Failed to load Media Management Library
    Additional information: 25

    RMAN> configure default device type to sbt;

    old RMAN configuration parameters:
    CONFIGURE DEFAULT DEVICE TYPE TO DISK;
    new RMAN configuration parameters:
    CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
    new RMAN configuration parameters are successfully stored

    RMAN> crosscheck backup;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of crosscheck command at 02/01/2009 14:33:29
    ORA-19554: error allocating device, device type: SBT_TAPE, device name:
    ORA-27211: Failed to load Media Management Library
    Additional information: 2

  显然也不行啊,因为无法连接到带库设备,自动也无法验证备份集。


  正如开往篇所说,这种问题并不常见,当然这也难不住俺,三思随便动了几个脑细胞,就想出如下几种解决方式:

  1、重建控制文件。
  绝对好使,但是代价较大,不仅之前备份统统无效,而且还需要停数据库服务,由于要操作的是一台较重要的生产服务器,因此这种方法不可行。

  2、通过rman命令手工将备份集状态置为unavailable。
  RMAN提供了一个change命令,可以修改备份集状态为AVAILABLE/UNAVAILABLE/UNCATALOG等等。手工使用该命令将所有不可访问的备份集置为unavailable,修改完状态后等待控制文件自动更新覆盖失效记录即可。这种方式最大的问题是见效太慢。
  注意,nocatalog模式才能使用这种方式。

  3、借助dbms_backup_restore包直接删除备份片段。
  dbms_backup_restore是oracle提供的系统包,官方文档中并没有提供该包的说明,但实际上rman在执行备份/恢复操作时就是调用的该包,该包提供了数十个满足不同需求的包/函数,功能那是岗岗的。该过程排毒养颜而且无任何毒副作用,不仅疗效好见效快老少皆宜,口感也非常好呢,实在是排解疑难,必备良工啊~~

    这里呢三思就通过dbms_backup_restore.deleteBackupPiece过程实战演示删除无法访问的备份集。
    dbms_backup_restore.deleteBackupPiece的定义如下:

    PROCEDURE DELETEBACKUPPIECE
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ ------------
     RECID                          NUMBER                  IN
     STAMP                          NUMBER                  IN
     HANDLE                         VARCHAR2                IN
     SET_STAMP                      NUMBER                  IN
     SET_COUNT                      NUMBER                  IN
     PIECENO                        BINARY_INTEGER          IN
     PARAMS                         VARCHAR2                IN     DEFAULT NULL

    前6项参数都需要我们指定,这些参数的参数值可以从v$backup_piece中获得。
    由前report可知需要处理的piece ID为11301->11478:

    SQL> select recid,stamp,set_stamp,set_count,handle,piece# from v$backup_piece where recid between 11301 and 11478;

         RECID      STAMP  SET_STAMP  SET_COUNT HANDLE                             PIECE#
    ---------- ---------- ---------- ---------- ------------------------------ ----------
         11301  674167385  674167385      11411 c-1984618042-20081222-00                1
         11304  674168006  674168005      11414 c-1984618042-20081222-01                1
         11306  674168089  674168089      11416 c-1984618042-20081222-02                1
         11312  674253707  674253707      11422 c-1984618042-20081223-00                1
         11315  674254316  674254315      11425 c-1984618042-20081223-01                1
         11317  674254397  674254397      11427 c-1984618042-20081223-02                1
         .........
         ...........
         ..............

    测试一下,先处理recid=11301的记录:

    SQL> DECLARE
      2   devtype varchar2(256);
      3   BEGIN
      4   devtype:=dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
      5   dbms_backup_restore.deleteBackupPiece(recid => 11301,stamp => 674167385,handle => 'c-1984618042-20081222-00',set_stamp => 674167385 ,set_count => 11411 ,pieceno => 1);
      6   dbms_backup_restore.deviceDeallocate;
      7  END;
      8  /

    PL/SQL procedure successfully completed.

    转到rman中再看一下:

    RMAN> list backupset 11301;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of list command at 02/01/2009 15:21:27
    RMAN-06160: no backup pieces found for backup set key: 11301

    ok,11301已经被成功删除。

    dbms_backup_restore.deleteBackupPiece一次只能处理一条,每次手动输入未免繁琐,我们可以通过sql语句直接生成处理脚本如下:

    SQL> select 'dbms_backup_restore.deleteBackupPiece(' || recid || ',' || stamp || ',''' || HANDLE || ''',' || SET_STAMP || ',' || SET_COUNT || ',' || PIECE# || ');' sqlt
      2    from v$backup_piece
      3*  where recid between 11301 and 11478
    SQL> /

    SQLT
    ----------------------------------------------------------------------------------------------------
    dbms_backup_restore.deleteBackupPiece(11301,674167385,'c-1984618042-20081222-00',674167385,11411,1);
    dbms_backup_restore.deleteBackupPiece(11304,674168006,'c-1984618042-20081222-01',674168005,11414,1);
    dbms_backup_restore.deleteBackupPiece(11306,674168089,'c-1984618042-20081222-02',674168089,11416,1);
    dbms_backup_restore.deleteBackupPiece(11312,674253707,'c-1984618042-20081223-00',674253707,11422,1);
    dbms_backup_restore.deleteBackupPiece(11315,674254316,'c-1984618042-20081223-01',674254315,11425,1);
    .............
    .................
    ....................

    复制到declare块执行即可。

  上述操作执行完之后重新,在rman中重新crosscheck及report验证,如无错误,则问题解决,详细过程限于篇幅此处不再描述。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7607759/viewspace-544956/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-21

  • 博文量
    640
  • 访问量
    4120428