The following are variations on the IPS CREATE PACKAGE command:

IPS CREATE PACKAGE

Entering the command without specifications creates an empty package. To add diagnostic data to the package before generating it, you then must use the IPS ADD INCIDENT or IPS ADD FILE commands.

IPS CREATE PACKAGE PROBLEM problem_ID

This command creates a package, and includes diagnostic information for incidents that reference the specified problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID, ADRCI adds to the package the diagnostic information for the first three incidents (early incidents) that occurred and last three incidents (late incidents) that occurred with this problem ID, excluding any incidents that are older than 90 days.

ADRCI may also add other incidents that correlate closely in time or in other criteria with the already added incidents.

IPS CREATE PACKAGE PROBLEMKEY "problem_key"

This command creates a package, and includes diagnostic information for incidents that reference the specified problem key. You can obtain problem keys from the report displayed by the SHOW INCIDENT command. Because there can be many incidents with the same problem key, ADRCI adds to the package only the diagnostic information for the first three early incidents, and the last three late incidents with this problem key, excluding incidents that are older than 90 days.

ADRCI may also add other incidents that correlate closely in time or in other criteria with the already added incidents.

The problem key must be enclosed in single quotation marks (') or double quotation marks (") if it contains spaces or quotation marks.

IPS CREATE PACKAGE SECONDS sec

This creates a package and includes diagnostic information for all incidents that occurred from sec seconds ago until now. sec must be an integer.

IPS CREATE PACKAGE TIME 'start_time' TO 'end_time'

This command creates a package and includes diagnostic information for all incidents that occurred within the specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'. This string is a valid format string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. The fraction (FF) portion of the time is optional, and the HH24:MI:SS delimiters can be either colons or periods.

For example, the following command creates a package with incidents that occurred between July 24th and July 30th of 2010:

IPS CREATE PACKAGE TIME '2010-07-24 00:00:00 -07:00' to '2010-07-30 23.59.59 -07:00'

Adding diagnostic information to a logical package enables you to add incident information after the package is created, such the following:

• All diagnostic information for a particular incident
• A named file within the Automatic Diagnostic Repository (ADR).

To access the current ADR home directory, use the <ADR_HOME> variable as follows:

ips add file <ADR_HOME>/trace/orcl_ora_13579.trc package 12

Use the <ADR_BASE> variable to access the ADR base directory as follows:

ips add file <ADR_BASE>/diag/rdbms/orcl/orcl/trace/orcl_ora_13579.trc package 12

Purpose

Adds incidents to a package.

Syntax and Description

ips add {incident first [n] | incident inc_id | incident last [n] | 
     problem first [n] | problem prob_id | problem last [n] |
     problemkey pr_key | seconds secs | time start_time to end_time} 
     package package_id

Table 20-2 describes the arguments of IPS ADD.

Examples

This example adds incident 22 to package 12:

ips add incident 22 package 12

This example adds the first three early incidents and the last three late incidents with problem ID 6 to package 2, exuding any incidents older than 90 days:

ips add problem 6 package 2

This example adds all incidents taking place during the last minute to package 5:

ips add seconds 60 package 5

This example adds all incidents taking place between 10:00 a.m. and 11:00 p.m. on May 1, 2010:

ips add  time '2010-05-01 10:00:00.00 -07:00' to '2010-05-01 23:00:00.00 -07:00'

Syntax and Description

ips add file file_name package package_id

file_name is the full path name of the file. You can use the <ADR_HOME> and <ADR_BASE> variables if desired. The file must be under the same ADR base as the package.

package_id is the package ID.

Example

This example adds a trace file to package 12:

ips add file <ADR_HOME>/trace/orcl_ora_13579.trc package 12

Syntax and Description

ips add new incidents package package_id

package_id is the ID of the package to update. Only new incidents of the problems in the package are added.

Example

This example adds up to three of the new late incidents for the problems in package 12:

ips add new incidents package 12

Purpose

To edit a file in a package, you must copy the file out to a designated directory, edit the file, and copy it back into the package. You may want to do this to delete sensitive data in the file before sending the package to Oracle Support.

Syntax and Description

ips copy in file filename [to new_name][overwrite] package package_id
     [incident incid]

Copies an external file, filename (specified with full path name) into the ADR, associating it with an existing package, package_id, and optionally an incident, incid. Use the to new_name option to give the copied file a new file name within the ADR. Use the overwrite option to overwrite a file that exists already.

Example

This example copies a trace file from the file system into the ADR, associating it with package 2 and incident 4:

ips copy in file /home/nick/trace/orcl_ora_13579.trc to <ADR_HOME>/trace/orcl_ora_13579.trc package 2 incident 4

Purpose

To edit a file in a package, you must copy the file out to a designated directory, edit the file, and copy it back into the package. You may want to do this to delete sensitive data in the file before sending the package to Oracle Support.

Syntax and Description

ips copy out file source to target [overwrite]

Copies a file, source, to a location outside the ADR, target (specified with full path name). Use the overwrite option to overwrite the file that exists already.

Example

This example copies the file orcl_ora_13579.trc, in the trace subdirectory of the current ADR home, to a local folder.

ips copy out file <ADR_HOME>/trace/orcl_ora_13579.trc to /home/nick/trace/orcl_ora_13579.trc

Purpose

Creates a new package. ADRCI automatically assigns the package number for the new package.

Syntax and Description

ips create package {incident first [n] | incident inc_id | 
     incident last [n] | problem first [n] | problem prob_id |
     problem last [n] | problemkey prob_key | seconds secs | 
     time start_time to end_time} [correlate {basic |typical | all}]

Optionally, you can add incidents to the new package using the provided options.

Table 20-3 describes the arguments for IPS CREATE PACKAGE.

Examples

This example creates a package with no incidents:

ips create package

Output:

Created package 5 without any contents, correlation level typical

This example creates a package containing all incidents between 10 AM and 11 PM on the given day:

ips create package time '2010-05-01 10:00:00.00 -07:00' to '2010-05-01 23:00:00.00 -07:00'

Output:

Created package 6 based on time range 2010-05-01 10:00:00.00 -07:00 to 2010-05-01 23:00:00.00 -07:00, correlation level typical

This example creates a package and adds the first three early incidents and the last three late incidents with problem ID 3, excluding incidents that are older than 90 days:

ips create package problem 3

Output:

Created package 7 based on problem id 3, correlation level typical

Syntax and Description

ips delete package package_id

package_id is the package to delete.

Example

ips delete package 12

Syntax and Description

ips finalize package package_id

package_id is the package ID to finalize.

Example

ips finalize package 12

Syntax and Description

ips generate package package_id [in path] [complete | incremental]

package_id is the ID of the package to generate. Optionally, you can save the file in the directory path. Otherwise, the package is generated in the current working directory.

The complete option means the package forces ADRCI to include all package files. This is the default behavior.

The incremental option includes only files that have been added or changed since the last time that this package was generated. With the incremental option, the command finishes more quickly.

Example

This example generates a physical package file in path /home/steve:

ips generate package 12 in /home/steve

This example generates a physical package from files added or changed since the last generation:

ips generate package 14 incremental

Syntax and Description

ips get manifest from file filename

filename is a package zip file. The manifest is an XML-formatted set of metadata for the package file, including information about ADR configuration, correlated files, incidents, and how the package was generated.

This command does not require an ADR home to be set before you can use it.

Example

ips get manifest from file /home/steve/ORA603_20060906165316_COM_1.zip

Syntax and Description

ips get metadata {from file filename | from adr}

filename is a package zip file. The metadata in a package file (stored in the file metadata.xml) contains information about the ADR home, ADR base, and product.

Use the from adr option to get the metadata from a package zip file that has been unpacked into an ADR home using IPS UNPACK.

The from adr option requires an ADR home to be set.

Example

This example displays metadata from a package file:

ips get metadata from file /home/steve/ORA603_20060906165316_COM_1.zip

This next example displays metadata from a package file that was unpacked into the directory /scratch/oracle/package1:

set base /scratch/oracle/package1
ips get metadata from adr

In this previous example, upon receiving the SET BASE command, ADRCI automatically adds to the homepath the ADR home that was created in /scratch/oracle/package1 by the IPS UNPACK FILE command.

Purpose

Creates a package and generates the physical package immediately.

Syntax and Description

ips pack [incident first [n] | incident inc_id | incident last [n] | 
     problem first [n] | problem prob_id | problem last [n] | 
     problemkey prob_key | seconds secs | time start_time to end_time] 
     [correlate {basic |typical | all}] [in path]

ADRCI automatically generates the package number for the new package. IPS PACK creates an empty package if no package contents are specified.

Table 20-4 describes the arguments for IPS PACK.

Example

This example creates an empty package:

ips pack

This example creates a physical package containing all information for incident 861:

ips pack incident 861

This example creates a physical package for all incidents in the last minute, fully correlated:

ips pack seconds 60 correlate all

Purpose

Removes incidents from an existing package.

Syntax and Description

ips remove {incident inc_id | problem prob_id | problemkey prob_key} 
     package package_id

After removing incidents from a package, the incidents continue to be tracked within the package metadata to prevent ADRCI from automatically including them later (such as with ADD NEW INCIDENTS).

Table 20-5 describes the arguments of IPS REMOVE.

Example

This example removes incident 22 from package 12:

ips remove incident 22 package 12

Syntax and Description

ips remove file file_name package package_id

file_name is the file to remove from package package_id. The complete path of the file must be specified. (You can use the <ADR_HOME> and <ADR_BASE> variables if desired.)

After removal, the file continues to be tracked within the package metadata to prevent ADRCI from automatically including it later (such as with ADD NEW INCIDENTS). Removing a file, therefore, only sets the EXCLUDE flag for the file to Explicitly excluded.

Example

This example removes a trace file from package 12:

ips remove file <ADR_HOME>/trace/orcl_ora_13579.trc package 12
Removed file <ADR_HOME>/trace/orcl_ora_13579.trc from package 12
ips show files package 12

.
.
.
FILE_ID                4
FILE_LOCATION          <ADR_HOME>/trace
FILE_NAME              orcl_ora_13579.trc
LAST_SEQUENCE          0
EXCLUDE                Explicitly excluded
.
.
.

Syntax and Description

ips set configuration {parameter_id | parameter_name} value

parameter_id is the ID of the parameter to change, and parameter_name is the name of the parameter to change. value is the new value. For a list of the configuration parameters and their IDs, use IPS SHOW CONFIGURATION.

Example

ips set configuration 3 10

Purpose

These parameters control various thresholds for IPS data, such as timeouts and incident inclusion intervals.

Syntax and Description

ips show configuration {parameter_id | parameter_name}]

IPS SHOW CONFIGURATION lists the following information for each configuration parameter:

• Parameter ID

• Name

• Description

• Unit used by parameter (such as days or hours)

• Value

• Default value

• Minimum Value

• Maximum Value

• Flags

Optionally, you can get information about a specific parameter by supplying a parameter_id or a parameter_name.

Example

This command describes all IPS configuration parameters:

ips show configuration

Output:

PARAMETER INFORMATION:
   PARAMETER_ID           1
   NAME                   CUTOFF_TIME
   DESCRIPTION            Maximum age for an incident to be considered for 
                          inclusion
   UNIT                   Days
   VALUE                  90
   DEFAULT_VALUE          90
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           2
   NAME                   NUM_EARLY_INCIDENTS
   DESCRIPTION            How many incidents to get in the early part of the range
   UNIT                   Number
   VALUE                  3
   DEFAULT_VALUE          3
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           3
   NAME                   NUM_LATE_INCIDENTS
   DESCRIPTION            How many incidents to get in the late part of the range
   UNIT                   Number
   VALUE                  3
   DEFAULT_VALUE          3
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           4
   NAME                   INCIDENT_TIME_WINDOW
   DESCRIPTION            Incidents this close to each other are considered 
                          correlated
   UNIT                   Minutes
   VALUE                  5
   DEFAULT_VALUE          5
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           5
   NAME                   PACKAGE_TIME_WINDOW
   DESCRIPTION            Time window for content inclusion is from x hours 
                          before first included incident to x hours after last 
                          incident
   UNIT                   Hours
   VALUE                  24
   DEFAULT_VALUE          24
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           6
   NAME                   DEFAULT_CORRELATION_LEVEL
   DESCRIPTION            Default correlation level for packages
   UNIT                   Number
   VALUE                  2
   DEFAULT_VALUE          2
   MINIMUM                1
   MAXIMUM                4
   FLAGS                  0

Examples

This command describes configuration parameter NUM_EARLY_INCIDENTS:

ips show configuration num_early_incidents

This command describes configuration parameter 3:

ips show configuration 3

Configuration Parameter Descriptions

Table 20-6 describes the IPS configuration parameters in detail.

Purpose

Lists files included in the specified package.

Syntax and Description

ips show files package package_id

package_id is the package ID to display.

Example

This example shows all files associated with package 1:

ips show files package 1

Output:

   FILE_ID                1
   FILE_LOCATION          <ADR_HOME>/alert
   FILE_NAME              log.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                2
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              alert_adcdb.log
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                27
   FILE_LOCATION          <ADR_HOME>/incident/incdir_4937
   FILE_NAME              adcdb_ora_692_i4937.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                28
   FILE_LOCATION          <ADR_HOME>/incident/incdir_4937
   FILE_NAME              adcdb_ora_692_i4937.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                29
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              adcdb_ora_692.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                30
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              adcdb_ora_692.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included
.
.
.

Syntax and Description

ips show incidents package package_id

package_id is the package ID to display.

Example

This example lists the incidents in package 1:

ips show incidents package 1

Output:

MAIN INCIDENTS FOR PACKAGE 1:
   INCIDENT_ID            4985
   PROBLEM_ID             1
   EXCLUDE                Included
 
CORRELATED INCIDENTS FOR PACKAGE 1:

Syntax and Description

ips show package package_id {basic | brief | detail}

package_id is the ID of the package to display.

Use the basic option to display a minimal amount of information. It is the default when no package_id is specified.

Use the brief option to display more information about the package than the basic option. It is the default when a package_id is specified.

Use the detail option to show the information displayed by the brief option, as well as some package history and information about the included incidents and files.

Example

ips show package 12

ips show package 12 brief

Syntax and Description

ips unpack file file_name [into path]

file_name is the full path name of the physical package (zip file) to unpack. Optionally, you can unpack the file into directory path, which must exist and be writable. If you omit the path, the current working directory is used. The destination directory is treated as an ADR base, and the entire ADR base directory hierarchy is created, including a valid ADR home.

This command does not require an ADR home to be set before you can use it.

Example

ips unpack file /tmp/ORA603_20060906165316_COM_1.zip into /tmp/newadr

Returns the average value of an expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the AVG function in the SELECT command:

• The expression must be a numeric column or a positive numeric constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Returns a concatenation of two character strings. The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the CONCAT function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

Returns the number of rows returned by the query.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the COUNT function in the SELECT command:

• The expression must be a column, a numeric constant, or a string constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

• The function always counts all rows for the query, including duplicates and nulls.

Examples

This example returns the number of incidents for which flood_controlled is 0 (zero):

select count(*) from incident where flood_controlled = 0;

This example returns the number of problems for which problem_key includes ORA-600:

select count(*) from problem where problem_key like '%ORA-600%';

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the DECODE function in the SELECT command:

• The search arguments must be character data.

• A default value must be specified.

Example

This example shows each incident_id and whether or not the incident is flood-controlled. The example uses the DECODE function to display text instead of numbers for the flood_controlled field.

select incident_id, decode(flood_controlled, 0, \
  "Not flood-controlled", "Flood-controlled") from incident;

Purpose

The character string can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of data type NUMBER. If the character sting has data type CHAR, then the length includes all trailing blanks. If the character string is null, then this function returns 0 (zero).

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The SELECT command does not support the following functions: LENGTHB, LENGTHC, LENGTH2, and LENGTH4.

Example

This example shows the problem_id and the length of the problem_key for each problem.

select problem_id, length(problem_key) from problem;

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the MAX function in the SELECT command:

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Example

This example shows the maximum last_incident value for all of the recorded problems.

select max(last_incident) from problem;

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the MIN function in the SELECT command:

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Example

This example shows the minimum first_incident value for all of the recorded problems.

select min(first_incident) from problem;

Purpose

If the first expression specified is null, then NVL returns second expression specified. If first expression specified is not null, then NVL returns the value of the first expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the NVL function in the SELECT command:

• The replacement value (second expression) must be specified as character data.

• The function does not support data conversions.

Example

This example replaces NULL in the output for signalling_component with the text "No component."

select nvl(signalling_component, 'No component') from incident;

Purpose

In SQL, REGEXP_LIKE is a condition instead of a function.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the REGEXP_LIKE function in the SELECT command:

• The pattern match is always case-sensitive.

• The function does not support the match_param argument.

Example

This example shows the problem_id and problem_key for all problems where the problem_key ends with a number.

select problem_id, problem_key from problem \
  where regexp_like(problem_key, '[0-9]$') = true

Purpose

The portion of data returned begins at the specified position and is the specified substring length characters long. SUBSTR calculates lengths using characters as defined by the input character set.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the SUBSTR function in the SELECT command:

• The function supports only positive integers. It does not support negative values or floating-point numbers.

• The SELECT command does not support the following functions: SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4.

Example

This example shows each problem_key starting with the fifth character in the key.

select substr(problem_key, 5) from problem;

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the SUM function in the SELECT command:

• The expression must be a numeric column or a numeric constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Purpose

If you do not specify a format, then the function converts values to the default timestamp format.

Syntax

See the syntax of the TO_CHAR function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TIMESTAMP_TO_CHAR function in the SELECT command:

• The function converts only TIMESTAMP data type. TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, and other data types are not supported.

• The function does not support the nlsparm argument. The function uses the default language for your session.

Example

This example converts the create_time for each incident from a TIMESTAMP data type to a VARCHAR2 data type in the DD-MON-YYYY format.

select timestamp_to_char(create_time, 'DD-MON-YYYY') from incident;

Purpose

The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data. The database sets the case of the characters based on the binary mapping defined for the underlying character set.

Syntax

See the syntax of the LOWER function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TOLOWER function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

Example

This example shows each problem_key in all lowercase letters.

select tolower(problem_key) from problem;

Purpose

The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data. The database sets the case of the characters based on the binary mapping defined for the underlying character set.

Syntax

See the syntax of the UPPER function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TOUPPER function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

Example

This example shows each problem_key in all uppercase letters.

select toupper(problem_key) from problem;