Wednesday, September 28, 2011

Database Health Check

If 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

SQL>  select name from v$hm_check where internal_check='N';

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.

Once you know what kind of check you are interested in, specify check name in the package as below

SQL> begin
  2  dbms_hm.run_check('Data Block Integrity Check','test_db_intg_chk');
  3  end;

Here is how to view the findings

SQL> select dbms_hm.get_run_report('test_db_intg_chk') from dual; 

You can run most of the health checks while the database is open.However, Redo Integrity 
Check and the DB Structure Integrity Check can only be executed while the database is closed—you must place the database in the NOMOUNT state to run these two checks. 

Use the show hm_run, create report, and show report commands to view health check reports with the ADRCI utility. For details, refer to this article
You can use the views V$HM_FINDING and V$HM_RECOMMENDATION to investigate the findings as well as the recommendations pursuant to a health check.

No comments:

Post a Comment