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.
Ahmed Fikri 