Using RMAN Repository for Database Growth Trend

Nov 27, 2006 / By Alex Gorbachev

Tags: ,

REPORT SCHEMA is quite handy to get growth rate of your database/datafile/tablespace providing RMAN repository is used. I don’t remember what triggered that thought but I see it in my “to blog” list so here it goes.

Running several times REPORT SCHEMA AT TIME 'SYSDATE-n'; and processing the results will provide nice trend with granularity up to datafile. It can be processed and aggregated to tablespace or the whole database.

I don’t see that historical datafile information is stored in a repository so RMAN, probably, calculates or implies it from data about backups taken (BDF table?). That means that timeline precision depends in the backups frequency. I’ve run REPORT SCHEMA AT TIME with DEBUG ON quickly but it shows only calls to RMAN internal package and I didn’t have enough time to trace the queries.

2 Responses to “Using RMAN Repository for Database Growth Trend”

  • Andrey Goryunov says:

    Hi Alex,

    I made a trace of REPORT SCHEMA command and found the following sql statements for permanent and temporary tablespaces:

    for REPORT SCHEMA for permanent tablespaces

    SELECT RC_DATAFILE.FILE#, RC_DATAFILE.CREATION_CHANGE#, CREATION_TIME, NAME,
    TABLESPACE_NAME, TS#, NULL, BLOCKS, BLOCK_SIZE, BYTES / 1024, NULL,
    STOP_CHANGE#, READ_ONLY, RFILE#, DECODE(INCLUDED_IN_DATABASE_BACKUP, ‘YES’,
    1, 0), AUX_NAME, RC_DATAFILE.DBINC_KEY, OFFR.OFFLINE_SCN, OFFR.ONLINE_SCN,
    OFFR.ONLINE_TIME, DECODE(ENCRYPT_IN_BACKUP, ‘ON’, 1, ‘OFF’,2, 3) ENCRYPT
    FROM
    RC_DATAFILE, OFFR WHERE DB_KEY = :B4 AND RC_DATAFILE.DBINC_KEY = :B3 AND
    OFFR.FILE#(+) = RC_DATAFILE.FILE# AND OFFR.CREATE_SCN(+) =
    RC_DATAFILE.CREATION_CHANGE# AND OFFR.DBINC_KEY(+) = :B3 AND
    OFFR.OFFR_STAMP(+) = 0 AND RC_DATAFILE.CREATION_CHANGE# :B1 ) ORDER BY RC_DATAFILE.FILE#

    where :B4 = 1 (index of registered database within catalog)
    :B3 = 2 (number of reincarnation of a database)
    :B2 and :B1 = 900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    for REPORT SCHEMA;
    and :B2 and :B1 = 245800 in my case
    that is equal to
    l1> select LOW_SCN, LOW_TIME, COMPLETION_TIME from al where LOW_SCN = 245800;

    LOW_SCN LOW_TIME COMPLETION_TIME
    ———- —————– —————–
    245800 11/20/06 07:01:25 12/03/06 17:13:33

    and SYSDATE was for Dec 3

    for temporary tablespace the sql is
    SELECT TF.FILE# TFNUMBER, TF.CREATE_SCN TFCREATIONSCN, TF.CREATE_TIME
    TFCREATIONTIME, TFATT.FNAME FILENAME, TS.TS_NAME TSNAME, TS.TS# TSNUMBER,
    DECODE(TFATT.AUTOEXTEND, ‘ON’, 16, 0) STATUS, TS.BIGFILE ISSFT,
    TFATT.BLOCKS BLOCKS, TF.BLOCK_SIZE BLOCKSIZE, TFATT.MAX_SIZE MAXSIZE,
    TFATT.NEXT_SIZE NEXTSIZE, TF.RFILE# RFNUMBER, DBINC.DBINC_KEY DBINCKEY
    FROM
    DBINC, TS, TF, TFATT WHERE DBINC.DBINC_KEY = TS.DBINC_KEY AND TS.DBINC_KEY =
    TF.DBINC_KEY AND TS.TS# = TF.TS# AND TS.CREATE_SCN = TF.TS_CREATE_SCN AND
    TF.DBINC_KEY = TFATT.DBINC_KEY AND TF.FILE# = TFATT.FILE# AND TF.CREATE_SCN
    = TFATT.CREATE_SCN AND TFATT.END_CKP_KEY IS NULL AND DBINC.DB_KEY = :B5 AND
    DBINC.DBINC_KEY = :B4 AND TF.DROP_SCN IS NULL AND (:B3 IS NULL OR
    ((TS.CREATE_SCN

  • Thanks Andrey. I’ll try it one day if I have time. Not many customers are using RMAN repository so we are not very keen on adopting it as general practice.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>