Running Health Checks Manually
1,Running Health Checks Using the DBMS_HM PL/SQL Package
BEGIN
DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
END;
/
To obtain a list of health check names, run the following query:
SELECT name FROM v$hm_check WHERE internal_check='N';
NAME
----------------------------------------------------------------
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check
2,Most health checks accept input parameters. You can view parameter names and descriptions with the V$HM_CHECK_PARAM view. Some parameters are mandatory while
others are optional. If optional parameters are omitted, defaults are used. The following query displays parameter information for all health checks:
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;
Input parameters are passed in the input_params argument as name/value pairs separated by semicolons (;). The following example illustrates how to pass the
transaction ID as a parameter to the Transaction Integrity Check:
BEGIN
DBMS_HM.RUN_CHECK (
check_name => 'Transaction Integrity Check',
run_name => 'my_run',
input_params => 'TXN_ID=7.33.2');
END;
/
3.Viewing Checker Reports
Viewing Reports Using DBMS_HM
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
sys@R2> SELECT DBMS_HM.GET_RUN_REPORT('MY_RUN') FROM DUAL;
DBMS_HM.GET_RUN_REPORT('MY_RUN')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Basic Run Information
Run Name : my_run
Run Id : 41
Check Name : Dictionary Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2016-06-12 09:08:27.394560 +08:00
End Time : 2016-06-12 09:08:28.934198 +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
4.Viewing Reports Using the ADRCI Utility
a.ADRCI
b.show hm_run
c.create report hm_run run_name
d.show report hm_run run_name
5.Health Monitor Views
Instead of requesting a checker report, you can view the results of a specific checker run by directly querying the ADR data from which reports are created. This data is
available through the views V$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION.
The following example queries the V$HM_RUN view to determine a history of checker runs:
SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;
RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT
---------- ------------ ---------------------------------- -------- ------------
1 HM_RUN_1 DB Structure Integrity Check REACTIVE 0
101 HM_RUN_101 Transaction Integrity Check REACTIVE 6073
121 TXNCHK Transaction Integrity Check MANUAL 0
181 HMR_tab$ Dictionary Integrity Check MANUAL 0
The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 1061:
SELECT type, description FROM v$hm_finding WHERE run_id = 1061;
TYPE DESCRIPTION
------------- -----------------------------------------
FAILURE Block 64349 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt
FAILURE Block 64351 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt
1,Running Health Checks Using the DBMS_HM PL/SQL Package
BEGIN
DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
END;
/
To obtain a list of health check names, run the following query:
SELECT name FROM v$hm_check WHERE internal_check='N';
NAME
----------------------------------------------------------------
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check
2,Most health checks accept input parameters. You can view parameter names and descriptions with the V$HM_CHECK_PARAM view. Some parameters are mandatory while
others are optional. If optional parameters are omitted, defaults are used. The following query displays parameter information for all health checks:
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;
Input parameters are passed in the input_params argument as name/value pairs separated by semicolons (;). The following example illustrates how to pass the
transaction ID as a parameter to the Transaction Integrity Check:
BEGIN
DBMS_HM.RUN_CHECK (
check_name => 'Transaction Integrity Check',
run_name => 'my_run',
input_params => 'TXN_ID=7.33.2');
END;
/
3.Viewing Checker Reports
Viewing Reports Using DBMS_HM
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
sys@R2> SELECT DBMS_HM.GET_RUN_REPORT('MY_RUN') FROM DUAL;
DBMS_HM.GET_RUN_REPORT('MY_RUN')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Basic Run Information
Run Name : my_run
Run Id : 41
Check Name : Dictionary Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2016-06-12 09:08:27.394560 +08:00
End Time : 2016-06-12 09:08:28.934198 +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
4.Viewing Reports Using the ADRCI Utility
a.ADRCI
b.show hm_run
c.create report hm_run run_name
d.show report hm_run run_name
5.Health Monitor Views
Instead of requesting a checker report, you can view the results of a specific checker run by directly querying the ADR data from which reports are created. This data is
available through the views V$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION.
The following example queries the V$HM_RUN view to determine a history of checker runs:
SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;
RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT
---------- ------------ ---------------------------------- -------- ------------
1 HM_RUN_1 DB Structure Integrity Check REACTIVE 0
101 HM_RUN_101 Transaction Integrity Check REACTIVE 6073
121 TXNCHK Transaction Integrity Check MANUAL 0
181 HMR_tab$ Dictionary Integrity Check MANUAL 0
The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 1061:
SELECT type, description FROM v$hm_finding WHERE run_id = 1061;
TYPE DESCRIPTION
------------- -----------------------------------------
FAILURE Block 64349 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt
FAILURE Block 64351 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt