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 http://oracle-script.com

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
Or 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')

For complete details on writing to the Oracle alert log, read the full tip here:

http://www.dba-oracle.com/t_writing_alert_log_message.htm

*****************************************

Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal settings that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author. Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training. Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf 

*****************************************

There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list:

http://www.dba-oracle.com/jobs.htm