Read Oracle alert log From SQL

dbtut January 3, 2020 ORACLE

It is possible to query the contents of the alertSID.log file in Oracle databases, such as a table in the database. To do this, you must first create a virtual directory that points to the trace directory that contains the alertSID.log file. The contents of the log file can then be queried with an external table.

Create a virtual directory

The following command can be used to create a virtual directory that points to the trace directory in the database named ORCL.

CREATE OR REPLACE DIRECTORY 
ALERT_LOG_DIR AS 
'/u01/app/oracle/diag/rdbms/orcl/ORCL/trace';

CREATE OR REPLACE DIRECTORY

ALERT_LOG_DIR AS

'/u01/app/oracle/diag/rdbms/orcl/ORCL/trace';

Create an External Table

We can create an external table as follows. The log file (orcl_alert_table.log) required for the external table will be created under the trace directory.

CREATE TABLE ORCL_ALERT_LOG
(
  MESSAGE  VARCHAR2(4000 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY ALERT_LOG_DIR
     ACCESS PARAMETERS 
       ( records delimited by newline logfile alert_log_dir:'orcl_alert_table.log'     )
     LOCATION (ALERT_LOG_DIR:'alert_ORCL.log')
  )
REJECT LIMIT UNLIMITED;

CREATE TABLE ORCL_ALERT_LOG

(

MESSAGE VARCHAR2(4000 BYTE)

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY ALERT_LOG_DIR

ACCESS PARAMETERS

( records delimited by newline logfile alert_log_dir:'orcl_alert_table.log' )

LOCATION (ALERT_LOG_DIR:'alert_ORCL.log')

)

REJECT LIMIT UNLIMITED;

Because trace indexes are different for each instance on RAC systems, different tables must be created.

For example, you can query if there is a ” ORA-12012 ” error in the alert log as follows.

SQL> set lines 1000
SQL> select * from DBA_ALERT_LOG where MESSAGE like '%ORA-12012%';

MESSAGE
----------------------------------------------------------------------------
ORA-12012: error on auto execute of job 2
ORA-12012: error on auto execute of job 2222

SQL> set lines 1000

SQL> select * from DBA_ALERT_LOG where MESSAGE like '%ORA-12012%';

MESSAGE

----------------------------------------------------------------------------

ORA-12012: error on auto execute of job 2

ORA-12012: error on auto execute of job 2222

Read Oracle Alert Log Using V$DIAG_ALERT_EXT

You can query any alert.log file line by line with the external table above. It is also possible to access the same information with the V$DIAG_ALERT_EXT view. With this view, you can view not only the database, but also the error messages related to different components (rdbms, diagtool, clients, asm, tnslsnr). Also, XML-based alert log files in the Automatic Diagnostic Repository (ADR) can be queried.

You can query V$DIAG_ALERT_EXT as follows:

select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME
,message_text
,host_id
,inst_id
,adr_home 
from V$DIAG_ALERT_EXT A
where component_id='rdbms'
and message_text like '%ORA-12012%'
order by 1 desc;

select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME

,message_text

,host_id

,inst_id

,adr_home

from V$DIAG_ALERT_EXT A

where component_id='rdbms'

and message_text like '%ORA-12012%'

order by 1 desc;

select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME
,message_text
,host_id
,inst_id
,adr_home 
from V$DIAG_ALERT_EXT A
where A.ORIGINATING_TIMESTAMP > sysdate-1
and component_id='rdbms'
and message_text like '%ORA-12012%'
order by 1 desc;