ADRCI SHOW ALERT Reference

Contents

  1. 1 Overview
  2. 2 References
  3. 3 Command Syntax
  4. 4 Listener Log
    1. 4.1 Sample Searches
    2. 4.2 Message Type
  5. 5 Database Log
    1. 5.1 Message ID
    2. 5.2 Module ID
  6. 6 Combined Listener and Database Valid Values
    1. 6.1 Component ID
    2. 6.2 Message Group
    3. 6.3 Message Level
    4. 6.4 Message Type
    5. 6.5 Organization ID
  7. 7 Predicate Complexity

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)

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:

Predicate Fields Data Type XML Attribute Name Value in Sample Entry
ORIGINATING_TIMESTAMP timestamp time '2012-09-01 19:55:56.245+10:00'
NORMALIZED_TIMESTAMP timestamp ?
ORGANIZATION_ID text(65) org_id 'oracle'
COMPONENT_ID text(65) comp_id 'tnslsnr'
HOST_ID text(65) host_id 'gridctrl.yaocm.id.au'
HOST_ADDRESS text(17) host_addr '192.168.1.252'
MESSAGE_TYPE number type 'UNKNOWN'
MESSAGE_LEVEL number level 16
MESSAGE_ID text(65) msg_id
MESSAGE_GROUP text(65) group
CLIENT_ID text(65) client_id
MODULE_ID text(65) module
PROCESS_ID text(33) pid
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) 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
MESSAGE_ARGUMENTS text(129)
SUPPLEMENTAL_ATTRIBUTES text(129)
SUPPLEMENTAL_DETAILS text(129)
PROBLEM_KEY text(65)

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:

MESSAGE_ID Sample Entry
'dbgrmmdcrf_create_relation_full:1941:1503427940' Create Relation IPS_PACKAGE_UNPACK_HISTORY
'dbkh_generate_new_failure_alert:2781:3311820762' Checker run found 1 new persistent data failures
'dglcccmd:2632:1763772155' LOGSTDBY: Validating controlfile with logical metadata
'dglcccmd:3027:1744485125' LOGSTDBY: Validation complete
'kcsnfy:331:968333812' Picked latch-free SCN scheme 3
'kcsnfy:335:968333812' Picked latch-free SCN scheme 3
'kesaiTuneSqlDrv:4625:2579917519' Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
'kesaiTuneSqlDrv:5167:3456118459' End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
'kkjshth:3846:4141434602' Stopping background process CJQ0
'krsd_init_sdips:2524:1211400554' Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
'krso_proc_stop:2124:825284245' Shutting down archive processes
'krso_proc_stop:2151:1369042098' Archiving is disabled
'krso_proc_stop:2175:139982718' Archive process shutdown avoided: 0 active
'ksbrdp:4225:3697353022' PMON started with pid=2, OS id=28720
PSP0 started with pid=3, OS id=28722
'ksbs1p_real:2438:2371767696' Starting background process MMON
Starting background process MMNL
'ksb_shut_detached_process:3333:3107995654' Stopping background process MMNL
'kspdmp:15134:789250895' Starting up:
'kspdmp:15137:3284844642' Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
'kspdmp:15150:4222364190' ORACLE_HOME = /u01/OracleHomes/db11g/product/11.2.0/dbhome_1
System name:Linux
Node name:adc6260076
Release:2.6.18-238.0.0.0.1.el5xen
Version:#1 SMP Tue Jan 4 09:38:01 EST 2011
Machine:x86_64
VM name:Xen Version: 3.4 (PVM)
'kspdmp:15176:1323239670' Using parameter settings in client-side pfile /u01/OracleHomes/db11g/admin/emrepus/pfile/init.ora on machine adc6260076
'kspdmp:15219:1489803337' Using parameter settings in server-side spfile /u01/OracleHomes/db11g/product/11.2.0/dbhome_1/dbs/spfileemrepus.ora
'kspdmp:15252:144004252' System parameters with non-default values:
'kspdmp:15279:3633090201' control_files = "/u01/OracleHomes/db11g/oradata/emrepus/control01.ctl"
control_files = "/u01/OracleHomes/db11g/fast_recovery_area/emrepus/control02.ctl"
compatible = "11.2.0.0.0"
'kspdmp:15288:527288951' processes = 150
sga_target = 2400M
'ksunfy:16486:2937430291' LICENSE_MAX_SESSION = 0
'ksunfy:16487:4207019197' LICENSE_SESSIONS_WARNING = 0
'ksu_setup_oracle_base:26045:2787919602' ORACLE_BASE from environment = /u01/OracleHomes/db11g
'ksu_setup_oracle_base:26052:1585839296' ORACLE_BASE not set in environment. It is recommended that ORACLE_BASE be set in the environment
'ksu_setup_oracle_base:26091:119623615' Reusing ORACLE_BASE from an earlier startup = /u01/OracleHomes/db11g
'ktinfy:1618:1526000287' IMODE=BR
'ktinfy:1626:3929296192' ILAT =27
'ktunfy:2308:3053135360' Autotune of undo retention is turned on.
'kupprdp:5376:3443112156' DM00 started with pid=35, OS id=28889, job SYS.SYS_IMPORT_TRANSPORTABLE_01
'kzanfy:1129:1161496215' SYS auditing is disabled
'kzunfy:2843:2892522327' LICENSE_MAX_USERS = 0
'opiexe:2999:4222364190' Create controlfile reuse set database "emrepus"
MAXINSTANCES 8
MAXLOGHISTORY 1
ALTER TABLESPACE example READ WRITE
'opiexe:3026:4222364190' ALTER DATABASE MOUNT
ALTER DATABASE OPEN
'opiexe:3061:2780954927' ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
'opiexe:3070:2802784106' Completed: Create controlfile reuse set database "emrepus"
'opiexe:3097:2802784106' Completed: ALTER DATABASE MOUNT
'opistp_real:1651:503144415' Instance shutdown complete
'opistp_real:1762:1141351617' Shutting down instance (immediate)
'opistp_real:1771:1758742897' Shutting down instance (normal)
'opistp_real:1835:3814512043' Shutting down instance: further logons disabled
'opistp_real:2114:2031367711' License high water mark = 1
'opistr_real:950:939128187' Starting ORACLE instance (restrict)
'opistr_real:953:3971575317' Starting ORACLE instance (normal)

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:

COMPONENT_ID Product
'rdbms' Oracle RDBMS Database Server
'tnslsnr' Oracle Listener

Message Group

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

MESSAGE_GROUP Product
'admin_ddl' Oracle RDBMS Database Server
'create' Oracle RDBMS Database Server
Oracle Listener
'DRA' Oracle RDBMS Database Server
'process Oracle RDBMS Database Server
'shutdown' Oracle RDBMS Database Server
'sqltune' Oracle RDBMS Database Server
'startup' Oracle RDBMS Database Server

Message Level

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

MESSAGE_LEVEL Product
1 Oracle RDBMS Database Server
16 Oracle RDBMS Database Server
Oracle Listener

Message Type

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

MESSAGE_TYPE Product
'ERROR' Oracle RDBMS Database Server
'NOTIFICATION' Oracle RDBMS Database Server
Oracle Listener
'UNKNOWN' Oracle RDBMS Database Server
Oracle Listener

Organization ID

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

ORGANIZATION_ID Product
'oracle' Oracle RDBMS Database Server
Oracle Listener

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')"