Check errors in alert log with SQL query in Oracle

Find the error occurred in alert log file of Oracle with SQL Query

Find the error in alert log of last 15 days:

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
SELECT record_id, to_char(originating_timestamp,’DD-MON-YYYY HH24:MI:SS’) , message_text FROM X$DBGALERTEXT WHERE originating_timestamp > systimestamp - 15 AND regexp_like(message_text, '(ORA-|error)') order by record_id;

Find the error in alert log of last 30 days:

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
SELECT record_id, to_char(originating_timestamp,’DD-MON-YYYY HH24:MI:SS’) , message_text FROM X$DBGALERTEXT WHERE originating_timestamp > systimestamp - 30 AND regexp_like(message_text, '(ORA-|error)') order by record_id;

Description of table if you need other information:

SQL> desc X$DBGALERTEXT

           Name                            Null?    Type
           ------------------------------- -------- -------------------------
    1      ADDR                                     RAW(4)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      ORIGINATING_TIMESTAMP                    TIMESTAMP(3) WITH TIME ZONE
    5      NORMALIZED_TIMESTAMP                     TIMESTAMP(3) WITH TIME ZONE
    6      ORGANIZATION_ID                          VARCHAR2(64)
    7      COMPONENT_ID                             VARCHAR2(64)
    8      HOST_ID                                  VARCHAR2(64)
    9      HOST_ADDRESS                             VARCHAR2(16)
   10      MESSAGE_TYPE                             NUMBER
   11      MESSAGE_LEVEL                            NUMBER
   12      MESSAGE_ID                               VARCHAR2(64)
   13      MESSAGE_GROUP                            VARCHAR2(64)
   14      CLIENT_ID                                VARCHAR2(64)
   15      MODULE_ID                                VARCHAR2(64)
   16      PROCESS_ID                               VARCHAR2(32)
   17      THREAD_ID                                VARCHAR2(64)
   18      USER_ID                                  VARCHAR2(64)
   19      INSTANCE_ID                              VARCHAR2(64)
   20      DETAILED_LOCATION                        VARCHAR2(160)
   21      PROBLEM_KEY                              VARCHAR2(64)
   22      UPSTREAM_COMP_ID                         VARCHAR2(100)
   23      DOWNSTREAM_COMP_ID                       VARCHAR2(100)
   24      EXECUTION_CONTEXT_ID                     VARCHAR2(100)
   25      EXECUTION_CONTEXT_SEQUENCE               NUMBER
   26      ERROR_INSTANCE_ID                        NUMBER
   27      ERROR_INSTANCE_SEQUENCE                  NUMBER
   28      VERSION                                  NUMBER
   29      MESSAGE_TEXT                             VARCHAR2(2048)
   30      MESSAGE_ARGUMENTS                        VARCHAR2(128)
   31      SUPPLEMENTAL_ATTRIBUTES                  VARCHAR2(128)
   32      SUPPLEMENTAL_DETAILS                     VARCHAR2(128)
   33      PARTITION                                NUMBER
   34      RECORD_ID                                NUMBER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.