Oracle Database (DBA) Health Check Best Practices

Periodic database health checks help keep your database running smoothly and prevent more serious conditions from developing later. The responsibility for keeping Oracle databases healthy extends to establishing a framework for monitoring the system that is hinged on business objectives. DBAs must closely monitor Oracle operations if the performance demands of database users are to be continuously met.

Following are some of the parameters you can use to check the health of your database:

• Check for accessibility and consistency of the database files by conducting a DB Structure Integrity Check. If the database is in MOUNT mode, the log files and data files are examined. If it is in NOMOUNT mode, only the control file is examined.
• Check for image block consistency such as logical consistencies and head/tail match by carrying out a Data Block Integrity Check.
• Check for log accessibility and consistency by performing a Redo Integrity Check on the contents of the redo log.
• Check for logical consistency by carrying out an Undo Integrity Check. This check uses PMON and SMON to try to recover corrupted transactions.
• Examine the integrity of the core dictionary objects such as tab$ and col$ by carrying out a Dictionary Integrity Check.

If your database constantly keeps crashing and you are always tangled in dozens of interrelated issues, there may be some undetected performance issues with your database. By scheduling regular health checks you will be able to safeguard your database from developing major problems.

Steps for obtaining a good Oracle database health report card:

1. Develop a strategy:

• Determine the period for collecting monitoring data.
• Decide on the metrics to monitor the reports that will be provided.
• Determine the optimal system health and performance statistics you’d like to see.

2. Use the Oracle Toolkit to detect inconsistencies in the system performance and predict future outages through data analysis provided by ASH and AWR. You can also use additional tools such as StatsPackAnalyzer to analyze data gathered by Oracle’s AWR and STATSPACK to identify issues without overloading your environment.

3. Perform a Security Check with the help of Toad for Oracle that can help you carry out vulnerability assessment under its own category. It does so by providing a description of each check as a reminder to the DBA.

4. Perform a thorough security audit.

5. Monitor and optimize your use of database space. Proactive tablespace management (PTM) can give you a warning when you’re running low on space.

6. Review alert logs and trace files.  Proper metrics on log files will bring critical problems to your attention and prevent you from wasting time researching errors that can be ignored.

7. Examine the database storage layer by checking the distribution of data files across I/O channels.  Optimizing storage can prevent read/write bottlenecks.

8. Get feedback from the users to find out how the database is performing on their end. This will help you determine what errors and frustrations the users are encountering.

9. Create and schedule scripts to run regularly. Review the output to spot emerging problems.

To learn more about why and how to perform database health checks please download Datavail’s recently released white paper, “The Real Value of Oracle Health Checks,” or contact Datavail today. With more than 600 database administrators worldwide, Datavail is the largest database services provider in North America. As a reliable provider of 24×7 managed services for applications, BI/Analytics, and databases, Datavail can support your organization, regardless of the build you’ve selected.