清理RMAN Catalog恢复目录

有这样一个需求，用户使用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 database

  ROUT表的定义存放在$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.   However where RMAN is used  very frequently, 60 days of ROUT entries is still too much.)   而从版本10.2.0.4 开始清理ROUT的存储过程cleanupROUT默认仅保留最近7天的数据，这就保证了ROUT不会占用过多的空间。   这里实际控制ROUT表中数据如何清理的recovery.sql中定义的cleanupROUT存储过程，在版本10.2.0.1 中该过程的定义如下：  

-- The procedure cleanupROUT (deletes) all RC_RMAN_OUTPUT rows corresponding
-- to job older than 60 days.
PROCEDURE cleanupROUT IS
  start_time       date;
  high_stamp       number;
  high_session_key number;
BEGIN
  IF (this_db_key IS NULL) THEN
    raise_application_error(-20021, 'Database not set');
  END IF;

  start_time := SYSDATE;
  high_stamp := date2stamp(start_time-60);

  SELECT nvl(max(session_key), 0) into high_session_key
     from rc_rman_status where session_stamp < high_stamp;

  DELETE FROM rout
        WHERE rout_skey <= high_session_key
          AND this_db_key = rout.db_key;

  deb('cleanupROUT deleted ' || sql%rowcount || ' rows from rout table');
  deb('cleanupROUT took ' || ((sysdate - start_time) * 86400) || ' seconds');

END cleanupROUT;

  实际我们只要修改这里high_stamp := date2stamp(start_time-60); 指定的60天的范围，就可以改变10.2.0.4前保留60天ROUT历史数据导致该表暴涨的问题。   首先备份recovery.sql 文件， 之后我们修改该cleanupROUT过程的定义如下：    

cd $ORACLE_HOME/rdbms/admin
cp  recover.bsq recover.bsq.bak

PROCEDURE cleanupROUT IS
  start_time       date;
  high_stamp       number;
  high_session_key number;
BEGIN
  IF (this_db_key IS NULL) THEN
    raise_application_error(-20021, 'Database not set');
  END IF;

  start_time      := SYSDATE;
  high_stamp      := date2stamp(start_time-7);

  SELECT max(rsr_key) into high_session_key
    FROM rsr, dbinc
   WHERE dbinc.db_key = this_db_key
     AND rsr.dbinc_key = dbinc.dbinc_key
     AND rsr.rsr_stamp < high_stamp;

  deb('cleanupROUT select took ' || ((sysdate - start_time) * 86400) ||
      ' seconds');

  -- Delete rows from rout table for jobs older than 7 days.
  If high_session_key IS NOT NULL THEN
     DELETE FROM rout
     WHERE  db_key     = this_db_key
       AND  rout_skey <= high_session_key;
     deb('cleanupROUT deleted ' || sql%rowcount || ' rows from rout table');
  END IF;

  deb('cleanupROUT took ' || ((sysdate - start_time) * 86400) || ' seconds');

END cleanupROUT;

  完成上述定义修改后，需要使以上的定义生效，连接到catalog库并升级catalog:  

[oracle@vrh8 admin]$ rman target / catalog rman/rman@VPROD

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 5 00:37:35 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: G10R21 (DBID=2807279631)
connected to recovery catalog database

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 10.02.00.00
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

  完成上述resync后，ROUT会保留最近7天以来的记录，但是原本占用的空间不会释放，因为cleanupROUT仅仅是delete记录。  

SQL> select count(*) from rman.rout;

  COUNT(*)
----------
        55

SQL>  select bytes/1024/1024 "MB" ,segment_name,segment_type from dba_segments where segment_name='ROUT';

        MB SEGMENT_NAME         SEGMENT_TYPE
---------- -------------------- ------------------
        88 ROUT                 TABLE

  这里存在2种方案： 1. 直接truncate rout表，因为本身rman output的数据只对Enterprise Manager有用，对于很多没有部署EM的环境，ROUT上的这些记录实际没有太大的意义，直接truncate rout 可以回收其原占有的空间：  

stop any rman catalog operation 停止任何RMAN操作

SQL> truncate table rman.rout;

Table truncated.

  2. 利用10g以后出现的shrink space功能收缩空间，这样既保留了ROUT中的少量记录，又回收了必要的空间:  

stop any rman catalog operation 停止任何RMAN操作

SQL>  alter table rman.rout enable row movement;

Table altered.

SQL> alter table rman.rout shrink space;

Table altered.

SQL> select bytes/1024/1024 "MB" ,segment_name,segment_type from dba_segments where segment_name='ROUT';

        MB SEGMENT_NAME         SEGMENT_TYPE
---------- -------------------- ------------------
     .0625 ROUT                 TABLE