Over time, corruptions can sneak into your Oracle Database. Such corruptions may surface at the most inconvenient times, like upgrades, migrations, or PDB conversions. Here is a quick way of checking your Oracle Database and increasing your chance of success.

Oracle Database Dictionary Check

The documentation states:

DBMS_DICTIONARY_CHECK is a read-only and lightweight PL/SQL package procedure that helps you identify Oracle Database dictionary inconsistencies that are manifested in unexpected entries in the Oracle Database dictionary tables or invalid references between dictionary tables. Oracle Database dictionary inconsistencies can cause process failures and, in some cases, instance crash.

How to Execute a Dictionary Check

• First, the database must be on Oracle Database 19.22.0 or higher.
• Start the check:

  set serverout on
  exec dbms_dictionary_check.full;
  

• The database spools to the console and a trace file:

  dbms_dictionary_check on 02-MAR-2024 13:31:56
  ----------------------------------------------
  Catalog Version 19.0.0.0.0 (1900000000)
  db_name: UP19
  Is CDB?: NO
  Trace File:
  /u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc
  
                                  Catalog       Fixed
  Procedure Name                  Version    Vs Release    Timestamp      Result
  --------------------------- ... ---------- -- ---------- -------------- ------
  .- OIDOnObjCol		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
  .- LobNotInObj		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
  .- SourceNotInObj	       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
  
  ...
  
  .- LobSeg                   ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
  ---------------------------------------
  02-MAR-2024 13:31:56  Elapsed: 0 secs
  ---------------------------------------
  Found 0 potential problem(s) and 0 warning(s)
  Trace File:
  /u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc
  
  PL/SQL procedure successfully completed.
  

  • In this case, all checks pass.
• If there are any warnings or errors, you can find a detailed description of the check in the documentation.
• In multitenant, you run the check in the root container and all PDBs. The PDBs are most important.
• In Oracle Database 23c, the full check even has a fix mode which can correct certain known issues.

Words of Advice

• Run the check in due time before a major maintenance operation. Although many checks have a fix option, you sometimes need to engage with Oracle Support. This can take time, so don’t do it the day before an important upgrade.

• AutoUpgrade makes your life easier. Of course, it can run the dictionary check for you. Add the following to your config file:

  <prefix>.run_dictionary_health=full
  

  During the pre-upgrade analysis, AutoUpgrade executes the dictionary check and stores the full report in the precheck folder in the AutoUpgrade log directory.

The Artist Formerly Known As

If you think: This does sound very similar to health check or hcheck.sql, then you are on to something. Oracle moved the health check code into the database. No need to separately download the script; it’s always there.

Weed out those corruptions!