你的位置:首页 > 数据库

[数据库]11g新特性:Health Monitor Checks


11g新特性:Health Monitor Checks一、什么是Health Monitor ChecksHealth Monitor Checks能够发现文件损坏,物理、逻辑块损坏,undo、redo损坏,数据字典损坏等等。Health Monitor Checks产生结果报告,它包含了解决问题的办法。-----oracle另外一个坏块检查工具DBVERIFY 详见我的这篇文章:Oracle坏块验证工具:DBVERIFYhttp://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 CheckCF Block Integrity CheckData Block Integrity CheckRedo Integrity CheckTransaction Integrity CheckUndo Segment Integrity CheckDictionary Integrity CheckASM 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 100000SET LONGCHUNKSIZE 1000SET PAGESIZE 1000SET LINESIZE 512SELECT 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 referenced2.检查数据块完整性(Data Block Integrity Check)和数据字典完整性检查不同,数据块完整性需要指定input_params参数<1>获得Data Block Integrity Check相关参数SELECT a.* FROM v$hm_check_param a, v$hm_check bWHERE a.check_id = b.idAND 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 100000SET LONGCHUNKSIZE 1000SET PAGESIZE 1000SET LINESIZE 512SELECT 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 : 0Input Paramters for the Run BLC_DF_NUM=5 BLC_BL_NUM=2Run Findings And Recommendations 其他类型的检查大同小异,就不一一列举了。 三、Health Monitor 相关视图--1.执行过哪些health MonitorSELECT 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$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE SQL dictionary health check: syn$.owner fk 95 on object SYN$ failedFAILURE 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 1ORA-01427: single-row subquery returns more than one rowORA-06512: at "SYS.DBMS_HM", line 191ORA-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_ID" END , CASE WHEN "REGION_ID" IS NULL THEN "OWNER" END , CASE WHEN "REGION_ID" IS NULL THEN "FOLDER_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 "WORKSHEET_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.htmhttp://www.oracledatabase12g.com/archives/dbms_hm-run_check-failed-ora-00604-ora-01427.html转载:十字螺丝钉http://blog.chinaunix.net/uid/23284114.html