摘自： 三思笔记

这里提供一种并不常见的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

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

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

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

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

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