It’s a common question if a database is in a healthy state. DBA_REGISTRY shows the status of components loaded into the database. Mike Dietrich mentions in his blog two additional checks that should be performed regularly during off-peak hours for Oracle databases:
- 836658.1: Use RMAN “backup check logical validate database” to identify logical or physical block corruptions.
The Oracle Support document also compares RMAN and DBVerify commands. - 136697.1: Run “hcheck.full” to check for known data dictionary issues.
The Oracle Support document contains the latest version of the healthCheck package including installation descriptions. It is ecessary to connect and check each PDB if multitenant option is used.
A sample output for a 12cR1 PDB database from hckeck.full is shown below
H.Check Version 4.1
---------------------------------------
Catalog Version 12.1.0.2.0 (1201000200)
db_name: ABUCDB
Is CDB?: YES CON_ID: 5 Container: ABUPDB1
15-MAY-2016 19:16:33
---------------------------------------
Catalog Fixed
Procedure Name Version Vs Release Run
------------------------------ ... ---------- -- ---------- ---
.- SynLastDDLTim ... 1201000200 > 1001000200 : n/a
.- LobNotInObj ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- MissingOIDOnObjCol ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- SourceNotInObj ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- IndIndparMismatch ... 1201000200 > 1102000100 : n/a
.- InvCorrAudit ... 1201000200 > 1102000100 : n/a
.- OversizedFiles ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- TinyFiles ... 1201000200 > 900010000 : n/a
.- PoorDefaultStorage ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- PoorStorage ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- MissTabSubPart ... 1201000200 > 900010000 : n/a
.- PartSubPartMismatch ... 1201000200 > 1102000100 : n/a
.- TabPartCountMismatch ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- OrphanedTabComPart ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- ZeroIndSubPart ... 1201000200 > 902000100 : n/a
.- MissingSum$ ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- MissingDir$ ... 1201000200 <= *All Rel* : Ok 05/15 19:16:33
.- DuplicateDataobj ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- ObjSynMissing ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- ObjSeqMissing ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedUndo ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedIndex ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedIndexPartition ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedIndexSubPartition ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedTable ... 1201000200 <= *All Rel* : Ok 05/15 19:16:34
.- OrphanedTablePartition ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- OrphanedTableSubPartition ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- MissingPartCol ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- OrphanedSeg$ ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- OrphanedIndPartObj# ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- DuplicateBlockUse ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- FetUet ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- Uet0Check ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- ExtentlessSeg ... 1201000200 > 1102000100 : n/a
.- SeglessUET ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadInd$ ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadTab$ ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadIcolDepCnt ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- WarnIcolDep ... 1201000200 > 1101000700 : n/a
.- ObjIndDobj ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- DropForceType ... 1201000200 > 1001000200 : n/a
.- TrgAfterUpgrade ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- ObjType0 ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- ObjOidView ... 1201000200 > 900010000 : n/a
.- Idgen1$TTS ... 1201000200 > 900010000 : n/a
.- DroppedFuncIdx ... 1201000200 > 902000100 : n/a
.- BadOwner ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- StmtAuditOnCommit ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadPublicObjects ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadSegFreelist ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- BadCol# ... 1201000200 > 1001000200 : n/a
.- BadDepends ... 1201000200 <= *All Rel* : Ok 05/15 19:16:35
.- CheckDual ... 1201000200 <= *All Rel* : Ok 05/15 19:16:36
.- ObjectNames ... 1201000200 <= *All Rel* : Ok 05/15 19:16:36
.- BadCboHiLo ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- ChkIotTs ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- NoSegmentIndex ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- BadNextObject ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- OrphanIndopt ... 1201000200 > 902000800 : n/a
.- UpgFlgBitTmp ... 1201000200 > 1001000100 : n/a
.- RenCharView ... 1201000200 > 1001000100 : n/a
.- Upg9iTab$ ... 1201000200 > 902000400 : n/a
.- Upg9iTsInd ... 1201000200 > 902000500 : n/a
.- Upg10gInd$ ... 1201000200 > 1002000000 : n/a
.- DroppedROTS ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- ChrLenSmtcs ... 1201000200 > 1101000600 : n/a
.- FilBlkZero ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- DbmsSchemaCopy ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- OrphanedIdnseqObj ... 1201000200 > 1201000000 : Ok 05/15 19:16:37
.- OrphanedIdnseqSeq ... 1201000200 > 1201000000 : Ok 05/15 19:16:37
.- OrphanedObjError ... 1201000200 > 1102000000 : Ok 05/15 19:16:37
.- ObjNotLob ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
.- MaxControlfSeq ... 1201000200 <= *All Rel* : Ok 05/15 19:16:37
Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.
A sample output for a 11gR2 database from hckeck.full is shown below
H.Check Version 4.1
---------------------------------------
Catalog Version 11.2.0.2.0 (1102000200)
db_name: ABU
15-MAI-2016 20:10:33
---------------------------------------
Catalog Fixed
Procedure Name Version Vs Release Run
------------------------------ ... ---------- -- ---------- ---
.- SynLastDDLTim ... 1102000200 > 1001000200 : n/a
.- LobNotInObj ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- MissingOIDOnObjCol ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- SourceNotInObj ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- IndIndparMismatch ... 1102000200 > 1102000100 : n/a
.- InvCorrAudit ... 1102000200 > 1102000100 : n/a
.- OversizedFiles ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- TinyFiles ... 1102000200 > 900010000 : n/a
.- PoorDefaultStorage ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- PoorStorage ... 1102000200 <= *All Rel* : Ok 05/15 20:10:33
.- MissTabSubPart ... 1102000200 > 900010000 : n/a
.- PartSubPartMismatch ... 1102000200 > 1102000100 : n/a
.- TabPartCountMismatch ... 1102000200 <= *All Rel* : Ok 05/15 20:10:38
.- OrphanedTabComPart ... 1102000200 <= *All Rel* : Ok 05/15 20:10:39
.- ZeroIndSubPart ... 1102000200 > 902000100 : n/a
.- MissingSum$ ... 1102000200 <= *All Rel* : Ok 05/15 20:10:39
.- MissingDir$ ... 1102000200 <= *All Rel* : Ok 05/15 20:10:39
.- DuplicateDataobj ... 1102000200 <= *All Rel* : Ok 05/15 20:10:39
.- ObjSynMissing ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- ObjSeqMissing ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedUndo ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedIndex ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedIndexPartition ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedIndexSubPartition ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedTable ... 1102000200 <= *All Rel* : Ok 05/15 20:10:41
.- OrphanedTablePartition ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- OrphanedTableSubPartition ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- MissingPartCol ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- OrphanedSeg$ ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- OrphanedIndPartObj# ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- DuplicateBlockUse ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- FetUet ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- Uet0Check ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- ExtentlessSeg ... 1102000200 > 1102000100 : n/a
.- SeglessUET ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- BadInd$ ... 1102000200 <= *All Rel* : Ok 05/15 20:10:42
.- BadTab$ ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- BadIcolDepCnt ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- WarnIcolDep ... 1102000200 > 1101000700 : n/a
.- ObjIndDobj ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- DropForceType ... 1102000200 > 1001000200 : n/a
.- TrgAfterUpgrade ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- ObjType0 ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- ObjOidView ... 1102000200 > 900010000 : n/a
.- Idgen1$TTS ... 1102000200 > 900010000 : n/a
.- DroppedFuncIdx ... 1102000200 > 902000100 : n/a
.- BadOwner ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- StmtAuditOnCommit ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- BadPublicObjects ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- BadSegFreelist ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- BadCol# ... 1102000200 > 1001000200 : n/a
.- BadDepends ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- CheckDual ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- ObjectNames ... 1102000200 <= *All Rel* : Ok 05/15 20:10:43
.- BadCboHiLo ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- ChkIotTs ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- NoSegmentIndex ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- BadNextObject ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- OrphanIndopt ... 1102000200 > 902000800 : n/a
.- UpgFlgBitTmp ... 1102000200 > 1001000100 : n/a
.- RenCharView ... 1102000200 > 1001000100 : n/a
.- Upg9iTab$ ... 1102000200 > 902000400 : n/a
.- Upg9iTsInd ... 1102000200 > 902000500 : n/a
.- Upg10gInd$ ... 1102000200 > 1002000000 : n/a
.- DroppedROTS ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- ChrLenSmtcs ... 1102000200 > 1101000600 : n/a
.- FilBlkZero ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- DbmsSchemaCopy ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- OrphanedIdnseqObj ... 1102000200 < 1201000000 : n/a
.- OrphanedIdnseqSeq ... 1102000200 < 1201000000 : n/a
.- OrphanedObjError ... 1102000200 > 1102000000 : Ok 05/15 20:10:44
.- ObjNotLob ... 1102000200 <= *All Rel* : Ok 05/15 20:10:44
.- MaxControlfSeq ... 1102000200 <= *All Rel* : Ok 05/15 20:10:45
Found 0 potential problem(s) and 0 warning(s)
