有这样一个需求，用户使用RMAN Catalog恢复目录保存多台Database Server的备份信息。 由于每天都会执行大量的backup操作，而且所备份的DB的结构本身都已经十分复杂了，导致recovery catalog恢复目录占用空间迅速增长，且用户的磁盘空间较为紧张，可能在短期内无法扩disk space，这就让我们考虑到需要清理RMAN Recovery Catalog中一些不再需要的记录。 首先搞清楚在Catalog库中那些数据段占用了最多的空间Space:
这里 RMAN 用户是Catalog Schema 的拥有者
SQL> select bytes/1024/1024 "MB" ,segment_name,segment_type
from dba_segments where owner='RMAN' order by bytes desc;
MB SEGMENT_NAME SEGMENT_TYPE
---------- -------------------- ------------------
88 ROUT TABLE
47 ROUT_U1 INDEX
31 ROUT_I_RSR INDEX
23 ROUT_I_DB INDEX
.125 RSR TABLE
可以看到是ROUT表占用了最多的空间，那么这张表是做什么用处的呢？ ROUT表用于记录RMAN在执行backup等命令时的输出内容，实际上堆积了V$RMAN_OUTPUT视图中的内容，当目标数据库连接到CATALOG库执行操作或Resync Catalog时会将V$RMAN_OUTPUT中的信息同步到Catalog库的ROUT表中。
SQL> desc v$rman_output; Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER RECID NUMBER STAMP NUMBER SESSION_RECID NUMBER SESSION_STAMP NUMBER OUTPUT VARCHAR2(130) RMAN_STATUS_RECID NUMBER RMAN_STATUS_STAMP NUMBER SESSION_KEY NUMBER SQL> select output from v$rman_output where output is not null and rownum <3; OUTPUT -------------------------------------------------------------------------------- connected to target database: VPROD (DBID=881465081) connected to recovery catalog databaseROUT表的定义存放在$ORACLE_HOME/rdbms/admin/recover.sql 中，具体如下:
CREATE TABLE rout ( db_key NUMBER NOT NULL, -- database output belongs to rsr_key NUMBER NOT NULL, -- command that generated the output rout_skey NUMBER NOT NULL, -- session that created the output rout_recid NUMBER NOT NULL, -- record id from server rout_stamp NUMBER NOT NULL, -- timestamp when row was added rout_text VARCHAR2(130) NOT NULL, -- RMAN output CONSTRAINT rout_u1 UNIQUE(db_key, rout_skey, rsr_key, rout_recid, rout_stamp), CONSTRAINT rout_f1 FOREIGN KEY(db_key) REFERENCES db ON DELETE CASCADE, CONSTRAINT rout_f2 FOREIGN KEY(rsr_key) REFERENCES rsr ON DELETE CASCADE ) &tablespace&正是因为该Catalog恢复目录每天都会记录大量结构复杂(拥有众多数据文件、归档日志)的数据库的备份输出信息，导致ROUT表迅速膨胀，可能会存放几十万条记录，在我们的例子中:
SQL> select count(*) from rman.rout; COUNT(*) ---------- 1069485
根据文档<Rman Catalog Resync Operation is Very slow at 10G [ID 378234.1]>的介绍ROUT表包含了所有RMAN会话的输出内容，且这些内容仅对 Enterprise Manager企业管理器有用。 默认情况下ROUT表也会在每次Resync Catalog操作时被清理(clean up)，但是在版本10.2.0.1-10.2.0.3仍会保留最近60天来的记录，当RMAN被频繁使用时60天的ROUT数据也可能非常多。(The ROUT table contains the RMAN output generated during all rman sessions and is used only by Enterprise Manager. The ROUT table is cleaned up automatically during each resync operation leaving by default, the last 60 days worth of entries in ROUT.
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1278171
