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';
--查看检查报告 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 )
再次执行下面的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) *************************************
