Investigating and Resolving Problems - Oracle 11g

Although you can access all the diagnostic data stored in the ADR through the Support Work bench facility in Database Control, Oracle also provides a powerful new command-line tool called the adrci (ADR Control Interface) to manage ADR data.You can use the adrci tool along with two new Oracle-supplied packages,DBMS_HM and DBMS_SQLDIAG,to investigate and report problems,as well as to fix the problems where possible.

The adrci tool is a command-line alternative to the Support Workbench and offers additional functionality such as the ability to list and query trace files.In the following two sections,we first discuss using the adrci tool and then examine problem resolution through the Support Work bench.

Using the Command-Line Tool adrci

Oracle provides a new command-line tool called adrci to help you mine the diagnostic data stored in the ADR.Besides letting you view the diagnostic data that the database stores in the ADR, adrci provides another and much more important function—it lets you package incident and problem information into ZIP files for sending to Oracle Support.This diagnostic data can include familiar data such as trace and dump files,alert log entries,and the new Oracle Data base 11g Health Monitor reports.

You can use adrci in interactive mode or use adrci commands inside operating system scripts for batch execution.Next,we’ll briefly review how to use adrci in both modes.

Using adrci in the Command-Line Mode

To log into the adrci and issue commands from there,just type adrci at the command line after logging in as the owner of the Oracle soft ware installation:

Once you see the adrci prompt,you can start entering adrci commands.Note that in the interactive mode,you can enter each command separately at the adrci command line,or you can execute scripts consisting of adrci commands.To view a list of all adrci commands,type help at the adrci prompt or just type adrci-help at the command line, as shown in the following example:

Using adrci in Scripts and in Batch Mode

In addition to using adrci interactively, you can also use adrci commands within scripts and operating system batch files.To incorporate a group of adrci commands into a script,simply create a file with all the adrci commands you want to execute, and then execute the script at the adrci prompt.

To use adrci commands in a shell script or a Windows batch file, you simply invoke adrci using the command-line parameter exec to which you pass the adrci commands you want to execute.That is, you must use the following syntax in your batch file when invoking adrci:

adrci exec="command[; command]..."

For example, if you want to use the two adrci commands show homes and show incident in a script, use the following syntax:

adrci exec="show homes; show incident"

Note that each of the commands must be separated by a semicolon (;). Here’s an example showing how to use the ADR command-line interface to do the equivalent of the usual vi alert$ORACLE_SID.log command, which lets you read the alert log in a text editor:

adrci exec="set homepath diag/rdbms/orcl/orcl1; set editor vi; show alert -V"

Alternatively, you can pipe adrci commands to a shell script, as shown in the following script example, which lets you see all the alert log entries that contain the word ORA-,which indicates an Oracle-related error:

You can use the entire set of adrci commands in batch mode.

Setting the Home path

All adrci commands you issue will run under your ADR root directory,which is the ADR base.Issue the show base command to see the location of the ADR base:

When you issue any adrci command, it’s in the ADR home defined in the “current” ADR homepath that Oracle searches for diagnostic data.If you don’t set the ADR homepath explicitly, all ADR homes under the ADR base will be current.

ADR commands always operate on the diagnostic data in the current ADR home.So if you issue a command such as show tracefile,for example,adrci will show you all the trace files in each of the ADR homes that are current.The show homes command shows all available ADR homes:

Although the show homes command shows three different ADR homes for the three different Oracle instances,the ADR homepath itself isn’t set,as shown by executing the show homepath command:

adrci>show homepath

It’s important to understand that if you don’t set any ADR homepath,all instance homes under the ADR base are considered to be active or current homes In our case,all three ADR homes under the ADR base are “ current” by default.If you want adrci commands to apply to only a single ADR home,you can do so by using the set home path command,which lets you change the current home path:

You can set the ADR homepath to multiple ADR homes if you want.When you do this,those adrci commands that can simultaneously work with multiple ADR homes (for example,show incident and show alert)will search the diagnostic data under all the ADR homes defined in the current ADR path.You can issue all other adrci commands only if a single ADR home is current.

If you issue such a command when multiple ADR homes are current,adrci will issue an error.For example,an attempt to create a package with the ips command (the ips command creates an incident package when you have multiple home paths set will lead to this error:

adrci>ips create package;
DIA-48448: This command does not support multiple ADR homes

Obviously,you must set a single ADR home path by using the set home path command before you can create an incident package with the ips command.

Viewing the List of Commands

You can see what adrci commands are available by typing the command help at the adrci command line:


If you want to see what adrci command-line options are available,type adrci -help at the command line.You can get additional information for a singlecommand by typing the command name after the keyword help.The following command,for example, lets you find out the syntax and usage details for the show incident command:

Purpose:Show the incident information. By default, this command will only show the last 50 incidents which are not flood controlled.


In the following sections,we highlight some of the key tasks you can perform with the adrci tool.

Viewing the Alert Log

In Oracle Database 11g,the alert log is stored as both a text file and as an XML-formatted file.You can use adrci to view the XML-for mattted alert log without the XML tags.Just type show alert at the adrci prompt to view the contents of the alert log:

adrci> show alert

Choose the alert log from the following homes to view:

1:diag/tnslsnr/localhost/listener 2:diag/rdbms/oracle11/oracle11 3:diag/rdbms/orcl11/orcl11 4:diag/rdbms/auxdb/auxdb 5:diag/rdbms/eleven/eleven 6:diag/rdbms/nina/nina Q:to quit

Please select option: 3

Output the results to file:/tmp/alert_15987_3086_orcl11_1.ado Starting ORACLE instance (normal) 2007-02-15 11:42:14.307000 -05:0 LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Shared memory segment forinstance monitoring created Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. . . . adrci>Viewing the Alert Log

In the example shown here,we’ve provided several options for the alert log file,because we haven’t set a homepath before issuing the show alert command.Once you set the homepath for a specific data base instance,the show alert command will act differently,as shown here:

adrci>set homepath diag/rdbms/orcl11/orcl11
adrci>show alert

The command will show the complete alert log (stripped of the XML tags).In addition,adrci outputs the complete contents of the current alert log file to the/tmp directory,as shown in both examples of the show alert command.You can use the show alert command with the-tail option to see only the last 20 to 30 messages of the alert log,as shown here:

adrci> show alert –tail

If you want to see the last 100 messages in the alert log,you can issue the command show alert -tail 100.The alert -tailcommands are similar to the tail commands you use in viewing Unix and Linux textfiles in that they let you actively monitor the additions to a file.You can see whether there are any ORA-600 errors in the alert log by issuing the following command:

adrci>show alert-P "MESSAGE TEXT LIKE '%ORA-600%'*

If you want to capture the contents of the alert log to a different file without the XML tags,you can do so by using the spool command before issuing the show alert command,as shown here:

We don’t want to bore you with the many ways to look at your beloved alert log in Oracle Database 11g,but we’ll be remiss if we don’t mention that you can still directly access a textonly alert log as in the old days by simply looking up the path for the Diag Trace entry in a query (select *) of the V$DIAG_INFO view.

For example,in our case, the Diag Trace entry has the path/u01/app/

oracle/diag/rdbms/orcl11/orcl11/trace.From here,you can open the alert log in a text editor and view it to your heart’s content

This is the textual format alert log, similar to what you used in the older releases of the database.This means that Oracle now generates two alert logs for the instance,one in text format and the other in XML format.

Listing Trace Files

You can get a listing of all available trace files in the current ADR homepath by using the show trace file command:adrci>show tracefile The show tracefile command lists all the available trace files.

Viewing Incidents

Use the show incident command to view all open incidents.The report will show you the incident ID,problem time,and creation time for each incident encountered by the data base and logged in the ADR.If you have multiple current adrci homes in your adrci homepath,the show incident command will display open incidents from all the instances in the various ADR homes.Here’s an example showing the output of the show incident command with multiple current ADR homes:

The show incident command reveals that for the first data base (auxdb)there aren’t any open incidents.For the second database(eleven) there are two open incidents,and for the third data base(orcl11)there are three open incidents.If you want to drill down to a particular incident,you can do so by using the show incident command with the -p option,as shown here(only the last part of the output is shown):.

The show incident command has two options:predicate string (-p)and mode (-mode).You can use the predicate string to specify various field names,such as the field incident_id in this example.You must enclose the field names with a pair of double quotes.for a list of all the field names you can use in a predicate string,type describe incident at the adrci command line,as shown here:

In addition to the predicate string(-p)option, we also used the -modeoption(detail) in this example to specify that the output be shown indetail. the other settings for the-mode option are basic and brief, both of which provide shorter displays of the output.

Packaging Incidents

One of the most useful new features of Oracle Database 11g is the new framework for packaging incidents for easy transmission of diagnostic data to Oracle Support.An incident package represents at least one problem.

When you create an incident package,you add one or more problems to the package,and the Support Workbench will then automatically add relevant trace files and dump files for those problems.By default,each incident package includes only the first and last three incidents per each problem.

The incident package contains all diagnostic data pertaining to a particular incident,or it can range over a period of time,with data for all incidents during that time interval.

Once you create an incident package,you can add or remove files from the incident package.You can also edit the external files that are part of the package to delete sensitive data.

To create a self-contained incident package with all supporting diagnostic data for an incident(or a group of incidents or a time interval), you must follow these steps:

  1. Create a logical package: You must first create a logical package, so named since it doesn’t exist in the form of a separate file you can actually send some where—it merely exists as meta data in the ADR.You may create an empty logical package with no diagnostic data,or you can create an incident number or problem number or time interval– based logical package,which will automatically contain the diagnostic data for the relevant problems/incidents.You use the command ips create package to create a logical package.There are many variations on this command.You can choose to create a package based on a particular problem number,incident number,problem key,or time period.
  2. Add diagnostic data to the logical package . This is an optional step,which applies only if you created an empty logical package in step 1.The problem and incident-based logical packages already contain the relevant diagnostic data.
  3. Generate the physical package (zipped file):In the final step,you generate the actual zipped physical file,ready for sending to Oracle Support.You can always add extra information to the ZIP file by creating incremental ZIP files for the same logical package.Here’s an example showing a complete(COM) ZIP file,with an associated incremental (INC) ZIP file for the same logical package:

Once you’re ready to upload the package to Oracle Support, you use the Support Workbench or the adrci utility to collect all files that the incident package’s metadata refers to and package them in a ZIP file before uploading it to Oracle Support.We’ll first show how to create incident packages with the adrci utility.

You use special adrci commands called IPS commands to manage packages in adrci.Here are the steps to package an incident so you can send it for analysis to the folks at Oracle Support:

  1. Use the ips create package command to create a new logical package.In this example,we show how to create an empty logical package,which means you don’t have to specify an incident or a time interval:

adrci>ips create package
Created package 1 without any contents,correlation level typical adrci>

Note that the usage of a semicolon to end the adrci commands is optional.The packages you create are serially numbered,starting with 1.In this example,the ips create package command results in the creation of package 1,since this is the first package ever created under this ADR base.

  1. Since you created an empty logical package in step 1,you must add diagnostic information to that package.You can add incidents and files to the logical package.The following example shows how to add a particular incident to your logical package:adrci>ips add incident 113769 package 1;

At this stage,your package still doesn’t have any actual diagnostic data.All it has is the metadata for the incident that you want Oracle Support to diagnose.In the next step, you create the actual physical package that you’ll send to Oracle Support.

  1. Generate the physical package by issuing the ips generate package command.When you issue this command,adrci collects all the required diagnostic files and packages them into a ZIP file:

The ips generate command shown here creates a ZIP file in the /u01/app/ oracle/adrci directory,which you can then send to the Oracle Support for diagnostic support.The file you create this way is called a complete ZIP file.If you want to add or change any diagnostic data later,you can do so by generating an incremental ZIP file.Use the keyword incre mental with the ips generate package command to generate an incremental ZIP file:

adrci> ips generate package 5 in /u01/app/oracle/adrci/support incremental

Here’s an example showing how to name a complete and an incre mental ZIP file,respectively:

In this example,the first file has the COM tag in its file name,indicating that it’s a complete ZIP file.The second file uses the INC tag,meaning it’s an incremental ZIP file.The files are processed in sequential order,with the complete file being processed first,followed by any incremental files,if any exist.

In this example,we showed how to use the ips create package command. Here are the variations of this command:

  • ips create package creates an empty package.
  • ips create package problem creates a package based on a problem ID.
  • ips create package problem key creates a problem key–based package.
  • ips create package incident creates a package based on an incident ID.
  • ips create package time creates a package for a specified time range.

You can configure various aspects such as incident metadata retention period by using the adrci tool.Issue the ips show configuration command to review all the available configuration parameters.Here’s an abbreviated output of the ips show configuration command:

Although you can employ the powerful and easy-to-use adrci tool toinvestigate problems and package and upload the incident packages to Oracle Support,the Support Work bench is the recommended tool to use,especially for simpler problems.

The Support Work bench

The Support Work bench is a new Enterprise Manager–based facility to help you investigate and report critical errors.The Support Work bench lets you gather diagnostic data and easily upload the data to Oracle Support.You can run health checks,invoke the IPS to package relevant diagnostic data for problems,and file and track service requests with Oracle Support.

The Support Work bench also lets you repair several types of problems by providing access to various Oracle advisors,such as the new SQL Repair Advisor and the SQL Test Case Builder.

In the following sections,you’ll learn how to resolve data baseproblems through the Support Work bench.You’ll then look at using the Support Work bench home page for viewing existing problems and creating “user-reported” problems.

Using the Support Workbench Home Page

You can view all current incidents and their descriptions by accessing the Support Work bench home page.Follow these steps to access the Support Work bench home page:

  1. Click the Software and Support link on the Data base home page.
  2. In the Support section,click Support Work bench
  3. In the Support Work bench home page,you can see all problems found in the previous 24 hours.
  4. Click All from the View list to see all problems.

You can also get all details for any incident as well as view the checker findings for that incident using the Support Work bench.

In addition,you can employ the Support Work bench to collect additional diagnostics for any incident.Although critical errors are automatically added to the ADR and consequently tracked by the Support Workbench,you can add a problem to the Support Workbench yourself by clicking the Create User-Reported Problem link on the Support Workbench home page.

User-created problems may be called for in situations where the data base doesn’t issue a critical incident alert but you know that there is a serious performance problem in the data base.Creating a user-reported problem is similar to creating a pseudo-problem within the Support Work bench frame work,allowing you to take advantage of the Work bench workflow for fixing the problem.

Whether it’s a system-generated problem or a user-reported problem, you can use the Support work bench to add diagnostic data for aiding problem diagnosis and upload the diagnostic data to Oracle Support by following the simple investigative and reporting procedure that we explain in the following section.

Using the Support Work bench to Resolve Problems

Although using the adrci tool to investigate problems and create packages in order to resolve those problems is indeed straight forward,Oracle recommends you use the Data base Control– based Support Work bench to take care of your database problems. For simpler problems.the Support Workbench may turn out to be all you need to fix the problem.In cases where you need to capture additionaldiagnostic data or customize the diagnostic data in certain ways before sending it in to Oracle Support,you may fall back on the adrci tool.

Using the Support Work bench to Resolve Problems

You can use the Support Work bench out of the box for all problem resolution actions,except for uploading the diagnostic information to Oracle Support.To be able to up load the zipped incident packages to Oracle Support directly from the Support Work bench,you must first install the Oracle Configuration Manager.

The Oracle Configu ration Manager enables you to link your systemconfiguration information with your Meta Link account.Thus,when you lodge a request with Metalink,you can link your configuration details to that request.

The easiest way to install the Oracle Configuration Manager is while insta lling the Oracle software,where you’re offered a choic to install it along with the other binaries.Figure shows the Oracle Configuration Manager installation screen during the Oracle server installation.

You can also install the Oracle Configuration Manger after theinstallation.Please refer to the Oracle manualOracle Configu ration Manager Installation Guidefor details.If you don’t install theOracle Configuration Manager,you must upload the incident package files your self the old-fashioned way,through Meta Link.

Oracle Configuration Manager registration

Oracle Configuration Manager registration

Here are the steps to follow to resolve a critical error usingthe Support Work bench:

  1. Check for critical error alerts.You’ll find all critical data base error alerts on the Data base home page of Database Control.On that page,examine the table of alerts in the Alerts section.Any alert with aseverity level marked by a red X in the Severity column and the text incidentin the Category column is a critical error alert.Figure shows the alert table with a number of critical errors indicating possible Oracle data block corruption.A count of all active incidents is also shown in the Diagnostic Summary section on the home page.
  2. Investigate the problem.Click the link provided in the Message column in the table of alerts in the Alerts section.This will take you to the Alert Details page,shown in Figure.The Alert Details page provides the problem infor mation and details such as the severity and the time stamp of the problem that raised the alert.You can also display finds from the auto matic health checks run by the data base.You can also add comments in a text box provided for that purpose.At this point,you can choose to run additional health checks to get more findings on the problem.You can also invoke the SQL Test CaseBuilder to gather data for a problem SQL so Oracle Support can reproduce the problem.
  3. The table of alerts on the Database home page

    The table of alerts on the Database home page

    The Alert Details page

    The Alert Details page

  4. Create a service request.Once you decide to ask Oracle Support for help in resolving the problem,you must first create a service request.On the Problem Detail page,click Go to Meta link.Enter your MetaLink credentials,and create a service request,just as you would normally.
  5. The Alert Details page

  6. Create the incident package,and upload data to Oracle Support.Before the Support Work bench creates the physical file for an incident package,it calls the IPS to finalize the incident package.Finalizing involves adding any data such as trace files that are correlated by item or process ID or by similar criteriaAdding diagnostic data that’s correlated to the main incident could be helpful in many cases.You have a choice of two types of packaging—Quick Packaging or Custom Packaging.Quick Packaging is simpler but won’t let you add or edit the diagnostic information you’re about to upload Custom Packaging gives you greater control by letting you edit diagnostic data or add fields before up loading to Oracle Support.In this example,we& rsquo;ll use the Quick Packaging process,so click Quick Package in the Investi gate and Resolve section of the Problem Details page.Enteryour package name and a description.Click Next,and follow the instructions on the remainingpages of the Quick Packaging Wizard.Once you are ready to upload the pac kage, click Submit on the Review page.Of course,this assumes your system is connec ted to the Internet Once you create a custom incident package,you can choose among several packaging tasks available on the Package Details page,such as editing the packaged incident files or adding external files to the incident package.
  7. Track the service request.Once you upload the diag nostic information to Oracle Support,you can revise the Problem Details page to perform additional activities such as adding an Oracle bug number to the problem information,adding comments to the problem activity log,and running Oracle advisors such as the Data Recovery Advisor to help repair critical errors.
  8. Close the problem(incident).Once you resolve a problem, you can close the incident. On the Support Workbench home page, click View in the Problem Details page that appears, select the incident you want to close, and click Close. By default all incidents, both open and closed, are closed after 30 days. You can, if you want, disable this automatic closure of incidents on the Incident Details page.

By accessing the Incident Packaging Configuration page from the Related Links section of the Support Work bench,you can configure various rules for generating packages and incident data retention.

From the Support Work bench,you can run key Oracle advisors such as the Data Recovery Advisor and SQL Repair Advisor to implement repairs.You canaccess these advisors by clicking the Self-Service tab in the Investigate and Resolve section of the Problem Details page.You can also access the advisors by going to the Checkers Findings sub page of the Incident Details page.

The SQL Repair Advisor helps you repair SQL statement failures by applying an Oracle supplied patch as a work around.The Data Recovery Advisor helps fix data failures such as block corruption and missing datafiles.

In the following section,we explain how to use one of the new advisors,the SQL Repair Advisor.We explain the Data Recovery Advisor.

Repairing SQL Failures with the SQL Repair Advisor

The Support Workbench doesn’t serve just as a tool to upload diagnostic data to Oracle Support.You can also launch the new SQL Repair Advisor tool from the Support Work bench.In Oracle Data base 11g,you can run the SQL Repair Advisor to fix a failed SQL statement.

The advisor usually recommends applying a patch to fix the failed state ment.If you accept the recommendation ( s) and apply the SQL patch,the optimizer chooses an alternate execution path,and the SQL code will work successfully,without having to change the original SQL statement at all.

If the SQL Repair Advisor fails to recommend a patch to make the SQL work,you can use the Support Work bench to package the incident files and relevant data and send them to Oracle Support. Here’s a quick summary of the steps to invoke the SQL Repair Advisor from the Support Work bench home page:

  1. Go to the Problem Details page,and click the specific problem message resulting from the failed SQL statement.
  2. Click SQL Repair Advisor in the Investigate and Resolve section on the Self Service tab.
  3. Enter the appropriate options to run the advisor immediately or at a scheduled time later.
  4. Click Submit.
  5. On the SQL Repair Results page that appears,click View to examine the Report Recommendations page.
  6. Once you’re sure you want to implement the recommendations, click Implement.

Once the SQL Repair Results page comes back with a confirmation message,you’re done.When you migrate to a newer release of the Oracle database,you may want to remove any patches installed through the SQL Repair Advisor’s recommendations.You can disable or remove a patch by going to the Database Control home page and navigating to Server -> SQL Plan Control -> SQL Patch -> Disable (or Drop).

You can also use various procedures from the new DBMS_SQLDIAG package to create and execute diagnostics tasks with the SQL Repair Advisor.The SQL Repair Advisor will,in most cases,produce a SQL patch as a work around for the problem SQL query.Here are the steps to create a task and apply and test a SQL patch offered by the SQL Repair Advisor:

  1. Once you identify a problem SQL statement, the first step is to create a diagnosis task using the create_ diagnosis_task procedure:
  2. The next step is to execute the diagnosis task.

  3. Use the task name from the previous step(test_task1) to execute the diag nostic task,which generates the workaround for the problem SQL:SQL>exec dbms_sqlldiag.execute_diagnosis_ task('test_task1'),PL/SQL proceduresuccessfully completed.SQL>
  4. When you execute the diagnostic task,Oracle usually provides a work around to fix the problem,but sometimes there may not be any recommendations.

  5. Access the analysis of the diagnosis task by executing the report_diagnsotic_task procedure,as shown here:

    In cases where the SQL Repair Advisor is able to find a fix for the problem SQL,it recommends a SQL patch. Although a SQL patch is similar to a SQL profile,it’s used only as a wor around to compile a failing SQL statement.

  1. If there is a patch recommendation in the previous step,run theaccept_sql_ patch procedure to accept the patch:

Once you implement a SQL patch,rerun the problem SQL statement to verify that the critical error is gone.You should also see the use of the SQL patch in the explain plan for the SQL statement now.You can query the DBA_SQL_PAT CHES view to find out the name of all the patches offered by the SQL Repair Advisor.You can drop a patch by executing the drop_sql_patch procedure.

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

Oracle 11g Topics