Writing to the Oracle Alert Log

Oracle Database 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 - This can be directed to write to the alert log file.
utl_file.put_line - This will also write to the alert log.
dbms_system.ksdwrt - This little-known undocumented utility will write to the alert log.

Here are some example scripts of writing to the alert log.

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 log,. try 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')

While not a utility per se, the ability to write messages to the Oracle alert log is an extremely useful ability. This is being mentioned in this chapter because it is an often needed and useful, but not well documented ability.

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.

By writing custom messages to the Oracle alert log file, the default Oracle alerts can be supplemented with custom messages. The best method to write to the alert log is to use SYS.DBMS_SYSTEM.KSDWRT. This very handy utility will write a message to the alert log; it takes two arguments, a number (1 or 2), and the text. The number indicates whether to write to alert or trace. If set to 1, it writes to a trace file.

If 2, it writes to the alert log. Because it uses the dbms_system package, write a wrapper around the call and grant privileges to the wrapper procedure or function. Then an execute will not have to be granted on the dbms_system package. A possible function to accomplish this purpose is below:

create or replace function write_alert_log(log_or_trace in number, text_message in varchar2) return number

begin

SYS.DBMS_SYSTEM.KSDWRT(log_or_trace,text_message);

return 0;

exception

when others then

return 1;

end;

It is also possible to write to the alert log using utl_file, but there are many privilege issues that arise when using this method and many more lines of programming. The easy method is to use the DBMS_SYSTEM.KSDWRT package.

My notes on writing to the Oracle alert log includes:

��