Query Alert Log Contents From Database

Get alert log contents directly from database by using SQL query

We can get alert log contents directly from database by using SQL query, though the x$ view name is hard to remember.

X$DBGALERTEXT

The view x$dbgalertext is the view to query to get alert log contents for current instance:

SQL> DESC X$DBGALERTEXT
 Name                        Null?    Type
 --------------------------- -------- ----------------------------
 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(64)
 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

To get alert log for the last five hours

SQL> select 
         to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
         MESSAGE_TEXT 
 from    X$DBGALERTEXT 
 where   ORIGINATING_TIMESTAMP>sysdate-5/24 
 order by RECORD_ID;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20171030-12:34:15 Thread 1 advanced to log sequence 1016 (LGWR switch)
20171030-12:34:15 Current log# 2 seq# 1016 mem# 0: +DATA2/RACTEST/ONLINELOG/group_2.1453.926116803
20171030-12:34:15 Current log# 2 seq# 1016 mem# 1: +FRA/RACTEST/ONLINELOG/group_2.20660.926116805
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_1_seq_844.2274.952747445
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_2_seq_875.15096.952747443
20171030-12:34:27 Archived Log entry 3854 added for thread 1 sequence 1015 ID 0x7553f3c0 dest 1:

6 rows selected.

To get alert log for the first 20 lines

SQL> select 
           to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
           MESSAGE_TEXT 
     from  X$DBGALERTEXT 
     order by RECORD_ID 
     fetch first 20 rows only;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20170825-16:13:10 Starting ORACLE instance (normal) (OS id: 10587)
20170825-16:13:10 CLI notifier numLatches:53 maxDescs:4998
20170825-16:13:10 **********************************************************************
20170825-16:13:10 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
20170825-16:13:10 Per process system memlock (soft) limit = UNLIMITED
20170825-16:13:10 Expected per process system memlock (soft) limit to lock
20170825-16:13:10 SHARED GLOBAL AREA (SGA) into memory: 16G
20170825-16:13:10 Available system pagesizes:
20170825-16:13:10 4K, 2048K
20170825-16:13:10 Supported system pagesize(s):
20170825-16:13:10 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
20170825-16:13:10 4K Configured 10 10 NONE
20170825-16:13:10 2048K 68613 8193 8193 NONE
20170825-16:13:10 **********************************************************************
20170825-16:13:10 LICENSE_MAX_SESSION = 0
20170825-16:13:10 LICENSE_SESSIONS_WARNING = 0
20170825-16:13:10 Initial number of CPU is 32
20170825-16:13:10 Number of processor cores in the system is 16
20170825-16:13:10 Number of processor sockets in the system is 2
20170825-16:13:10 Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.

20 rows selected.

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.