[20190306]11g health monitor.txt

--//前幾天做刪除tab$的恢復,看連結https://blog.csdn.net/Enmotech/article/details/87834503,我的測試

--//連結:http://blog.itpub.net/267265/viewspace-2637010/=>[20190225]刪除tab$記錄的恢復5.txt.

--//理論講不會遇到對方的資料字典不一致的問題.

--//blog裡面提到hcheck指令碼,不知道是什麼,不過11g提供health monitor,裡面也提供資料字典的檢查.

--//對這方面知識不是很瞭解,學習看看:

1.環境:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.瞭解有那些檢查專案:

--//主要的檢視集中v$hm開頭的檢視.

SYS@book> select * from v$hm_check where internal_check='N';

ID NAME NAME_NLS CLSID CLS_NAME FLAGS I O DESCRIPTION

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

2 DB Structure Integrity Check DB Structure Integrity Check 2 PERSISTENT_DATA 4098 N Y Checks integrity of all database files

25 CF Block Integrity Check CF Block Integrity Check 2 PERSISTENT_DATA 4098 N Y Checks integrity of a control file block

3 Data Block Integrity Check Data Block Integrity Check 2 PERSISTENT_DATA 4098 N Y Checks integrity of a data file block

4 Redo Integrity Check Redo Integrity Check 2 PERSISTENT_DATA 4098 N Y Checks integrity of redo log content

10 Transaction Integrity Check Transaction Integrity Check 2 PERSISTENT_DATA 4096 N N Checks a transaction for corruptions

11 Undo Segment Integrity Check Undo Segment Integrity Check 2 PERSISTENT_DATA 4096 N N Checks integrity of an undo segment

24 Dictionary Integrity Check Dictionary Integrity Check 2 PERSISTENT_DATA 4096 N N Checks dictionary integrity

27 ASM Allocation Check ASM Allocation Check 3 ASM 8194 N Y Diagnose allocation failure

8 rows selected.

3.執行時相關引數:

CHECK_NAME PARAMETER_NAME TYPE DEFAULT_VALUE DESCRIPTION

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

ASM Allocation Check ASM_DISK_GRP_NAME DBKH_PARAM_TEXT ASM group name

CF Block Integrity Check CF_BL_NUM DBKH_PARAM_UB4 Control file block number

Data Block Integrity Check BLC_DF_NUM DBKH_PARAM_UB4 File number

Data Block Integrity Check BLC_BL_NUM DBKH_PARAM_UB4 Block number

Dictionary Integrity Check CHECK_MASK DBKH_PARAM_TEXT ALL Check mask

Dictionary Integrity Check TABLE_NAME DBKH_PARAM_TEXT ALL_CORE_TABLES Table name

Redo Integrity Check SCN_TEXT DBKH_PARAM_TEXT 0 SCN of the latest good redo (if known)

Transaction Integrity Check TXN_ID DBKH_PARAM_TEXT Transaction ID

Undo Segment Integrity Check USN_NUMBER DBKH_PARAM_TEXT Undo segment number

9 rows selected.

4.手工測試資料字典:

--//拿我恢復的測試測試看看.

SYS@book> exec dbms_hm.run_check('Dictionary Integrity Check','test_dict_check');

BEGIN dbms_hm.run_check('Dictionary Integrity Check','test_dict_check'); END;

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01427: single-row subquery returns more than one row

ORA-06512: at "SYS.DBMS_HM", line 191

ORA-06512: at line 1

--//報錯,跟蹤發現:執行如下報錯:

/* Formatted on 2019/3/6 10:17:25 (QP5 v5.252.13127.32867) */

SELECT 52, ROWID, 'ind$.obj#'

FROM IND$

WHERE obj# < 0

UNION ALL

SELECT 57, ROWID, 'ind$.type#'

FROM IND$

WHERE type# NOT BETWEEN 1 AND 9

UNION ALL

SELECT 58, ROWID, 'ind$.pctfree$'

FROM IND$

WHERE pctfree$ NOT BETWEEN 0 AND 99

UNION ALL

SELECT 59, ROWID, 'ind$.analyzetime <= SYSDATE'

FROM IND$

WHERE analyzetime > SYSDATE

UNION ALL

SELECT 51, ROWID, 'ind$.obj# pk'

FROM IND$

WHERE obj# IS NULL

UNION ALL

SELECT 51, ROWID, 'ind$.obj# pk'

FROM IND$

WHERE 1 > ( SELECT obj#

FROM IND$

GROUP BY obj#

HAVING COUNT (*) > 1)

UNION ALL

SELECT 53, ROWID, 'ind$.dataobj# range'

FROM IND$

WHERE 1 > ( SELECT dataobj#

FROM IND$

GROUP BY dataobj#

HAVING COUNT (*) > 1)

UNION ALL

SELECT 54, ROWID, 'ind$.ts# fk'

FROM IND$

WHERE (ts#) IN (SELECT ts#

FROM IND$

WHERE (ts#) NOT IN (SELECT ts# FROM ts$)

AND ts# != 2147483647)

UNION ALL

SELECT 55, ROWID, 'ind$.ts,file,block fk'

FROM IND$

WHERE (ts#, file#, block#) IN (SELECT ts#, file#, block#

FROM IND$

WHERE (ts#, file#, block#) NOT IN (SELECT ts#

,file#

,block#

FROM seg$)

AND file# != 0

AND block# != 0)

UNION ALL

SELECT 56, ROWID, 'ind$.obj# fk_obj$'

FROM IND$

WHERE (obj#) IN (SELECT obj#

FROM IND$

WHERE (obj#) NOT IN (SELECT obj# FROM obj$));

--//執行如下返回多行: SELECT dataobj# FROM IND$ GROUP BY dataobj# HAVING COUNT (*) > 1;

SYS@book> SELECT dataobj# FROM IND$ GROUP BY dataobj# HAVING COUNT (*) > 1;

DATAOBJ#

----------

87367

87374

87372

87378

SELECT *

FROM dba_objects

WHERE data_object_id IN ( SELECT /*+ unnest */ dataobj#

FROM IND$ where dataobj# is not null

GROUP BY dataobj#

HAVING COUNT (*) > 1);

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

SYS SYS_C0011093 87367 87367 INDEX 2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID N Y N 4

SYS SYS_IOT_TOP_87371 87372 87372 INDEX 2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID N Y N 4

SYS SYS_IOT_TOP_87373 87374 87374 INDEX 2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID N Y N 4

SYS SYS_IOT_TOP_87376 87378 87378 INDEX 2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID N Y N 4

OE PROD_SUPPLIER_IX 88151 87378 INDEX 2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID N N N 4

OE WHS_LOCATION_IX 88160 87367 INDEX 2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID N N N 4

OE CUST_ACCOUNT_MANAGER_IX 88164 87374 INDEX 2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID N N N 4

OE ORD_CUSTOMER_IX 88166 87372 INDEX 2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID N N N 4

8 rows selected.

--//很明顯oracle Dictionary Integrity Check存在bug問題,OE這些索引是透過oracle自帶的例子,透過傳輸表空間安裝上來的.

--//資料段號與系統表空間的這些索引段號重合了.

5.檢視報表:

SYS@book> select dbms_hm.get_run_report('test_dict_check') from dual;

DBMS_HM.GET_RUN_REPORT('TEST_DICT_CHECK')

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

Basic Run Information

Run Name : test_dict_check

Run Id : 93224

Check Name : Dictionary Integrity Check

Mode : MANUAL

Status : ERROR-NOT COMPLETED

Start Time : 2019-03-06 10:13:08.197565 +08:00

End Time : 2019-03-06 10:13:08.686826 +08:00

Error Encountered : 604

Source Incident Id : 0

Number of Incidents Created : 0

Input Paramters for the Run

TABLE_NAME=ALL_CORE_TABLES

CHECK_MASK=ALL

Run Findings And Recommendations

Finding

Finding Name : Dictionary Inconsistency

Finding ID : 93225

Type : FAILURE

Status : OPEN

Priority : CRITICAL

Message : SQL dictionary health check: file$ pk 42 on object FILE$

failed

Message : Damaged rowid is AAAAARAABAAAADpAAG - description: No further

damage description available

--//沒有執行完成.

SYS@book> @ rowid AAAAARAABAAAADpAAG

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

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

17 1 233 6 0x4000E9 1,233 alter system dump datafile 1 block 233 ;

SYS@book> select * from dba_objects where object_id=17;

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

SYS FILE$ 17 17 TABLE 2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID N N N 1

SYS@book> column SPARE2 noprint

SYS@book> column SPARE3 noprint

SYS@book> column SPARE4 noprint

SYS@book> select rowid,file$.* from file$;

ROWID FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1

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

AAAAARAABAAAADpAAA 1 2 64000 0 1 4194302 1280 0 7 4194306

AAAAARAABAAAADpAAB 2 2 51200 1 2 4194302 1280 0 1834 8388610

AAAAARAABAAAADpAAC 3 2 3200 2 3 4194302 640 0 923328 12582914

AAAAARAABAAAADpAAD 4 2 640 4 4 4194302 160 0 16143 16777218

AAAAARAABAAAADpAAE 5 2 12800 6 5 4194302 80 0 952916 20971522

AAAAARAABAAAADpAAF 6 2 5120 7 6 4194302 128 3 391355879 25165826

AAAAARAABAAAADpAAG 7 1 1280 4194302 2048 3 393536480 29360130

7 rows selected.

--//FILE#=7我已經刪除了.資料檔案已經刪除了.

6.相關檢視如下:

SYS@book> @ view v_$hm

old 6: WHERE view_name LIKE UPPER ('%' || '&&1' || '%')

new 6: WHERE view_name LIKE UPPER ('%' || 'v_$hm' || '%')

OWNER VIEW_NAME C30

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

SYS GV_$HM_CHECK GV$HM_CHECK

SYS GV_$HM_CHECK_PARAM GV$HM_CHECK_PARAM

SYS GV_$HM_FINDING GV$HM_FINDING

SYS GV_$HM_INFO GV$HM_INFO

SYS GV_$HM_RECOMMENDATION GV$HM_RECOMMENDATION

SYS GV_$HM_RUN GV$HM_RUN

SYS V_$HM_CHECK V$HM_CHECK

SYS V_$HM_CHECK_PARAM V$HM_CHECK_PARAM

SYS V_$HM_FINDING V$HM_FINDING

SYS V_$HM_INFO V$HM_INFO

SYS V_$HM_RECOMMENDATION V$HM_RECOMMENDATION

SYS V_$HM_RUN V$HM_RUN

12 rows selected.

--//可以用檢視來代替檢查報告來檢視特定檢查的結果.可用的檢視有v$hm_run,v$hm_finding,v$hm_recommendation

--//執行失敗,僅僅瞭解看看.

[20190306]11g health monitor.txt

相關文章