Proactively and Reactively check status of datafile, memory, transaction integrity, metadata and process usage.
To displays information about all the checks registered with Health Monitor
Note: You can use with nomount, mount or open status of database.
SQL> select ID, NAME, OFFLINE_CAPABLE, INTERNAL_CHECK from v$hm_check order by 4;
ID NAME O I
---------- ---------------------------------------------------------------- - -
2 DB Structure Integrity Check Y N
24 Dictionary Integrity Check N N
4 Redo Integrity Check Y N
3 Data Block Integrity Check Y N
25 CF Block Integrity Check Y N
11 Undo Segment Integrity Check N N
10 Transaction Integrity Check N N
27 ASM Allocation Check Y N
16 Log Group Check Y Y
17 Log Group Member Check Y Y
38 ASM Sync IO Fail Check Y Y
19 Redo Revalidation Check Y Y
20 IO Revalidation Check Y Y
21 Block IO Revalidation Check Y Y
34 Failover Check Y Y
22 Txn Revalidation Check N Y
23 Failure Simulation Check Y Y
26 ASM Mount Check Y Y
28 ASM Disk Visibility Check Y Y
29 ASM File Busy Check Y Y
32 ASM Toomanyoff Check Y Y
33 ASM Insufficient Disks Check Y Y
35 ASM Insufficient Mem Check Y Y
36 ASM DGFDM Check No DG Name Y Y
37 ASM DG Force Dismount Check Y Y
30 Tablespace Check Check Y Y
15 Single Datafile Check Y Y
14 All Datafiles Check Y Y
13 CF Member Check Y Y
31 Mount CF Check Y Y
12 No Mount CF Check Y Y
5 Logical Block Check N Y
1 HM Test Check Y Y
18 Archived Log Check Y Y
34 rows selected.
SQL> select ID, NAME, OFFLINE_CAPABLE, INTERNAL_CHECK from v$hm_check order by 4;
ID NAME O I
---------- ---------------------------------------------------------------- - -
2 DB Structure Integrity Check Y N
24 Dictionary Integrity Check N N
4 Redo Integrity Check Y N
3 Data Block Integrity Check Y N
25 CF Block Integrity Check Y N
11 Undo Segment Integrity Check N N
10 Transaction Integrity Check N N
27 ASM Allocation Check Y N
16 Log Group Check Y Y
17 Log Group Member Check Y Y
38 ASM Sync IO Fail Check Y Y
19 Redo Revalidation Check Y Y
20 IO Revalidation Check Y Y
21 Block IO Revalidation Check Y Y
34 Failover Check Y Y
22 Txn Revalidation Check N Y
23 Failure Simulation Check Y Y
26 ASM Mount Check Y Y
28 ASM Disk Visibility Check Y Y
29 ASM File Busy Check Y Y
32 ASM Toomanyoff Check Y Y
33 ASM Insufficient Disks Check Y Y
35 ASM Insufficient Mem Check Y Y
36 ASM DGFDM Check No DG Name Y Y
37 ASM DG Force Dismount Check Y Y
30 Tablespace Check Check Y Y
15 Single Datafile Check Y Y
14 All Datafiles Check Y Y
13 CF Member Check Y Y
31 Mount CF Check Y Y
12 No Mount CF Check Y Y
5 Logical Block Check N Y
1 HM Test Check Y Y
18 Archived Log Check Y Y
34 rows selected.
To see description of bout all the checks registered with Health Monitor
select name,description from v$hm_check;
RUN CHECK
select name,description from v$hm_check;
RUN CHECK
To run check of any name of v$hm_check order view
note: For INTERNAL CHECK=Y only ADRCI (Method 3)
note: For INTERNAL CHECK=Y only ADRCI (Method 3)
exec dbms_hm.run_check(check_name=>'Dictionary Integrity Check',run_name=>'my_DIC_check');
To see the report of previous run check
select dbms_hm.get_run_report('my_DIC_check') from dual;
METHOD 1
select dbms_hm.get_run_report(‘ my_DIC_check’) from dual;
METHOD 2
select name,run_id from v$hm_run where name=’my_DIC_check′;
METHOD 3
To check any error on Health Monitor
[oracle@ol7-122 ~]$ adrci
ADRCI: Release 12.1.0.2.0 - Production on Sun Sep 10 14:34:00 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/rman/rman
diag/clients/user_oracle/host_1145887873_82
diag/tnslsnr/ol7-122/listener
diag/tnslsnr/ol7-122/listener_ol7-122
adrci> set home path diag/rdbms/rman/rman
adrci> show HM_RUN
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 1
RUN_NAME my_DIC_check
CHECK_NAME Dictionary Integrity Check
NAME_ID 24
MODE 0
START_TIME 2017-09-10 13:43:09.027402 +01:00
RESUME_TIME <NULL>
END_TIME 2017-09-10 13:43:09.705922 +01:00
MODIFIED_TIME 2017-09-10 13:43:19.653586 +01:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /u01/app/oracle/diag/rdbms/rman/rman/hm/HMREPORT_my_DIC_check.hm
1 rows fetched
METHOD 4 :
Using EM tool also we can view the report.
EM Navigation :
Database page -> Related Links -> Advisor Central -> Advisors and Checkers -> Checkers
Availability> Manage> Perform Recovery> Perform Automated Repair
ARDCI
adrci> show hm_run -p “run_id=36961”
To get the report
dbms_hm.get_run_report(
run_name IN VARCHAR2,
type IN VARCHAR2 := 'TEXT', -- 'XML', 'TEXT', 'HTML'
level IN VARCHAR2 := 'BASIC') -- 'BASIC', 'DETAIL'
RETURN CLOB;
Query
set linesize 131
col check_name format a30
col parameter_name format a15
col type format a15
col default_value format a15
col description format a40
SELECT c.name check_name, p.name parameter_name, p.type,
p.default_value, p.description
FROM v$hm_check_param p, v$hm_check c
WHERE p.check_id = c.id and c.internal_check = 'N'
ORDER BY c.name;
Views
V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_RUN
V$HM_FINDING
V$HM_RECOMMENDATION
gv$hm_check
gv$hm_info
gv$hm_check_param
gv$hm_recommendation
gv$hm_finding
gv$hm_run
To get the report
dbms_hm.get_run_report(
run_name IN VARCHAR2,
type IN VARCHAR2 := 'TEXT', -- 'XML', 'TEXT', 'HTML'
level IN VARCHAR2 := 'BASIC') -- 'BASIC', 'DETAIL'
RETURN CLOB;
Query
set linesize 131
col check_name format a30
col parameter_name format a15
col type format a15
col default_value format a15
col description format a40
SELECT c.name check_name, p.name parameter_name, p.type,
p.default_value, p.description
FROM v$hm_check_param p, v$hm_check c
WHERE p.check_id = c.id and c.internal_check = 'N'
ORDER BY c.name;
Views
V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_RUN
V$HM_FINDING
V$HM_RECOMMENDATION
gv$hm_check
gv$hm_info
gv$hm_check_param
gv$hm_recommendation
gv$hm_finding
gv$hm_run