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

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

如下：

RMAN> report obsolete device type sbt;


using target database controlfile instead of recovery catalog
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

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

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

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

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

　　３、借助dbms_backup_restore包直接删除备份片段。
　　dbms_backup_restore是提供的系统包，官方文档中并没有提供该包的说明，但实际上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

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
     .........
     ...........
     ..............

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> 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验证，如无错误，则问题解决，详细过程限于篇幅此处不再描述。