1 、故障描述
某次，用户反馈归档空间使用比较紧张，需要清理，当执行并保留最近7天日志时，发现归档删除操作虽然很快，但是该任务却是迟迟无法自动退出，该现象有点的异常，需要进一步剖析。
RMAN> crosscheck archivelog all;
^C
user interrupt received
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
RMAN-08132: warning: cannot update recovery area reclaimable file list
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03099: job cancelled at user request
RMAN>
2 、根因分析
通常，hang问题的排查往往都相对困难。对于此类场景，找准分析入口就尤为关键。对于操作系统命令，我们通常会使用strace、perf进行跟踪；对于数据库操作，则会使用dump、hanganalyze、10053或者10046跟踪。但对于rman操作的跟踪，日常接触确实不多，那么rman命令出现hang，又该如何排查。这里先给出两个方向，可以先借鉴思考。一是，进入rman交互式窗口后，我们总是可以看到有using target database control file instead of recovery catalog，控制文件是否是一个点？二是，rman是Oracle内置的一个用于物理备份的基础工具，任务的背后无非也是对数据库的调用，那么数据库的跟踪手段是否适用rman，top event是否有关注？
受限于篇幅，我们直接基于第一个点展开，由于当前rman备份并没有使用独立的catalog数据库，因此备份信息默认都会记录到控制文件中，如果我们不对历史备份信息做定期清理，就会导致控制文件愈发膨胀，对于备份元数据的检索和处理也会越来越慢。一般来讲，新数据库被创建之后，控制文件大小也就在20M左右，通常来讲不会大幅增长。在该案例中，我们可以确认控制文件已经上涨到117M，确实增长不少。
ASMCMD> cp control01.ctl /tmp/control01.ctl
copying +data/topprd/controlfile/control01.ctl -> /tmp/control01.ctl
ASMCMD> exit
$ ls -rtlh /tmp/control01.ctl
-rw-r----- 1 grid oinstall 117M Jul 24 18:50 /tmp/control01.ctl
在告警日志中，也能发现扩展的证据，如下：
2024-07-15T16:08:13.291883+08:00
Expanded controlfile section 15 from 32938 to 65876 records
Requested to grow by 32938 records; added 153 blocks of records
2024-07-17T11:48:04.573533+08:00
Expanded controlfile section 15 from 65876 to 131752 records
Requested to grow by 65876 records; added 306 blocks of records
2024-07-20T11:43:32.937681+08:00
Expanded controlfile section 15 from 131752 to 263504 records
Requested to grow by 131752 records; added 612 blocks of records
这里面有几个细节，section 15、records和blocks。
如果想知道控制文件block的size，可以查询v$controlfile视图，如下：
如果想知道控制文件对应section对应的record的size和数量，可以查询v$controlfile_record_section视图，如下：
了解控制文件数据结构的同学应该清楚，控制文件将内部数据分为两类：循环重用记录和非循环重用记录，循环重用的记录可以是控制文件中删除的信息，比如RMAN备份记录等。因此，为了控制文件不在大量扩展，我们需要定位哪些记录比较多并且可以清理。
在客户的环境中，我们也确实发现BACKUP REDOLOG这个类型所使用的空间是最多的，当然其他BACKUP类型占用也不少，因此需要进一步排查。
此处，以检查备份集举例，输出如下所示。其他检查操作也是类似，输出将省略。
RMAN> crosscheck backupset;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6529 instance=xxxxxx1 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l22utvrf_1_1.bak RECID=280167 STAMP=1173290864
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l32uu01t_1_1.bak RECID=280168 STAMP=1173291074
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l42uu08d_1_1.bak RECID=280169 STAMP=1173291281
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l52uu0du_1_1.bak RECID=280170 STAMP=1173291455
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l62uu0k3_1_1.bak RECID=280171 STAMP=1173291655
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l72uu0qa_1_1.bak RECID=280172 STAMP=1173291851
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l82uu10f_1_1.bak RECID=280173 STAMP=1173292047
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_l92uu152_1_1.bak RECID=280174 STAMP=1173292197
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_la2uu1al_1_1.bak RECID=280175 STAMP=1173292374
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_lb2uu1fs_1_1.bak RECID=280176 STAMP=1173292540
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_lc2uu1l2_1_1.bak RECID=280177 STAMP=1173292708
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_ld2uu1qk_1_1.bak RECID=280178 STAMP=1173292887
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rman/fulldb_xxxxxx_le2uu20f_1_1.bak RECID=280179 STAMP=1173293072
Crosschecked 13 objects
RMAN>
首先，检查备份集、副本和归档日志，并没有发现异常情况
RMAN> crosscheck backupset;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
继续，检查过期的备份集、副本和归档日志，也没有发现异常情况
RMAN> list expired backupset;
RMAN> list expired copy;
RMAN> list expired archivelog all;
最后，检查冗余策略超期的备份集、副本和归档日志，当执行report obsolete时发现大量的句柄未清理
RMAN> report schema;
RMAN> report obsolete;
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 271174 20-JUN-24
Backup Piece 271174 20-JUN-24 XXXXXX_2971348685_DIFF_20240620_013001_273280_1172109149_1
Backup Set 271176 20-JUN-24
Backup Piece 271176 20-JUN-24 XXXXXX_2971348685_DIFF_20240620_013001_273282_1172109156_1
Backup Set 271175 20-JUN-24
Backup Piece 271175 20-JUN-24 XXXXXX_2971348685_DIFF_20240620_013001_273283_1172109156_1
Backup Set 271177 20-JUN-24
Backup Piece 271177 20-JUN-24 XXXXXX_2971348685_DIFF_20240620_013001_273284_1172109163_1
Backup Set 271178 20-JUN-24
Backup Piece 271178 20-JUN-24 XXXXXX_2971348685_DIFF_20240620_013001_273285_1172109163_1
。。。。。。
Backup Set 287267 23-JUL-24
Backup Piece 287267 23-JUL-24 arch_dXXXXXX_ul330klvl_s289443_p1_t1175082997
Backup Set 287266 23-JUL-24
Backup Piece 287266 23-JUL-24 arch_dXXXXXX_ul230klvt_s289442_p1_t1175083005
Backup Set 287265 23-JUL-24
Backup Piece 287265 23-JUL-24 arch_dXXXXXX_ul430km0k_s289444_p1_t1175083028
Backup Set 287268 23-JUL-24
Backup Piece 287268 23-JUL-24 arch_dXXXXXX_ul530km8q_s289445_p1_t1175083290
Backup Set 287269 23-JUL-24
Backup Piece 287269 23-JUL-24 arch_dXXXXXX_ul630kmal_s289446_p1_t1175083349
3 、解决方案
由于已经定位是rman保留策略导致产生大量obsolete备份集handle产生，同时从客户侧了解到nbu大量频繁调用，导致obsolete句柄越积越多，进而备份信息元数据查询速度愈发变慢，因此建议优先将obsolete句柄清理，加快检测效率。
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 07/23/2024 15:41:42
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
但在清理过程中，删除任务碰到RMAN-06091，根据经验，以往做DISK类型备份时几乎没有出现过此类问题，但考虑到客户采用NBU一体机备份，使用磁带库方式进行，会不会是这个问题导致任务异常。带着这种可能，在MOS网站翻了翻，确认该任务可能命中Bug 28391990，MOS文档ID参考如下：
RMAN Backup Returns RMAN-06091: No Channel Allocated For Maintenance (of An Appropriate Type) After Applying July (DBPSU/BP/RU) (Doc ID 2428682.1)
此时，可以通过显示设置DISK或者TAPE方式规避，如下：
RMAN> allocate channel for maintenance type 'SBT_TAPE';
RMAN> allocate channel for maintenance type disk;
RMAN> delete obsolete device type sbt;
RMAN> delete obsolete device type disk;
由于，磁带库备份使用的是NBU一体机，因此还需要指定NBU的依赖库文件，不然会导致任务直接报错退出
RMAN> allocate channel for maintenance type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
RMAN> delete noprompt obsolete device type sbt;
当然，如果为了简化操作命令，可以将nbu库文件以软链的方式指定到$ORACLE_HOME/lib下，并在oracle用户家目录下.bash_profile文件在设置环境变量LD_LIBRARY_PATH，方便后续使用。
为了加快obsolete句柄清理速度，此处开启了4个并行，但是在任务执行过程中命中ORA-01652，这个错误号很好理解，应该是临时表空间被打满导致。
RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL c2 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL c3 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL c4 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
DELETE NOPROMPT OBSOLETE DEVICE TYPE 'SBT_TAPE';
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 07/24/2024 15:20:56
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
检查数据库告警日志，在delete obsolete任务执行的同时，nbu又发起了新的备份任务，导致临时表空间过载，感兴趣的同学可以分析top event，并抓一下machine和program，就能发现是本地rman执行的SQL语句在使用。
2024-07-24T17:17:33.226407+08:00
Starting control autobackup
2024-07-24T17:18:56.194462+08:00
Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 5.0.0.0'
media '@aaaad'
handle 'c-2971348685-20240724-0c'
如果想观察temp临时表空间使用率，可以参考下列SQL，实时跟踪temp使用率情况，再评估是否有拓展的必要
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name in ('TEMP');
继续，执行delete obsolete任务，此时又碰到了新的问题，通过字面解决应该能nbu相关，但由于报出来错误都是ORA-，因此建议继续分析下。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on c1 channel at 07/24/2024 16:25:24
ORA-19509: failed to delete sequential file, handle="bk_dTOPPRD_u592vo47i_s283817_p1_t1174147314", parms=""
ORA-27027: sbtremove2 returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
Failed to remove, bk_dTOPPRD_u592vo47i_s283817_p1_t1174147314, from image catalog.
在MOS网站匹配到，疑似可能是nbu client和server版本不兼容，即nbu client版本过低导致，MOS文档ID参考如下：
ORA-19509 ORA-27027 ORA-19511 ORA-19513 ORA-27206 During RMAN Backup to Tape Library via NBU (Doc ID 2947330.1)
接着在数据库服务器上，分别在两个节点上检查nbu client的版本，客户版本为10.3。因此，基本可以排除client版本过低的原因。
shell> cat /usr/openv/netbackup/bin/version
NetBackup-RedHat3.10.0 10.3
后续，继续在mos上匹配，有查到相关的方案，建议在delete obsolete任务中加入force选型，强制删除obsolete句柄，MOS文档ID参考如下：
ORA-19509 ORA-27027 ORA-19511 ORA-19513 ORA-27206 During RMAN Backup to Tape Library via NBU (Doc ID 2947330.1)
控制文件元数据的清理很慢，从结果反推来看像是串行执行，感兴趣的同学建议跟踪下top event，会发现很多control file sequential read等等待事件，通过客观的数据来验证自己观点，无疑是推荐的路子。
当大量累计的obsolete handle记录清理之后，可以明显的看到rman任务再执行之后都能很快退出，处理效果满足预期。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30310891/viewspace-3024434/ ，如需转载，请注明出处，否则将追究法律责任。
