Oracle 如何删除控制文件中rman备份到磁带的备份集信息(RMAN-06091)

如何删除控制文件中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 catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1634 device type=DISK
specification does not match any backup in the repository

RMAN> delete expired backup;
using channel ORA_DISK_1
specification does not match any backup in the repository

----很疑惑，为什么没有匹配的备份
继续尝试删除过时备份obsolete

RMAN>delete obsolete
  Backup Piece       9649   17-SEP-20          arch_dXXX_u0ivakcnk_s11282_p1_t1051341556
Backup Set           9656   20-SEP-20         
  Backup Piece       9656   20-SEP-20          bk_dXXX_u1hvasgcv_s11313_p1_t1051607455
Backup Set           9657   20-SEP-20         
  Backup Piece       9657   20-SEP-20          bk_dXXX_u1ivasgdv_s11314_p1_t1051607487
Backup Set           9661   20-SEP-20         
  Backup Piece       9661   20-SEP-20          arch_dXXX_u1kvat9se_s11316_p1_t1051633550
Backup Set           9666   21-SEP-20         
  Backup Piece       9666   21-SEP-20          bk_dXXX_u1vvau6d5_s11327_p1_t1051662757
Backup Set           9667   21-SEP-20         
  Backup Piece       9667   21-SEP-20          bk_dXXX_u20vau6dn_s11328_p1_t1051662775
Backup Set           9675   21-SEP-20         
  Backup Piece       9675   21-SEP-20          arch_dXXX_u21vauj0l_s11329_p1_t1051675669
Backup Set           9676   21-SEP-20         
  Backup Piece       9676   21-SEP-20          ctrl_dXXX_u26vaumdb_s11334_p1_t1051679147
Backup Set           9681   22-SEP-20         
  Backup Piece       9681   22-SEP-20          bk_dXXX_u2evb0rld_s11342_p1_t1051750061
Backup Set           9682   22-SEP-20         
  Backup Piece       9682   22-SEP-20          bk_dXXX_u2fvb0rmd_s11343_p1_t1051750093
Backup Set           9691   22-SEP-20         
  Backup Piece       9691   22-SEP-20          ctrl_dXXX_u2lvb17e7_s11349_p1_t1051762119
Backup Set           9697   22-SEP-20         
  Backup Piece       9697   22-SEP-20          bk_dXXX_u2tvb3d3k_s11357_p1_t1051833460
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 09/23/2020 17:00:04
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

查看备份信息：
RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
9669    B  1  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200920T181543
9670    B  1  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200920T181543
9671    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9672    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9673    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9674    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9675    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
...
...
9702    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9703    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9704    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9705    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9706    B  A  U SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9707    B  F  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T041616

可以看出这些备份记录都属于磁带上的备份

错误分析：

      出现这样的错误是因为这些备份集处于SBT磁带机上，在控制文件中仍然记录了一些被分在磁带上的备份集信息，因为连不上磁带机, 所以没法用类似于delete obsolete 或者 delete backupset xxx这样的命令去删除这些备份集的信息，需要手动分配磁带类型通道才可以删除。

手动分配磁带类型通道：

从网上找到分配磁带通道的方式
RMAN> allocate channel for maintenance device type sbt
parms 'SBT_LIBRARY=Oracle.disksbt,
ENV=(BACKUP_DIR=/tmp)';
allocated channel: ORA_MAINT_SBT_TAPE_4
channel ORA_MAINT_SBT_TAPE_4: SID=2131 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_4: WARNING: Oracle Test Disk API

RMAN> delete obsolete;


再次查看备份集信息，发现居然还有SBT类型的备份，并没有清理
RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
9669    B  1  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200920T181543
9670    B  1  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200920T181543
9671    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9672    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9673    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9674    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
9675    B  A  A SBT_TAPE    21-SEP-20       1       1       NO         TAG20200921T040740
...
...
9702    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9703    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9704    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9705    B  A  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9706    B  A  U SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T033003
9707    B  F  A SBT_TAPE    23-SEP-20       1       1       NO         TAG20200923T041616

尝试再次手动分配磁带通道

RMAN> allocate channel for maintenance device type sbt parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';
allocated channel: ORA_MAINT_SBT_TAPE_4
channel ORA_MAINT_SBT_TAPE_4: SID=2131 device type=SBT_TAPE
channel 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
===============
Key     TY 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备份删除
但是无法删除存放在磁带上的备份信息