9 Managing Diagnostic Data

Fault Diagnosability Infrastructure Overview

The fault diagnosability infrastructure aids in preventing, detecting, diagnosing, and resolving problems. The problems that are targeted in particular are critical errors such as those caused by code bugs, metadata corruption, and customer data corruption.

When a critical error occurs, it is assigned an incident number, and diagnostic data for the error (such as trace files) are immediately captured and tagged with this number. The data is then stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database—where it can later be retrieved by incident number and analyzed.

The goals of the fault diagnosability infrastructure are the following:

• First-failure diagnosis

• Problem prevention

• Limiting damage and interruptions after a problem is detected

• Reducing problem diagnostic time

• Reducing problem resolution time

• Simplifying customer interaction with Oracle Support

The keys to achieving these goals are the following technologies:

• Automatic capture of diagnostic data upon first failure—For critical errors, the ability to capture error information at first-failure greatly increases the chance of a quick problem resolution and reduced downtime. An always-on memory-based tracing system proactively collects diagnostic data from many database components, and can help isolate root causes of problems. Such proactive diagnostic data is similar to the data collected by airplane "black box" flight recorders. When a problem is detected, alerts are generated and the fault diagnosability infrastructure is activated to capture and store diagnostic data. The data is stored in a repository that is outside the database (and therefore available when the database is down), and is easily accessible with command line utilities and Enterprise Manager.

• Standardized trace formats—Standardizing trace formats across all database components enables DBAs and Oracle Support personnel to use a single set of tools for problem analysis. Problems are more easily diagnosed, and downtime is reduced.

• Health checks—Upon detecting a critical error, the fault diagnosability infrastructure can run one or more health checks to perform deeper analysis of a critical error. Health check results are then added to the other diagnostic data collected for the error. Individual health checks look for data block corruptions, undo and redo corruption, data dictionary corruption, and more. As a DBA, you can manually invoke these health checks, either on a regular basis or as required.

• Incident packaging service (IPS) and incident packages—The IPS enables you to automatically and easily gather the diagnostic data—traces, dumps, health check reports, and more—pertaining to a critical error and package the data into a zip file for transmission to Oracle Support. Because all diagnostic data relating to a critical error are tagged with that error's incident number, you do not have to search through trace files and other files to determine the files that are required for analysis; the incident packaging service identifies the required files automatically and adds them to the zip file. Before creating the zip file, the IPS first collects diagnostic data into an intermediate logical structure called an incident package (package). Packages are stored in the Automatic Diagnostic Repository. If you choose to, you can access this intermediate logical structure, view and modify its contents, add or remove additional diagnostic data at any time, and when you are ready, create the zip file from the package. After these steps are completed, the zip file is ready to be uploaded to Oracle Support.

• Data Recovery Advisor—The Data Recovery Advisor integrates with database health checks and RMAN to display data corruption problems, assess the extent of each problem (critical, high priority, low priority), describe the impact of a problem, recommend repair options, conduct a feasibility check of the customer-chosen option, and automate the repair process.

• SQL Test Case Builder—For many SQL-related problems, obtaining a reproducible test case is an important factor in problem resolution speed. The SQL Test Case Builder automates the sometimes difficult and time-consuming process of gathering as much information as possible about the problem and the environment in which it occurred. After quickly gathering this information, you can upload it to Oracle Support to enable support personnel to easily and accurately reproduce the problem.

About Incidents and Problems

To facilitate diagnosis and resolution of critical errors, the fault diagnosability infrastructure introduces two concepts for Oracle Database: problems and incidents.

A problem is a critical error in a database instance, Oracle Automatic Storage Management (Oracle ASM) instance, or other Oracle product or component. Critical errors manifest as internal errors, such as ORA-00600, or other severe errors, such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool). Problems are tracked in the ADR. Each problem has a problem key, which is a text string that describes the problem. It includes an error code (such as ORA 600) and in some cases, one or more error parameters.

An incident is a single occurrence of a problem. When a problem (critical error) occurs multiple times, an incident is created for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database:

• Makes an entry in the alert log.

• Sends an incident alert to Oracle Enterprise Manager (Enterprise Manager).

• Gathers first-failure diagnostic data about the incident in the form of dump files (incident dumps).

• Tags the incident dumps with the incident ID.

• Stores the incident dumps in an ADR subdirectory created for that incident.

Diagnosis and resolution of a critical error usually starts with an incident alert. Incident alerts are displayed on the Enterprise Manager Database Home page or Oracle Automatic Storage Management Home page. The Database Home page also displays in its Related Alerts section any critical alerts in the Oracle ASM instance or other Oracle products or components. After viewing an alert, you can then view the problem and its associated incidents with Enterprise Manager or with the ADRCI command-line utility.

The following sections provide more information about incidents and problems:

• Incident Flood Control

• Related Problems Across the Topology

Fault Diagnosability Infrastructure Components

The following are the key components of the fault diagnosability infrastructure:

• Automatic Diagnostic Repository (ADR)

• Alert Log

• Trace Files, Dumps, and Core Files

• Other ADR Contents

• Enterprise Manager Support Workbench

• ADRCI Command-Line Utility

Structure, Contents, and Location of the Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down.

The ADR root directory is known as ADR base. Its location is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

• If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

• If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

Within ADR base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data—traces, dumps, the alert log, and so on—for a particular instance of a particular Oracle product or component. For example, in an Oracle Real Application Clusters environment with Oracle ASM, each database instance, Oracle ASM instance, and listener has an ADR home.

ADR homes reside in ADR base subdirectories that are named according to the product or component type. Figure 9-1 illustrates these top-level subdirectories.

Figure 9-1 Product/Component Type Subdirectories in the ADR

Description of "Figure 9-1 Product/Component Type Subdirectories in the ADR"

The location of each ADR home is given by the following path, which starts at the ADR base directory:

diag/product_type/product_id/instance_id

As an example, Table 9-1 lists the values of the various path components for an Oracle Database instance.

For example, for a database with a SID and database unique name both equal to orclbi, the ADR home would be in the following location:

ADR_base/diag/rdbms/orclbi/orclbi/

Similarly, the ADR home path for the Oracle ASM instance in a single-instance environment would be:

ADR_base/diag/asm/+asm/+asm/

ADR Home Subdirectories

Within each ADR home directory are subdirectories that contain the diagnostic data. Table 9-2 lists some of these subdirectories and their contents.

Figure 9-2 illustrates the complete directory hierarchy of the ADR for a database instance.

Figure 9-2 ADR Directory Structure for a Database Instance

Description of "Figure 9-2 ADR Directory Structure for a Database Instance"

ADR in an Oracle Real Application Clusters Environment

In an Oracle Real Application Clusters (Oracle RAC) environment, each node can have ADR base on its own local storage, or ADR base can be set to a location on shared storage. You can use ADRCI to view aggregated diagnostic data from all instances on a single report.

ADR in Oracle Client

Each installation of Oracle Client includes an ADR for diagnostic data associated with critical failures in any of the Oracle Client components. The ADRCI utility is installed with Oracle Client so that you can examine diagnostic data and package it to enable it for upload to Oracle Support.

Viewing ADR Locations with the V$DIAG_INFO View

The V$DIAG_INFO view lists all important ADR locations for the current Oracle Database instance.

SELECT * FROM V$DIAG_INFO;

INST_ID NAME                  VALUE
------- --------------------- -------------------------------------------------------------
      1 Diag Enabled          TRUE
      1 ADR Base              /u01/oracle
      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi
      1 Diag Trace            /u01/oracle/diag/rdbms/orclbi/orclbi/trace
      1 Diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert
      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident
      1 Diag Cdump            /u01/oracle/diag/rdbms/orclbi/orclbi/cdump
      1 Health Monitor        /u01/oracle/diag/rdbms/orclbi/orclbi/hm
      1 Default Trace File    /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc
      1 Active Problem Count  8
      1 Active Incident Count 20

The following table describes some of the information displayed by this view.

Viewing Critical Errors with the V$DIAG_CRITICAL_ERROR View

The V$DIAG_CRITICAL_ERROR view lists all of the non-internal errors designated as critical errors for the current Oracle Database release. The view does not list internal errors because internal errors are always designated as critical errors.

The following example shows the output for the V$DIAG_CRITICAL_ERROR view in Oracle Database 11g Release 2 (11.2.0.2):

SELECT * FROM V$DIAG_CRITICAL_ERROR;

FACILITY   ERROR
---------- ----------------------------------------------------------------
ORA        7445
ORA        4030
ORA        4031
ORA        29740
ORA        255
ORA        355
ORA        356
ORA        239
ORA        240
ORA        494
ORA        3137
ORA        227
ORA        353
ORA        1578
ORA        32701
ORA        32703
ORA        29770
ORA        29771
ORA        445
ORA        25319
OCI        3106
OCI        3113
OCI        3135

The following table describes the information displayed by this view.

Roadmap—Investigating, Reporting, and Resolving a Problem

You can begin investigating a problem by starting from the Support Workbench home page in Enterprise Manager. However, the more typical workflow begins with a critical error alert on the Database Home page. This section provides an overview of that workflow.

Figure 9-3 illustrates the tasks that you complete to investigate, report, and in some cases, resolve a problem.

Figure 9-3 Workflow for Investigating, Reporting, and Resolving a Problem

Description of "Figure 9-3 Workflow for Investigating, Reporting, and Resolving a Problem"

The following are task descriptions. Subsequent sections provide details for each task.

• Task 1: View Critical Error Alerts in Enterprise Manager

  Start by accessing the Database Home page in Enterprise Manager, and reviewing critical error alerts. Select an alert for which to view details, and then go to the Problem Details page.

• Task 2: View Problem Details

  Examine the problem details and view a list of all incidents that were recorded for the problem. Display findings from any health checks that were automatically run.

• Task 3: (Optional) Gather Additional Diagnostic Information

  Optionally run additional health checks or other diagnostics. For SQL-related errors, optionally invoke the SQL Test Case Builder, which gathers all required data related to a SQL problem and packages the information in a way that enables the problem to be reproduced at Oracle Support.

• Task 4: (Optional) Create a Service Request

  Optionally create a service request with My Oracle Support and record the service request number with the problem information. If you skip this step, you can create a service request later, or the Support Workbench can create one for you.

• Task 5: Package and Upload Diagnostic Data to Oracle Support

  Invoke a guided workflow (a wizard) that automatically packages the gathered diagnostic data for a problem and uploads the data to Oracle Support.

• Task 6: Track the Service Request and Implement Any Repairs

  Optionally maintain an activity log for the service request in the Support Workbench. Run Oracle advisors to help repair SQL failures or corrupted data.

• Task 7: Close Incidents

  Set status for one, some, or all incidents for the problem to Closed.

Task 1: View Critical Error Alerts in Enterprise Manager

You begin the process of investigating problems (critical errors) by reviewing critical error alerts on the Database Home page or Oracle Automatic Storage Management Home page.

To view critical error alerts:

1. Access the Database Home page in Enterprise Manager.

   For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.

2. Do one of the following to view critical error alerts. (Critical error alerts are indicated by a red × in the Severity column, and the text "Incident" in the Category column.)

   • View the alerts in the Alerts section.

     You may have to click the hide/show icon next to the Alerts heading to display the alerts.

   • View the alerts in the Related Alerts section.

     The Target Name indicates the Oracle product or component that experienced the critical error. See "Related Problems Across the Topology" for more information.

   • View critical alerts for the Oracle ASM instance by completing these steps:

     1. In the General section, click the link next to the label ASM.

     2. On the Oracle Automatic Storage Management Home page, scroll down to view the Alerts section.

   Figure 9-4 Alerts Section of the Database Home Page

   
   Description of "Figure 9-4 Alerts Section of the Database Home Page"
   
   

3. In the Message column, click the message of the critical error alert that you want investigate.

   The Incident page or Data Failure page appears. This page includes:

   • Problem information, including the number of incidents for the problem

   • A Performance and Critical Error graphical timeline for the 24-hour period in which the critical error occurred (database instance only).

   • Alert details, including severity, timestamp, and message

   • Controls that enable you to clear the alert or record a comment about it.

4. Review the Performance and Critical Error graphical timeline if present, and note any time correlation between performance issues and the critical error. Optionally clear the alert or leave a comment about it.

Task 2: View Problem Details

You continue your investigation by viewing the Problem Details page.

To view problem details:

1. On the Incident page or Data Failure page, click View Problem Details.

   The Problem Details page appears, showing the Incidents subpage.

2. (Optional) Do one or both of the following:

   • In the Investigate and Resolve section, on the Self Service tab, under Diagnose, click Related Problems Across Topology.

     A page appears showing any related problems in the local Oracle Automatic Storage Management (Oracle ASM) instance, or in the database or Oracle ASM instances on other nodes in an Oracle Real Application Clusters environment. This step is recommended if any critical alerts appear in the Related Alerts section on the Enterprise Manager Database Home page.

     See "Related Problems Across the Topology" for more information.

   • View incident details by completing these steps:

     1. In the Incidents subpage, select an incident, and then click View.

     2. (Optional) On the Incident Details page, click Checker Findings to view the Checker Findings subpage.

        This page displays findings from any health checks that were automatically run when the critical error was detected.

        See "Running Health Checks with Health Monitor" for more information.

Task 3: (Optional) Gather Additional Diagnostic Information

You can perform the following activities to gather additional diagnostic information for a problem. This additional information is then automatically included in the diagnostic data that is uploaded to Oracle Support. If you are unsure about performing these activities, then check with your Oracle Support representative.

• Manually invoke additional health checks

  See "Running Health Checks with Health Monitor"

• Invoke the SQL Test Case Builder

  See Oracle Database Performance Tuning Guide for instructions.

Task 4: (Optional) Create a Service Request

At this point, you can create an Oracle Support service request and record the service request number with the problem information. If you choose to skip this task, the Support Workbench will automatically create a draft service request for you in Task 5.

To create a service request:

1. On the Problem Details page, in the Investigate and Resolve section, click Go to My Oracle Support and Research.

   The My Oracle Support Sign In and Registration page appears in a new browser window.

   Note:

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions for returning to the Problem Details page if you are not already there.

2. Log in to My Oracle Support and create a service request in the usual manner.

   (Optional) Remember the service request number (SR#) for the next step.

3. (Optional) Return to the Problem Details page, and then do the following:

   1. In the Summary section, click the Edit button that is adjacent to the SR# label.

   2. Enter the SR#, and then click OK.

   The SR# is recorded in the Problem Details page. This is for your reference only.

Task 5: Package and Upload Diagnostic Data to Oracle Support

For this task, you use the quick packaging process of the Support Workbench to package and upload the diagnostic information for the problem to Oracle Support. Quick packaging has a minimum of steps, organized in a guided workflow (a wizard). The wizard assists you with creating an incident package (package) for a single problem, creating a zip file from the package, and uploading the file. With quick packaging, you are not able to edit or otherwise customize the diagnostic information that is uploaded. However, quick packaging is the more direct, straightforward method to package and upload diagnostic data.

To edit or remove sensitive data from the diagnostic information, enclose additional user files (such as application configuration files or scripts), or perform other customizations before uploading, you must use the custom packaging process, which is a more manual process and has more steps. See "Creating, Editing, and Uploading Custom Incident Packages" for instructions. If you choose to follow those instructions instead of the instructions here in Task 5, do so now and then continue with Task 6: Track the Service Request and Implement Any Repairs when you are finished.

To package and upload diagnostic data to Oracle Support:

1. On the Problem Details page, in the Investigate and Resolve section, click Quick Package.

   The Create New Package page of the Quick Packaging wizard appears.

   Note:

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions for returning to the Problem Details page if you are not already there.
   
   Description of the illustration quick_package_wizard_page1.gif
   
   

2. Optionally enter a package name and description.

3. Fill in the remaining fields on the page. If you already created a service request for this problem, select No next to Create new Service Request (SR).

   If you select Yes, the Quick Packaging wizard creates a draft service request on your behalf. You must later log in to My Oracle Support and fill in the details of the service request.

4. Click Next, and then proceed with the remaining pages of the Quick Packaging wizard.

   When the Quick Packaging wizard is complete, the package that it creates remains available in the Support Workbench. You can then modify it with custom packaging operations (such as adding new incidents) and reupload at a later time. See "Viewing and Modifying Incident Packages".

Task 6: Track the Service Request and Implement Any Repairs

After uploading diagnostic information to Oracle Support, you might perform various activities to track the service request, to collect additional diagnostic information, and to implement repairs. Among these activities are the following:

• Adding an Oracle bug number to the problem information.

  To do so, on the Problem Details page, click the Edit button that is adjacent to the Bug# label. This is for your reference only.

• Adding comments to the problem activity log.

  You may want to do this to share problem status or history information with other DBAs in your organization. For example you could record the results of your conversations with Oracle Support. To add comments, complete the following steps:

  1. Access the Problem Details page for the problem, as described in "Viewing Problems with the Enterprise Manager Support Workbench".

  2. Click Activity Log to display the Activity Log subpage.

  3. In the Comment field, enter a comment, and then click Add Comment.

     Your comment is recorded in the activity log.

• As new incidents occur, adding them to the package and reuploading.

  For this activity, you must use the custom packaging method described in "Creating, Editing, and Uploading Custom Incident Packages".

• Running health checks.

  See "Running Health Checks with Health Monitor".

• Running a suggested Oracle advisor to implement repairs.

  Access the suggested advisor in one of the following ways:

  • Problem Details page—In the Self-Service tab of the Investigate and Resolve section

  • Support Workbench home page—on the Checker Findings subpage

  • Incident Details page—on the Checker Findings subpage

  Table 9-5 lists the advisors that help repair critical errors.

  Table 9-5 Oracle Advisors that Help Repair Critical Errors

  Advisor	Critical Errors Addressed	See
  Data Recovery Advisor	Corrupted blocks, corrupted or missing files, and other data failures	"Repairing Data Corruptions with the Data Recovery Advisor"
  SQL Repair Advisor	SQL statement failures	"Repairing SQL Failures with the SQL Repair Advisor"

  
  

Task 7: Close Incidents

When a particular incident is no longer of interest, you can close it. By default, closed incidents are not displayed on the Problem Details page.

All incidents, whether closed or not, are purged after 30 days. You can disable purging for an incident on the Incident Details page.

To close incidents:

1. Access the Support Workbench home page.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. Select the desired problem, and then click View.

   The Problem Details page appears.

3. Select the incidents to close and then click Close.

   A confirmation page appears.

4. Enter an optional comment and click OK.

About Health Monitor

Beginning with Release 11g, Oracle Database includes a framework called Health Monitor for running diagnostic checks on the database.

Running Health Checks Manually

Health Monitor provides two ways to run health checks manually:

• By using the DBMS_HM PL/SQL package

• By using the Enterprise Manager interface, found on the Checkers subpage of the Advisor Central page

BEGIN
  DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
END;
/

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

SELECT c.name check_name, p.name parameter_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.internal_check = 'N'
ORDER BY c.name;

BEGIN
  DBMS_HM.RUN_CHECK (
   check_name   => 'Transaction Integrity Check',
   run_name     => 'my_run',
   input_params => 'TXN_ID=7.33.2');
END;
/

Viewing Checker Reports

After a checker has run, you can view a report of its execution. The report contains findings, recommendations, and other information. You can view reports using Enterprise Manager, the ADRCI utility, or the DBMS_HM PL/SQL package. The following table indicates the report formats available with each viewing method.

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

• Viewing Reports Using DBMS_HM

• Viewing Reports Using the ADRCI Utility

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')
-----------------------------------------------------------------------
 
 Run Name                     : HM_RUN_1061
 Run Id                       : 1061
 Check Name                   : Data Block Integrity Check
 Mode                         : REACTIVE
 Status                       : COMPLETED
 Start Time                   : 2007-05-12 22:11:02.032292 -07:00
 End Time                     : 2007-05-12 22:11:20.835135 -07:00
 Error Encountered            : 0
 Source Incident Id           : 7418
 Number of Incidents Created  : 0
 
Input Paramters for the Run
 BLC_DF_NUM=1
 BLC_BL_NUM=64349
 
Run Findings And Recommendations
 Finding
 Finding Name  : Media Block Corruption
 Finding ID    : 1065
 Type          : FAILURE
 Status        : OPEN
 Priority      : HIGH
 Message       : Block 64349 in datafile 1:
               '/u01/app/oracle/dbs/t_db1.f' is media corrupt
 Message       : Object BMRTEST1 owned by SYS might be unavailable
 Finding
 Finding Name  : Media Block Corruption
 Finding ID    : 1071
 Type          : FAILURE
 Status        : OPEN
 Priority      : HIGH
 Message       : Block 64351 in datafile 1:
               '/u01/app/oracle/dbs/t_db1.f' is media corrupt
 Message       : Object BMRTEST2 owned by SYS might be unavailable

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$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION.

The following example queries the V$HM_RUN view to determine a history of checker runs:

SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

    RUN_ID NAME         CHECK_NAME                         RUN_MODE SRC_INCIDENT
---------- ------------ ---------------------------------- -------- ------------
         1 HM_RUN_1     DB Structure Integrity Check       REACTIVE            0
       101 HM_RUN_101   Transaction Integrity Check        REACTIVE         6073
       121 TXNCHK       Transaction Integrity Check        MANUAL              0
       181 HMR_tab$     Dictionary Integrity Check         MANUAL              0
          .
          .
          .
       981 Proct_ts$    Dictionary Integrity Check         MANUAL              0
      1041 HM_RUN_1041  DB Structure Integrity Check       REACTIVE            0
      1061 HM_RUN_1061  Data Block Integrity Check         REACTIVE         7418

The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 1061:

SELECT type, description FROM v$hm_finding WHERE run_id = 1061;

TYPE          DESCRIPTION
------------- -----------------------------------------
FAILURE       Block 64349 in datafile 1: '/u01/app/orac
              le/dbs/t_db1.f' is media corrupt
 
FAILURE       Block 64351 in datafile 1: '/u01/app/orac
              le/dbs/t_db1.f' is media corrupt

Health Check Parameters Reference

The following tables describe the parameters for those health checks that require them. Parameters with a default value of (none) are mandatory.

About the SQL Repair Advisor

You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

Running the SQL Repair Advisor

You run the SQL Repair Advisor from the Problem Details page of the Support Workbench. The instructions in this section assume that you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. In the Investigate and Resolve section, under the Self Service tab, under the Resolve heading, click SQL Repair Advisor.

   
   Description of the illustration advisor_access1.gif
   
   

3. On the SQL Repair Advisor page, complete these steps:

   1. Modify the preset task name if desired, optionally enter a task description, modify or clear the optional time limit for the advisor task, and adjust settings to schedule the advisor to run either immediately or at a future date and time.

   2. Click Submit.

   A "Processing" page appears. After a short delay, the SQL Repair Results page appears.

   
   Description of the illustration sql_repair_advisor_results.gif
   
   

   A check mark in the SQL Patch column indicates that a recommendation is present. The absence of a check mark in this column means that the SQL Repair Advisor was unable to devise a patch for the SQL statement.

   Note:

   If the SQL Repair Results page fails to appear, then complete these steps to display it:

   1. Go to the Database Home page.

   2. Under Related Links, click Advisor Central.

   3. On the Advisor Central page, in the Results list, locate the most recent entry for the SQL Repair Advisor.

   4. Select the entry and click View Result.

4. If a recommendation is present (there is a check mark in the SQL Patch column), click View to view the recommendation.

   The Repair Recommendations page appears, showing the recommended patch for the statement.

5. Click Implement.

   The SQL Repair Results page returns, showing a confirmation message.

6. (Optional) Click Verify using SQL Worksheet to run the statement in the SQL worksheet and verify that the patch successfully repaired the statement.

Viewing, Disabling, or Removing a SQL Patch

After you apply a SQL patch with the SQL Repair Advisor, you may want to view it to confirm its presence, disable it, or remove it. One reason to remove a patch is if you install a later release of Oracle Database that fixes the bug that caused the failure in the patched SQL statement.

To view, disable, or remove a SQL patch:

1. Access the Database Home page in Enterprise Manager.

   For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.

2. At the top of the page, click Server to display the Server page.

3. In the Query Optimizer section, click SQL Plan Control.

   The SQL Plan Control page appears. See the online help for information about this page.

4. At the top of the page, click SQL Patch to display the SQL Patch subpage.

   The SQL Patch subpage displays all SQL patches in the database.

5. Locate the desired patch by examining the associated SQL text.

   Click the SQL text to view the complete text of the statement.

6. To disable the patch, select it, and then click Disable.

   A confirmation message appears, and the patch status changes to DISABLED. You can later reenable the patch by selecting it and clicking Enable.

7. To remove the patch, select it, and then click Drop.

   A confirmation message appears.

About Incident Packages

For the customized approach to uploading diagnostic data to Oracle Support, you first collect the data into an intermediate logical structure called an incident package (package). A package is a collection of metadata that is stored in the Automatic Diagnostic Repository (ADR) and that points to diagnostic data files and other files both in and out of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems. Because a problem can have many incidents (many occurrences of the same problem), by default only the first three and last three incidents for each problem are added to the package, excluding any incidents that are over 90 days old. You can change these default numbers on the Incident Packaging Configuration page of the Support Workbench.

After the package is created, you can add any type of external file to the package, remove selected files from the package, or edit selected files in the package to remove sensitive data. As you add and remove package contents, only the package metadata is modified.

When you are ready to upload the diagnostic data to Oracle Support, you first create a zip file that contains all the files referenced by the package metadata. You then upload the zip file through Oracle Configuration Manager.

More information about packages is presented in the following sections:

• About Correlated Diagnostic Data in Incident Packages

• About Quick Packaging and Custom Packaging

• About Correlated Packages

Packaging and Uploading Problems with Custom Packaging

You use Enterprise Manager Support Workbench (Support Workbench) to create and upload custom incident packages (packages). Before uploading, you can manually add, edit, and remove diagnostic data files from the package.

To package and upload problems with custom packaging:

1. Access the Support Workbench home page.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. (Optional) For each problem that you want to include in the package, indicate the service request number (SR#) associated with the problem, if any. To do so, complete the following steps for each problem:

   1. In the Problems subpage at the bottom of the Support Workbench home page, select the problem, and then click View.

      Note:

      If you do not see the desired problem in the list of problems, or if there are too many problems to scroll through, select a time period from the View list and click Go. You can then select the desired problem and click View.

      The Problem Details page appears.

   2. Next to the SR# label, click Edit, enter a service request number, and then click OK.

      The service request number is displayed on the Problem Details page.

   3. Return to the Support Workbench home page by clicking Support Workbench in the locator links at the top of the page.

      
      Description of the illustration problem_details_breadcrumb.gif
      
      

3. On the Support Workbench home page, select the problems that you want to package, and then click Package.

   The Select Packaging Mode page appears.

   Note:

   The packaging process may automatically select additional correlated problems to add to the package. An example of a correlated problem is one that occurs within a few minutes of the selected problem. See "About Correlated Diagnostic Data in Incident Packages" for more information.

4. Select the Custom packaging option, and then click Continue.

   The Select Package page appears.

   Figure 9-6 Select Package Page

   
   Description of "Figure 9-6 Select Package Page"
   
   

5. Do one of the following:

   • To create a new package, select the Create new package option, enter a package name and description, and then click OK.

   • To add the selected problems to an existing package, select the Select from existing packages option, select the package to update, and then click OK.

   The Customize Package page appears. It displays the problems and incidents that are contained in the package, plus a selection of packaging tasks to choose from. You run these tasks against the new package or the updated existing package.

   Figure 9-7 Customize Package Page

   
   Description of "Figure 9-7 Customize Package Page"
   
   

6. (Optional) In the Packaging Tasks section, click links to perform one or more packaging tasks. Or, use other controls on the Customize Package page and its subpages to manipulate the package. Return to the Customize Package page when you are finished.

   See "Viewing and Modifying Incident Packages" for instructions for some of the most common packaging tasks.

7. In the Packaging Tasks section of the Customize Package page, under the heading Send to Oracle Support, click Finish Contents Preparation to finalize the package.

   A list (or partial list) of files included in the package is displayed. (This may take a while.) The list includes files that were determined to contain correlated diagnostic information and added by the finalization process.

   See "About Correlated Diagnostic Data in Incident Packages" for a definition of package finalization.

8. Click the Files link to view all the files in the package. Examine the list to see if there are any files that might contain sensitive data that you do not want to expose. If you find such files, exclude (remove) or edit them.

   See "Editing Incident Package Files (Copying Out and In)" and "Removing Incident Package Files" for instructions for editing and removing files.

   To view the contents of a file, click the eyeglasses icon in the rightmost column in the table of files. Enter host credentials, if prompted.

   Note:

   Trace files are generally for Oracle internal use only.

9. Click Generate Upload File.

   The Generate Upload File page appears.

10. Select the Full or Incremental option to generate a full package zip file or an incremental package zip file.

    For a full package zip file, all the contents of the package (original contents and all correlated data) are always added to the zip file.

    For an incremental package zip file, only the diagnostic information that is new or modified since the last time that you created a zip file for the same package is added to the zip file. For example, if trace information was appended to a trace file since that file was last included in the generated physical file for a package, the trace file is added to the incremental package zip file. Conversely, if no changes were made to a trace file since it was last uploaded for a package, that trace file is not included in the incremental package zip file.

    Note:

    The Incremental option is dimmed (unavailable) if an upload file was never created for the package.

11. Schedule file creation either immediately or at a future date and time (select Immediately or Later), and then click Submit.

    File creation can use significant system resources, so it may be advisable to schedule it for a period of low system usage.

    A Processing page appears, and creation of the zip file proceeds. A confirmation page appears when processing is complete.

    Note:

    The package is automatically finalized when the zip file is created.

12. Click OK.

    The Customize Package page returns.

13. Click Send to Oracle.

    The View/Send Upload Files page appears.

14. (Optional) Click the Send Correlated Packages link to create correlated packages and send them to Oracle.

    See "Creating, Editing, and Uploading Correlated Packages". When you are finished working with correlated packages, return to the View/Send Upload Files page by clicking the Package Details link at the top of the page, clicking Customize Package, and then clicking Send to Oracle again.

15. Select the zip files to upload, and then click Send to Oracle.

    The Send to Oracle page appears. The selected zip files are listed in a table.

16. Fill in the requested My Oracle Support information. Next to Create new Service Request (SR), select Yes or No. If you select Yes, a draft service request is created for you. You must later log in to My Oracle Support and fill in the service request details. If you select No, enter an existing service request number.

17. Schedule the upload to take place immediately or at a future date and time, and then click Submit.

    A Processing page appears. If the upload is completed successfully, a confirmation page appears. If the upload could not complete, an error page appears. The error page may include a message that requests that you upload the zip file to Oracle manually. If so, contact your Oracle Support representative for instructions.

18. Click OK.

    The View/Send Upload Files page returns. Under the Time Sent column, check the status of the files that you attempted to upload.

    Note:

    The Support Workbench uses Oracle Configuration Manager to upload the physical files. If Oracle Configuration Manager is not installed or properly configured, the upload may fail. In this case, a message is displayed with a path to the package zip file and a request that you upload the file to Oracle Support manually. You can upload manually with My Oracle Support.

    For more information about Oracle Configuration Manager, see Oracle Configuration Manager Installation and Administration Guide.

19. (Optional) Create and upload correlated packages.

    See "Creating, Editing, and Uploading Correlated Packages" for instructions.

Viewing and Modifying Incident Packages

After creating an incident package with the custom packaging method, you can view or modify the contents of the package before uploading the package to Oracle Support. In addition, after using the quick packaging method to package and upload diagnostic data, you can view or modify the contents of the package that the Support Workbench created, and then reupload the package. To modify a package, you choose from among a selection of packaging tasks, most of which are available from the Customize Package page. (See Figure 9-7.)

This section provides instructions for some of the most common packaging tasks. It includes the following topics:

• Editing Incident Package Files (Copying Out and In)

• Adding an External File to an Incident Package

• Removing Incident Package Files

• Viewing and Updating the Incident Package Activity Log

Also included are the following topics, which explains how to view package details and how to access the Customize Package page for a particular package:

• Viewing Package Details

• Accessing the Customize Package Page

Creating, Editing, and Uploading Correlated Packages

After you upload a package to Oracle Support, you can create and upload one or more correlated packages. This is recommended if critical alerts appeared in the Related Alerts section of the Database Home page. The correlated packages are associated with the original package, also known as the main package. The main package contains problems that occurred in a database instance. Correlated packages contain problems that occurred on other instances (Oracle ASM instances or other database instances) and that are related problems for the problems in the main package. There can be only one correlated package for each related instance.

To create, edit, and upload a correlated package:

1. View the Package Details page for the main package.

   See "Viewing Package Details" for instructions.

2. On the Package Details page, click Customize Package.

3. On the Customize Package page, in the Packaging Tasks section, under Additional Diagnostic Data, click Create/Update Correlated Packages.

   See Figure 9-7.

4. On the Correlated Packages page, under Correlated Packages, select one or more instances that have incidents and click Create.

   A confirmation message appears, and the package IDs of the newly created correlated packages appear in the ID column.

5. Select the instance on which you created the correlated package, and click Finish Contents Preparation.

   A confirmation message appears.

6. (Optional) View and edit a correlated package by completing these steps:

   1. Click the package ID to view the package.

      If prompted for credentials, enter them and click Login.

   2. On the Package Details page, click Files to view the files in the package.

   3. Click Customize Package and perform any desired customization tasks, as described in "Viewing and Modifying Incident Packages".

7. For each correlated package to upload, click Generate Upload File.

8. For each correlated package to send to Oracle, select the package and click Send to Oracle.

   Note:

   If Send to Oracle is unavailable (dimmed), there were no correlated incidents for the instance.

Deleting Correlated Packages

You delete a correlated package with the Enterprise Manager Support Workbench for the target for which you created the package. For example, if you created a correlated package for an Oracle ASM instance target, access the Support Workbench for that Oracle ASM instance.

To delete a correlated package:

1. Access the Support Workbench for the target on which you created the correlated package.

   Tip:

   See the Related Links section at the bottom of any Support Workbench page. Or, see "Viewing Problems with the Enterprise Manager Support Workbench"

2. Click Packages to view the Packages subpage.

3. Locate the correlated package in the list. Verify that it is a correlated package by viewing the package description.

4. Select the package and click Delete.

5. On the confirmation page, click Yes.

Setting Incident Packaging Preferences

This section provides instructions for setting incident packaging preferences. Examples of incident packaging preferences include the number of days to retain incident information, and the number of leading and trailing incidents to include in a package for each problem. (By default, if a problem has many incidents, only the first three and last three incidents are packaged.) You can change these and other incident packaging preferences with Enterprise Manager or with the ADRCI utility.

To set incident packaging preferences with Enterprise Manager:

1. Access the Support Workbench home page.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. In the Related Links section at the bottom of the page, click Incident Packaging Configuration.

   The View Incident Packaging Configuration page appears. Click Help to view descriptions of the settings on this page.

3. Click Edit.

   The Edit Incident Packaging Configuration page appears.

4. Edit settings, and then click OK to apply changes.