The Oracle DBA wants to run Health Checks command with Health Monitor upon his database.
With Oracle 12c/18c, these checks can be done on a regular basis daily/monthly:
– 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
Perhaps you have datafile, dictionary, block, undo, redo, or another corruption in your database? You might actually be running just fine and not even know it.
Oracle Database 12c/18c includes a framework called Health Monitor for running diagnostic checks on your database.
How to run a health check on the Oracle database?
BEGIN
DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/
or
BEGIN
DBMS_HM.RUN_CHECK (check_name => 'Transaction Integrity Check',
run_name => 'my_transaction_run',
input_params => 'TXN_ID=22.87.1');
END;
Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;
Listing all the Health Check executed (Health Monitor View):
SELECT run_id,
name,
check_name,
run_mode,
status,
src_incident,
num_incident,
error_number
FROM v$hm_run;
Viewing the list of checks that can be done on your database
SELECT name FROM v$hm_check WHERE internal_check = 'N';
Health checks accept input parameters, some are mandatory while others are optional.
Displaying 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;
Periodic database health checks help keep your database running smoothly without corruption and prevent more serious conditions from developing later.
Health Monitor checks and examine the several parts of the Oracle database stack. This tool detects data dictionary corruptions, datafile corruptions. It will check logical or physical logical block corruptions and rollback (undo) or redo corruptions.
The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.
For a Healthy database!
Vincent Fenoll – Oracle OCP Database administrator in Montreal
