A tool/script catalog for Oracle Database Health
Businesses rely heavily on their databases to efficiently manage, store, and process large amounts of crucial information. While Oracle databases are known for their robustness, like any other complex system, they require continuous maintenance and monitoring to ensure peak performance and avoid unexpected issues.
For me, a general rule of thumb is to address tomorrow’s problems before they arise, while ensuring that today’s problems were resolved yesterday. This proactive mindset is essential for any Database Administrator (DBA).
That’s where proactive database health checks come into play. By definition, proactive health checks are actions of observation, analysis and correction of impending issues that might later scale to larger serious problems. This blog post provides a discussion on some of the tools and scripts that may become indispensable in handling database health.
You may already be familiar with many of these tools, but some will likely be new to you. I’ll provide a catalog without going into detail.
AHF Insights
AHF Insights is the latest advanced suite of tools designed to autonomously maintain the health and performance of Oracle database systems. It provides deeper diagnostic insights into Oracle diagnostic collections gathered by AHF utilities, including Oracle EXAchk/ORAchk, Oracle Trace File Analyzer, Exawatcher, and Cluster Health Monitor.
ORACHK / EXACHK
ORACHK / EXACHK is the number one free, lightweight, and non-intrusive health check framework for Oracle software and hardware components. It helps identify and address factors affecting Oracle compliance, stability, and performance. It includes numerous checks at the database, cluster, and OS levels. Previously a separate tool, it is now part of AHF (Autonomous Health Framework), which has been shipped with Oracle Grid Infrastructure since version 11.2.0.4. Its default autostart_client scheduled runs provide data for AHF Insights.
OSWatcher / ExaWatcher Utility
This utility collects and archives operating system and network metrics that help diagnose performance issues. It also provides graphical analysis tools. I have a blog post detailing how it can be used for: “Troubleshooting Oracle Clusterware Node Evictions and Reboot Problems.“
Procwatcher
Procwatcher is a tool to examine and monitor Oracle database and/or clusterware processes at an interval. The tool will collect stack traces of these processes using Oracle tools like oradebug short_stack and/or OS debuggers like pstack, gdb, dbx, or ladebug and collect SQL data if specified. It can be used to proactively monitor shared pool memory allocation if there are ORA-4031 errors or other problems. You may refer to “How To Troubleshoot Database Contention With Procwatcher (Doc ID 1352623.1)” and “How To Troubleshoot ORA-4031’s and Shared Pool Issues With Procwatcher (Doc ID 1355030.1)” for more information.
TFA
TFA collects and packages diagnostic data, primarily used for creating incident packages for Oracle Support and troubleshooting ORA errors. SRDC (Service Request Data Collection) packages are collected with it. For example, to diagnose Oracle Text Performance Issues, you can use the tfactl diagcollect -srdc dbtextissue command to collect all the required information to share with the Oracle Support Team. As of February 2025, there are 385 different documents in the catalog “Oracle Catalog: Service Request Data Collections (SRDCs) for all Products and Services (Doc ID 51.2).” Since the UI is not self-guiding, I will explain how to access these 385 documents. Database-related documents can be found under the Hardware Tab. After clicking the Hardware tab, click “Show All” in the upper right corner.
There is also another link ” Oracle Catalog: Dynamic Toolbox – Diagnostic Tools, Scripts, and Analyzers for All Product and Services (Doc ID 52.2)” contains these documents and much more.
tfactl summary option
This option worths mentioning. This TFA flag/function offers a high-level, real-time status summary of your entire database environment. It provides detailed information about CRS, ASM, ACFS, Database, Patching Information, Network, Listeners, and Operating System. Refer to Prashant Dixit‘s blog post – “SUMMARY, my favorite Trace File Analyzer (TFA) command” for more information about it.
These four tools, along with TFA (Trace File Analyzer), which collects and packages diagnostic data, are all included with AHF and can be downloaded from “Autonomous Health Framework (AHF) – Including Trace File Analyzer and Orachk/Exachk (Doc ID 2550798.1)“
ADRCI Command-Line Utility
The ADR Command Interpreter (ADRCI) is a command-line utility that allows you to investigate problems, view health check reports, and package first-failure diagnostic data. It utilizes the Automatic Diagnostic Repository (ADR) as its data source. For more information about it, refer to Oracle Database Administrator’s Guide – 9.Diagnosing and Resolving Problems.
CVU (Cluster Verification Utility – cluvfy)
CVU can also be used to verify the readiness and health of an Oracle Clusterware or Real Application Clusters (RAC) environment. By default the CVU resource (ora.cvu) in Oracle Clusterware is configured to run every 6 hours controlled by “RUN_INTERVAL”.
DBSAT (Database Security Assessment Tool)
DBSAT checks for common database security issues and best practices while also providing insights into sensitive data and high-privileged users. It produces an output format similar to ORACHK. Unlike other tools, DBSAT does not require a traditional installation: it is a standalone tool provided as a ZIP package that you simply extract and run. It can be downloaded from MOS Note: Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)
The Hang File Generator(HANGFG)
HANGFG (Hang File Generator) is a series of Unix shell scripts designed to automate the generation and collection of hanganalyze and systemstate trace files. It can also be considered a post-mortem analysis tool. HANGFG generates and collects hang trace files based on the impact of collecting diagnostic traces on a system that is already in a degraded state. The decision on what level of impact the user can afford is left up to the user when running HANGFG, as the level of impact is passed in as an argument to the tool. However, HANGFG can also make this decision automatically if the user selects light or medium impact (option 1 or 2) as an argument. HANGFG is RAC-aware and can run in either a RAC or non-RAC environment.Download of HANGFG is available through “HANGFG User Guide (Doc ID 362094.1)” and can be downloaded as a tar file. The user can then unarchive the tar file to the directory where HANGFG is to be installed.
Health Check Scripts for Database Components :
Oracle OLAP
This script will provide output to show the overall health of the Oracle Workspace Manager component. It can be downloaded from MOS Note: Script OLAPHCDR.SQL: OLAP Health-Checks and Diagnostics Reports (Doc ID 2079036.1)
APEX
There is a script which will help to verify the status of the APEX installation. The output also helps DB objects needed to support the Apex installation. It can be downloaded from MOS Note: SRDC – Collect Installation Verification Script / Health Check for Apex (Doc ID 2036107.1)
Oracle Workspace Manager
This script was developed by the Server Technologies (ST) Center of Expertise (CoE). It gathers a set of pre-defined health checks across the entire OLAP (Online Analytical Processing) setup within the system. It can be downloaded from MOS Note: Oracle Workspace Manager Health Check (Doc ID 1906147.1)
Oracle Text
This script will provide output to show the overall health of the Oracle Text component. It can be downloaded from MOS Note: Oracle Text Health Check (Doc ID 823649.1)
Oracle Spatial
This script will provide output to show the overall health of the Oracle Spatial component. It can be downloaded from MOS Note: Oracle Spatial Health Check (Doc ID 800725.1)
Oracle Java Virtual Machine (OJVM)
OJVM is generally installed by default in a database and can be checked by querying dba_registry view. (SELECT version, status FROM dba_registry WHERE comp_id=’JAVAVM’)
Oracle JSON DB/SODA DB Health-Check Script
This script checking for the current status of JSON DB and SODA DB components, makes recommendations based on current settings and checks if the components are being used.It can be downloaded from MOS Note: JSON DB and SODA DB Health-Check Script (Doc ID 2212664.1)
As Oracle Multimedia (ORDIM), Oracle Warehouse Builder (OWB), and Oracle Locator have been desupported starting with Oracle 19c, I did not include them.
Scripts and Tools for SQL Performance Analysis :
The Automatic Workload Repository (AWR) is the primary guide for diagnosing and resolving database performance issues, including SQL statement performance.
oradebug
oradebug is a command-line utility in Oracle used for debugging and troubleshooting the Oracle database, utilizing a numeric events tracing system (Oracle’s kernel service debugging mechanism). For more information about oradebug, refer to Tanel Poder’s ‘ORADEBUG DOC‘ page.
Sayan Malakshinov informed me about the HTML version of the ORADEBUG help documentation he built. Since ORADEBUG lacks detailed documentation (only some limited internal documentation is available) his guide serves as a valuable resource. You may also refer to the examples provided in his blog post, “Oracle Diagnostic Events — Cheat Sheet.”
There are various types of events available for troubleshooting, with the most commonly used trace events being
10046 – Captures wait events and bind variable values for detailed SQL execution analysis.
10053 – Provides query optimizer decision-making insights, useful for understanding execution plans.
Additionally, TKPROF is a powerful tool used to format Oracle trace files. It processes raw trace data and provides a structured output with execution statistics, wait events, and performance metrics for SQL statements.
SQL Repair Advisor – DBMS_SQLDIAG
SQL Repair Advisor has been available since Oracle 11g to help diagnose and fix valid SQL statements that fail with critical errors at runtime. The advisor performs a diagnostic operation on the statement and may provide a patch to repair it. It is accessible through Enterprise Manager and is facilitated by the DBMS_SQLDIAG package, which is also used for creating SQL patches.
VERSION_RPT
Version_rpt is a script that can be used for investigating child cursors. Script’s latest version is available in the link. The function generates a summary report from the v$sql_shared_cursor view, providing additional diagnostic details based on the reason code. It counts all versions with a ‘Y’ in any column and identifies those where all columns contain ‘N’. More information is available on High SQL Version Counts – Script to determine reason(s) (Doc ID 438755.1). A sample output is available on my blog post “Child Cursors: BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE and BIND_EQUIV_FAILURE“
SQLHC – SQL HEALTH CHECK
The SQL Tuning Health-Check Script (SQLHC) is a tool provided by Oracle that analyzes the environment in which a specified SQL statement runs. It examines Cost-Based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other factors that may impact performance. SQLHC is free and does not require a license. However, if the Diagnostic or Tuning Pack is installed, SQLHC will request permission to determine whether these packs are licensed, as it can utilize data from AWR reports. When executed with a specific SQL_ID, SQLHC generates an HTML report summarizing the results of various health checks for the SQL statement.
For downloading SQLHC and accessing more details, refer to MOS Note: SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1) and MOS Note: FAQ: SQL Health Check (SQLHC) Frequently Asked Questions (Doc ID 1417774.1).
SQLTXPLAIN – also known as SQLT
SQLT is a PL/SQL-based tool developed by Carlos Sierra that takes a single SQL statement as input and generates a ZIP file containing comprehensive diagnostic information. This package includes execution plans, Cost-Based Optimizer (CBO) statistics, schema object metadata, and various configuration details to assist in SQL performance tuning and troubleshooting.
SQLT is a far more advanced tool than SQLHC. While it includes similar checks, it provides significantly broader functionality. SQLHC serves as a quick health check for a SQL statement, acting as a subset of SQLT but without leaving a database footprint. SQLT, on the other hand, offers in-depth SQL tuning capabilities, making it a powerful tool for performance analysis. However, leveraging SQLT effectively requires some level of expertise.
For downloading SQLT and accessing more details, refer to “All About the SQLT Diagnostic Tool (Doc ID 215187.1)“
SQLdb360
SQLdb360 is a “free to use toolset” to perform an initial assessment of an entire Oracle database or a particular SQL statement. SQLdb360 is made of two independent tools, eDB360 (database-wide analysis) and SQLd360 (individual SQL analysis). You can access its Github repository for downloads and the README file.
Any Other Tools/Scripts :
Materialized Views Health-Checks and Diagnostics Reports
The mvhcdr.sql script performs some health-checks around Materialized Views. It also generates some html and csv reports to diagnose Materialized Views issues. These diagnostics reports are based mainly on static SYS views. This script installs nothing on the database, and updates nothing. It can be downloaded from MOS Note: Script mvhcdr.sql: Materialized Views Health-Checks and Diagnostics Reports (Doc ID 1517362.1)
Parallel Execution Health-Checks and Parallel Execution Health-Checks and Diagnostics Reports
The pxhcdr.sql script performs a set of pre-defined health-checks around parallel execution setup system-wide and generates some html reports to diagnose PX issues. These diagnostics reports are based mainly on PX dynamic and static views. This script installs nothing on the database, and updates nothing. It can be downloaded from MOS Note: Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports (Doc ID 1460440.1)
RAC Diagnostic Information (racdiag.sql)
This script is intended to provide a user friendly guide to troubleshoot RAC hung sessions or slow performance scenerios and includes information to gather a variety of important debug information to determine the cause of a RAC hung sessions or slow performance. It can be downloaded from MOS Note: Script to Collect RAC Diagnostic Information (racdiag.sql) (Doc ID 135714.1)
DRM Information (drmdiag.sql)
The drmdiag.sql is a script to collect information related to DRM (Dyanamic Resource Re-mastering), particularly for troubleshooting “gcs drm freeze in enter server mode” wait events. It can be downloaded from MOS Note: Script to Collect DRM Information (drmdiag.sql) (Doc ID 1492990.1)
DBMS_HM Package
DBMS_HM is an Oracle package used for performing health checks on an Oracle database. It stands for Database Health Monitor, and the package provides a set of built-in procedures that help assess the database’s health and performance. By running health checks using DBMS_HM, database administrators can monitor various aspects of database performance and configuration. These are the checks can be run. Refer to the related documents page (PL/SQL Packages and Types Reference – Oracle Database Release 19) for more information.
DBMS_HM is an Oracle package used to perform health checks on an Oracle database. It stands for Database Health Monitor, and it provides a set of built-in procedures to assess the database’s health and performance. By using DBMS_HM to run health checks, database administrators can monitor various aspects of database performance and configuration. These checks can be executed to detect potential issues. For more information, refer to the related documents page in the ‘PL/SQL Packages and Types Reference – Oracle Database Release 19′.
SQL> select id,name,description from V$HM_CHECK order by id;
ID NAME DESCRIPTION
___ ___________________________ _____________________________________
1 HM Test Check Check for health monitor functionality
2 DB Structure Integrity Check Checks integrity of all database files
3 Data Block Integrity Check Checks integrity of a data file block
4 Redo Integrity Check Checks integrity of redo log content
5 Logical Block Check Checks logical content of a block
10 Transaction Integrity Check Checks a transaction for corruptions
11 Undo Segment Integrity Check Checks integrity of an undo segment
12 No Mount CF Check Checks control file in NOMOUNT mode
13 CF Member Check Checks a multiplexed copy of the control file
14 All Datafiles Check Checks all datafiles in the database
15 Single Datafile Check Checks a data file
16 Log Group Check Checks all members of a log group
17 Log Group Member Check Checks a particular member of a log group
18 Archived Log Check Checks an archived log
19 Redo Revalidation Check Checks redo log content
20 IO Revalidation Check Checks file accessibility
21 Block IO Revalidation Check Checks file accessibility
22 Txn Revalidation Check Revalidate corrupted transaction
23 Failure Simulation Check Creates dummy failures
24 Dictionary Integrity Check Checks dictionary integrity
25 CF Block Integrity Check Checks integrity of a control file block
26 ASM Mount Check Diagnose mount failure
27 ASM Allocation Check Diagnose allocation failure
28 ASM Disk Visibility Check Diagnose add disk failure
29 ASM File Busy Check Diagnose file drop failure
30 Tablespace Check Check Checks a tablespace
31 Mount CF Check Checks control file in mount mode
32 ASM Toomanyoff Check Diagnose mount failed because there were too many offline disks
33 ASM Insufficient Disks Check Diagnose mount failed because there were insufficient disks
34 Failover Check Check if failover has happened
35 ASM Insufficient Mem Check Check to adjust memory on allocation failure
36 ASM DGFDM Check No DG Name Check if a diskgroup (no diskgroup name) was forcibly dismounted
37 ASM DG Force Dismount Check Check if a diskgroup was forcibly dismounted
38 ASM Sync IO Fail Check Diagnose ASM diskgroup synchronous I/O operation failedData Dictionary Health Check
The hcheck.sql script is a lightweight, read-only script that checks the health of the data dictionary and detects orphaned tables and indexes left in the dictionary. It verifies the consistency of selected dictionary relationships and identifies certain known issues; though some reported problems may be normal and expected. This script is primarily intended for use under the guidance of Oracle Support. It has been available since 8.1.7.4 and can be downloaded from MOS Note: hcheck.sql – Script to Check Data Dictionary for Known Problems (Doc ID 136697.1).
The good news is that for databases version 19.22 and later, performing a dictionary health check no longer requires downloading the hcheck.sql script. Instead, you can use the DBMS_DICTIONARY_CHECK package. Its usage is as simple as shown below. It also provides guidance to the related support document for solutions to your problems, warnings, errors, etc. Here is a sample run output.
SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.full
dbms_dictionary_check on 18-FEB-2023 14:07:10
----------------------------------------------
Catalog Version 19.0.0.0.0 (1900000000)
db_name: BLTDB
Is CDB?: NO
Trace File: /oracle/log/diag/rdbms/bltdb/bltdb1/trace/bltdb1_ora_905567_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- OIDOnObjCol ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- LobNotInObj ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- SourceNotInObj ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- OversizedFiles ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- PoorDefaultStorage ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- PoorStorage ... 1900000000 <= *All Rel* 02/18 14:07:48 PASS
.- TabPartCountMismatch ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- TabComPartObj ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- Mview ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- ValidDir ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- DuplicateDataobj ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- ObjSyn ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- ObjSeq ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- UndoSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- IndexSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- IndexPartitionSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- IndexSubPartitionSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- TableSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- TablePartitionSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- TableSubPartitionSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- PartCol ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- ValidSeg ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- IndPartObj ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- DuplicateBlockUse ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- FetUet ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- Uet0Check ... 1900000000 <= *All Rel* 02/18 14:07:49 PASS
.- SeglessUET ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ValidInd ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ValidTab ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- IcolDepCnt ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ObjIndDobj ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- TrgAfterUpgrade ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ObjType0 ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ValidOwner ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- StmtAuditOnCommit ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- PublicObjects ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- SegFreelist ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ValidDepends ... 1900000000 <= *All Rel* 02/18 14:07:50 WARN
HCKW-0016: Dependency$ p_timestamp mismatch from VALID objects (Doc ID 1361045.1)
[W] - P_OBJ#=66168 D_OBJ#=66224
[W] - P_OBJ#=66169 D_OBJ#=66225
[W] - P_OBJ#=66170 D_OBJ#=66226
[W] - P_OBJ#=66172 D_OBJ#=66228
[W] - P_OBJ#=66178 D_OBJ#=66234
[W] - P_OBJ#=66179 D_OBJ#=66235
[W] - P_OBJ#=66180 D_OBJ#=66236
[W] - P_OBJ#=66181 D_OBJ#=66237
[W] - P_OBJ#=66183 D_OBJ#=66239
[W] - P_OBJ#=66184 D_OBJ#=66240
[W] - P_OBJ#=66185 D_OBJ#=66241
[W] - P_OBJ#=66186 D_OBJ#=66242
[W] - P_OBJ#=66189 D_OBJ#=66245
[W] - P_OBJ#=66190 D_OBJ#=66246
[W] - P_OBJ#=66191 D_OBJ#=66247
[W] - P_OBJ#=66193 D_OBJ#=66249
[W] - P_OBJ#=66194 D_OBJ#=66250
[W] - P_OBJ#=66195 D_OBJ#=66251
[W] - P_OBJ#=66199 D_OBJ#=66255
[W] - P_OBJ#=66200 D_OBJ#=66256
[W] - P_OBJ#=66201 D_OBJ#=66257
[W] - P_OBJ#=66202 D_OBJ#=66258
[W] - P_OBJ#=66203 D_OBJ#=66259
[W] - P_OBJ#=66204 D_OBJ#=66260
[W] - P_OBJ#=66205 D_OBJ#=66261
[W] - P_OBJ#=66206 D_OBJ#=66262
[W] - P_OBJ#=66207 D_OBJ#=66263
[W] - P_OBJ#=66208 D_OBJ#=66264
[W] - P_OBJ#=66209 D_OBJ#=66265
[W] - P_OBJ#=66210 D_OBJ#=66266
[W] - P_OBJ#=66211 D_OBJ#=66267
[W] - P_OBJ#=66212 D_OBJ#=66268
[W] - P_OBJ#=66213 D_OBJ#=66269
[W] - P_OBJ#=66214 D_OBJ#=66270
[W] - P_OBJ#=66215 D_OBJ#=66271
[W] - P_OBJ#=66216 D_OBJ#=66272
[W] - P_OBJ#=66219 D_OBJ#=66275
[W] - P_OBJ#=66220 D_OBJ#=66276
[W] - P_OBJ#=66221 D_OBJ#=66277
[W] - P_OBJ#=66222 D_OBJ#=66278
[W] - P_OBJ#=66283 D_OBJ#=66305
[W] - P_OBJ#=66288 D_OBJ#=66310
[W] - P_OBJ#=66289 D_OBJ#=66311
[W] - P_OBJ#=66296 D_OBJ#=66318
[W] - P_OBJ#=66301 D_OBJ#=66323
[W] - P_OBJ#=66302 D_OBJ#=66324
[W] - P_OBJ#=66325 D_OBJ#=66333
[W] - P_OBJ#=66327 D_OBJ#=66335
[W] - P_OBJ#=66328 D_OBJ#=66336
[W] - P_OBJ#=66329 D_OBJ#=66337
[W] - P_OBJ#=66330 D_OBJ#=66338
[W] - P_OBJ#=66331 D_OBJ#=66339
[W] - P_OBJ#=66332 D_OBJ#=66340
[W] - P_OBJ#=70394 D_OBJ#=70502
[W] - P_OBJ#=70395 D_OBJ#=70503
[W] - P_OBJ#=70396 D_OBJ#=70504
[W] - P_OBJ#=70397 D_OBJ#=70505
[W] - P_OBJ#=70398 D_OBJ#=70506
[W] - P_OBJ#=70402 D_OBJ#=70510
[W] - P_OBJ#=70403 D_OBJ#=70511
[W] - P_OBJ#=70404 D_OBJ#=70512
[W] - P_OBJ#=70405 D_OBJ#=70513
[W] - P_OBJ#=70406 D_OBJ#=70514
.- CheckDual ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ObjectNames ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ChkHiLo ... 1900000000 <= 1902000000 02/18 14:07:50 PASS
.- ChkIotTs ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- NoSegmentIndex ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- NextObject ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- DroppedROTS ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- FilBlkZero ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- DbmsSchemaCopy ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- IdnseqObj ... 1900000000 > 1201000000 02/18 14:07:50 PASS
.- IdnseqSeq ... 1900000000 > 1201000000 02/18 14:07:50 PASS
.- ObjError ... 1900000000 > 1102000000 02/18 14:07:50 PASS
.- ObjNotLob ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- MaxControlfSeq ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- SegNotInDeferredStg ... 1900000000 > 1102000000 02/18 14:07:50 PASS
.- SystemNotRfile1 ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- DictOwnNonDefaultSYSTEM ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ValidateTrigger ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- ObjNotTrigger ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- InvalidTSMaxSCN ... 1900000000 > 1202000000 02/18 14:07:50 PASS
.- OBJRecycleBin ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
.- LobSeg ... 1900000000 <= *All Rel* 02/18 14:07:50 PASS
---------------------------------------
18-FEB-2025 14:07:13 Elapsed: 3 secs
---------------------------------------
Found 0 potential problem(s) and 63 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
Trace File: /oracle/log/diag/rdbms/bltdb/bltdb1/trace/bltdb1_ora_905567_DICTCHECK.trc In this sample output, we see some objects (parent object ID P_OBJ#) that have a dependent object ID D_OBJ#, where the dependent timestamp (P_TIMESTAMP) does not match the parent timestamp (STIME). We followed My Oracle Support Note 1361045.1 to resolve the issue. Specifically, we compiled 63 public synonyms, and the underlying issue was resolved.
What is good about DBMS_DICTIONARY_CHECK is that it is self guiding, for each specific case you will encounter there is a support document you may refer to. Here is the list.
Data Recovery Advisor
The Data Recovery Advisor integrates with database health checks and RMAN to display data corruption problems, assess the extent of each problem (critical, high priority, low priority), describe the impact of a problem, recommend repair options, conduct a feasibility check of the customer-chosen option, and automate the repair process.
Final Thoughts:
Of course, there are many other tools and checks available for maintaining a robust database environment. Since each database is unique in its applications and infrastructure, DBAs should also implement custom checks tailored to their specific needs. There are many best practices you can follow, some of which are listed in the Customer Recommended Document titled ‘How to Perform a Health Check on the Database (Doc ID 122669.1)‘. These practices focus on areas such as the parameter file, control files, redo log files, archiving, datafile auto-extend attributes and locations, tablespace fragmentation and extent management, undo management, memory management, logging and tracing, among others.
Also, take a look at Carlos Sierra’s post, ‘A Healthy Way to Do an Oracle Database Health Check‘ , which shares valuable insights gained through years of experience, focusing more on the approach to be taken rather than purely technical details.
Hope it helps.
Osman’s DBlog 
Leave your comment