Oracle 11g Health Monitor Checks
说明:
Health Monitor是11g引入的用于检查数据库的各个组件(包括文件、内存、事务处理完整性、 元数据和进程使用情况)。这些检查器将生成检查结果报表以及解决问题的建议。 可在数据库脱机(nomount)和联机(open或mount)的情况下执行,运行状况检查都可以在DB-online模式下运行。只有redo完整性检查和db结构完整性检查需要在数据库脱机模式下进行。 支持的检查条目: SQL> select name,offline_capable,description,internal_check from v$hm_check; NAME O DESCRIPTION I ----------------------------------- - ----------------------------------------------------------------- - HM Test Check Y Check for health monitor functionality Y DB Structure Integrity Check Y Checks integrity of all database files N CF Block Integrity Check Y Checks integrity of a control file block N Data Block Integrity Check Y Checks integrity of a data file block N Redo Integrity Check Y Checks integrity of redo log content N Logical Block Check N Checks logical content of a block Y Transaction Integrity Check N Checks a transaction for corruptions N Undo Segment Integrity Check N Checks integrity of an undo segment N No Mount CF Check Y Checks control file in NOMOUNT mode Y Mount CF Check Y Checks control file in mount mode Y CF Member Check Y Checks a multiplexed copy of the control file Y All Datafiles Check Y Checks all datafiles in the database Y Single Datafile Check Y Checks a data file Y Tablespace Check Check Y Checks a tablespace Y Log Group Check Y Checks all members of a log group Y Log Group Member Check Y Checks a particular member of a log group Y Archived Log Check Y Checks an archived log Y Redo Revalidation Check Y Checks redo log content Y IO Revalidation Check Y Checks file accessibility Y Block IO Revalidation Check Y Checks file accessibility Y Txn Revalidation Check N Revalidate corrupted transaction Y Failure Simulation Check Y Creates dummy failures Y Dictionary Integrity Check N Checks dictionary integrity N ASM Mount Check Y Diagnose mount failure Y ASM Allocation Check Y Diagnose allocation failure N ASM Disk Visibility Check Y Diagnose add disk failure Y ASM File Busy Check Y Diagnose file drop failure Y ASM Toomanyoff Check Y Diagnose mount failed because there were too many offline disks Y ASM Insufficient Disks Check Y Diagnose mount failed because there were insufficient disks Y ASM Insufficient Mem Check Y Check to adjust memory on allocation failure Y 30 rows selected.
--mountopen
--会捕获损坏的块信息记录在V$DATABASE_BLOCK_CORRUPTION视图中,不会检测块间或段间损坏。
--在线日志及归档都会进行检查
--PMON和SMON尝试恢复损坏的事务,当损坏的事务不能恢复时记录在V$CORRUPT_XID_LIST视图中。
--与4相同
涉及到的字典:
手动运行健康检查 需要用到DBMS_HM.RUN_CHECK包:
DBMS_HM.RUN_CHECK ( check_name IN VARCHAR2,--> 要调用的检查的名称,从V$HM_CHECK和V$HM_CHECK_PARAM视图查看,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 ,v$hm_check视图确定。 1)运行DBMS_HM.RUN_CHECK BEGIN DBMS_HM.RUN_CHECK(‘Dictionary Integrity Check‘,‘Dict_Check‘); END; /
2)SQLPLUS中查看报告 SQL> SET LONG 100000 SQL> SET LONGCHUNKSIZE 1000 SQL> SET PAGESIZE 1000 SQL> SET LINESIZE 512 SQL> SELECT DBMS_HM.GET_RUN_REPORT(‘Dict_Check‘) FROM DUAL; DBMS_HM.GET_RUN_REPORT(‘DICTCHECK‘) --------------------------------------------------------- Basic Run Information Run Name : Dict Check Run Id : 55001 Check Name : Dictionary Integrity Check Mode : MANUAL Status : COMPLETED Start Time : 2019-07-03 09:49:44.365864 +08:00 End Time : 2019-07-03 09:50:04.668621 +08:00 Error Encountered : 0 Source Incident Id : 0 Number of Incidents Created : 0 Input Paramters for the Run TABLE_NAME=ALL_CORE_TABLES CHECK_MASK=ALL v Run Findings And Recommendations Finding Finding Name : Dictionary Inconsistency Finding ID : 55002 Type : FAILURE Status : OPEN Priority : CRITICAL Message : SQL dictionary health check: analyzetime for object new than sysdate 12 on object TAB$ failed Message : Damaged rowid is AAAAACAABAAAACRAAC - description: Object SYS.UNDO$ is referenced Finding Finding Name : Dictionary Inconsistency Finding ID : 55005 Type : FAILURE Status : OPEN Priority : CRITICAL Message : SQL dictionary health check: analyzetime for object new than sysdate 12 on object TAB$ failed Message : Damaged rowid is AAAAACAABAAAACVAAA - description: Object SYS.SEQ$ is referenced 3)adrci中查看报告 ADRCI: Release 11.2.0.4.0 - Production on Wed Jul 3 09:52:43 2019 Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle" adrci> show hm_run ADR Home = /u01/app/oracle/diag/tnslsnr/prodb/listener: ************************************************************************* ... ********************************************************** HM RUN RECORD 130 ********************************************************** RUN_ID 56581 RUN_NAME Dict_Check CHECK_NAME Dictionary Integrity Check NAME_ID 24 MODE 0 START_TIME 2019-07-03 09:58:40.350313 +08:00 RESUME_TIME <NULL> END_TIME 2019-07-03 09:58:56.425829 +08:00 MODIFIED_TIME 2019-07-03 09:58:56.425829 +08:00 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE <NULL> adrci> create report hm_run Dict_Check adrci> show report hm_run Dict_Check ... adrci> show hm_run ********************************************************** HM RUN RECORD 130 ********************************************************** RUN_ID 56581 RUN_NAME Dict_Check CHECK_NAME Dictionary Integrity Check NAME_ID 24 MODE 0 START_TIME 2019-07-03 09:58:40.350313 +08:00 RESUME_TIME <NULL> END_TIME 2019-07-03 09:58:56.425829 +08:00 MODIFIED_TIME 2019-07-03 09:59:40.845354 +08:00 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE /u01/app/oracle/diag/rdbms/PROD/PROD/hm/HMREPORT_Dict_Check.hm 已生成REPORT_FILE,xml格式。 其他: 每个类型的检查参数可能会不一样,如数据块完整性检查: begin DBMS_HM.RUN_CHECK(‘Data Block Integrity Check‘,‘f7_block_check‘,input_params => ‘BLC_DF_NUM=7;BLC_BL_NUM=100‘); end; / 具体参数: 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‘; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |