Writing to the Oracle Alert Log

Oracle Tips by Burleson Consulting

There are many cases where the Oracle DBA may want to write custom messages directly into the standard Oracle alert log file (located in $ORACLE_HOME/admin/$ORACLE_SID/bdump).

Why write to the alert log? In Oracle11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

By writing custom messages to the Oracle alert log file you can supplement your default Oracle alerts with custom messages. If you don't want to write your own scripts to write to the alert log, see my script download:

Writing to the Oracle alert log is exactly the same as writing to any flat file and we have these choices:

Alert log x$dbgalertext

OS scripts and languages - You can use UNIX/Linux utilities to write OS messages (e.g. RAM swapping alerts) into the alert log. Of course, all procedural languages (C++) can also write messages to the Oracle alert log.

PL/SQL - You can use the standard utl_file package or call the dbms_system.ksdwrt procedure to write messages to the Oracle alert log file. See Oracle utl_file Package and Oracle alert log in stored procedure .

SQL (read only) - You can define the alert log as an external table and access the alert log messages with SQL statements. See Oracle Access your Alert Log via SQL with External Tables and Extract alert log messages with SQL.
dbms_output.put_line
utl_file.put_line
dbms_system.ksdwrt

Examples of writing to the alert log

dbms_output.put_line: This writes from Oracle to the alert log file:

-- ******************************************************
-- Gather the location of the alert log directory
-- ******************************************************

select
name into :alert_loc
from
v$parameter
where
name = ‘background_dump_destination’;

-- ******************************************************
-- Set the utl_file_dir
-- ******************************************************
alter system set utl_file_dir = ‘:alert_loc’);

-- ******************************************************
-- Open the alert log file for write access
-- ******************************************************
utl_file.fopen(':alert_loc',’alertprod.log’,'W');

-- ******************************************************
-- Write the custom message to the alert log file
-- ******************************************************
dbms_output.put_line('custom error message');

-- ******************************************************
-- Close the alert log file
-- ******************************************************
utl_file.fclose(':alert_loc');

# -----------------------------------------------

Using utl_file to write to the alert logOr this way to write to the alert log:

declare
v_file_handle utl_file.file_type;
begin
v_file_handle:=utl_file.fopen('u01/app/oracle/admin/bdump','alert_log.log', 'a');
utl_file.put_line (v_file_handle, 'custom error message');
utl_file.fclose (v_file_handle);
end;
/

Another obscure way to write to the alert log file is to invoke ksdwrt, which to alert log file

dbms_system.ksdwrt(1,'test')

My notes on writing to the Oracle alert log includes:

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books