Reporting Database Object Increase On Oracle
May 30, 2003
On the production Oracle system, a DBA is responsible for collecting database increase statistics. Collected information will be the basic triggers for purchasing additional storage resources. A DBA has several possibilities when it comes to the question of how to collect day-to-day database increase information. The minimal questions which need to be answered before starting with any procedure are:
If the answers to the above questions dictate no changes to the database, then you will need to find a solution that does not require touching the existing database. Nevertheless, a DBA needs to cut all of the solutions that require a tremendous amount of investigation and testing, and he needs to come up with an easy and economical solution.
This article covers:
Definition of Database Daily Increase
We can define database daily increase as an amount of new information, which has been stored in the database per day. Measurement is appropriate on the tablespace level (storage in MB) and on the object level (table increase in number of records).
Tablespace increase information provides direct storage information, while object increase information has only indirect storage information.
Object increase information uses a table as the smallest unit of change, based on the absolute number of new records per table. The absolute number is derived as a result of the insert and deletes on the table level.
This article will focus on collecting database increase information at the object level.
Overview of the main methods for collecting object level activity:
Methods of Monitoring Database Object Increase
The Oracle Flashback method uses a version based read consistency mechanism. For every database change, a 'before' image is stored in the undo tablespace (Rollback Segments). Starting with version 9i, the user can control how long this change is kept in the database. To reproduce database activity, we need to examine and glue together the undo tablespace information about database changes. The Flashback method requires time and knowledge to sort and extract the database growth information. Besides, it is supported only on 9i.
The Logminer method utilizes archive and online logs for getting the necessary information. The redolog information is written in a condensed binary form, unsuitable for regular text editors; log files need to be converted into the ANSI format. Logiminer uses a special dictionary and produces output on the database defined utl_file_dir location.
Potential problems using this method are the impossibility to restart database to initialize a new database parameter plus the very complicated procedure to get object activity information in the desired format.
Table level auditing is the next alternate way to obtain the object increase report. Database level auditing is enabled setting the database parameter audit_trail to DB. Auditing records are written to the database audit trail (SYS.AUD$ table). Object level auditing for insert, delete, and update activities can later be enabled per object level.
Auditing will cut down the database performance and utilize database disk space for storing audit information. If performance is not a big issue, we might still be faced with a database which could no be restarted, or we have a storage problem for storing auditing activity.
TheTable level monitoring method, utilizes Oracle's facility to monitor table modifications. The command ALTER TABLE MONITORING will start the table modification monitoring. All table changes are logged in the database dictionary table. The only real problem with this method is inaccuracy of the collected data in a case of transaction rollback. Changes already written to the modification table are not rolled back.
The Third Party tool can be also one of the ways to collect object increase information. There are lots of the tools on the market, capable of doing very good database and object level reporting. Most of them will collect statistics in the internal repository tables or will utilize database-auditing logs. Collected information will later be analysed and converted to a HTML reports. Third party tools are usually expensive and require creating special a database repository.
The Export Utility is very often used as a user schema backup method, as a protection against logical corruptions. If such export logic exists on the system we can use it for generating table reports which will show an increase in table size. If it does not exist we can easily add it as a UNIX cron job. After generating the report, the exported data file can be deleted and the export log file used as a source for reporting. This method has no known impact on the database and will not require a restart, or any kind of changes in the database.