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.
