程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 11g新特性：Health Monitor Checks，11gchecks

11g新特性：Health Monitor Checks，11gchecks

編輯：Oracle教程

11g新特性：Health Monitor Checks，11gchecks

11g新特性：Health Monitor Checks 一、什麼是Health Monitor Checks Health Monitor Checks能夠發現文件損壞，物理、邏輯塊損壞，undo、redo損壞，數據字典損壞等等。 Health Monitor Checks產生結果報告，它包含了解決問題的辦法。 ----- oracle另外一個壞塊檢查工具DBVERIFY 詳見我的這篇文章： Oracle壞塊驗證工具：DBVERIFY http://blog.chinaunix.net/uid-23284114-id-3687810.html ----- Health Monitor Checks有兩種運行模式：只有redo檢查需要在DB-offline模式下運行，其他檢查都是在DB-online下。 ?DB-online mode means the check can be run while the database is open (that is, in OPEN mode or MOUNT mode). ?DB-offline mode means the check can be run when the instance is available but the database itself is closed (that is, in NOMOUNT mode). --可以手動檢查的項目 SELECT name FROM V$HM_CHECK WHERE INTERNAL_CHECK = 'N' ; NAME ---------------------------------------------------------------- DB Structure Integrity Check CF Block Integrity Check Data Block Integrity Check Redo Integrity Check Transaction Integrity Check Undo Segment Integrity Check Dictionary Integrity Check ASM Allocation Check --需要用到的程序包 DBMS_HM.RUN_CHECK ( check_name IN VARCHAR2, --檢查的項目名，通過SELECT name FROM V$HM_CHECK WHERE INTERNAL_CHECK = 'N' 可以查到。 run_name IN VARCHAR2 := NULL, --用戶自定義的該次運行檢查的名稱 timeout IN NUMBER := NULL, --超時時間。 input_params IN VARCHAR2 := NULL); --輸入參數，多個參數用分號(;)隔開。具體制定檢查項目。 --具體參數可以從V$HM_CHECK_PARAM視圖中找到。 --例子，'BLC_DF_NUM=1;BLC_BL_NUM=23456'表示檢查第1個數據文件的第23456個block。 --通過下面的SQL，可以找出檢查項目對應的參數。 --找出Data Block Integrity Check項目對應的參數 SELECT a.* FROM v$hm_check_param a, v$hm_check b WHERE a.check_id = b.id AND b.name = 'Data Block Integrity Check'; 二、手動運行Health Checks 1.數據字典完整性檢查（Dictionary Integrity Check） --執行檢查 BEGIN DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'run_dict1'); END; / --查看檢查報告 SET LONG 100000 SET LONGCHUNKSIZE 1000 SET PAGESIZE 1000 SET LINESIZE 512 SELECT DBMS_HM.GET_RUN_REPORT('run_dict1') FROM DUAL; DBMS_HM.GET_RUN_REPORT('RUN_DICT1') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finding Finding Name : Dictionary Inconsistency Finding ID : 336 Type : FAILURE Status : OPEN Priority : CRITICAL Message : SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed Message : Damaged rowid is AAAABEAABAAANWhABE - description: Synonymn APEX_THEMES is referenced ......... ......... ......... Finding Name : Dictionary Inconsistency Finding ID : 411 Type : FAILURE Status : OPEN Priority : CRITICAL Message : SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed Message : Damaged rowid is AAAABEAABAAANWgAB6 - description: Synonymn HTMLDB_ACTIVITY_LOG is referenced 2.檢查數據塊完整性(Data Block Integrity Check) 和數據字典完整性檢查不同，數據塊完整性需要指定input_params參數 <1>獲得Data Block Integrity Check相關參數 SELECT a.* FROM v$hm_check_param a, v$hm_check b WHERE a.check_id = b.id AND b.name = 'Data Block Integrity Check'; ID NAME CHECK_ID TYPE DEFAU FLAGS DESCRIPTION ---------- ---------- ---------- -------------------- ----- ---------- ---------------------------------------- 8 BLC_DF_NUM 3 DBKH_PARAM_UB4 0 File number 9 BLC_BL_NUM 3 DBKH_PARAM_UB4 0 Block number <2>執行檢查 begin DBMS_HM.RUN_CHECK( check_name => 'Data Block Integrity Check', run_name => 'chk_df5', input_params => 'BLC_DF_NUM=5;BLC_BL_NUM=2' ); end; / <3>獲得報告 SET LONG 100000 SET LONGCHUNKSIZE 1000 SET PAGESIZE 1000 SET LINESIZE 512 SELECT DBMS_HM.GET_RUN_REPORT('chk_df5') FROM DUAL; DBMS_HM.GET_RUN_REPORT('CHK_DF5') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Basic Run Information Run Name : chk_df5 Run Id : 781 Check Name : Data Block Integrity Check Mode : MANUAL Status : COMPLETED Start Time : 2014-06-27 02:44:18.704834 +08:00 End Time : 2014-06-27 02:44:19.964315 +08:00 Error Encountered : 0 Source Incident Id : 0 Number of Incidents Created : 0 Input Paramters for the Run BLC_DF_NUM=5 BLC_BL_NUM=2 Run Findings And Recommendations 其他類型的檢查大同小異，就不一一列舉了。三、Health Monitor 相關視圖 --1.執行過哪些health Monitor SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run; RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT ---------- -------------------------------- -------------------------------- -------- ------------ 41 run_dict1 Dictionary Integrity Check MANUAL 0 1 run_dict Dictionary Integrity Check MANUAL 0 --2.根據run_id獲得health Monitor執行細節 SELECT type, description FROM v$hm_finding WHERE run_id = 41; TYPE DESCRIPTION ------------- ---------------------------------------------------------------------------------------------------- FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed FAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed 四、其他 *****11.2.0.3版本執行數據字典完整性檢查會報錯***** SQL> BEGIN 2 DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'run_dict'); 3 END; 4 / BEGIN * 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 2 非官方解決辦法： -- Determine DDL statements (note: this will take a while to return results!) set long 100000 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false); -- Checking the DDL statement col DDL form a100 word_wrapped select dbms_metadata.get_ddl(RTRIM(UPPER(object_type)), RTRIM(UPPER(object_name)), RTRIM(UPPER(owner))) DDL from DBA_OBJECTS where object_type='INDEX' and object_id in (select x from (select obj# x, obj#||','||intcol#, count(obj#||','||intcol#) from ICOLDEP$ group by obj#, obj#||','||intcol# having count(*) > 1) ); 返回結果： DDL ---------------------------------------------------------------------------------------------------- CREATE UNIQUE INDEX "APEX_030200"."WWV_FLOW_WORKSHEETS_UNQ_IDX" ON "APEX_030200"."WWV_FLOW_WORKSHEETS" (CASE WHEN "REGION_ID" IS NULL THEN "FLOW_I D" END , CASE WHEN "REGION_ID" IS NULL THEN "OWNER" END , CASE WHEN "REGION_ID" IS NULL THEN "FOLD ER_ID" END , CASE WHEN "REGION_ID" IS NULL THEN "NAME" END ) CREATE UNIQUE INDEX "APEX_030200"."WWV_FLOW_WS_UNQ_ALIAS_IDX" ON "APEX_030200"."WWV_FLOW_WORKSHEETS" (CASE WHEN "ALIAS" IS NOT NULL THEN "FLOW_ID" END , "ALIAS") CREATE UNIQUE INDEX "APEX_030200"."WWV_FLOW_WORKSHEET_RPTS_UK" ON "APEX_030200"."WWV_FLOW_WORKSHEET_RPTS" (CASE WHEN "SESSION_ID" IS NULL THEN "WO RKSHEET_ID" END , CASE WHEN "SESSION_ID" IS NULL THEN "APPLICATION_USER" END , CASE WHEN "SESSION_ ID" IS NULL THEN "CATEGORY_ID" END , CASE WHEN "SESSION_ID" IS NULL THEN "NAME" END ) 對於安裝了APEX 組件或者在DBCA創建數據庫時選擇了General Purpose從Seed中clone數據庫而非Custom Database的DB ，都會創建有”APEX_030200″.”WWV_FLOW_WORKSHEETS_UNQ_IDX”、”APEX_030200″.”WWV_FLOW_WS_UNQ_ALIAS_IDX”、”APEX_030200″.”WWV_FLOW_WORKSHEET_RPTS_UK” 三個函數索引。如果沒有實際使用APEX組件的話，我們可以直接DROP掉APEX_030200: SQL> drop user "APEX_030200" cascade; 再次執行下面的SQL， -- Determine DDL statements (note: this will take a while to return results!) set long 100000 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false); -- Checking the DDL statement col DDL form a100 word_wrapped select dbms_metadata.get_ddl(RTRIM(UPPER(object_type)), RTRIM(UPPER(object_name)), RTRIM(UPPER(owner))) DDL from DBA_OBJECTS where object_type='INDEX' and object_id in (select x from (select obj# x, obj#||','||intcol#, count(obj#||','||intcol#) from ICOLDEP$ group by obj#, obj#||','||intcol# having count(*) > 1) ); no rows selected --沒有結果返回就正確了官方解決辦法：打Patch:12385172補丁 MOS:ORA-01427 occurs when running Dictionary Integrity Check: dbms_hm.run_check (文檔 ID 1410513.1) ************************************* 參考： http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag007.htm http://www.oracledatabase12g.com/archives/dbms_hm-run_check-failed-ora-00604-ora-01427.html 轉載：十字螺絲釘 http://blog.chinaunix.net/uid/23284114.html

Oracle教程