About Health Monitor Checks Health Monitor checks (also known as checkers, health checks, or checks) examine various layers and components of the database. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems. Health checks can be run in two ways:. Reactive—The fault diagnosability infrastructure can run health checks automatically in response to a critical error. Manual—As a DBA, you can manually run health checks using either the DBMSHM PL/SQL package or the Enterprise Manager interface. You can run checkers on a regular basis if desired, or Oracle Support may ask you to run a checker while working with you on a service request.
Database Health Check - Download as PDF File (.pdf), Text File (.txt) or read online. Hi All, Lot of time DBAs are asked to check the health of the Database,Health of the Database can be check in various ways.It includes: SL No Monitoring.
Performing an Oracle database health check? Toad users can launch database health checks either from the database browser, or from the diagnose menu.
Oracle Database Health Check Template
Health Monitor checks store findings, recommendations, and other information in the Automatic Diagnostic Repository (ADR). Health checks can run in two modes:. DB-online mode means the check can be run while the database is open (that is, in OPEN mode or MOUNT mode).
DB-offline mode means the check can be run when the instance is available but the database itself is closed (that is, in NOMOUNT mode). All the health checks can be run in DB-online mode. Only the Redo Integrity Check and the DB Structure Integrity Check can be used in DB-offline mode. Types of Health Checks Health monitor runs the following checks:. DB Structure Integrity Check—This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is in NOMOUNT mode, only the control file is checked.
Data Block Integrity Check—This check 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. Corrupted block information is also captured in the V$DATABASEBLOCKCORRUPTION view. This check does not detect inter-block or inter-segment corruption. Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption. Undo Segment Integrity Check—This check finds logical undo corruptions.
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$CORRUPTXIDLIST. Most undo corruptions can be resolved by forcing a commit. Transaction Integrity Check—This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.
Dictionary Integrity Check—This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:. Verifies the contents of dictionary entries for each dictionary object. Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced. Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.
The Dictionary Integrity Check operates on the following dictionary objects: tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typedview$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$. Running Health Checks Using the DBMSHM PL/SQL Package The DBMSHM procedure for running a health check is called RUNCHECK. To call RUNCHECK, supply the name of the check and a name for the run, as follows: BEGIN DBMSHM.RUNCHECK('Dictionary Integrity Check', 'myrun'); END; To obtain a list of health check names, run the following query: SELECT name FROM v$hmcheck WHERE internalcheck='N'; NAME - DB Structure Integrity Check Data Block Integrity Check Redo Integrity Check Transaction Integrity Check Undo Segment Integrity Check Dictionary Integrity Check Most health checks accept input parameters.
You can view parameter names and descriptions with the V$HMCHECKPARAM view. Some parameters are mandatory while others are optional. If optional parameters are omitted, defaults are used. The following query displays parameter information for all health checks: SELECT c.name checkname, p.name parametername, p.type, p.defaultvalue, p.description FROM v$hmcheckparam p, v$hmcheck c WHERE p.checkid = c.id and c.internalcheck = 'N' ORDER BY c.name; Input parameters are passed in the inputparams argument as name/value pairs separated by semicolons (;). The following example illustrates how to pass the transaction ID as a parameter to the Transaction Integrity Check: BEGIN DBMSHM.RUNCHECK ( checkname = 'Transaction Integrity Check', runname = 'myrun', inputparams = 'TXNID=7.33.2'); END. Running Health Checks Using Enterprise Manager Enterprise Manager provides an interface for running Health Monitor checkers.
To run a Health Monitor Checker using Enterprise Manager:. On the Database Home page, in the Related Links section, click Advisor Central. Click Checkers to view the Checkers subpage. In the Checkers section, click the checker you want to run. Enter values for input parameters or, for optional parameters, leave them blank to accept the defaults.
Click Run, confirm your parameters, and click Run again. Report Viewing Method Report Formats Available Enterprise Manager HTML DBMSHM PL/SQL package HTML, XML, and text ADRCI utility XML Results of checker runs (findings, recommendations, and other information) are stored in the ADR, but reports are not generated immediately. When you request a report with the DBMSHM PL/SQL package or with Enterprise Manager, if the report does not yet exist, it is first generated from the checker run data in the ADR, stored as a report file in XML format in the HM subdirectory of the ADR home for the current instance, and then displayed. If the report file already exists, it is just displayed. When using the ADRCI utility, you must first run a command to generate the report file if it does not exist, and then run another command to display its contents.
The preferred method to view checker reports is with Enterprise Manager. The following sections provide instructions for all methods:. Viewing Reports Using Enterprise Manager You can also view Health Monitor reports and findings for a given checker run using Enterprise Manager. To view run findings using Enterprise Manager. Access the Database Home page. For Oracle Enterprise Manager Database Control, see for instructions.
For Oracle Enterprise Manager Grid Control, go to the desired database target. In the Related Links section, click Advisor Central.
Click Checkers to view the Checkers subpage. Click the run name for the checker run that you want to view. The Run Detail page appears, showing the findings for that checker run. Click Runs to display the Runs subpage. Enterprise Manager displays more information about the checker run.
Click View Report to view the report for the checker run. The report is displayed in a new browser window. Viewing Reports Using DBMSHM You can view Health Monitor checker reports with the DBMSHM package function GETRUNREPORT. This function enables you to request HTML, XML, or text formatting. 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.
Ensure that operating system environment variables (such as ORACLEHOME) are set properly, and then enter the following command at the operating system command prompt: ADRCI The utility starts and displays the following prompt: adrci Optionally, you can change the current ADR home. Use the SHOW HOMES command to list all ADR homes, and the SET HOMEPATH command to change the current ADR home.
See for more information. Enter the following command: show hmrun This command lists all the checker runs (stored in V$HMRUN) registered in the ADR repository.
Locate the checker run for which you want to create a report and note the checker run name. The REPORTFILE field contains a filename if a report already exists for this checker run. Otherwise, generate the report with the following command: create report hmrun runname.
To view the report, enter the following command: show report hmrun runname. 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$HMRUN, V$HMFINDING, and V$HMRECOMMENDATION.
The following example queries the V$HMRUN view to determine a history of checker runs: SELECT runid, name, checkname, runmode, srcincident FROM v$hmrun; RUNID NAME CHECKNAME RUNMODE SRCINCIDENT - - - - - 1 HMRUN1 DB Structure Integrity Check REACTIVE 0 101 HMRUN101 Transaction Integrity Check REACTIVE 6073 121 TXNCHK Transaction Integrity Check MANUAL 0 181 HMRtab$ Dictionary Integrity Check MANUAL 0. Table 8-9 Parameters for Dictionary Integrity Check Parameter Name Type Default Value Description CHECKMASK Text ALL Possible values are:. COLUMNCHECKS—Run column checks only. Verify column-level constraints in the core tables. ROWCHECKS—Run row checks only.
Verify row-level constraints in the core tables. REFERENTIALCHECKS—Run referential checks only. Verify referential constraints in the core tables. ALL—Run all checks. TABLENAME Text ALLCORETABLES Name of a single core table to check.
If omitted, all core tables are checked. Scripting on this page enhances content navigation, but does not change the content in any way.