Keeping your Oracle Database Healthy

I recently gave a virtual presentation about database patching and managing risk.  During the session one of the key takeaways is keeping your database healthy.  Although patching is part of keeping things healthy, there is a lot more.

One key is being proactive about health checks.  You should always do some basic database health checks during every patch or upgrade. In fact, you may also want to proactively perform some database health checks weekly or monthly, since many things can happen outside of DB patching.

I've personally been in many patching exercises where we planed a specific amount of time. Having tested the patch in a non production, feeling confident things will go well.  Only to find out that something else in the database is not healthy.  Something only in production.  Some code is invalid, or some system issue is going on.  The patching window is now two times longer as we are fixing the problem we didn't know and still trying to patch.

Oracle provides many great tools to help, ORAchk, EXAchk, and Autonomous Health Framework (AFH).

See note: Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAChk (Doc ID 2550798.1)

Running these tools proactively and reviewing the results on a regular basses is critical to a healthy system.  Always do some basic database health checks on your database

• Every patch or upgrade
• Proactively perform weekly or monthly
• Before and after a patch (compare)

Notice this is not just during patching, many things can happen outside of DB patching to corrupt your system!

Knowing there is an issue will help you plan your work and make sure your outages have the time you need and everyone is clear on the change that is needed.  The less surprises there are in the work you do, the more the business and management will trust what you are doing in the future.

There are other tools that Oracle provides outside of AHF.  Here is a list of some more Oracle provided scripts and technical notes on things you can check in your database to show it is healthy.  This does not guarantee that the application that uses the database is healthy, this is more about the technical aspects fo the database itself.

• FAQ: Database Upgrade and Migration (Doc ID 1352987.1)
• Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAChk (Doc ID 2550798.1)
• Script to Collect DB Upgrade / Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)
• hcheck.sql Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c (Doc ID 136697.1)
• Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (DOC ID 2118136.2)
• Best Practices for running catalog, catproc and utlrpscript (Doc ID 863312.1)
• Debug and Validate Invalid Objects (Doc ID 300056.1)
• Where Can I Find the Parallel Version of Utlrp.sql? (Doc ID 230136.1)
• Master Note: Troubleshooting Oracle Data Dictionary (Doc ID 1506140.1)
• Overview of Refreshing Materialized Views (Doc ID 549874.1)
• How to Monitor the Progress of a Materialized View Refresh (MVIEW) (Doc ID 258021.1)
• Script to Check the Status or State of the JVM within the Database (Doc ID 456949.1)

Hopefully these notes give you a point in the right direction on how to help keep your database healthy during patching activities, and even when you are not patching.