ADRCI SHOW ALERT Reference

Overview

The SHOW ALERT command of the ADR Command Interpreter has an interesting search facility which does not seem to be well documented. This Wiki page contains my notes about my investigations.

References

Oracle® Database Utilities 11g Release 2 (11.2)

• Chapter 16: ADRCI: ADR Command Interpreter
  • ADRCI Command Reference
    • SHOW ALERT

Command Syntax

The ADRCI help facility gives the following syntax for SHOW ALERT:

Usage: SHOW ALERT [-p <predicate_string>] [-term] [ [-tail [num] [-f]] | [-file <alert_file_name>] ] Purpose: Show alert messages. Options: [-p <predicate_string>]: The predicate string must be double-quoted. The fields in the predicate are the fields: ORIGINATING_TIMESTAMP timestamp NORMALIZED_TIMESTAMP timestamp ORGANIZATION_ID text(65) COMPONENT_ID text(65) HOST_ID text(65) HOST_ADDRESS text(17) MESSAGE_TYPE number MESSAGE_LEVEL number MESSAGE_ID text(65) MESSAGE_GROUP text(65) CLIENT_ID text(65) MODULE_ID text(65) PROCESS_ID text(33) THREAD_ID text(65) USER_ID text(65) INSTANCE_ID text(65) DETAILED_LOCATION text(161) UPSTREAM_COMP_ID text(101) DOWNSTREAM_COMP_ID text(101) EXECUTION_CONTEXT_ID text(101) EXECUTION_CONTEXT_SEQUENCE number ERROR_INSTANCE_ID number ERROR_INSTANCE_SEQUENCE number MESSAGE_TEXT text(2049) MESSAGE_ARGUMENTS text(129) SUPPLEMENTAL_ATTRIBUTES text(129) SUPPLEMENTAL_DETAILS text(129) PROBLEM_KEY text(65) [-tail [num] [-f]]: Output last part of the alert messages and output latest messages as the alert log grows. If num is not specified, the last 10 messages are displayed. If "-f" is specified, new data will append at the end as new alert messages are generated. [-term]: Direct results to terminal. If this option is not specified, the results will be open in an editor. By default, it will open in emacs, but "set editor" can be used to set other editors. [-file <alert_file_name>]: Allow users to specify an alert file which may not be in ADR. <alert_file_name> must be specified with full path. Note that this option cannot be used with the -tail option Examples: show alert show alert -p "message_text like '%incident%'" show alert -tail 20

The deficiency in the documentation is around the -p option which is used for searching.

Listener Log

In my listener, I found a raw XML entry as follows:

<msg time='2012-09-01T19:55:56.245+10:00' org_id='oracle' comp_id='tnslsnr'

 type='UNKNOWN' level='16' host_id='gridctrl.yaocm.id.au'

 host_addr='192.168.1.252'>

 <txt>01-SEP-2012 19:55:56 * (CONNECT_DATA=(SERVICE_NAME=repos.yaocm.id.au)(CID=(PROGRAM=perl)(HOST=gridctrl.yaocm.id.au)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.252)(PORT=10034)) * establish * repos.yaocm.id.au * 0

 </txt>

</msg>

I think the following mapping applies between the search fields and the XML attribute names:

As I went through the database log as described below, I encountered more field mappings whicg I added to the above table.

Sample Searches

For the Listener log, I could do searches as described below:

show alert -p "ORIGINATING_TIMESTAMP BETWEEN '2012-09-01 19:55:56+10:00' AND '2012-09-01 19:55:57+10:00'" returns the sample entry which is:

2012-09-01 19:55:56.245000 +10:00

01-SEP-2012 19:55:56 * (CONNECT_DATA=(SERVICE_NAME=repos.yaocm.id.au)(CID=(PROGRAM=perl)(HOST=gridctrl.yaocm.id.au)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.252)(PORT=10034)) * establish * repos.yaocm.id.au * 0

show alert -p "ORIGINATING_TIMESTAMP = '2012-09-01 19:55:56.245000 +10:00'" returns nothing.

show alert -p "NORMALIZED_TIMESTAMP = '2012-09-01 19:55:56.245000 +10:00'" returns nothing.

show alert -p "NORMALIZED_TIMESTAMP BETWEEN '2012-09-01 19:55:56+10:00' AND '2012-09-01 19:55:57+10:00'" returns nothing.

show alert -p "MESSAGE_TEXT LIKE '01-SEP-2012 19:55:56%'" returns only the sample entry.

The following four (4) entries all have the same value in the XML alert log got the listener:

comp_id='tnslsnr' group='create' host_addr='192.168.1.103' host_id='cloudctl.yaocm.id.au' level='16' msg_id='dbgrmmdcrf_create_relation_full:1941:1503427940' org_id='oracle' version='1'

Message Type

The following are the only values I can find for MESSAGE_TYPE:

type='NOTIFICATION' type='UNKNOWN'

show alert -p "MESSAGE_ID = 'dbgrmmdcrf_create_relation_full:1941:1503427940'" returns:

2012-10-27 06:17:19.671000 -04:00

Create Relation ADR_CONTROL

Create Relation ADR_INVALIDATION

Create Relation INC_METER_IMPT_DEF

Create Relation INC_METER_PK_IMPTS

show alert -p "MESSAGE_GROUP = 'create'"

shows the same messages.

Database Log

I found sample values for the following fields in the database alert log:

• MESSAGE_ID
• MODULE_ID

Message ID

Following the same analysis for the RDBMS alert log, I find the following MESSAGE_ID values:

Module ID

Below is a list of MODULE_ID values that I found in the database alert log:

module='' module='Data module='DBMS_SCHEDULER' module='JDBC module='MMON_SLAVE' module='restore full datafile' module='SQL*Plus' module='sqlplus@adc6260076 (TNS V1-V3)' module='sqlplus@cloudctl.yaocm.id.au (TNS V1-V3)'

Combined Listener and Database Valid Values

This section combines the results from both the database and listener alert logs for the following fields:

• COMPONENT_ID
• MESSAGE_GROUP
• MESSAGE_LEVEL
• MESSAGE_TYPE
• ORGANIZATION_ID

Component ID

The valid values of COMPONENT_ID I have found so far are:

Message Group

The following are the only values I can find for MESSAGE_GROUP:

Message Level

The valid values of MESSAGE_LEVEL I have found so far are:

Message Type

The following are the only values I can find for MESSAGE_TYPE:

Organization ID

The following are the only value I can find for ORGANIZATION_ID:

Predicate Complexity

The syntax of the predicate seems to follow the rules for SQL statements.

All of the following predicates worked (as in no syntax errors were found):

show alert -p "MESSAGE_GROUP = 'create' AND MESSAGE_LEVEL = 16" show alert -p "MESSAGE_LEVEL != 16" show alert -p "MESSAGE_LEVEL = 0 OR MESSAGE_LEVEL = 1" show alert -p "MESSAGE_LEVEL IN (0,1,2,3,4)" show alert -p "MESSAGE_GROUP IS NULL" show alert -p "COMPONENT_ID IS NOT NULL AND (ORGANIZATION_ID IS NULL OR ORGANIZATION_ID != 'oracle')"