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: 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s