askMLabs

Automatic Health Monitor :
It is similar to advisors introduced in 10g. The Automatic Health Monitor "checkers" monitor (automatically after a failure or on demand) various components, such as the datafiles and dictionary, to make sure they are not corrupted physically or logically. IPS (Incident Packaging Service) is used to bundle all supporting files for use to Oracle Support.

EM Navigation :
Database page -> Related Links -> Advisor Central -> Advisors and Checkers -> Checkers

Checkers :

DB Structure Integrity Check
Data Block Integrity Checker
Redo Integrity Check
Undo Segment Integrity Check
Transaction Integrity Check
Dictionary Integrity Check

Checkers generate reports of their findings and recommendations for resolving problems.
Run Type : "Manual" and "Reactive" (automatic). We can follow the suggestions from the above health check runs.

Data Recovery Advisor :

Using OEM :  Availability> Manage> Perform Recovery> Perform Automated Repair

Automatic Diagnostic Repository :
All the critical events in the database are recorded in a repository called Automatic Diagnostic Repository. It is newly introduced in 11g.
It is a new directory structure which can be set using diagnostic_dest ( Default ORACLE_BASE ).

EM Navigation :
Database page -> Software and Support -> Support Workbench
Navigate to the corresponding issue/error/incident and create a package( all required info for Oracle Support ) and then pass it to support.
We can use Configuration Manager to pass the collected pacakge to Oracle Support.

We can also collect the packages using the command line utility ( ADRCI )

ADR Home :

The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.
This is the same DIAGNOSTIC_DEST value. This home stores all the logs and traces of all the oracle components like ASM, CRS, listener, DB at a single location unlike oracle 10g.

Configuring ADR :

Set value for init.ora parameter  DIAGNOSTIC_DEST

Query the view V$DIAG_INFO to display all the ADR-related locations (also number of active problems and incidents):

Using the Tool :

adrci

adrci>> show homes
adrci>> help
adrci>> help show incident
adrci>> show incident -mode basic
adrci>> show incident -mode detail -p "incident_id=14556"
adrci>> set homepath diag/rdbms/askmdb/ASKMDB11
adrci>> show alert
adrci>> show alert -tail -f
adrci>> show alert -p "module_id='DBMS_SCHEDULER'"
adrci>> show alert -p "module_id != 'DBMS_SCHEDULER'"
adrci>> show alert -p "module_id like '%SCHEDULER'"
adrci>> spool a
adrci>> show alert -tail 50
adrci>> spool off
adrci>> show alert -p "message_text like '%STREAM%'"
adrci>> show tracefile
adrci>> show tracefile %reco% -rt

Problems and Incidents :

Problem is a critical error occurred in the database. Each problem has a problem key. The problem key consists of the Oracle error number and error argument. Here is an example: ORA 600 [4899].

The first time a problem occurs, an incident is also created. When the same problem takes place again, another incident will be generated for the same problem i.e relation between problem and incident is one to many. Thus, you may have multiple incidents for a one problem.

When an incident occurs, Oracle performs the following:

a) An alert will be issued.

b) An entry will be added in the alert log file.

c) Related data will be stored in the ADR directory.


IMPORTANT TABLES :

V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_RUN
V$HM_FINDING
V$HM_RECOMMENDATION

GV$HM_INFO
GV$HM_CHECK
GV$HM_CHECK_PARAM
GV$HM_RUN
GV$HM_FINDING
GV$HM_RECOMMENDATION

==== DEMO DEMO DEMO DEMO DEMO DEMO ========

=== ====== DEMO  ON  IPS           =========  =========

Incident Packaging Service (IPS) wraps up all information about an incident and allows you to send the whole package to Oracle Support.

adrci>> show incident
adrci>> ips create package -- this will create a logical package
adrci>> help ips
adrci>> ips add incident xxxx package 4
adrci>> ips add file <alert loc>  package 4
adrci>> ips finalize package 2
adrci>> ips generate package 2 in <local folder>

=== ====== DEMO  ON  HEALTH  MONITOR  =========

SQL> select name,user from v$database;

NAME      USER
--------- ------------------------------
ASKMDB   SYS

SQL> select name,description from v$hm_check;

NAME                                DESCRIPTION
----------------------------------- ---------------------------------------------

HM Test Check                       Check for HM Functionality
DB Structure Integrity Check        Checks integrity of all database files
Data Block Integrity Check          Checks integrity of a datafile block
Redo Integrity Check                Checks integrity of redo log content
Logical Block Check                 Checks logical content of a block
Transaction Integrity Check         Checks a transaction for corruptions
Undo Segment Integrity Check        Checks integrity of an undo segment
All Control Files Check             Checks all control files in the database
CF Member Check                     Checks a multiplexed copy of the control file
All Datafiles Check                 Check for all datafiles in the database
Single Datafile Check               Checks a datafile
Log Group Check                     Checks all members of a log group
Log Group Member Check              Checks a particular member of a log group
Archived Log Check                  Checks an archived log
Redo Revalidation Check             Checks redo log content
IO Revalidation Check               Checks file accessability
Block IO Revalidation Check         Checks file accessability
Txn Revalidation Check              Revalidate corrupted txn
Failure Simulation Check            Creates dummy failures
Dictionary Integrity Check          Checks dictionary integrity

21 rows selected.

SQL> 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

6 rows selected.

SQL> select name,id from v$hm_check where name='Transaction Integrity Check';

NAME                                        ID
----------------------------------- ----------
Transaction Integrity Check                 10

SQL> select c.name , p.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.name like 'Transaction Integrity Check' order by c.name;

NAME                     NAME      TYPE              DEFAU  DESCRIPTION
----------------------------------- --------------------------------
Transaction Integrity    TXN_ID    DBKH_PARAM_TEXT          Transaction ID
Check

Connect to any user and do some un commited transactions

SQL> sho user
USER is "SCOTT"
SQL> create table test_hm_table as select * from emp;

Table created.

SQL> insert into test_hm_table select * from emp;

14 rows created.

SQL> SELECT dbms_transaction.local_transaction_id FROM dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
8.29.494

AS SYS user

SQL> sho user
USER is "SYS"
SQL> exec dbms_hm.run_check(check_name => 'Transaction Integrity Check', run_name => 'RUN_1', input_params => 'TXN_ID=8.29.494');

PL/SQL procedure successfully completed.

SQL>

Getting Report:

METHOD 1

SQL> set long 100000
SQL> select dbms_hm.get_run_report('RUN_1') from dual;

DBMS_HM.GET_RUN_REPORT('RUN_1')
--------------------------------------------------------------------------------
Basic Run Information
Run Name                     : RUN_1
Run Id                       : 41
Check Name                   : Transaction Integrity Check
Mode                         : MANUAL
Status                       : COMPLETED
Start Time                   : 2009-07-24 08:15:42.163947 -07:00
End Time                     : 2009-07-24 08:15:42.299963 -07:00
Error Encountered            : 0
Source Incident Id           : 0
Number of Incidents Created  : 0

Input Paramters for the Run
TXN_ID=8.29.494

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

METHOD 2

SQL> select name,run_id from v$hm_run where name='RUN_1';

NAME                                    RUN_ID
----------------------------------- ----------
RUN_1                                       41

SQL> select * from v$hm_run where name='RUN_1';

RUN_ID NAME                                CHECK_NAME                       RUN_MODE    TIMEOUT
---------- ----------------------------------- -------------------------------- -------- ----------
START_TIME
---------------------------------------------------------------------------
LAST_RESUME_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
MODIFIED_TIME                                                               STATUS      SRC_INCIDENT NUM_INCIDENT
--------------------------------------------------------------------------- ----------- ------------ ------------
ERROR_NUMBER PROBLEM_ID
------------ ----------
41 RUN_1                               Transaction Integrity Check      MANUAL            0
24-JUL-09 08.15.42.163947 AM

24-JUL-09 08.15.42.299963 AM
24-JUL-09 08.15.42.299963 AM                                                COMPLETED              0            0
0          0

METHOD 3 :

adrci> show homes
ADR Homes:
diag/rdbms/askmdb/ASKMDB
adrci> show hm_run

**********************************************************
HM RUN RECORD 3
**********************************************************
RUN_ID                        41
RUN_NAME                      RUN_1
CHECK_NAME                    Transaction Integrity Check
NAME_ID                       10
MODE                          0
START_TIME                    2009-07-24 08:15:42.163947 -07:00
RESUME_TIME                   <NULL>
END_TIME                      2009-07-24 08:15:42.299963 -07:00
MODIFIED_TIME                 2009-07-24 08:16:24.444919 -07:00
TIMEOUT                       0
FLAGS                         0
STATUS                        5
SRC_INCIDENT_ID               0
NUM_INCIDENTS                 0
ERR_NUMBER                    0
REPORT_FILE                   /u01/app/oracle/diag/rdbms/askmdb/ASKMDB/hm/HMREPORT_RUN_1.hm
3 rows fetched

adrci> show report hm_run RUN_1

Transaction Integrity Check
41
RUN_1
MANUAL
COMPLETED
0
0
0
2009-07-24 08:15:42.163947 -07:00
2009-07-24 08:15:42.299963 -07:00

TXN_ID=8.29.494

TXN not corrupt
42
INFORMATIONAL
OPEN
HIGH
0
2009-07-24 08:15:42.293064 -07:00
Transaction 8.29.494 is not corrupted

adrci>

METHOD 4 :
Using EM tool also we can view the report.