DBMS_DICTIONARY_CHECK for Seamless Upgrades to 23ai

With the release of Oracle 23ai, a plethora of new features awaits exploration, and DBMS_DICTIONARY_CHECK stands out among them. Delving into the documentation, and accompanying MOS notes reveals that this package is hccheck.sql, now conveniently packaged within the database itself. Since it is now packaged within the database, the autoupgrade tool has also integrated it as an option in the configuration file.

DBMS_DICTIONARY_CHECK

So let’s dive in. There are three ways to execute the check.

• Individual checks: These can be executed by calling the specific procedure for the check that you want to execute

set serveroutput on size unlimited
execute dbms_dictionary_check.ValidDir;

• Only Critical Checks: This procedure runs the following checks UndoSeg, MaxControlfSeq and InvalidTSMaxSCN

set serveroutput on size unlimited
execute dbms_dictionary_check.critical ;

                          Catalog       Fixed
Procedure Name            Version    Vs Release    Timestamp      Result
------------------- ... ---------- -- ---------- -------------- ------
.- UndoSeg          ... 1900000000 <=  *All Rel* 05/16 04:15:05. PASS
.- MaxControlfSeq   ... 1900000000 <=  *All Rel* 05/16 04:15:05  PASS
.- InvalidTSMaxSCN  ... 1900000000 >  1202000000 05/16 04:15:05. PASS

• ALL the Checks: As the name suggests this procedure will run all the checks. This can be done by the FULL procedure.

19c vs 23ai

This package has been backported to version January 2024 RU [19.22], however, one difference between the 19.23 and 23ai is that “REPAIR OPTION” has not been backported. Below is when I attempted to execute this check on 19.23

SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE)
BEGIN dbms_dictionary_check.full(repair=>TRUE); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FULL'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Interpreting the results

Let’s look at the execution of DBMS_DICTIONARY_CHECK with an example involving synonyms on 19.23. So when you execute the package, along with the output, a trace file with the suffix DICTCHECK is also created.

As shown below, the check flaged some inconsistencies. Reviewing it you will notice, that some of the warnings are related to the check ValidDepends. The way to investigate further is to review the document referenced in the output, in this case, MOS note 1361045.1. We can further use the SQL in the MOS note to list the offending objects.

set serveroutput on size unlimited
exec dbms_dictionary_check.full;
                          Catalog       Fixed
Procedure Name            Version    Vs Release    Timestamp      Result
------------------- ... ---------- -- ---------- -------------- ------
.- UndoSeg          ... 1900000000 <=  *All Rel* 05/16 04:15:05. PASS
.- ValidDepends     ... 1900000000 <=  *All Rel* 05/16 03:18:44 WARN
HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID
1361045.1)
Found 0 potential problem(s) and 123 warning(s)

Contact Oracle Support with the output and trace file
to check if the above needs attention or not
Trace File: /ora01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_23719_DICTCHECK.trc

SQL> @script_in_1361045.sql
D_OBJ D_NAME D_TYPE P_OBJ P_NAME P_Timestamp STIME X

---------- -------------------- ---------- ---------- -------------------- ----------------------------- ----------------------------- --------

66039 oracle/spatial/geocoder/Geocoder 5 65983 oracle/spatial/geocoder/Geocoder 15-MAY-2024 18:57:13 15-MAY-2024 18:59:30 *DIFFER*  

As the output indicated there were 123 warnings. Review the warnings further, the objects appeared to be public synonyms. Another relevant document to resolve the problem at hand was the Dependency$ P_timestamp Mismatch For VALID Public Synonyms Owned By MDSYS (Doc ID 1541636.1)

SELECT 'alter public synonym "'||do.name||'" compile;'
FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po
WHERE P_OBJ#=po.obj#(+)
AND D_OBJ#=do.obj#
AND do.status=1 /*dependent is valid*/
AND po.status=1 /*parent is valid*/
AND po.stime!=p_time  2  stamp;
ORDER BY 2,1;  3    4    5    6    7
'ALTERPUBLICSYNONYM"'||DO.NAME||'"COMPILE;'
-----------------------------------------------------------------------------
alter public synonym "oracle/spatial/geocoder/SQLParserStub" compile;
....
..

SQL> alter public synonym "oracle/spatial/geocoder/SQLParserStub" compile;

Synonym altered.

SQL> execute dbms_dictionary_check.full;
...
..
Found 0 potential problem(s) and 0 warning(s)
Trace File: /ora01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_24030_DICTCHECK.trc

PL/SQL procedure successfully completed.

So following the document, all the synonyms were compiled, and the check completed successfully. Hence, as you can see we can harness the capabilities of this package to mitigate potential risks of dictionary corruption. This can help for a smooth database upgrade 23ai. In case, you want to upgrade to 23ai, then do check out the potential issues to be avoided.