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.
Oracle® Database Utilities 11g Release 2 (11.2)
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.
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.
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:0001-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 * 0show 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'
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:00Create Relation ADR_CONTROLCreate Relation ADR_INVALIDATIONCreate Relation INC_METER_IMPT_DEFCreate Relation INC_METER_PK_IMPTSshow alert -p "MESSAGE_GROUP = 'create'"shows the same messages.
I found sample values for the following fields in the database alert log:
Following the same analysis for the RDBMS alert log, I find the following MESSAGE_ID values:
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)'
This section combines the results from both the database and listener alert logs for the following fields:
The valid values of COMPONENT_ID I have found so far are:
COMPONENT_ID
'rdbms'
'tnslsnr'
Product
Oracle RDBMS Database Server
Oracle Listener
The following are the only values I can find for MESSAGE_GROUP:
The valid values of MESSAGE_LEVEL I have found so far are:
MESSAGE_LEVEL
1
16
Product
Oracle RDBMS Database Server
Oracle RDBMS Database Server
Oracle Listener
The following are the only values I can find for MESSAGE_TYPE:
The following are the only value I can find for ORGANIZATION_ID:
ORGANIZATION_ID
'oracle'
Product
Oracle RDBMS Database Server
Oracle Listener
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')"