11g新特性：Health Monitor Checks

最新推荐文章于 2021-04-13 14:32:47 发布

weixin_30246221

最新推荐文章于 2021-04-13 14:32:47 发布

阅读量72

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/oracle-ziyuhou/p/6092422.html

版权

一、什么是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);

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

转载于:https://www.cnblogs.com/oracle-ziyuhou/p/6092422.html

weixin_30246221

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
11g新特性：Health Monitor Checks

一、什么是Health Monitor ChecksHealth Monitor Checks能够发现文件损坏，物理、逻辑块损坏，undo、redo损坏，数据字典损坏等等。Health Monitor Checks产生结果报告，它包含了解决问题的办法。-----oracle另外一个坏块检查工具DBVERIFY 详见我的这篇文章：Oracle坏块验证工具：DBVERIFYhttp://blog....
复制链接

扫一扫