Tag: Query Alert Log Contents via sql

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.