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:
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.Options:
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: 3Output 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>
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
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.
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.
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:
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:
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.
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.
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:ORA222_20070304124515_COM_1.zip
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:
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:
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.
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
Here are the steps to follow to resolve a critical error usingthe Support Work bench:
The table of alerts on the Database home page
The Alert 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:
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:
The next step is to execute the diagnosis task.
When you execute the diagnostic task,Oracle usually provides a work around to fix the problem,but sometimes there may not be any recommendations.
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.
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.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.