Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 30, 2003

Reporting Database Object Increase On Oracle

By Marin Komadina

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:

  • Does the system have to be up and running 24 hours a day / 7 days a week for 12 months in a year and can't be shut down for even one hour in a week or month
  • Can you install any database object or load any data in the database

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
  • Methods for Monitoring Database Object Increase
  • Object Increase Reporting Using Export Utility
  • Conclusion

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:

  • Flashback query
  • LogMiner
  • Table level auditing
  • Table level monitoring
  • Third party tool
  • Export utility

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM