Searching entries on Alert.log: A Better Way

Hi all!
As the oldest readers know, someday I had to found some entries in the alertlog and I had a really big log. So I jerrry-ringed some scripts for grepping alert with auxiliar files and etc.
I can see the posts here: Grepping Alert by Day  and Grepping Entries from Alert.log.

So… They are functional, but probably the worst ways to get it. I didn’t know and was innocent to not search by the view x$dbgalertext.
There is also possible to write on alert through the procedure SYS.DBMS_SYSTEM.KSDWRT.

Ok, so let me fix this situation with theese two good guys: @write_alert and @find_alert

greporadb> @write_alert
Enter value for text: best blog ever!
PL/SQL procedure successfully completed.
greporadb> @find_alert
Enter value for inst: 1
Enter value for host: 
Enter value for message:
ORIGINATING_TIMESTAMP                    Inst# HOST_ID         MESSAGE_TEXT                           
---------------------------------------- ----- --------------- ---------------------------------------
13/06/16 16:53:13,699 +00:00                 1 greporasrvr best blog ever!            
1 row selected.

In alert log we can see:

[oracle@greporasrvr trace]$ tail -3 alert_GREPORADB.log
Archived Log entry 29824 added for thread 1 sequence 15786 ID 0x87039d01 dest 1:
Mon Jun 13 16:53:13 2016 best blog ever!

And the scripts:

## write_alert.sql
## find_alert.sql
col host_id for a15
col inst_id for 99
col MESSAGE_TEXT for a100
set linesize 500
FROM  x$dbgalertext
where 1=1
and inst_id like '%&INST%'
and upper(host_id) like upper('%&host%')
and upper(message_text) like upper('%&message%')
order by record_id asc;

Ok, fixed!
See ya!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.