11g Oracle RMAN Catalog: ROUT table and ROUT_U1 index are growing. Can you purge it ?

Issue: I faced a problem with my Oracle database backup using RMAN. The RMAN catalog database was generating huge number of archive logs. I have created the new catalog schema APPLECAT like two weeks ago. Full backup of the target APPLE was taken. But unfortunately today, I happen to clean the archivelogs three times and soon it gets filled up.

ARC0: Encountered disk I/O error 19502
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/rman02/arch/arch_RMANCAT_5230_1_798569661.log' (error 19502) (RMANCAT)
ARC0: I/O error 19502 archiving log 1 to '/rman02/arch/arch_RMANCAT_5230_1_798569661.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance RMANCAT - Archival Error
ORA-16038: log 1 sequence# 5230 cannot be archived

Other symptoms that I have been informed lately was that RMAN jobs using the recovery catalog are slow. The incremental backup that usually takes 2 hour became 6 hours. RMAN Catalog schemas were consuming lot of space. ROUT table was 800 MB and ROUT_U1 556MB.

Environment details:

Target Database
SID: APPLE
Version : Oracle RDBMS 11g (11.2.0.3)
OS: HP UX Itanium 11.31

Catalog Database
SID: RMANCAT
Version:11.2.0.3
OS: HP UX Itanium
Catalog Schema Name: APPLECAT

Media Manager: HP DataProtector 6.0

I use same catalog database but different schemas for each target database that I backup using RMAN.

Top SQLs that were executed at the time of issue were:
INSERT INTO ROUT VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 );
DELETE FROM ROUT WHERE DB_KEY = :B2 AND ROUT_SKEY <= :B1 ;
SELECT DB_ID FROM DB WHERE DB_KEY = :B1 FOR UPDATE;

Cause:
Large # of records in sys.aud$, ROUT and RSR tables.

SYS@RMANCAT SQL>select count(1) from sys.aud$;

COUNT(1)
----------
1736100

SYS@RMANCAT SQL>select count(1) from APPLECAT.ROUT ;

COUNT(1)
----------
9258449

SYS@RMANCAT SQL>select count(1) from SYS.AUD$;

  COUNT(1)
----------
   1736206

ROUT and RSR tables are used by Grid/Dbconsole/OEM to view the previous RMAN jobs output/logs.

Solution:

sqlplus APPLECAT/XXXXX
APPLECAT@RMANCAT SQL>truncate table APPLECAT.ROUT;

Table truncated.
APPLECAT@RMANCAT SQL>select count(1) from APPLECAT.ROUT;

  COUNT(1)
----------
         0
APPLECAT@RMANCAT SQL> delete from APPLECAT.RSR;

APPLECAT@RMANCAT SQL> Commit;

APPLECAT@RMANCAT SQL>execute dbms_utility.analyze_schema('APPLECAT','COMPUTE');

PL/SQL procedure successfully completed.

sqlplus / as sysdba

SYS@RMANCAT SQL>truncate table SYS.AUD$;

Table truncated.

Other Details:
Entries in ROUT table will be linked to a specific dbkey so query the catalog:
SQL>select count(*),DB_KEY from .rout group by db_key;

You can find the db_key for your target:
SQL>select * from rc_database;

By default 7 days of data is retained in ROUT.If OEM is not used to run RMAN jobs and view logs, you can reduce the above to keep just 1 days worth of logs. This is defined in $ORACLE_HOME/rdbms/admin/recover.bsq. Backup a copy of the recover.bsq file before making any changes. Look for procedure cleanupROUT:

Replace
high_stamp := date2stamp(start_time-7);
with
high_stamp := date2stamp(start_time-1);

$ rman catalog APPLECAT/XXXXXXX
RMAN>upgrade catalog;

Reference:
RMAN Catalog Response Is Slow Processing ROUT table (Doc ID 1600112.1)
Known RMAN Performance Problems (Doc ID 247611.1)