How to view alert log file

First of all, in order to find your log files location in the operating system where the database is located, you can query to V$DIAG_INFO view. It contains location details about all type of log files.

SQL> set lines 200
SQL> col name format a30
SQL> col value format a80
SQL> SELECT * FROM V$DIAG_INFO;

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 Diag Enabled                   TRUE                                                                                      0
         1 ADR Base                       /opt/oracle                                                                               0
         1 ADR Home                       /opt/oracle/diag/rdbms/oracdb/oracdb                                                      0
         1 Diag Trace                     /opt/oracle/diag/rdbms/oracdb/oracdb/trace                                                0
         1 Diag Alert                     /opt/oracle/diag/rdbms/oracdb/oracdb/alert                                                0
         1 Diag Incident                  /opt/oracle/diag/rdbms/oracdb/oracdb/incident                                             0
         1 Diag Cdump                     /opt/oracle/diag/rdbms/oracdb/oracdb/cdump                                                0
         1 Health Monitor                 /opt/oracle/diag/rdbms/oracdb/oracdb/hm                                                   0
         1 Default Trace File             /opt/oracle/diag/rdbms/oracdb/oracdb/trace/oracdb_ora_24743.trc                           0
         1 Active Problem Count           0                                                                                         0
         1 Active Incident Count          0                                                                                         0

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 ORACLE_HOME                    /opt/oracle/product/19.0.0/dbhome_1                                                       0

You can go to trace file directory now. You can use TAIL command in order to get the last N lines of the file. The -f parameter refreshes the screen when the file gets populated.

[root@dblive ~]# su - oracle
Last login: Sun Mar 28 15:01:46 +03 2021 on pts/0
[oracle@dblive ~]$
[oracle@dblive ~]$ pwd
/home/oracle
[oracle@dblive ~]$ cd /opt/oracle/diag/rdbms/oracdb/oracdb/trace
[oracle@dblive trace]$ find . -type f -iname "*alert*"
./alert_oracdb.log.save
./.alert_oracdb.log.swp
./.alert_oracdb.log.swo
./.alert_oracdb.log.swn
./.alert_oracdb.log.swm
./.alert_oracdb.log.swl
./alert_oracdb.log
[oracle@dblive trace]$ tail -100f alert_oracdb.log

Viewing alert log file via SQL

There is a fixed table called X$DBGALERTEXT. If you query it, Oracle reads the log.xml file, parses the data and returns as rows. You can also use V$DIAG_ALERT_EXT view. MESSAGE_TYPE and MESSAGE_LEVEL columns description is shown below.

SELECT * FROM X$DBGALERTEXT;

SELECT * FROM V$DIAG_ALERT_EXT;
MESSAGE_TYPE

1: UNKNOWN - Essentially the NULL type

2: INCIDENT_ERROR - The program has encountered an error for some internal or unexpected reason, and it must be reported to Oracle Support

3: ERROR - An error of some kind has occurred

4: WARNING: An action occurred or a condition was discovered that should be reviewed and may require action

5: NOTIFICATION: reports a normal action or event. This could be a user action such as "logon completed"

6: TRACE: Output of a diagnostic trace
MESSAGE_LEVEL

1: CRITICAL: critical errors

2: SEVERE: severe errors

8: IMPORTANT: important message

16: NORMAL: normal message
SQL> desc X$DBGALERTEXT;
 Ad²                                       Bo■?     T³r
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(46)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(128)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(550)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(512)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(512)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(4000)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 CON_UID                                            NUMBER
 CONTAINER_NAME                                     VARCHAR2(64)
 ATTENTION_ID                                       NUMBER
 ID_SUFFIX                                          VARCHAR2(64)
 OPERATION_ID                                       VARCHAR2(64)
 CAUSE_TEXT                                         VARCHAR2(2048)
 ACTION_TEXT                                        VARCHAR2(2048)
 ORACLE_PROCESS_ID                                  NUMBER
 DATABASE_ID                                        VARCHAR2(30)
 SQL_ID                                             VARCHAR2(13)
 SESSION_ID                                         VARCHAR2(16)
 IMPACT_ID                                          VARCHAR2(32)
 IMPACT_SCOPE                                       VARCHAR2(32)
 CALL_STACK                                         VARCHAR2(1024)
 FLAGS                                              NUMBER


Using ADRCI

You can also use ADRCI utility, which stands for Automatic Diagnostic Repository Command Interface. ADRCI is the command-line tool that enables you to view diagnostic data as well as the health monitor reports. The main purposes of ADRCI is, monitoring the diagnostic files, purging them and creating incident packages to send the Oracle support.

[oracle@dblive ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Sun Mar 28 16:37:33 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"
adrci> help

 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        ESTIMATE
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SELECT
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW LOG
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

 There are other commands intended to be used directly by Oracle, type
 "HELP EXTENDED" to see the list

adrci>

If you want to get information about any of the ADR commands, you can use HELP. In order to get detailed explanation, type HELP at the beginning of the command as shown below.

adrci> help 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(47)
        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(513)
        SUPPLEMENTAL_ATTRIBUTES       text(513)
        SUPPLEMENTAL_DETAILS          text(4000)
        PROBLEM_KEY                   text(551)
        CON_UID                       number
        CONTAINER_ID                  number
        CONTAINER_NAME                text(31)

    [-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 vi on Linux, 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

To show the last lines of the alert file, -tail parameter can be used. Also, -f is used to wait and report the new added lines. In the following example the last 50 lines are shown from alert log.

show alert -tail 50

Show the last 50 lines and wait any new lines.

show alert -tail 50 -f

Filter the alert logs which contains ‘ORA’ in MESSAGE_TEXT in the last one day.

show alert -p "MESSAGE_TEXT LIKE '%ORA%' AND ORIGINATING_TIMESTAMP > SYSTIMESTAMP - 1"

Show trace files that contains lgwr in the filename. -t is used to sort the filenames in the modified timestamp order.

show tracefile %lgwr% -t

Show the content of a specific trace file.

show trace diag/rdbms/tamlivec/tamlivec/trace/oracdb_lgwr_12007.trc

Show ADR purge settings.

show control

I hope this helps 🙂

Posted in DBA
Leave a Reply

Your email address will not be published.