Procedure to get the alert log from within the database

Here’s a quick and easy procedure (if you’re on Oracle 11), to get the alert log from within the database. Because the alert log table (X$DBGALERTEXT) is an “X$”, you need to be SYSDBA in order to access it. So the best way to allow “normal” users access to it is to create a procedure. This will return a CLOB containing the lines of the alert log. This functionality will be included in Oracletool 2.3.4, btw.


Create or replace procedure get_alertlog_lines (num_lines in number, alertlog out clob)
/*
   This procedure should be owned by SYS, and you should grant
   execute privileges on it to whatever user you connect to your
   database as within Oracletool.

   Oracletool will not display the alertlog menu item unless this
   procedure exists.
*/

as

   v_alertlog clob;

Begin

   For i in (
      Select
         indx,
         to_char(originating_timestamp,'MM/DD/YYYY HH24:MI:SS') as timestamp,
         message_text as text
      from X$DBGALERTEXT
         where indx > (Select max(indx)-num_lines from X$DBGALERTEXT) order by indx asc)
   loop
      v_alertlog := v_alertlog||i.timestamp||': '||i.text;
   end loop;

   alertlog := v_alertlog;

End get_alertlog_lines;
/

--
-- Example grant
--
Grant execute on get_alertlog_lines to ADAM;

That’s it! let’s try it out via SQL*Plus! First thing I’m going to do is create a synonym for the procedure.


[oracle@mastiff /home/oracle] [DEMO]$ sqlplus adam/<password>

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 22 09:25:30 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

09:25:30 SQL> Set serveroutput on;
09:25:35 SQL> Create synonym get_alertlog_lines for sys.get_alertlog_lines;

Synonym created.

09:25:43 SQL> Declare
09:25:54 SQL> alertlog_lines clob;
09:26:10 SQL> Begin
09:26:14 SQL>    get_alertlog_lines(25,alertlog_lines);
09:26:38 SQL>    dbms_output.put_line(alertlog_lines);
09:26:54 SQL> End;
09:26:55 SQL> /
03/22/2012 03:51:08: Private strand flush not complete
03/22/2012 03:51:08: Current log# 2 seq# 3512 mem# 0:/apps/oracle/oradata/RMAN/onlinelog/o1_mf_2_5rg7qlfr_.log
03/22/2012 03:51:08: Current log# 2 seq# 3512 mem# 1:/apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_2_5rg7qp8t_.log
03/22/2012 03:51:12: Thread 1 advanced to log sequence 3513 (LGWR switch)
03/22/2012 03:51:12:   Current log# 3 seq# 3513 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_3_5rg7qsmw_.log
03/22/2012 03:51:12: Current log# 3 seq# 3513 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_3_5rg7qwm0_.log
03/22/2012 03:51:18: Archived Log entry 3470 added for thread 1 sequence 3512 ID 0x5f45a87f dest 1:
03/22/2012 08:50:07: ALTER SYSTEM ARCHIVE LOG
03/22/2012 08:50:07: Thread 1 cannot allocate new log, sequence 3514
03/22/2012 08:50:07: Private strand flush not complete
03/22/2012 08:50:07:   Current log# 3 seq# 3513 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_3_5rg7qsmw_.log
03/22/2012 08:50:07: Current log# 3 seq# 3513 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_3_5rg7qwm0_.log
03/22/2012 08:50:09: Thread 1 advanced to log sequence 3514 (LGWR switch)
03/22/2012 08:50:09:   Current log# 1 seq# 3514 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_1_5rg7q7wc_.log
03/22/2012 08:50:09: Current log# 1 seq# 3514 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_1_5rg7qfn0_.log
03/22/2012 08:50:14: Archived Log entry 3471 added for thread 1 sequence 3513 ID 0x5f45a87f dest 1:
03/22/2012 08:50:19: ALTER SYSTEM ARCHIVE LOG
03/22/2012 08:50:20: Thread 1 cannot allocate new log, sequence 3515
03/22/2012 08:50:20: Private strand flush not complete
03/22/2012 08:50:20:   Current log# 1 seq# 3514 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_1_5rg7q7wc_.log
03/22/2012 08:50:20: Current log# 1 seq# 3514 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_1_5rg7qfn0_.log
03/22/2012 08:50:22: Thread 1 advanced to log sequence 3515 (LGWR switch)
03/22/2012 08:50:22:   Current log# 2 seq# 3515 mem# 0: /apps/oracle/oradata/RMAN/onlinelog/o1_mf_2_5rg7qlfr_.log
03/22/2012 08:50:22: Current log# 2 seq# 3515 mem# 1: /apps/oracle/backup/flash_recovery_area/RMAN/onlinelog/o1_mf_2_5rg7qp8t_.log
03/22/2012 08:50:24: Archived Log entry 3472 added for thread 1 sequence 3514 ID 0x5f45a87f dest 1:

PL/SQL procedure successfully completed.

🙂

Leave a Reply

Your email address will not be published.