Database Maintenance

Database Maintenance

Unlike the first four phases which are one-time developmental tasks, database maintenance is a set of ongoing tasks that are performed by the DBAs regularly and routinely to ensure data integrity, security, proper backup and recovery, and overall database tuning and performance.

(a) Monitor the performance at the internal level by checking tablespace fragmentation, chaining of rows in tables, proper sizing of tables and indices, proper allocation of extents to them, sizing of the system global area (SGA) and its components, and computation of various hit ratios. In addition, periodically run the Oracle diagnostic tools UTILBSTAT and UTLESTAT and examine the generated report REPORT.TXT to track overall database performance. To allow running the utilities UTILBSTAT and UTLESTAT, you need to set the initialization parameter TIMED_STATISTICS=TRUE either at the system level via the initSID.ora file, or at the session level via the command
ALTER SESSION SET TIMED_STATISTICS=TRUE;

(b) Monitor the performance at the external level by tracking and improving query performance through EXPLAIN PLAN and other utilities, and rebuilding indices as appropriate.

(c) Monitor the performance at the conceptual level by validating the database structure after any changes to it caused by denormalization, partitioning of very large tables, data replication, etc. This process ensures that the database has not suffered any loss of information content.

(d) Set the initialization parameter AUDIT_TRAIL=TRUE in the initSID.ora file so that the AUDIT option can be turned on for selected tables to monitor user transactions against them.