Finding the alert log file anywhere

“Opinions expressed are solely my own and do not express the views or opinions of my employer.”

Some database administrators have a hard time finding the alert log file in systems that they are not familiar with. Therefore, I decided to share two simple techniques to find the alert log file regardless of whether you know the system. Thus, the first technique is to check the alert log being connected to the SQL prompt using the x$dbgalertext view that brings the current information of the alert log. Therefore, this SQL follow as below:

SQL>set linesize 160 pagesize 200
col RECORD_ID for 9999999 head ID
col ORIGINATING_TIMESTAMP for a20 head Date
col MESSAGE_TEXT for a120 head Message
select 
    record_id,
    to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
    message_text 
from 
    x$dbgalertext where  originating_timestamp > sysdate-2;

Another form to find the alert log file is to go to the directory where the file is located. After, open it with some editor such as “vi,” for instance in Linux systems or “notepad” for Windows systems. To be able to find the location, you can use the following SQL query below:

SQL> SET PAGES 9999
SET LINES 9999
COLUMN name FORMAT A25
COLUMN value FORMAT A65
SELECT name, value FROM v$diag_info;

NAME                      VALUE


Diag Enabled              TRUE
ADR Base                  /u01/app/oracle
ADR Home                  /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1
Diag Trace                /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1/trace
Diag Alert                /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1/alert
Diag Incident             /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1/incident
Diag Cdump                /u01/app/oracle/admin/techdatabasket/cdump
Health Monitor            /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1/hm
Default Trace File        /u01/app/oracle/diag/rdbms/techdatabasket/techdatabasket1/trace/techdatabasket1_ora_32298.trc
Active Problem Count      15
Active Incident Count     486
ORACLE_HOME               /u01/app/oracle/19.3.0/db

12 rows selected.

I hope this post helps you!

brunors

Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)