Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘containers’

Oracle 23ai DBMS_HCHECK is your new best friend for Data Dictionary health

Posted by FatDBA on September 22, 2024

Today, while experimenting with Oracle 23ai new features, I came across a particularly helpful feature called DBMS_DICTIONARY_CHECK (referred to as DBMS_HCHECK in Oracle 23c). This tool proves to be both critical and incredibly useful for identifying and resolving data dictionary issues, which can sometimes be indirectly linked to errors like ORA-00600 or any inconsistencies or changes in DD.

I tested both options—FULL and CRITICAL—and found them highly effective. Both procedures are quick and performs a deep validation of the data dictionary, and while they print the results on the screen, they also write the output to a file on the operating system for further review. The FULL procedure conducts an exhaustive check, while the CRITICAL option focuses on only the most essential checks, making it quicker to execute. One of the most valuable aspects of these procedures is their ability not just to identify issues but also to repair them if you set the repair=>TRUE parameter.

The results of the execution are reported as:

CRITICAL: Requires immediate attention and fixing.
FAIL: High-priority issue that needs resolution.
WARN: Non-urgent but worth resolving.
PASS: No issues detected.

It brings back memories of the days when you had to manually download Oracle’s hcheck.sql script to analyze your data dictionary for known problems. Now, with this feature integrated directly into the database, it makes the process significantly more convenient and efficient.

Note: In Oracle 23c, this package is called DBMS_HCHECK, while in earlier versions (like Oracle 23.3ai), it’s referred to as DBMS_DICTIONARY_CHECK.

This integrated approach makes monitoring and maintaining the database’s health much simpler and more automated.

Below is how you perform a full check on the data dictionary.

SQL>  set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.full
dbms_dictionary_check on 22-SEP-2024 05:04:51
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 09/22 05:04:51 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ValidateSeg                 ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 09/22 05:04:52 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 09/22 05:04:53 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 09/22 05:04:53 PASS
.- ObjError                    ... 2300000000 >  1102000000 09/22 05:04:53 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 09/22 05:04:53 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:04:53 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- ObjLogicalConstraints       ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
.- ValidateFile                ... 2300000000 <=  *All Rel* 09/22 05:04:53 PASS
---------------------------------------
22-SEP-2024 05:04:53  Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:05:17
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:05:17 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:05:17 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:05:17 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:05:17 PASS
---------------------------------------
22-SEP-2024 05:05:17  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:06:45
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 09/22 05:06:45 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ValidateSeg                 ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 09/22 05:06:46 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 09/22 05:06:47 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 09/22 05:06:47 PASS
.- ObjError                    ... 2300000000 >  1102000000 09/22 05:06:47 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 09/22 05:06:47 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:06:47 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- ObjLogicalConstraints       ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
.- ValidateFile                ... 2300000000 <=  *All Rel* 09/22 05:06:47 PASS
---------------------------------------
22-SEP-2024 05:06:47  Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:09:29
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 09/22 05:09:29 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidateSeg                 ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 09/22 05:09:30 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 09/22 05:09:30 PASS
.- ObjError                    ... 2300000000 >  1102000000 09/22 05:09:30 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 09/22 05:09:30 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:09:30 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- ObjLogicalConstraints       ... 2300000000 <=  *All Rel* 09/22 05:09:30 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:09:31 PASS
.- ValidateFile                ... 2300000000 <=  *All Rel* 09/22 05:09:31 PASS
---------------------------------------
22-SEP-2024 05:09:31  Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:11:55
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 09/22 05:11:55 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidateSeg                 ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 09/22 05:11:56 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 09/22 05:11:56 PASS
.- ObjError                    ... 2300000000 >  1102000000 09/22 05:11:56 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 09/22 05:11:56 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:11:56 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ObjLogicalConstraints       ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
.- ValidateFile                ... 2300000000 <=  *All Rel* 09/22 05:11:56 PASS
---------------------------------------
22-SEP-2024 05:11:56  Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

PL/SQL procedure successfully completed.

SQL>

This is how you can run a quick CRITICAL check on the data dictionary.

SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.critical
dbms_dictionary_check on 22-SEP-2024 05:15:42
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:15:42 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:15:42 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:15:42 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:15:42 PASS
---------------------------------------
22-SEP-2024 05:15:42  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

PL/SQL procedure successfully completed.

It also creates a report within your DIAG directory as well.

-rw-r-----. 1 oracle oinstall  15K Sep 22 05:15 FREE_ora_3941_DICTCHECK.trm
-rw-r-----. 1 oracle oinstall  35K Sep 22 05:15 FREE_ora_3941_DICTCHECK.trc

And below is how you can even repair issues or inconsistencies with the data dictionary. In my case there isn’t any, but this is how you can do it.

SQL>
SQL>
SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE)
dbms_dictionary_check on 22-SEP-2024 05:13:36
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidateSeg                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 09/22 05:13:36 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 09/22 05:13:36 PASS
.- ObjError                    ... 2300000000 >  1102000000 09/22 05:13:36 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 09/22 05:13:36 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 09/22 05:13:36 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ObjLogicalConstraints       ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- SysSequences                ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
.- ValidateFile                ... 2300000000 <=  *All Rel* 09/22 05:13:36 PASS
---------------------------------------
22-SEP-2024 05:13:36  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc

PL/SQL procedure successfully completed.

SQL>

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , | Leave a Comment »

Dockers – OCI runtime create failed: container_linux.go:349: starting container process caused – process_linux.go:449: container init caused \ – write /proc/self/attr/keycreate: permission denied\’

Posted by FatDBA on September 29, 2020

Hi Guys,

Today I would be discussing about one of the problem that I have encountered while starting PostgreSQL on a docker container. This is the very first time we are calling any container to run on this machine. The error says something like ‘OCI runtime create failed: container_linux.go:349’, followed by “process_linux.go:449: container init caused: permission denied”.I was totally dumbstruck as the error doesn’t give us any clue or idea where and what is failing.

The exact error is given below, and is simulated case on my personal sandbox, but with exact error and issue.


[root@fatdba-doccass ~]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              817f2d3d51ec        4 days ago          314MB
[root@fatdba-doccass ~]# docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres
c90d92ea603044d72ffed2449e550bfd39d328beacb6a55e17c4515861f86140
docker: Error response from daemon: OCI runtime create failed: container_linux.go:349: starting container process caused "process_linux.go:449: container init caused 
\"write /proc/self/attr/keycreate: permission denied\"": unknown.


I remember we fixed something similar, not exactly the same on one another docker setup, where we disabled the SELINUX and that worked for me. So, we planned to give it a try to see if that works, this being a test setup, we didn’t hesitate to try the said option. It was set up to value ‘ENFORCING’ and we will have to set it to value ‘disabled’ and reboot the machine.


[root@fatdba-doccass ~]# more /etc/selinux/config |grep "SELINUX="
SELINUX=disabled
[root@fatdba-doccass ~]# reboot


Now, when the system is back, we are all set to run the postgresql image. 


[root@fatdba-doccass ~]# docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres
6aebd2ea4304202980daeff761857f5aa53deaf51cf7d13b1d00974219b6f80c
[root@fatdba-doccass ~]#
[root@fatdba-doccass ~]#


Awesome, it worked, let’s check the status of the container.


[root@fatdba-doccass ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
6aebd2ea4304        postgres            "docker-entrypoint.s…"   8 seconds ago       Up 4 seconds        5432/tcp            postgres
[root@fatdba-doccass ~]#
[root@fatdba-doccass ~]#


Next, we tried to connect with the host and psql terminal and that worked too!


[root@fatdba-doccass ~]# docker exec -it fatdba_psql bash
Error: No such container: fatdba_psql
[root@fatdba-doccass ~]# docker exec -it postgres bash
root@6aebd2ea4304:/#
root@6aebd2ea4304:/# psql -U postgres postgres
psql (13.0 (Debian 13.0-1.pgdg100+1))
Type "help" for help.

postgres=#
postgres=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

postgres=#
postgres=#
postgres-# \dt+
                             List of relations
 Schema | Name  | Type  |  Owner   | Persistence |    Size    | Description
--------+-------+-------+----------+-------------+------------+-------------
 public | dixit | table | postgres | permanent   | 8192 bytes |

Hope That Helped!
Prashant Dixit

Posted in Advanced | Tagged: , , , | Leave a Comment »