Adding Custom Messages to Oracle Alert Log!!!

[Kalyan]

Adding Custom Messages to Oracle Alert Log!!!

Using Oracle DBMS_SYSTEM Package

Oracle DBMS_SYSTEM package supports an undocumented procedure KSDWRT. You can use KSDWRT procedure to write custom messages to your Oracle alert log.

Oracle KSDWRT procedure takes two parameters. The first parameter is a number that indicates the location. If you set it to 1 then it will write to trace file, if you set it to 2 then it will write to the alert file and if you set it to 3 then it will write your custom message to both alert log and the trace file. The second parameter is your custom message that you want to write to the alert log. For example if you want to add timestamp to your alert log then you will need to execute below command.

EXECUTE SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE)|| ' -- ');

Accessing Alert Log as External Table:

Another option to write custom messages to Oracle alert log is to map external files to Oracle table. Once your alert log is defined as an external table then you use SQL statements to access the alert log messages.

First of all we will create directory for alert log location.

CREATE DIRECTORY BDUMP AS '/U01/APP/ORACLE/ADMIN/MYSID/BDUMP';

Now we will create our alert log table.

CREATE TABLE    MY_ALERTLOG (
MYCUSTOMESSAGE VARCHAR2(80)
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER  DEFAULT DIRECTORY BDUMP ACCESS PARAMETERS
(
         RECORDS DELIMITED BY NEWLINE
   )
   LOCATION('ALRT_MYSID.LOG'
)
)
REJECT LIMIT 1000;

Now all you have to do is to query it with standard SQL as follows.

SELECT MYCUSTOMESSAGE FROM MY_ALERTLOG WHERE MYCUSTOMESSAGE LIKE '%ORA-00600%';

Oracle UTL_FILE Package:

Oracle UTL_FILE package provides an easy mechanism to write into Oracle alert file by allowing Oracle SQL and PL/SQL to read and write directly from flat files on the server. You just have to follow below simple steps.

In the first step we will get the location of Oracle alert log by running below command.

SELECT NAME INTO :ALERT_LOC FROM V$PARAMETER
WHERE NAME ='BACKGROUND_DUMP_DESTINATION';

Now we will set the UTL_FILE_DIR parameter.

ALTER SYSTEM SET UTL_FILE_DIR = ':MY_ALERTLOG');

Now we will open the file for write access.

UTL_FILE.FOPEN(':ALERT_LOC','ALERTPROD.LOG','W');

Finally we will write our custom message to the alert log and then close the file.

DBMS_OUTPUT.PUT_LINE('INVALID_APPLICATION_ERROR');
UTL_FILE.FCLOSE(':ALERT_LOC');

You can also write a stored procedure that will perform above steps so as to write custom messages to your Oracle alert log.

Operating System Utilities:

A number of UNIX/Linux utilities are available that can be used to write operating system messages to your Oracle alert log.

Procedural Languages:

Almost all procedural languages support functions for writing into file. Therefore you can use any procedural language to write messages to the Oracle alert log