Description of Oracle hcheck script for checking data dictionary consistency

 

1. Description of inconsistent data dictionaries

Mos Description: Identify data dictionary inconsistency [ID 456468.1]

 

1.1 data dictionary corruption

Data Dictionary corruption is usually caused by the following situations:

1. Data Dictionary inconsistency, missingrows in tables:

-Tab $/ind $ with no entries in OBJ $
-Undo $/TAB $/ind $ withno entries in seg $
-Seg $ with no entriesin tab $/ind $/obj $
2. Missing Data Dictionary objects
3. partitioned upted data dictionary objects (table, index, or table-indexinconsistency)
4. Invalid entries in data dictionary tables.

 

The impact of data dictionary damage on the system is that some users and objects cannot be deleted. In this case, you can check the data dictionary consistency.

 

1.2 identify inconsistency of data dictionaries

In order todetect data dictionary inconsistency we need to run hcheck. Full procedure, see note136697.1.
-- To check the inconsistency of the data dictionary, Oracle provides the hcheck. fuu process. The usage is as follows:

A. Connect assys schema in sqlplus
B. Create package Hout as described in note101468.1
C. Create package hcheck in SYS schema asdescribed in note136697.1 attachment.
D. Set serveroutput on
E. Execute hcheck. Full

The script willreport varous dictionary related issues that may or may not be aproblem. Any problems reported shocould be reviewed by an experiencedsupport analyst as some reported "problems" may be normal andexpected.

-- This script will report various issues related to the data dictionary.

 

Example of hcheck. Full output:

-- Hcheck. Full output example

Problem: orphaned ind $ (no seg $)-See note 65987.1 (BUG: 624613/3655873)
Orphan ind $: OBJ = 200449 dobj = 200449 Ts = 0 rfile/block = 0 0 bo # = 200446 segtype =
^-May be OK. Needs manual check
Orphan ind $: OBJ = 39442 dobj = 39442 Ts = 14 rfile/block = 2 49 bo # = 39438 segtype =

Problem: orphaned tab $ (no seg $)
Orphan tab $: OBJ = 1817074 dobj = 0 Ts = 0 rfile/block = 0 0 bobj # = segtype =
^-May be OK. Needs manual check
Orphan tab $: OBJ = 2149126 dobj = 2149126 Ts = 19 rfile/block = 31 44291 bobj # = segtype =

Problem: orphaned seg $ entry
Orphan seg $: segtype = index Ts = 20 rfile/block = 33 28435

 

Based on thehcheck. Full output you will have to identify the objects that show a ddinconsistency, and verify the reported inconsistency.

-- Based on the output result, we can verify the inconsistency:

 

Selectname, type # From OBJ $ where OBJ # = <OBJ>;/* 1 = index, 2 = table, 3 = cluster, 21 = lob, 25 = Iot

 

Selectobject_name, owner, object_type from dba_objects whereobject_id = <OBJ>;

 

Some of theproblems, mainly the one marked as 'may be OK. Needs manual check 'couldbe a false alarm.

Check the typeof the object.

Lob index onTemporary table or Iot do not have a segment, than the problem message is afalse alarm.

 

Ii. hcheck script

Mos document:

Scripts to install the "Hout" helper package ("Hout. SQL") [ID 101468.1]

"Hcheck. SQL" script to check forknown problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g [ID 136697.1]

 

The two scripts here are long and can be downloaded directly from csdn:

Http://download.csdn.net/detail/tianlesoftware/4046773

 

 

Iii. Example

 

3.1 create a Hout package with the Sys user

For the script, refer to [id101468.1].

 

3.2 Create an hcheck package under the Sys user

Script Reference: [ID 136697.1]

 

3.3 run hcheck. Full

 

SQL> @ E: \ Software \ oraclesoftware \ hcheck_full_scripts \ Hout. SQL

Package created.

No errors.

Package body created.

 

-- Note that there are two versions of hcheck script on MOS: hcheck2. SQL and hcheck3. SQL.

 

-- Execute hcheck2. SQL

SQL> @ E: \ Software \ oraclesoftware \ hcheck_full_scripts \ hcheck2. SQL

Package created.

No errors.

Package body created.

No errors.

 

SQL>

 

SQL> exec hcheck. Full

Hcheck version 8i-11/2.00 -- version 2 corresponds to 8i

 

Problem: seg $ bad lists/groups (= 1)-see tar: 2470806.1

May be OK for lobsegment/securefile inrelease 11gr1 +

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 22177 type # = 8 lists = 2 groups = 1

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 22233 type # = 8 lists = 2 groups = 1

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 22937 type # = 8 lists = 2 groups = 1

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 22977 type # = 8 lists = 2 groups = 1

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 23017 type # = 8 lists = 2 groups = 1

Bad seg $ lists/groups: ts # = 1 rfile # = 2blk # = 24505 type # = 8 lists = 2 groups = 1

 

Warning: object name clashes with schema name-BUG: 2894111 Etc ..

Schema = Dave object = Dave. Dave (table)

Schema = DVD object = DVD. DVD (table)

 

Found 6 potential problems and 2 warnings

Contact Oracle support with the output

To check if the above needs attention ornot

 

PL/SQL procedure successfully completed.

 

 

-- Execute hcheck3. SQL

SQL> @ E: \ Software \ oraclesoftware \ hcheck_full_scripts \ hcheck3. SQL

Package created.

Package body created.

 

SQL> spool D: \ hcheck.txt

SQL> exec hcheck. Full

H. Check version 9i +/hc3.35 -- Version 3 corresponds to 9i

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

Catalog version 11.2.0.1.0 (1102000100)

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

 

Catalog fixed

Procedure name VERSION vs release run

------------------------------...-------------------------

.-Synlastddltim... 1102000100> 1001000200: N/

.-Lobnotinobj... 1102000100> 1000000200: N/

.-Missingoidonobjcol... 1102000100 <= * All rel *: OK

.-Sourcenotinobj... 1102000100> 1002000100: N/

.-Indindparmismatch... 1102000100 <= 1102000100: OK

.-Invcorraudit... 1102000100 <= 1102000100: OK

.-Oversizedfiles... 1102000100 <= * All rel *: OK

.-Tinyfiles... 1102000100> 900010000: N/

.-Poordefaultstorage... 1102000100 <= * All rel *: OK

.-Poorstorage... 1102000100 <= * All rel *: OK

.-Misstabsubpart... 1102000100> 900010000: N/

.-Partsubpartmismatch... 1102000100 <= 1102000100: OK

.-Tabpartcountmismatch... 1102000100 <= * All rel *: OK

.-Orphanedtabcompart... 1102000100> 900010000: N/

.-Zerotabsubpart... 1102000100> 902000100: N/

.-Missingsum $... 1102000100 <= * All rel *: OK

.-Missingdir $... 1102000100 <= * All rel *: OK

.-Duplicatedataobj... 1102000100 <= * All rel *: OK

.-Objsynmissing... 1102000100 <= * All rel *: OK

.-Objseqmissing... 1102000100 <= * All rel *: OK

.-Orphanedundo... 1102000100 <= * All rel *: OK

.-Orphanedindex... 1102000100 <= * All rel *: OK

.-Orphanedindexpartition... 1102000100 <= * All rel *: OK

.-Orphanedindexsubpartition... 1102000100 <= * All rel *: OK

.-Orphanedtable... 1102000100 <= * All rel *: OK

.-Orphanedtablepartition... 1102000100 <= * All rel *: OK

.-Orphanedtablesubpartition... 1102000100 <= * All rel *: OK

.-Missingpartcol... 1102000100 <= * All rel *: OK

.-Orphanedseg $... 1102000100 <= * All rel *: OK

.-Orphanedindpartobj #... 1102000100> 1101000600: N/

.-Duplicateblockuse... 1102000100 <= * All rel *: OK

.-Highobjectids... 1102000100> 801060000: N/

.-Pqsequence... 1102000100> 800060000: N/

.-Truncatedcluster... 1102000100> 801070000: N/

.-Fetuet... 1102000100 <= * All rel *: OK

.-Uet0check... 1102000100 <= * All rel *: OK

.-Extentlessseg... 1102000100 <= * All rel *: OK

.-Seglessuet... 1102000100 <= * All rel *: OK

.-Badind $... 1102000100 <= * All rel *: OK

.-Badtab $... 1102000100 <= * All rel *: OK

.-Badicoldepcnt... 1102000100> 1101000700: N/

.-Warnicoldep... 1102000100> 1101000700: N/

.-Onlinerebuild $... 1102000100 <= * All rel *: OK

.-Dropforcetype... 1102000100> 1001000200: N/

.-Trgafterupgrade... 1102000100 <= * All rel *: OK

.-Failedinitjvmrun... 1102000100 <= * All rel *: OK

.-Typereusedafterdrop... 1102000100> 900010000: N/

.-Idgen1 $ TTS... 1102000100> 900010000: N/

.-Droppedfuncidx... 1102000100> 902000100: N/

.-Badowner... 1102000100> 900010000: N/

.-Upgcheckc0801070... 1102000100 <= * All rel *: OK

.-Badpublicobjects... 1102000100 <= * All rel *: OK

.-Badsegfreelist... 1102000100 <= * All rel *: OK

.-Badcol #... 1102000100> 1001000200: N/

.-Baddepends... 1102000100 <= * All rel *: OK

.-Checkdual... 1102000100 <= * All rel *: OK

.-Objectnames... 1102000100 <= * All rel *: OK

 

HCKW-0018: object name clashes with schemaname

Schema = Dave object = Dave. Dave (table)

Schema = DVD object = DVD. DVD (table)

 

.-Badcbohilo... 1102000100 <= * All rel *: OK

.-Chkiotts... 1102000100 <= * All rel *: OK

.-Nosegmentindex... 1102000100 <= * All rel *: OK

.-Badnextobject... 1102000100 <= * All rel *: OK

.-Orphanindopt... 1102000100> 902000800: N/

.-Upgflgbittmp... 1102000100> 1001000100: N/

.-Rencharview... 1102000100> 1001000100: N/

.-Upg9itab $... 1102000100> 902000400: N/

.-Upg9itsind... 1102000100> 902000500: N/

.-Upg10gind $... 1102000100> 1002000000: N/

.-Droppedrots... 1102000100 <= * All rel *: OK

.-Chrlensmtcs... 1102000100> 1101000600: N/

.-Filblkzero... 1102000100 <= * All rel *: OK

 

Found 0 potential problem (s) and 2 warning (s)

Contact Oracle support with the output

To check if the above needs attention ornot

 

PL/SQL procedure successfully completed.

 

SQL> spool off

 

 

Based on the preceding execution results, the two hcheck scripts are different. View the descriptions of the two scripts:

 

Rem hcheck8i. SQL version 2.00 Tue Mar 1 11:13:40 CET 2011

Rem

Rem purpose:

Rem toprovide a single package which looks for common data dictionary

Rem problems.

Rem notethat this version has not been checked with locally managed

Rem tablespacesand may give spurious output if these are in use.

Rem thisscript is for use mainly under the guidance of Oracle Support.

-- Note that locallymanaged tablespace is not detected.

 

For details about the tablespace type, refer:

Oracle automatic segment space management (assm: autosegment space management)

Http://blog.csdn.net/tianlesoftware/article/details/4958989

 

Rem notes:

Rem mustbe installed in SYS Schema

Rem thispackage is intended for use in Oracle 8.1 through 11.1

Rem this package will not work in 8.0 or earlier.

Rem in all cases any output reporting "problems" shocould be

Rem passed by an experienced Oracle Support Analyst to confirm

Rem if any action is required.

Rem

Rem caution

Rem the sample program in this article is provided for educational

Rem purposes only and is not supported by Oracle Support Services.

Rem it has been tested internally, however, and works as incluented.

Rem we do not guarantee that it will work for you, so be sure to test

Rem it in your environment before relying on it.

 

 

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

-- Hcheck. SQL version 3.35 Thu Dec 22 09:44:47 CET 2011

--

-- Purpose:

-- To provide a single package which looks for common data dictionary

-- Problems.

-- Note that this version has not been checked with locally managed

-- Tablespaces and may give spurious output if these are in use.

-- This script is for use mainly under the guidance of Oracle Support.

 

-- Notes:

-- Must be installed in SYS Schema

-- This package is intended for use in Oracle Releases 9i onwards

-- This package will not work in 8i or earlier.

-- In all cases any output reporting "problems" shocould be

-- Parsed by an experienced Oracle Support Analyst to confirm

-- If any action is required.

--

-- Caution

-- The sample program in this article is provided for educational

-- Purposes only and is not supported by Oracle Support Services.

-- It has been tested internally, however, and works as incluented.

-- We do not guarantee that it will work for you, so be sure to test

-- It in your environment before relying on it.

 

From this point of view, the two scripts are the same. From the version perspective, we recommend that you use the hcheck3. SQL script.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940