Oracle hcheck指令碼檢查 資料字典一致性 說明

 

一.資料字典不一致說明

MOS 文檔說明：Identify Data Dictionary Inconsistency [ID 456468.1]

 

1.1 資料字典損壞

資料字典損壞通常有如下情況：

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. Corrupted data dictionary objects (table, index, or table-indexinconsistency) 
4. Invalid entries in data dictionary tables. 

 

資料字典損壞對系統的影響是可能某些使用者和對象無法進行刪除，當遇到這種情況時，就可以檢查一下資料字典的一致性。

 

1.2 識別資料字典的不一致性

In order todetect data dictionary inconsistency we need to run hcheck.full procedure,see Note136697.1. 
       --為了檢查資料字典的不一致性，Oracle 提供了hcheck.fuu 過程。其具體的使用方法如下：

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 various dictionary related issues that may or may not be aproblem. Any problems reported should be reviewed by an experiencedsupport analyst as some reported "problems" may be normal andexpected. 

--該指令碼會報告資料字典相關的各種問題。

 

Example of HCHECK.FULL output: 

--個HCHECK.FULL輸出樣本

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.

--根據這個輸出結果，我們可以驗證這個不一致性：

 

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.

 

二.Hcheck 指令碼

MOS 文檔：

Script 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]

 

這裡的2個指令碼較長，可以直接從csdn 下載：

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

 

 

三.樣本

 

3.1 用SYS 使用者建立Hout包

指令碼參考[ID101468.1]

 

3.2 在SYS使用者下建立hcheck包

指令碼參考：[ID 136697.1]

 

3.3 執行hcheck.full

 

SQL>@E:\Software\OracleSoftware\Hcheck_Full_Scripts\hout.sql

Package created.

No errors.

Package body created.

 

--這裡注意，MOS上對hcheck指令碼提供了2個版本：hcheck2.sql 和 hcheck3.sql。

 

--執行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 對應的是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.

 

 

--執行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對應的是9i

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

Catalog Version 11.2.0.1.0 (1102000100)

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

 

                                   Catalog       Fixed

Procedure Name                     Version    Vs Release      Run

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

.- SynLastDDLTim               ... 1102000100 >  1001000200 : n/a

.- LobNotInObj                 ... 1102000100 >  1000000200 : n/a

.- MissingOIDOnObjCol          ... 1102000100 <=  *All Rel* : Ok

.- SourceNotInObj              ... 1102000100 >  1002000100 : n/a

.- IndIndparMismatch           ... 1102000100 <= 1102000100 : Ok

.- InvCorrAudit                ... 1102000100 <= 1102000100: Ok

.- OversizedFiles              ... 1102000100 <=  *All Rel* : Ok

.- TinyFiles                   ... 1102000100 >   900010000 : n/a

.- PoorDefaultStorage          ... 1102000100 <=  *All Rel* : Ok

.- PoorStorage                 ... 1102000100 <=  *All Rel* : Ok

.- MissTabSubPart              ... 1102000100 >   900010000 : n/a

.- PartSubPartMismatch         ... 1102000100 <= 1102000100 : Ok

.- TabPartCountMismatch        ... 1102000100 <=  *All Rel* : Ok

.- OrphanedTabComPart          ... 1102000100 >   900010000 : n/a

.- ZeroTabSubPart              ... 1102000100 >   902000100 : n/a

.- 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/a

.- DuplicateBlockUse           ... 1102000100 <=  *All Rel* : Ok

.- HighObjectIds               ... 1102000100 >   801060000 : n/a

.- PQsequence                  ... 1102000100 >   800060000 : n/a

.- TruncatedCluster            ... 1102000100 >   801070000 : n/a

.- 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/a

.- WarnIcolDep                 ... 1102000100 >  1101000700 : n/a

.- OnlineRebuild$              ... 1102000100 <=  *All Rel* : Ok

.- DropForceType               ... 1102000100 >  1001000200 : n/a

.- TrgAfterUpgrade             ... 1102000100 <=  *All Rel* : Ok

.- FailedInitJVMRun            ... 1102000100 <=  *All Rel* : Ok

.- TypeReusedAfterDrop         ... 1102000100 >   900010000 : n/a

.- Idgen1$TTS                  ... 1102000100 >   900010000 : n/a

.- DroppedFuncIdx              ... 1102000100 >   902000100 : n/a

.- BadOwner                    ... 1102000100 >   900010000 : n/a

.- UpgCheckc0801070            ... 1102000100 <=  *All Rel* : Ok

.- BadPublicObjects            ... 1102000100 <=  *All Rel* : Ok

.- BadSegFreelist              ... 1102000100 <=  *All Rel* : Ok

.- BadCol#                     ... 1102000100 >  1001000200 : n/a

.- 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/a

.- UpgFlgBitTmp                ... 1102000100 >  1001000100 : n/a

.- RenCharView                 ... 1102000100 >  1001000100 : n/a

.- Upg9iTab$                   ... 1102000100 >   902000400 : n/a

.- Upg9iTsInd                  ... 1102000100 >   902000500 : n/a

.- Upg10gInd$                  ... 1102000100 >  1002000000 : n/a

.- DroppedROTS                 ... 1102000100 <=  *All Rel* : Ok

.- ChrLenSmtcs                 ... 1102000100 >  1101000600 : n/a

.- FilBlkZero                  ... 1102000100 <=  *All Rel* : Ok

 

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

Contact Oracle Support with the output

to check if the above needs attention ornot

 

PL/SQL procedure successfully completed.

 

SQL> spool off

 

 

通過以上的執行結果，hcheck 的2個指令碼顯示的不一樣。查看這2個指令碼的說明部分：

 

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.

--這裡注意的是對locallymanaged tablespace 不檢測。

 

關於資料表空間類型這塊，可以參考：

Oracle 自動段空間管理(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" should 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 documented. 

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" should 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 documented.

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

--  it in your environment before relying on it.

 

從這上面來看，2個指令碼說明也一樣，從版本上來看，還是推薦使用hcheck3.sql 這個指令碼。

 

 

 

 

 

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

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

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

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940