hcheck.sql - Script to Check Data Dictionary for Known Problems (Doc ID 136697.1)

 

In this Document

Purpose
 Current Versions
Troubleshooting Steps
 Requirements
 Configuring
 Instructions
 Script
 Sample Output
References

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Purpose

  • To provide a single anonymous PL/SQL block to check for common Data Dictionary problems.
  • The script checks consistency of selected dictionary relationships and looks for certain known issues - some reported "problems" will be normal and expected.
  • The script can be used with Oracle Release 9i onwards.  hcheck8i.sql includes 8.1.
  • It is a lightweight "read-only" script with no impact.
  • The script is for use mainly under the guidance of Oracle Support.

Current Versions

Note: Although this script has been tested internally by Oracle Support and Development, it should be run (and resolve any issues) in a development or test environment before running it in production.

Troubleshooting Steps

Requirements

Execution Environment: SQL*Plus

Access Privileges: Requires to be run connected as SYS schema

Usage:

$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 19 08:14:13 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter username: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off

For 12c with Multitenant, connect to each PDB to run the script. Example:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 19 08:14:13 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter username: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

CON_ID   CON_NAME     OPEN MODE      RESTRICTED
-------- ------------ -------------- -------------
2        PDB$SEED     READ ONLY      NO
3        CDB1_PDB1    READ WRITE     NO
4        CDB1_PDB2    READ WRITE     NO

SQL> alter session set container=CDB1_PDB1;

Session altered.

SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off

Configuring

For 19.22.0.0.240116 onward :

SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.full

For Oracle 10.2.0.5 to Database Jan 2024 Release Update (DB RU) 19.22.0.0.240116:

  1. Connect as SYS schema in SQL*Plus.
  2. Run script hcheck.sql. If using Oracle 12c with Multitenant (CDB), connect to each PDB (including CDB$ROOT) as needed.

For Oracle 9i to 10.2.0.4:

  1. Connect as SYS schema in SQL*Plus.
  2. Run script hcheck_10204.sql.

For Oracle 8i:

  1. Connect as SYS schema in SQL*Plus.
  2. Create package hOut as described in Note:101468.1.
  3. Create package hcheck by running hcheck8i.sql.

Instructions

1. Connect as SYS schema in SQL*Plus. If using Oracle 12c with Multitenant (CDB), connect to each PDB.

2. spool outputfile (optional, as a trace file with the output is created).

3. Run hcheck.sql in SYS schema - this is for Oracle 10.2.0.5 onwards. For Oracle 9i until 10.2.0.4, use hcheck_10204.sql.  For Oracle 8i, create package hOut as described in Note:101468.1 and run hcheck8i.sql. 

4. Output will go to the spool file (if spool was defined) and the session trace file.

5. spool off

6. Review the spool file or the trace file (sid_ora_pid_HCHECK.trc).
  • The script reports various dictionary related issues which may or may not be a problem.  Any problems reported should be reviewed by an Oracle Support analyst as some reported "problems" may be normal and expected.
  • HCKE errors are considered as a potential problem.
  • HCKW errors are considered as a warning.
  • The hcheck.sql script for Oracle 9i onwards has these status results:
    CRITICAL: Is of highest risk and requires urgent fix.
    FAIL:         Requires resolution on priority.
    WARN:      Good to resolve.
    PASS:        No problem.

Script

The relevant script can be downloaded here:

 

 


NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s),
Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead,
is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Schema=SCOTT
Tables=SALES_REP

 

Sample Output

$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on ...

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter username: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> spool hcheck.log

SQL> @hcheck

HCheck Version 07MAY18 on .....
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name:
Is CDB?: YES CON_ID: 3 Container: CDB1_PDB1

                                        Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp      Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj                 ... 1202000100 <= *All Rel*  ..... 09:49:01 PASS
.- MissingOIDOnObjCol          ... 1202000100 <= *All Rel*  ..... 09:49:01 PASS
...

.- OrphanedTable               ... 1202000100 <= *All Rel*  ..... 09:49:02 FAIL

HCKE-0019: Orphaned TAB$ (no SEG$) (Doc ID 1360889.1)
ORPHAN TAB$: OBJ#=105785 DOBJ#=105785 TS=7 RFILE/BLOCK=7/113 TABLE=SCOTT.SALES_REP BOBJ#=

.- MaxControlfSeq              ... 1202000100 <= *All Rel*  ..... 09:49:02 PASS
.- SegNotInDeferredStg         ... 1202000100 > 1102000000  ..... 09:49:02 PASS
---------------------------------------
..................... Elapsed: 1 secs
---------------------------------------
Found 1 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/<path>/diag/rdbms/<db_name>/<oracle_sid>/trace/<oracle_sid>_<ora>_<pid>_HCHECK.trc


SQL> spool off

 

References

NOTE:101468.1 - Script to Install the "hOut" Helper Package ("hout.sql")
NOTE:101466.1 - Introduction to the "H*" Helper Scripts

Comments

Post a Comment

Popular posts from this blog

How To Check if Asynchronous I/O is Working On Linux (Doc ID 237299.1)

  Caveat for ASMLib APPLIES TO: Linux OS - Version Enterprise Linux 3.0 to Oracle Linux 6.5 with Unbreakable Enterprise Kernel [3.8.13] [Release RHEL3 to OL6U5] Oracle Database - Enterprise Edition - Version 9.0.1.0 to 12.1.0.2 [Release 9.0.1 to 12.1] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Linux x86 Linux x86-64 **Checked for relevance on 10-Nov-2010*** GOAL In this document we are going to explain how to check that asynchronous I/O (AIO) is working. AIO can be enabled in Oracle 9i 9.2 and higher. Many times there is a requirement to check if Asynchronous I/O is working on Linux Platform, so we can try to use it for our datafiles access inside database. SOLUTION The slabinfo maintains statistics about objects in memory. Some of the structs used by Asynchronous I/O are treated as objects in th...
Read more

Primary Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)

Image
  To Bottom In this Document Purpose Details   Actions    CORRUPTION EXPLANATION   TYPES OF CORRUPTIONS   HOW TO HANDLE SEGMENT CORRUPTIONS   PACKAGE INFORMATION AND EXAMPLES   TOOLS   GENERIC LINKS   COMMON ERRORS References Applies to: Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Service - Version N/A and later Information in this document applies to any platform. Purpose Primary Note for Handling Oracle Database Corruption Issues Details The goal of this article is to give a global picture about corruption errors and appropriate actions depending where the corruption is found.  In addition to the Support Notes below, you can get additional information here:  My Oracle Support Comunity The infor...
Read more

Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes (Doc ID 836658.1)

Image
  To Bottom In this Document Purpose Scope   Corruption Types Details   Datafile Block Corruption - Intra block corruption   RMAN - Identify Datafile Block Corruptions   DBVerify - Identify Datafile Block Corruptions   RMAN Vs DBVerify - Datafile Intra Block Corruption   Identify TABLE / INDEX Mismatch   Identify Extents Inconsistency in Locally Managed Tablespace   Identify Extents Inconsistency in Dictionary Managed Tablespaces   Identify Data Dictionary Inconsistency   Identify corruption caused by LOST WRITES References Applies to: Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1] Oracle Database Cloud Schema Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Information in this doc...
Read more