Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Run Database Health Check using DBMS_HM PL/SQL Package

Posted by Hendry chinnapparaj on June 2, 2010

To obtain a list of health check names, run the following query:

SELECT name FROM v$hm_check WHERE internal_check='N';

SQL> SELECT name FROM v$hm_check WHERE internal_check=’N’;

NAME

—————————————————————-

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

8 rows selected.

Running Health Checks Using the DBMS_HM PL/SQL Package

The DBMS_HM procedure for running a health check is called RUN_CHECK. To call RUN_CHECK, supply the name of the check and a name for the run, as follows:

SQL> BEGIN

2      DBMS_HM.RUN_CHECK(‘Dictionary Integrity Check’, ‘my_run’);

3  END;

4  /

PL/SQL procedure successfully completed.

set pages 1000 lines 160

column check_name format a30

column parameter_name format a20

column default_value format a20

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

CHECK_NAME                     PARAMETER_NAME       TYPE                 DEFAULT_VALUE        DESCRIPTION

—————————— ——————– ——————– ——————– —————————————-

ASM Allocation Check           ASM_DISK_GRP_NAME    DBKH_PARAM_TEXT                           ASM group name

CF Block Integrity Check       CF_BL_NUM            DBKH_PARAM_UB4                            Control file block number

Data Block Integrity Check     BLC_DF_NUM           DBKH_PARAM_UB4                            File number

Data Block Integrity Check     BLC_BL_NUM           DBKH_PARAM_UB4                            Block number

Dictionary Integrity Check     CHECK_MASK           DBKH_PARAM_TEXT      ALL                  Check mask

Dictionary Integrity Check     TABLE_NAME           DBKH_PARAM_TEXT      ALL_CORE_TABLES      Table name

Redo Integrity Check           SCN_TEXT             DBKH_PARAM_TEXT      0                    SCN of the latest good redo (if known)

Transaction Integrity Check    TXN_ID               DBKH_PARAM_TEXT                           Transaction ID

Undo Segment Integrity Check   USN_NUMBER           DBKH_PARAM_TEXT                           Undo segment number

9 rows selected.

Viewing Reports Using the ADRCI Utility

You can create and view Health Monitor checker reports using the ADRCI utility.

To create and view a checker report using ADRCI

  1. Ensure that operating system environment variables (such as ORACLE_HOME) are set properly, and then enter the following command at the operating system command prompt:
2.         ADRCI

The utility starts and displays the following prompt:

adrci>>

adrci> show hm_run

ADR Home = c:\app\oracle\diag\clients\user_bb15908\host_3515219834_76:

*************************************************************************

0 rows fetched

<ADR_RELATION>

<ADR_HOME name=”c:\app\oracle\diag\clients\user_bb15908\host_3515219834_76″>

ADR Home = c:\app\oracle\diag\clients\user_unknown\host_3515219834_76:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name=”c:\app\oracle\diag\clients\user_unknown\host_3515219834_76″>

ADR Home = c:\app\oracle\diag\rdbms\oracle11g\oracle11g:

*************************************************************************

**********************************************************

HM RUN RECORD 1

**********************************************************

RUN_ID                        1

RUN_NAME                      HM_RUN_1

CHECK_NAME                    DB Structure Integrity Check

NAME_ID                       2

MODE                          2

START_TIME                    2010-05-24 07:52:24.556000 +08:00

RESUME_TIME                   <NULL>

END_TIME                      2010-05-24 07:52:26.478000 +08:00

MODIFIED_TIME                 2010-05-24 07:52:26.478000 +08:00

TIMEOUT                       0

FLAGS                         0

STATUS                        6

SRC_INCIDENT_ID               0

NUM_INCIDENTS                 0

ERR_NUMBER                    210

REPORT_FILE                   <NULL>

**********************************************************

HM RUN RECORD 2

**********************************************************

RUN_ID                        21

RUN_NAME                      HM_RUN_21

CHECK_NAME                    DB Structure Integrity Check

NAME_ID                       2

MODE                          2

START_TIME                    2010-05-24 17:08:21.525000 +08:00

RESUME_TIME                   <NULL>

END_TIME                      2010-05-24 17:08:23.150000 +08:00

MODIFIED_TIME                 2010-05-24 17:08:23.150000 +08:00

TIMEOUT                       0

FLAGS                         0

STATUS                        6

SRC_INCIDENT_ID               0

NUM_INCIDENTS                 0

ERR_NUMBER                    210

REPORT_FILE                   <NULL>

**********************************************************

HM RUN RECORD 3

**********************************************************

RUN_ID                        41

RUN_NAME                      HM_RUN_41

CHECK_NAME                    DB Structure Integrity Check

NAME_ID                       2

MODE                          2

START_TIME                    2010-05-31 22:00:03.194000 +08:00

RESUME_TIME                   <NULL>

END_TIME                      2010-05-31 22:00:05.085000 +08:00

MODIFIED_TIME                 2010-05-31 22:00:05.085000 +08:00

TIMEOUT                       0

FLAGS                         0

STATUS                        5

SRC_INCIDENT_ID               0

NUM_INCIDENTS                 0

ERR_NUMBER                    0

REPORT_FILE                   <NULL>

**********************************************************

HM RUN RECORD 4

**********************************************************

RUN_ID                        61

RUN_NAME                      HM_RUN_61

CHECK_NAME                    DB Structure Integrity Check

NAME_ID                       2

MODE                          2

START_TIME                    2010-05-31 22:00:05.397000 +08:00

RESUME_TIME                   <NULL>

END_TIME                      2010-05-31 22:00:06.585000 +08:00

MODIFIED_TIME                 2010-05-31 22:00:06.585000 +08:00

TIMEOUT                       0

FLAGS                         0

STATUS                        5

SRC_INCIDENT_ID               0

NUM_INCIDENTS                 0

ERR_NUMBER                    0

REPORT_FILE                   <NULL>

**********************************************************

HM RUN RECORD 5

**********************************************************

RUN_ID                        81

RUN_NAME                      my_run

CHECK_NAME                    Dictionary Integrity Check

NAME_ID                       24

MODE                          0

START_TIME                    2010-06-02 14:52:41.657000 +08:00

RESUME_TIME                   <NULL>

END_TIME                      2010-06-02 14:52:48.172000 +08:00

MODIFIED_TIME                 2010-06-02 14:59:47.235000 +08:00

TIMEOUT                       0

FLAGS                         0

STATUS                        5

SRC_INCIDENT_ID               0

NUM_INCIDENTS                 0

ERR_NUMBER                    0

REPORT_FILE                   c:\app\oracle\diag\rdbms\oracle11g\oracle11g\hm\HMREPORT_my_run.hm

5 rows fetched

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:

SQL> SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

RUN_ID NAME                             CHECK_NAME

———- ——————————– ——————————–

RUN_MODE SRC_INCIDENT

——– ————

21 HM_RUN_21                        DB Structure Integrity Check

REACTIVE            0

41 HM_RUN_41                        DB Structure Integrity Check

REACTIVE            0

61 HM_RUN_61                        DB Structure Integrity Check

REACTIVE            0

RUN_ID NAME                             CHECK_NAME

———- ——————————– ——————————–

RUN_MODE SRC_INCIDENT

——– ————

81 my_run                           Dictionary Integrity Check

MANUAL              0

1 HM_RUN_1                         DB Structure Integrity Check

REACTIVE            0

The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 21

SQL> SELECT type, description FROM v$hm_finding WHERE run_id = 21;

no rows selected

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: