說明：

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.

健康檢查的類型:

1.DB Structure Integrity Check—This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is in NOMOUNT mode, only the control file is checked.

--mount\open

模式檢查控制文件中列出的日志文件和數據文件。NOMOUNT模式，則僅檢查控制文件。

2.Data Block Integrity Check—This check detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in the V$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.

--會捕獲損壞的塊信息記錄在V$DATABASE_BLOCK_CORRUPTION視圖中，不會檢測塊間或段間損壞。

3.Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

--在線日志及歸檔都會進行檢查

4.Undo Segment Integrity Check—This check finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

--PMON和SMON嘗試恢復損壞的事務，當損壞的事務不能恢復時記錄在V$CORRUPT_XID_LIST視圖中。

5.Transaction Integrity Check—This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.

--與4相同

6.Dictionary Integrity Check—This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:

1)Verifies the contents of dictionary entries for each dictionary object.

--驗證每個字典對象的字典條目的內容。

2)Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.

--執行跨行級別檢查，強制執行驗證字典中行的邏輯約束。

3)Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.

--執行對象關系檢查，強制執行驗證字典對象之間的父子關系。

涉及到的字典：

The Dictionary Integrity Check operates on the following dictionary objects:

tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$.

手動運行健康檢查

需要用到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

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

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

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';

總結

以上是生活随笔為你收集整理的oracle health monitor,Oracle 11g Health Monitor Checks的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。