Using RMAN Repository for Database Growth Trend

Posted in: Technical Track

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.

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

2 Comments. Leave new

Andrey Goryunov
December 4, 2006 7:06 am

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

Reply

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.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *