This post is the fourth (and final) in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.


Any DBA that has had to file a support request with Oracle Support will be violently nodding their head in agreement when I ask if they've ever had Oracle Support ask for log file after trace file after log file after trace file. It's hard to not be cynical, especially when they sometimes request a file that was already provided! In 11g, Oracle has made some of that work easier by providing the Incident Packaging Service, or IPS, with the ADR. IPS lets you quickly and easily gather up diagnostic files related to a given incident, problem, or timeframe. You can also create empty packages and add whatever files you see fit.

It all starts with the IPS CREATE PACKAGE command. This creates a logical package (no package files are created at this point) in the ADR. Various options include:

  • adrci> ips create package incident <incident_id>;
  • adrci> ips create package problem <problem_id>;
  • adrci> ips create package problemkey "problem_key_string";
  • adrci> ips create package seconds <seconds_before_now>;
  • adrci> ips create package time 'start_timestamp' to 'end_timestamp';
  • adrci> ips create package;

It is definitely worth your while to review the output of help ips create package to get details on these, as I won't be touching most of them any further. The last command listed creates an empty package, after which you can use the ips add incident or ips add file commands to add data to the package before generating it.

When you are ready to generate the package file to send to Oracle Support, run the IPS GENERATE PACKAGE command, e.g.

adrci> ips generate package 3 in /home/dts/files/;

This will create a zip file in the specified directory. The package number is given to you when you first create the package, as our later example will demonstrate.

Focusing on the first example, when you run "show incident" in adrci, it will provide you with the list of incidents and their incident numbers. If you feel the need to create an SR with Oracle Support for this incident, you can use that command to create a package based on that incident number, and then generate the package file for that package. However you can do both in one step using IPS PACK INCIDENT:

adrci> ips pack incident <incident_id> in <path>;

For eaxmple:

adrci> ips pack incident 13579 in /home/dts/files/;

This command creates a package and generates the zip file in one step.

Example Time!

Now that I've bored you with the details, lets go through an example incident that you can reproduce in your sandboxes at home, as I'm doing now as I write this.

Step 1: Cause an Incident!

Rather than really cause some harm to my sandbox, let's just set things up so something that normally wouldn't create an incident does so this once:

SQL> alter session set events '942 incident(table_missing)';
Session altered.

SQL> drop table doesnotexist;
drop table doesnotexist
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter session set events '942 trace name context off';
Session altered.

Viewing the trace (human-readable text) alert log:

Wed Jan 30 13:56:20 2013
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25751.trc  (incident=10993):
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [TABLE_MISSING], [], [], [], [], [], [], [], [], []
ORA-00942: table or view does not exist
Incident details in: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_10993/orcl_ora_25751_i10993.trc
Wed Jan 30 13:56:25 2013
Dumping diagnostic data in directory=[cdmp_20130130135624], requested by (instance=1, osid=25751), summary=[incident=10993].
Wed Jan 30 13:56:25 2013
Sweep [inc][10993]: completed
Sweep [inc2][10993]: completed

Step 2: Use ADRCI to view the incident

So our weary DBA is awaked by a page from the monitoring system and can now quickly see what the problem was without having to track down alert log locations and such.

[oracle@localhost ~]$ adrci

ADRCI: Release - Production on Wed Jan 30 13:58:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/home/oracle/app/oracle"
adrci> show homes
ADR Homes:
adrci> set home diag/rdbms/orcl/orcl
adrci> show incident

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
10993                ORA 700 [EVENT_CREATED_INCIDENT] [942] [TABLE_MISSING]      2013-01-30 13:56:20.294000 -06:00
1 rows fetched

Here we see the INCIDENT_ID is 10993, which we'll need to reference later. Now let's assume it's something more sinister and we feel the need to file an SR with Oracle Support and want to upload some files.

Step 3: Create a Package!

adrci> ips create package incident 10993;
Created package 1 based on incident id 10993, correlation level typical
adrci> ips generate package 1 in /home/oracle;
Generated package 1 in file /home/oracle/, mode complete

... or ...

adrci> ips pack incident 10993;
Generated package 2 in file /home/oracle/, mode complete

Both methods achieve the same result, with the exception of the resulting package file name, which as you can see is based on YYYYMMDDHH24MMSS date format. You can see that a new package is generated (1 vs 2). The zip file that is created is a standard zip file, you can use unzip -l <file_name> to look at the contents if you wish. You'll see a number of .dmp files, along with the XML and trace alert log files and other trace files that Oracle has deemed relevant to its needs. Alternatively, you can use IPS to query this information as well:

adrci> ips show files package <package_number>;

This will give you a nicely formatted listing of each file in the package, with some metadata for each file. All that's left now is to upload the package zip file to your SR and hope that Oracle Support can see what they need to see!

Well, that's it folks. Hopefully you found this somewhat informative and it leads you to do a little more digging into ADRCI on your own. At the very least you can now get rid of those ugly alert log text scraping scripts!