如何删除控制文件中rman备份到磁带的备份集信息
SQL> select name,block_size*file_size_blks/1024/1024 bytes from v$controlfile;NAME BYTES------------------------------ ----------/oracle/app/data/control01.ctl 66.46875/oracle/app/data/control02.ctl 66.46875
最初由于控制文件太大了,想要清理一下，控制文件中记录的备份集信息有9000多个，由于该控制文件是拷贝过来的，所以绝大部分的备份是不存在的，想要删除无效备份记录.
删除流程如下：
RMAN> crosscheck backup;using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1634 device type=DISKspecification does not match any backup in the repositoryRMAN> delete expired backup;using channel ORA_DISK_1specification does not match any backup in the repository----很疑惑，为什么没有匹配的备份继续尝试删除过时备份obsoleteRMAN>delete obsoleteBackup Piece 9649 17-SEP-20arch_dXXX_u0ivakcnk_s11282_p1_t1051341556Backup Set 9656 20-SEP-20 Backup Piece 9656 20-SEP-20bk_dXXX_u1hvasgcv_s11313_p1_t1051607455Backup Set 9657 20-SEP-20 Backup Piece 9657 20-SEP-20bk_dXXX_u1ivasgdv_s11314_p1_t1051607487Backup Set 9661 20-SEP-20 Backup Piece 9661 20-SEP-20arch_dXXX_u1kvat9se_s11316_p1_t1051633550Backup Set 9666 21-SEP-20 Backup Piece 9666 21-SEP-20bk_dXXX_u1vvau6d5_s11327_p1_t1051662757Backup Set 9667 21-SEP-20 Backup Piece 9667 21-SEP-20bk_dXXX_u20vau6dn_s11328_p1_t1051662775Backup Set 9675 21-SEP-20 Backup Piece 9675 21-SEP-20arch_dXXX_u21vauj0l_s11329_p1_t1051675669Backup Set 9676 21-SEP-20 Backup Piece 9676 21-SEP-20ctrl_dXXX_u26vaumdb_s11334_p1_t1051679147Backup Set 9681 22-SEP-20 Backup Piece 9681 22-SEP-20bk_dXXX_u2evb0rld_s11342_p1_t1051750061Backup Set 9682 22-SEP-20 Backup Piece 9682 22-SEP-20bk_dXXX_u2fvb0rmd_s11343_p1_t1051750093Backup Set 9691 22-SEP-20 Backup Piece 9691 22-SEP-20ctrl_dXXX_u2lvb17e7_s11349_p1_t1051762119Backup Set 9697 22-SEP-20 Backup Piece 9697 22-SEP-20bk_dXXX_u2tvb3d3k_s11357_p1_t1051833460RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of delete command at 09/23/ 17:00:04RMAN-06091: no channel allocated for maintenance (of an appropriate type)查看备份信息：RMAN> list backup summary;List of Backups===============KeyTY LV S Device Type Completion Time #Pieces #Copies Compressed Tag9669 B 1 A SBT_TAPE 21-SEP-20 1 1 NO TAGT1815439670 B 1 A SBT_TAPE 21-SEP-20 1 1 NO TAGT1815439671 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409672 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409673 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409674 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409675 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT040740......9702 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039703 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039704 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039705 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039706 B A U SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039707 B F A SBT_TAPE 23-SEP-20 1 1 NO TAGT041616
可以看出这些备份记录都属于磁带上的备份
错误分析：
出现这样的错误是因为这些备份集处于SBT磁带机上，在控制文件中仍然记录了一些被分在磁带上的备份集信息，因为连不上磁带机, 所以没法用类似于delete obsolete 或者 delete backupset xxx这样的命令去删除这些备份集的信息，需要手动分配磁带类型通道才可以删除。
手动分配磁带类型通道：
从网上找到分配磁带通道的方式RMAN> allocate channel for maintenance device type sbtparms 'SBT_LIBRARY=Oracle.disksbt,ENV=(BACKUP_DIR=/tmp)';allocated channel: ORA_MAINT_SBT_TAPE_4channel ORA_MAINT_SBT_TAPE_4: SID=2131 device type=SBT_TAPEchannel ORA_MAINT_SBT_TAPE_4: WARNING: Oracle Test Disk APIRMAN> delete obsolete;再次查看备份集信息，发现居然还有SBT类型的备份，并没有清理RMAN> list backup summary;List of Backups===============KeyTY LV S Device Type Completion Time #Pieces #Copies Compressed Tag9669 B 1 A SBT_TAPE 21-SEP-20 1 1 NO TAGT1815439670 B 1 A SBT_TAPE 21-SEP-20 1 1 NO TAGT1815439671 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409672 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409673 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409674 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT0407409675 B A A SBT_TAPE 21-SEP-20 1 1 NO TAGT040740......9702 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039703 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039704 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039705 B A A SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039706 B A U SBT_TAPE 23-SEP-20 1 1 NO TAGT0330039707 B F A SBT_TAPE 23-SEP-20 1 1 NO TAGT041616
尝试再次手动分配磁带通道
RMAN> allocate channel for maintenance device type sbt parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';allocated channel: ORA_MAINT_SBT_TAPE_4channel ORA_MAINT_SBT_TAPE_4: SID=2131 device type=SBT_TAPEchannel ORA_MAINT_SBT_TAPE_4: WARNING: Oracle Test Disk API注：如果曾经使用过SBT，现在没有磁带备份设备，而且rman也无法连接到磁带通道，oracle给出上述测试API。再次执行crsscheck backup(必须的操作)RMAN> crosscheck backup;RMAN> delete expired backup;RMAN> delete obsolete;RMAN> list backup summary;List of Backups===============KeyTY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---清除成功
结论：
从上述可看到，未删除的SBT备份是因其SBT_TAPE 不匹配状态，
手动分配SBT_TAPE后,需再次执行CROSSCHECK检查备份的有效性.
附加：
如果想要单独删除本地备份记录应该如何删除：
RMAN> allocate channel for maintenance type disk;RMAN> delete obsolete device type disk;这样执行会将本地磁盘的obsolete备份删除但是无法删除存放在磁带上的备份信息
