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
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)
