Alert log monitor from a table not from ‘alert.log’ file.

Description:

This script mainly used for monitoring database alert log from a table. Instead of login in the Unix and start doing vi and tail to see the contents of alert log you can store it in a table.

Step 1: Find the path for alert log .

SQL>SELECT NAME, VALUE FROM v$parameter WHERE NAME = ‘background_dump_dest’;

Step 2: Create a directory:

CREATE OR REPLACE DIRECTORY alert_log_dir1 AS ‘<your path location>’;

Step 3 :Create a table:

CREATE TABLE alert_log1 ( text VARCHAR2(4000) )
ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY alert_log_dir1 ACCESS PARAMETERS (records delimited BY newline
nobadfile
nodiscardfile
nologfile) LOCATION(alert_log_dir1:’alert_SID.log’)
) REJECT LIMIT UNLIMITED;

Step 4 :Query the last 500 lines of alert.log:

SELECT TEXT FROM
(SELECT ROWNUM ID, LPAD(‘—->’,DECODE(YEAR,’2021′,0,6)) || text text FROM
(SELECT ROWNUM, SUBSTR(text, 21,6) YEAR, text FROM (SELECT ROWNUM, text
FROM sys.ALERT_LOG1 –where text like ‘%INF%’
ORDER BY ROWNUM DESC)
WHERE ROWNUM < 500)) ORDER BY ID DESC;

Recent Posts