Analyzing Data Dictionary Corruption Using Support Workbench

Purpose

This tutorial describes how you analyze a data dictionary corruption using Support Workbench.

Time to Complete

Approximately 30 minutes

Topics

This tutorial discusses the following:

 Overview
 Prerequisites
 Setting Up a Data Dictionary Corruption
 Creating a Dictionary Integrity Check
 Reviewing the Active Incident
 Creating a Package of the Problem
 Cleanup
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

The Enterprise Manager Support Workbench (Support Workbench) is a facility that enables you to investigate, report, and in some cases, repair problems (critical errors), all with an easy-to-use graphical interface. The Support Workbench provides a self-service means for you to gather first-failure diagnostic data, obtain a support request number, and upload diagnostic data to Oracle Support with a minimum of effort and in a very short time, thereby reducing time-to-resolution for problems. The Support Workbench also recommends and provides easy access to Oracle advisors that help you repair SQL-related problems, data corruption problems, and more.

Back to Topic List

Prerequisites

Before starting this tutorial, you should first complete the following steps:

1.

Install Oracle Database 11g.

2.

Download and unzip the swkbnch.zip files into your working directory.

Back to Topic List

Setting Up a Data Dictionary Corruption

In this tutorial, you analyze a data dictionary corruption and then use support workbench to analyze the problem and then package it to sent to Oracle support. In this section, you setup the problem. Perform the following steps:

1.

Open a terminal window. From the directory you unzipped the files to, execute the following command to create a data dictionary corruption

                               
./corr_setup.sh
                            

Move your mouse over this icon to see the image

 

Back to Topic List

Creating a Dictionary Integrity Check

To begin analyzing the problem, you can trigger a health check on the data dictionary by creating a dictionary integrity check. Perform the following steps:

1.

Open a browser and enter the following URL:

                               
http://<hostname>:1158/em
                            

Enter sys as the username, specify the password oracle, select SYSDBA for Connect As and then click Login.

Move your mouse over this icon to see the image

 

2.

From the home page, scroll down and under Related Links, select Advisor Central.

Move your mouse over this icon to see the image

 

3.

Select the Checkers tab.

Move your mouse over this icon to see the image

 

4.

Click Dictionary Integrity Check.

Move your mouse over this icon to see the image

 

5.

Enter DicoCheck1 for the Value of the Parameter Run Name and click OK.

Move your mouse over this icon to see the image

 

6.

The check ran successfully. To view the details, select the DicoCheck1 Run from the list and click Details.

Move your mouse over this icon to see the image

 

7.

You can see some data corruption. In particular, you see the following finding: "SQL dictionary health check: invalid column number 9 on object TAB$ failed". This corresponds to a row in the TAB$ that references HR.OBETABLE. Click the Database breadcrumb.

Move your mouse over this icon to see the image

 

Back to Topic List

Reviewing the Active Incident

In this section, you investigate the problem further. Perform the following steps:

1.

From the home page, review the Alerts section. You see that you have a new critical alert (Data Failure) for the previously detected corruption.

Move your mouse over this icon to see the image

 

2.

Switch to your terminal window and executed the following commands:

                                 
sqlplus / as sysdba
alter system flush shared_pool;
alter system flush buffer_cache;
select * from hr.obetable;
                              

Move your mouse over this icon to see the image

Notice that you receive a end-of-file on communication channel error. This means that something is definitely wrong. You will now investigate the problem.

 

3.

Switch back to Enterprise Manager and notice that you have an Active Incident in the Diagnostic Summary section of the Home page. Select the number link.

Move your mouse over this icon to see the image

 

4.

On the Support Workbench Problems page, you should see a new Active Incident whose Description is similar to ORA 7445 [qcstda()+515]. Select the + to expand the details for the problem.

Move your mouse over this icon to see the image

 

5.

Click the incident number.

Move your mouse over this icon to see the image

 

6.

The Incident Details page appears. Make note of the incident number. In the Application Information section, you see that the statement for SQL Text ' select * from hr.obetable' is what caused the incident. You also see two dump files generated.

Move your mouse over this icon to see the image

 

Back to Topic List

Creating a Package of the Problem

If the problem is something you can not fix, you need to notify Oracle Support of the problem. Oracle Support wants to gather as much detail as possible to figure out what the problem is so they can report it effectively to development. To do this, you can package your problem so that it can be sent to Oracle Support. Perform the following steps:

1.

On the Problem Details page, In the Investigate and Resolve section, select Quick Package.

Move your mouse over this icon to see the image

 

2.

On the Create New Package window, select No for Send to Oracle Support.

Move your mouse over this icon to see the image

 

3.

When the page is refreshed, click Next.

Move your mouse over this icon to see the image

 

4.

The incidents to be packaged are listed, click Next.

Move your mouse over this icon to see the image

 

5.

The manifest details are shown. Click Next.

Move your mouse over this icon to see the image

 

6.

Make sure Immediately is selected and click Submit.

Move your mouse over this icon to see the image

 

7.

The package is being generated.

Move your mouse over this icon to see the image

 

8.

The package file has been successfully generated. Click OK.

Move your mouse over this icon to see the image

 

9.

To view the package, in the Summary section, click Yes under Packaged.

Move your mouse over this icon to see the image

 

10.

Select the file that was generated and click View.

Move your mouse over this icon to see the image

 

11.

On the Packaging Details page, select the Files tab.

Move your mouse over this icon to see the image

 

12.

The list of files included in the package are displayed . This package can now be sent to Oracle Support for further analysis.

Move your mouse over this icon to see the image

 

Back to Topic List

Cleanup

After analying the problem you realize that the number of columns for OBETABLE is wrong in TAB$. To fix the issue, you need to update the TAB$ and close your problem. Perform the following steps:

1.

Switch back to your terminal window and execute the following script:

                               
./corr_fix.sh
                            

Move your mouse over this icon to see the image

 

2.

Switch to Enterprise Manager and select the link next to Problems in Package.

                                 
                                   Move your mouse over this icon to see the image
                                
                              

 

3.

Select the checkbox in front of the problem and click Close.

Move your mouse over this icon to see the image

 

4.

To confirm, click Yes.

Move your mouse over this icon to see the image

 

5.

The incidents have been closed.

Move your mouse over this icon to see the image

 

Back to Topic List

Summary

In this tutorial, you've learned how to:

 Create a Dictionary Integrity Check
 Review and investigate an active incident
 Create a package of a problem

Back to Topic List

 Move your mouse over this icon to hide all screenshot

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document