Scribd Logo
Upload

Welcome to Scribd!

  • 27 views

    Query Alert Log

    Uploaded by

    greetje zwart
    This document contains SQL queries to retrieve alert logs and error messages from an Oracle database from the last 300-500 entries. It defines views for the last 300 alerts and the alert log to more easily query recent errors and messages.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Query Alert Log

    Uploaded by

    greetje zwart
    27 views3 pages
    This document contains SQL queries to retrieve alert logs and error messages from an Oracle database from the last 300-500 entries. It defines views for the last 300 alerts and the alert log to more easily query recent errors and messages.

    Original Title

    Query alert log

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains SQL queries to retrieve alert logs and error messages from an Oracle database from the last 300-500 entries. It defines views for the last 300 alerts and the alert log to more easily query recent errors and messages.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    27 views3 pages

    Query Alert Log

    Uploaded by

    greetje zwart
    This document contains SQL queries to retrieve alert logs and error messages from an Oracle database from the last 300-500 entries. It defines views for the last 300 alerts and the alert log to more easily query recent errors and messages.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 3

    select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.

    yyyy hh24:mi:ss') MESSAGE_TIME


    ,message_text
    from V$DIAG_ALERT_EXT A
    where A.ORIGINATING_TIMESTAMP > trunc(sysdate)-6
    --and component_id='rdbms'
    and message_text like '%ORA-%'
    and message_text not like '% Result = ORA-0%'
    order by A.ORIGINATING_TIMESTAMP desc;

    TRACE FILE ERBIJ:


    ================
    select payload
    from v$diag_trace_file_contents
    where trace_filename LIKE'%ESTRONP_ora_30231.trc%'
    -- and timestamp > trunc(sysdate - 4)
    -- WHERE TRACE_FILENAME =
    '//opt//oracle//diag//rdbms//estronp//ESTRONP//trace//ESTRONP_ora_31502.trc'
    -- and adr_home ='D:\APP\VNAMEIT\VIRTUAL\diag\rdbms\ora12c\ora12c'
    order by line_number

    ----------------------------------------------------------------
    -- last 500 alerts
    ----------------------------------------------------------------
    SELECT LINENO,MESSAGE_TEXT, originating_timestamp
    FROM (SELECT ROWNUM AS lineno, MESSAGE_TEXT, originating_timestamp
    FROM x$dbgalertext
    )
    WHERE lineno > (SELECT COUNT (*) - 500 FROM x$dbgalertext)
    --AND message_text like '%ORA-%'
    ORDER BY lineno

    1125 project 1 VRAGEN UITZOEKEN/BEANTWOORDEN kolommen besteltijd/dag op TEST


    1144 project 2 nieuwe kolom Productgroep in Geladen AKTIE
    1157 service

    ----------------------------------------------------------------
    -- Errors in last 300 alerts
    ----------------------------------------------------------------
    SELECT * FROM sys.last_300_alerts
    where message_text like '%ORA-%'
    /

    ----------------------------------------------------------------
    -- Errors in last 500 alerts
    ----------------------------------------------------------------
    SELECT LINENO,MESSAGE_TEXT, originating_timestamp
    FROM (SELECT ROWNUM AS lineno, MESSAGE_TEXT, originating_timestamp
    FROM v$alert_log
    )
    WHERE lineno > (SELECT COUNT (*) - 500 FROM v$alert_log)
    AND message_text like '%ORA-%'
    ORDER BY lineno
    /

    ----------------------------------------------------------------
    -- last 300 messages from alert log
    ----------------------------------------------------------------

    SELECT MESSAGE_TEXT, originating_timestamp


    FROM (SELECT ROWNUM AS lineno, MESSAGE_TEXT, originating_timestamp
    FROM v$alert_log)
    WHERE lineno > (SELECT COUNT (*) - 300 FROM v$alert_log)
    ORDER BY lineno

    ----------------------------------------------------------------
    -- view definition LAST_300_ALERTS
    ----------------------------------------------------------------
    CREATE OR REPLACE FORCE VIEW LAST_300_ALERTS
    (
    LINENO,
    MESSAGE_TEXT,
    ORIGINATING_TIMESTAMP
    )
    AS
    SELECT lineno, MESSAGE_TEXT, originating_timestamp
    FROM (SELECT ROWNUM AS lineno, MESSAGE_TEXT, originating_timestamp
    FROM v$alert_log)
    WHERE lineno > (SELECT COUNT (*) - 300 FROM v$alert_log)
    ORDER BY lineno;

    ----------------------------------------------------------------
    -- view V$ALERT_LOG
    ----------------------------------------------------------------

    CREATE OR REPLACE FORCE VIEW V$ALERT_LOG


    (
    ADDR,
    INDX,
    INST_ID,
    ORIGINATING_TIMESTAMP,
    NORMALIZED_TIMESTAMP,
    ORGANIZATION_ID,
    COMPONENT_ID,
    HOST_ID,
    HOST_ADDRESS,
    MESSAGE_TYPE,
    MESSAGE_LEVEL,
    MESSAGE_ID,
    MESSAGE_GROUP,
    CLIENT_ID,
    MODULE_ID,
    PROCESS_ID,
    THREAD_ID,
    USER_ID,
    INSTANCE_ID,
    DETAILED_LOCATION,
    PROBLEM_KEY,
    UPSTREAM_COMP_ID,
    DOWNSTREAM_COMP_ID,
    EXECUTION_CONTEXT_ID,
    EXECUTION_CONTEXT_SEQUENCE,
    ERROR_INSTANCE_ID,
    ERROR_INSTANCE_SEQUENCE,
    VERSION,
    MESSAGE_TEXT,
    MESSAGE_ARGUMENTS,
    SUPPLEMENTAL_ATTRIBUTES,
    SUPPLEMENTAL_DETAILS,
    PARTITION,
    RECORD_ID
    )
    AS
    SELECT "ADDR",
    "INDX",
    "INST_ID",
    "ORIGINATING_TIMESTAMP",
    "NORMALIZED_TIMESTAMP",
    "ORGANIZATION_ID",
    "COMPONENT_ID",
    "HOST_ID",
    "HOST_ADDRESS",
    "MESSAGE_TYPE",
    "MESSAGE_LEVEL",
    "MESSAGE_ID",
    "MESSAGE_GROUP",
    "CLIENT_ID",
    "MODULE_ID",
    "PROCESS_ID",
    "THREAD_ID",
    "USER_ID",
    "INSTANCE_ID",
    "DETAILED_LOCATION",
    "PROBLEM_KEY",
    "UPSTREAM_COMP_ID",
    "DOWNSTREAM_COMP_ID",
    "EXECUTION_CONTEXT_ID",
    "EXECUTION_CONTEXT_SEQUENCE",
    "ERROR_INSTANCE_ID",
    "ERROR_INSTANCE_SEQUENCE",
    "VERSION",
    "MESSAGE_TEXT",
    "MESSAGE_ARGUMENTS",
    "SUPPLEMENTAL_ATTRIBUTES",
    "SUPPLEMENTAL_DETAILS",
    "PARTITION",
    "RECORD_ID"
    FROM x$dbgalertext;

    You might also like