Oracle alert log in stored procedure Oracle Tips by Burleson Consulting Don Burleson

Note:  Scanning the alert log is not trivial and it can take weeks to write a comprehensive set of intelligent scripts to scan the alert logs for important massages.  If you don't want to write your own custom scripts, see my Oracle script download.

You can read and write to the Oracle alert log from inside an Oracle stored procedure:

1 - Locate the background dump directory (the location of the alert log).
2 - Set the utl_file_dir initialization parameter.
3 - Execute utl_file.fopen to open the file for write access.
4 - Use dbms_output.put_line to write the custom message to the alert log.
5 - Execute utl_file.fclose to close the 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
-- (prior to Oracle9i, you must bounce the database)
-- ******************************************************
alter system set utl_file_dir = ?:alert_log?);


-- ******************************************************
-- 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('invalid_application_error');

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

There are several other ways to scan the alert log:

• Ion for Oracle - The Ion tool has complete mechanisms for setting custom alert log extracts.

• In 11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

• SQL against the alert log - You can define the alert log file as an external table and detect important messages with SQL and e-mail them to a central repository.

• Scripts - You can write Oracle shell scripts OS shell scripts to detect alert log messages. 

---