Database Health Checks - Oracle 11g

Proactive checking of various data base components can help you avoid pote ntial problems.Release 11g of the Oracle Data base provides a new monitoring frame work called the Health Monitor, which runs diagnostic checks on data base components such as the file system, memory, and transactionintegrity. Each check generates a report containing findings as well asrecommendations to fix the problem.

The Health Monitor automatically examines the data base to check the state of the various components of the database whenever there is a database error.

The goal of these checks that are automatically made pursuant to errors in the database is to catch dangerous things such as file corruptions,physical and logical block corruptions,and data dictionary corruption before they can seriously damage the data base.These checks result in a report of the data base error and,in most cases,also recommendations to fix the problem.You,as the DBA,also have the option to proactively run database health checks using the same mechanism as the automatic checks made by the data base.

Whenever there’s a critical error in the database,the database automa tically runs the Health Monitor to diagnose the problem.The Health Monitor logs all its findings in the ADR, from where the Data Recovery Advisor can access the results in order to generate reports or repair the problems. You can also run the Health Monitor manually,either following a database problem or as a regular part of your DBA activities, through Database Control or with the help of the new Oracle-supplied package, DBMS_HM.

Checks, Failures, and Repairs

Checks (also called checkers or health checks) are diagnostic operations or procedures that are registered with the new Health Monitor to help assess the health of your database or other Oracle components.If a database check results in the discovery of a data base failure,oracle maps that failure to a database repair.

In Oracle Database 11g,the database will perform automatic reactive checks for you.Each time there’s an error of any kind,the database will automatically invoke a database checker to search for failures related to that error.This is called a reactive check,since it’s made in response to an event in the data base.However,Oracle recommends you schedule periodic proactive checks during periods of low database usage.

You can perform a proactive data base check through RMAN or the Data base Control/Grid Control. if an automatic reactive check shows a problem in a par ticular database component,it’s a good idea to further investigate the problem with a proactive check of the affected database component.

A failure represents a diagnosed problem and is defined as persistent data corruption that’s detected by a database checker,such as the Redo Check checker,which checks the integrity of the redo logs,for example.If a checker diagnoses a database failure,it records that information in the ADR.You& rsquo;ll be able to employ the new Data Recovery Advisor(explained later in this chapter) to get advice on failures and repair them if those failures are recorded in the ADR.

Types of Health Monitor Checks

You can view the input para meters that you can specify for the health checks by using the V$CHECK_PARAM view.

You can run the health checks in two modes—DB-online (database in the open or mount state)and DB-offline(data base in the nomount mode,with just the instance up).You can perform most Health Mon itor checks even when the data base is offline.The column off line_ capable in the V$HM_CHECK view shows whether you can perform an offline check for a particular type of check.

As mentioned earlier,although the database is capable of detecting fail ures by the automatic execution of checkers,that process is set off only when the data base encounters a problem.such as corrupted data.By running various checks manually,you can catch failures before they cause extensive damage .we’ ll discuss manual checks in the following section.

Manual Checks

Although the data base automatically performs reactive checks,it’s a good idea to proactively run the checkers on a regular basis to detect potential failures that haven’t affected the data base yet.Reactive checks are run only when a failure affects the data base,say,when a user tries to retrieve data from a corrupted data block.However,a corrupted data block won’t automatically cause a failure alert to be lod ged in the database if users haven’t accessed that block after it was corrupted.Manual checks help catch such hidden fail ures that may strike during the course of a busy day and potentially hinder the availability of the database.

You can perform manual data base checks by using Data base Control or by using the DBMS_HM PL/SQL package.In addition, you can also perform data base corruption checks using the new RMAN validate command.We’ll first look at how to run checks using the DBMS_HM package.

Manual Checking Using the DBMS_HM Package

As mentioned earlier,Oracle makes the Health Monitor functio nality available through the DBMS_HM package.Use the run_check procedure of the DBMS_HM package to run a proactive health check.Here is the structure of the run_check procedure:

  1. CHECKNAME:Name of the database check.This is a mandatory argument and must exactly match a check name from the V$HM_CHECK view.
  2. RUNNAME:This is an optional argument that lets you specify a name for the check run.
  3. TIMEOUT:Optionally,you can set a limit on the length of time a check can run.
  4. PARAMS:This is an input parameter that controls the exec ution of the check.You can view the various types of inputs by querying the V$HM_ CHECK_PARM view.

In the following example,we show you how to perform a data base cross check,which tests the integrity of all data files in the data base.Just pass the type of check and a name for the test run to the run_check procedure,as shown here:

The Health Monitor saves the report of all its data base checks in the ADR home of this,data base instance.The show hm_run comm and shows a summary of all checker executions that are registered in the ADR.These are the same chec ker runs that will be visible from the V$HM_RUN view.Here are sample results of the show hm_run command:

You can view a detailed report of a particular execution of a check by the Health Monitor by using the adrci command show report:

The Health Monitor report shown in the previous example reveals block corruption in datafile 2,which happens to belong to the sysaux tablespace.

All Health Monitor reports are stored in the V$HM_RUN view.But you must generate the reports first,using either the adrci tool as shown in this exa mple or using the DBMS_HM package,as shown in the following example:

In addition to using adrci or the DBMS_HM package to get the report for a database check,you can directly query the V$HM_RUN view to get infor mation about the checks.For example,you can issue the following query to see the results of a check,as shown here:

The run_mode column tells you whether a particular health check wasproactive(manual) or reactive.Proactive checks are checks made by you.

You can also get details about the findings by que rying the V$HM_FINDING view and can get recommendations for fixing the problems by querying the V$HM_RE COMMEN DATION view.You can also access the Health Monitor reports through Data base Control,as we explain later in this chapter.

Manual Checks Using Data base Control

You can also run a Health Monitor check through Database Control by using the following steps:

  1. Click Advisor Central on the Data base home page.
  2. Click Checkers to go to the Checkers subpage.
  3. Click the checker you want to run.
  4. Enter values for each parameter for the checker run.
  5. Click Run.After confirming your choices,click Run again to start the check.

Figure shows the various checkers provided by Data base Control Each of the checkers has a different number of para meters and arguments.

The Checkers subpage in Data base Control

The Checkers subpage in Data base Control

Manual Checks with the RMAN Validate Command

In previous releases of the Oracle Data base,you could use the backup ...vali date command to validate your back ups.Essentially,what this command helped you do was check for both logical and physical intrablock corruption in the data files and also whether the data files could be backed up by RMAN.The command doesn’t actually perform the actual back up but only validates the data files so you can ensure you’ll have usable and valid back ups when you use RMAN to back up those data files.

Manual Checks with the RMAN Validate Command

In Oracle Data base 11g,there is a new command called vali date that has semantically similar options as the older back up...validate command but can vali date at a much finer level of granula rity.Whereas you could use the back up...validate command at only the data base level,you can use the vali date command to per form the same job at the backup set,table space,datafile,or even at the data block level.You may even use it to check the integrity of the flash recovery area or all recovery files.

If you think there are failures that haven’t been trapped auto matically by the data base,run the validate command to check for missing files and corrupt data blocks.

The validate command helps you invoke a data base health check to assess the failure when it finds any type of data failure.RMAN logs the failure information in the ADR.

You can then use the Data Recovery Advisor’s list failure,advise failure and repair failure commands in RMAN to view the failures and repair them.By default, the validate command looks for physical corruption of data blocks.By including the check logical clause,you can make the vali date command look for logical intrablock corruption as well.

Manual Checks with the RMAN Validate Command

Here’s an example show ing how to check for block corruption through out the data base using the new vali date command:

Unlike the RMAN backup ...validate command,the vali date command lets you parallelize the validation by dividing each file into sections.You must use the option section sizeto parallelize the validation, which covers backup and recovery new features,discusses both the validate command and the new backup parameter section_size in detail.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics