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: GrepOra.com best blog ever! PL/SQL procedure successfully completed.
greporadb> @find_alert Enter value for inst: 1 Enter value for host: Enter value for message: GrepOra.com ORIGINATING_TIMESTAMP Inst# HOST_ID MESSAGE_TEXT ---------------------------------------- ----- --------------- --------------------------------------- 13/06/16 16:53:13,699 +00:00 1 greporasrvr GrepOra.com 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 GrepOra.com best blog ever!
And the scripts:
## write_alert.sql EXEC SYS.DBMS_SYSTEM.KSDWRT(2, '&TEXT');
## find_alert.sql
col ORIGINATING_TIMESTAMP for a40
col host_id for a15
col inst_id for 99
col MESSAGE_TEXT for a100
set linesize 500
SELECT
originating_timestamp,inst_id,host_id,message_text
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!
Matheus.
