ORACLE 11g HEALTH CHECK MONITOR

HEALTH CHECK
transaction check

you want to find out any potential problems in the database like file or any data dictionary related corruption then run a health check. Now, there are several kind of health check that can be executed in the database. To find what kind of checks that we can execute, run the below mentioned script



SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run


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;



SQL> select NAME,CHECK_ID from v$hm_check_param ;

NAME CHECK_ID
---------------------------------------------------------------- ----------
NUM_FDGS_TO_CREATE 1
NUM_RECOS_TO_CREATE 1
DAMAGE_PARAM_VALUE 1
FDG_PARAM_VALUE 1
NUM_LIST_PARAMETER 1
TEXT_LIST_PARAMETER 1
CF_BL_NUM 25
BLC_DF_NUM 3
BLC_BL_NUM 3
SCN_TEXT 4
BLKCHK_TSN 5
BLKCHK_RDBA 5
BLKCHK_SEGDBA 5
BLKCHK_OBJN 5
BLKCHK_OBJD 5



==================================

BEGIN
dbms_hm.run_check('Dictionary Integrity Check', 'my_health_check');
END;
/






declare
V_RPT CLOB;
begin
V_RPT := DBMS_HM.GET_RUN_REPORT('HM01');
end;





V$HM_CHECK_PARAM.CHECK_ID to V$HM_CHECK.ID



col name format a20
col desc1 format a50
col DEFAULT_VALUE format a20
select pa.NAME,pa.DEFAULT_VALUE,substr(pa.DESCRIPTION,1,50) desc1 from V$HM_CHECK_PARAM pa,V$HM_CHECK ch
where pa.CHECK_ID=ch.id



SET LINESIZE 120
COLUMN check_name FORMAT A30
COLUMN parameter_name FORMAT A15
COLUMN type FORMAT A15
COLUMN default_value FORMAT A15
COLUMN description FORMAT A20
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;




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;



BEGIN
DBMS_HM.run_check ( check_name => 'DB Structure Integrity Check', run_name => 'Anuj_test_run');
END;
/




SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report('Anuj_test_run') FROM dual;



SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report('Anuj_test_run') FROM dual;

DBMS_HM.GET_RUN_REPORT('ANUJ_TEST_RUN')

Basic Run Information
Run Name : Anuj_test_run
Run Id : 1105121
Check Name : DB Structure Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2011-10-04 12:33:47.931025 +00:00
End Time : 2011-10-04 12:33:48.372996 +00:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0

Input Paramters for the Run
Run Findings And Recommendations



SET LONG 1000000
SET LONGCHUNKSIZE 10000
SET PAGESIZE 0
SET LINESIZE 250
select DBMS_HM.GET_RUN_REPORT('redo_run_1') from dual;


SELECT run_id, name, check_name, status FROM v$hm_run;


list of views used with Health Monitor:

I will only give a brief description of each. See Oracle documentation for details.

V$HM_CHECK – lists all Health checks
V$HM_CHECK_PARAM - lists information about Health checks, input parameters and defaults. Join CHECK_ID with V$HM_CHECK.ID.
V$HM_FINDING – Information about findings of Health Monitor runs.
V$HM_INFO – Information about runs, finding and recommendations.
V$HM_RECOMMENDATION – information about any recommendation from Health check runs.
V$HM_RUN – Information about checker runs like name, mode, time of run, etc.






SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT(‘HM_RUN_1061′) FROM DUAL;
DBMS_HM.GET_RUN_REPORT(‘HM_RUN_1061′)



SELECT a.run_id, a.name, a.description desc_finding,
b.description desc_repair, b.repair_script
FROM v$hm_finding a, v$hm_recommendation b
WHERE a.run_id=b.run_id (+);



SQL> desc V$CORRUPT_XID_LIST
Name Null? Type
---------------------------------------- -------- ---------------------------
CORRUPT_XID VARCHAR2(256)

SQL> select * from V$CORRUPT_XID_LIST;

no rows selected




input_params =>


SQL> col desc1 format a50
SELECT name,internal_check,offline_capable,substr(description,1,50) desc1 FROM v$hm_check;SQL>

NAME I O DESC1
---------------------------------------------------------------- - - --------------------------------------------------
HM Test Check Y Y Check for health monitor functionality
DB Structure Integrity Check N Y Checks integrity of all database files
CF Block Integrity Check N Y Checks integrity of a control file block
Data Block Integrity Check N Y Checks integrity of a data file block
Redo Integrity Check N Y Checks integrity of redo log content
Logical Block Check Y N Checks logical content of a block
Transaction Integrity Check N N Checks a transaction for corruptions
Undo Segment Integrity Check N N Checks integrity of an undo segment
No Mount CF Check Y Y Checks control file in NOMOUNT mode
Mount CF Check Y Y Checks control file in mount mode
CF Member Check Y Y Checks a multiplexed copy of the control file
All Datafiles Check Y Y Checks all datafiles in the database
Single Datafile Check Y Y Checks a data file
Tablespace Check Check Y Y Checks a tablespace
Log Group Check Y Y Checks all members of a log group
Log Group Member Check Y Y Checks a particular member of a log group
Archived Log Check Y Y Checks an archived log
Redo Revalidation Check Y Y Checks redo log content
IO Revalidation Check Y Y Checks file accessibility
Block IO Revalidation Check Y Y Checks file accessibility
Txn Revalidation Check Y N Revalidate corrupted transaction
Failure Simulation Check Y Y Creates dummy failures
Dictionary Integrity Check N N Checks dictionary integrity
ASM Mount Check Y Y Diagnose mount failure
ASM Allocation Check N Y Diagnose allocation failure
ASM Disk Visibility Check Y Y Diagnose add disk failure
ASM File Busy Check Y Y Diagnose file drop failure

27 rows selected.







adrci> set homepath diag/rdbms/db11g/DB11G
adrci> create report hm_run my_test_run
adrci> show report hm_run my_test_run



SET LINESIZE 120
COLUMN check_name FORMAT A30
COLUMN parameter_name FORMAT A15
COLUMN type FORMAT A15
COLUMN default_value FORMAT A15
COLUMN description FORMAT A20

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;






Using undocumented procedure of DBMS_HM package :

SQL> exec DBMS_HM.DROP_SCHEMA();




When running the following statement:

exec dbms_hm.run_check('Dictionary Intergirty Check','AnujCheck',0,'TABLE_NAME=tab$');

set long 100000
select dbms_hm.get_run_report('AnujCheck') from dual;








BEGIN

DBMS_HM.RUN_CHECK (
check_name => ‘Transaction Integrity Check’,
run_name => ‘my_run’,
input_params => ‘TXN_ID=7.33.2′);
END;



DBMS_HM.RUN_CHECK





col FINDING_NAME format a30
select execution_name, finding_name, parent, impact
from dba_addm_findings
where task_name = 'AnujCheck';






The DBMS healtmonitor can be used. The below options can be chosen

1. DB Structure Integrity Check
2. Data Block Integrity Check
3. Redo Integrity Check
4. Transaction Integrity Check
5. Undo Segment Integrity Check
6. Dictionary Integrity Check


DB Structure Integrity Check - on NOMOUNT : only check control file

if mount or open mode, this check examines the log files and data files listed in the control file

Data Block Integrity Check This check does not detect inter-block or inter-segment corruption.
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

Redo Integrity Check scans the contents of the redo log for accessibility and corruption, and for archive logs as well


Undo Segment Integrity Check 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.

Transaction Integrity Check






SQL> BEGIN
2 DBMS_HM.run_check (
3 check_name => 'DB Structure Integrity Check',
4 run_name => 'my_test_run');
5 END;
6 /


SQL> SET LONG 100000

SQL> SET LONGCHUNKSIZE 1000
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 512
SQL> SELECT DBMS_HM.get_run_report('MY_TEST_RUN') FROM dual;


SQL> begin

2 dbms_hm.run_check(
3 check_name=>'Redo Integrity Check',
4 run_name=>'my_redo_check');
5 end;
6 /


SQL> SET LONG 100000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 512
SQL> SELECT DBMS_HM.get_run_report('MY_REDO_CHECK') from dual;


exec dbms_hm.run_check('Dictionary Integrity Check','TestANUJ',0);

SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run


Transaction Integrity Check <<<<<<<<<<<<<<<

SQL> delete scott.temp1 where object_id=25;
64 rows deleted


SQL> update EMP set sal=4000 ;

14 rows updated.



2. transaction



select ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN
---------------- ---------- ---------- ----------
000000009034B748 8 22 10125


SQL> exec dbms_hm.run_check (check_name => 'Transaction Integrity Check',run_name => 'test_ANUJ2',input_params => 'TXN_ID=8.22.10125');

PL/SQL procedure successfully completed.



SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report('test_ANUJ2') from dual;SQL> SQL> SQL> SQL>

DBMS_HM.GET_RUN_REPORT('TEST_ANUJ2')

Basic Run Information
Run Name : test_ANUJ2
Run Id : 1105221
Check Name : Transaction Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2011-10-04 16:27:43.351221 +00:00
End Time : 2011-10-04 16:27:43.768180 +00:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0

Input Paramters for the Run
TXN_ID=8.22.10125

Run Findings And Recommendations
Finding
Finding Name : TXN not corrupt
Finding ID : 1105222
Type : INFORMATIONAL
Status : OPEN
Priority : HIGH
Message : Transaction 8.22.10125 is not corrupted