How do you check whether your Oracle database is running smoothly or not? Do you check everything from scratch or have hired a DBA staff for doing it? Well both are very time consuming process. Oracle 11g has introduced Automatic Health Monitor to monitor the health of database. It checks various components like data dictionary and data files for physical and logical corruption. As the name suggests it automatically monitor the database components and store all the details in Automatic Diagnostic Repository. When an error is detected, packages are created by Packing services and send it to Oracle support.
Health Monitor also suggests solutions for solving errors. You can access Health Monitor through command line or Oracle Enterprise Manager GUL. Health Monitor can be run in both offline and online mode. You can run Health Monitor in two ways; one is Reactive and another is Manual. In Reactive, Health Check Monitor run automatically in response to an error while in Manual, you have have to run it manually whenever you want using either Enterprise Manager or DBMS_HM PL/SQL.
Types of Health Checks run by Health Monitor
- DB Structure Integrity Check: This check is used to check the integrity of database files and also reports failure in case of corruption or inaccessibility. It checks for data and logfile if database is in online mode. In case of offline mode it will check only control file.
- Data Block Integrity Check: This check is used to check block corruption or inconsistencies within the block. It also stores information of corrupted block in V$DATABASE_BLOCK_CORRUPTION view.
- Redo Integrity Check: This check is used to check the redo and archived log for corruption.
- Undo Segment Integrity Check: It checks for logical undo corruption. If corruption is detected, this check tries to recover corrupt transaction using PMON and SMON. In case of failure of recovery, Health Monitor will store all the information of corruption in V$CORRUPT_XTD_LIST.
- Transaction Integrity Check: This check work similar to Undo Segment Integrity Check. The only difference is that it checks only single transaction.
- Dictionary Integrity Check: This check is used to verify the integrity of core dictionary objects. It also verifies the contents of each dictionary object.