Oracle reading alert log via sql

In many situations, an Oracle developer needs access to the alert log file to explain some problems occurring on the database. Errors that may be logged there are e.g. problems with tablespaces, online / archive log files, deadlocks or Ora-00600 errors. In the event of serious errors, detailed information is logged in trace files. The trace file name is written in the alert file. However, the alert file is located on the Oracle server and the developers usually do not have access to the Oracle server. Well, we are going to see how we can still access the content of this file through SQL.

From my own experience during the installation a new version of our software at the customer. The customer in Germany has a RAC database with two nodes. The database instances were somewhere in France. It was Friday very late and our software aborts when executing an SQL statement. The sessions just disappear without leaving any traces in our software logging. The DBA in France was unfortunately not available at this time (It was quite simply very late on Friday). In the log of our software, I could find nothing relevant. It was clear that the problem has to do with the Oracle Instance. In such cases, it would be very helpful to take look in the alert file for more information.

Then I came up with the following idea (I already knew that something like that is possible, I’ve read in a book): Luckily we had a database user who is allowed to create directories, external table and has select privelige on gv$process and gv$instance.

Why GV$INSTANCE? Because it was a RAC database so several nodes therefore GV$
The Alert file is called (most) alert_SID.log, where SID is instance_name: SELECT instance_name  FROM GV$INSTANCE

Why GV$PROCESS? Because tt was a RAC database so several nodes therefore GV$ With following query you can get the full path on the OS of the alert file.

SELECT DISTINCT INST_ID,SUBSTR(TRACEFILE,1,INSTR(TRACEFILE, '/', -1)) A  FROM GV$PROCESS 

This block conveniently generates the code to create the directories and the tables:

DECLARE
  STMT VARCHAR2(4000) := 'create table 
   alert_log__inst?int? ( msg varchar2(1024) )
organization external (
   type oracle_loader
   default directory dir4alertfile?int?
   access parameters (
      records delimited by newline
   )
   location(''alert_?SID?.log'')
)
reject limit 100000';
BEGIN
  FOR REC IN (SELECT INSTANCE_NAME, P.*
                FROM GV$INSTANCE I
                JOIN (SELECT DISTINCT INST_ID,
                                     SUBSTR(TRACEFILE,
                                            1,
                                            INSTR(TRACEFILE, '/', -1)) A
                       FROM GV$PROCESS) P
                  ON I.INST_ID = P.INST_ID) LOOP
    DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE directory dir4alertfile' ||
                         REC.INST_ID || ' as ''' || REC.A || '''');
    DBMS_OUTPUT.PUT_LINE(CHR(10) || CHR(10));
    DBMS_OUTPUT.PUT_LINE(REPLACE(REPLACE(STMT, '?int?', REC.INST_ID),
                                 '?SID?',
                                 REC.INSTANCE_NAME));
    DBMS_OUTPUT.PUT_LINE(CHR(10) || CHR(10));
  END LOOP;
END;

generated code:

--first directory (node1)
CREATE OR REPLACE directory dir4alertfile1 as '/u01/app/oracle/diag/rdbms/mydb/INST1/trace/'


--first external table (node1)
create table
   alert_log__inst1 ( msg varchar2(1024) )
organization external (
   type oracle_loader
   default directory dir4alertfile1
   access parameters (
      records delimited by newline
   )
   location('alert_INST1.log')
)
reject limit 100000

--second directory (node2)
CREATE OR REPLACE directory dir4alertfile2 as '/u01/app/oracle/diag/rdbms/mydb/INST2/trace/'


--second external table (node2)
create table
   alert_log__inst2 ( msg varchar2(1024) )
organization external (
   type oracle_loader
   default directory dir4alertfile2
   access parameters (
      records delimited by newline
   )
   location('alert_INST2.log')
)
reject limit 100000

We can use the same to read in the trace files.