oracle数据字典一致性检查

来源：互联网发布：淘宝开店卖什么好一点编辑：程序博客网时间：2024/06/06 18:30

一.数据字典一致性检查

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,seeNote136697.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]

三.示例

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 这个脚本。