Analyzing Data Dictionary Corruption Using Support Workbench
Analyzing Data Dictionary Corruption Using Support Workbench
This tutorial describes how you analyze a data dictionary corruption using Support Workbench.
Approximately 30 minutes
This tutorial discusses the following:
 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.
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
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
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
|
Back to Topic List
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.
|
| 2. |
From the home page, scroll down and under Related Links, select Advisor Central.
|
|
3. |
Select the Checkers tab.
|
| 4. |
Click Dictionary Integrity Check.
|
|
5. |
Enter DicoCheck1 for the Value of the Parameter Run Name and click OK.
|
| 6. |
The check ran successfully. To view the details, select the DicoCheck1 Run from the list and click Details.
|
|
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.
|
Back to Topic List
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.
|
| 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;
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.
|
| 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.
|
| 5. |
Click the incident number.
|
| 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.
|
Back to Topic List
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.
|
| 2. |
On the Create New Package window, select No for Send to Oracle Support.
|
| 3. |
When the page is refreshed, click Next.
|
| 4. |
The incidents to be packaged are listed, click Next.
|
| 5. |
The manifest details are shown. Click Next.
|
| 6. |
Make sure Immediately is selected and click Submit.
|
| 7. |
The package is being generated.
|
| 8. |
The package file has been successfully generated. Click OK.
|
| 9. |
To view the package, in the Summary section, click Yes under Packaged.
|
| 10. |
Select the file that was generated and click View.
|
| 11. |
On the Packaging Details page, select the Files tab.
|
| 12. |
The list of files included in the package are displayed. This package can now be sent to Oracle Support for further analysis.
|
Back to Topic List
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
|
| 2. |
Switch to Enterprise Manager and select the link next to Problems in Package.
|
| 3. |
Select the checkbox in front of the problem and click Close.
|
| 4. |
To confirm, click Yes.
|
| 5. |
The incidents have been closed.
|
Back to Topic List
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
|
Bookmark