Re: file alert
- From: Morten Egan <meg@xxxxxxxxxxxx>
- To: Jamie Kinney <OracleDude@xxxxxxxxx>
- Date: Mon, 04 Oct 2004 23:11:06 +0200
Hi,
I should have just supplied the code straight away, sorry bout that.
Here's how to do it.
First of, in your dba schema (sys, system ... whatever you use) create a
directory pointing to background_dump_dest.
Example:
create or replace directory bdump_dir as '/u01/admin/MYDB/bdump';
(by the way, all dba account's should have directory access to the most
important places such as bdump, udump etc.)
Next we create a small procedure to check the filesize, and if it's bigger
than let's say 10000 bytes, we switch to a new file, and send an email to
ourself, telling us what have happened.
create or replace procedure check_my_alert_file (file_limit number default
10000)
as
log_exist boolean := false;
log_size_in_bytes number;
log_size_in_blocks number;
alert_log_name varchar2(256);
alert_log_name_moved varchar2(256);
-- Mail vars
smtp_conn utl_smtp.connection;
smtp_server varchar2(256) := 'my.smtp.com';
crlf varchar2(2) := chr(13) || chr(10);
mail_from varchar2(256) := 'myemail@xxxxxxxxxxxxxxxxxxxxx[1]';
mail_to varchar2(256) := 'mydbaemail@xxxxxxxxxxxxx[2]';
full_mail varchar2(4000);
begin
select 'alert_'||name||'.log'
into alert_log_name
from v$database;
-- Get the file attributes
utl_file.fgetattr('BDUMP_DIR', alert_log_name, log_exist,
log_size_in_bytes, log_size_in_blocks);
if log_exist = true then
if log_size_in_bytes >file_limit then
alert_log_name_moved :=
alert_log_name||'.'||to_char(sysdate,'YYYYDDMM');
utl_file.frename('BDUMP_DIR', alert_log_name, 'BDUMP_DIR',
alert_log_name_moved, true);
-- Let's send a mail telling us that we switched the alert log
smtp_conn := utl_smtp.open_connection(smtp_server,25);
-- smtp handshake
utl_smtp.helo(smtp_conn, smtp_server);
-- Mail coming from
utl_smtp.mail(smtp_conn, mail_from);
-- Mail going to
utl_smtp.rcpt(smtp_conn, mail_to);
full_mail := 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss'
)||crlf || 'From:'|| mail_from || crlf || 'Subject: Alert Log switched' ||
crlf || 'To: '||mail_to || crlf || '' || crlf || 'Alert log was moved to:
'||alert_log_name_moved;
utl_smtp.data(smtp_conn, full_mail);
utl_smtp.quit(smtp_conn);
end if;
end if;
end;
/
and then we schedule that to run, at what ever interval we want from
dbms_jobor if we're on 10g dbms_scheduler.
Regards,
Morten
Jamie Kinney wrote:
How do you check the alert log via SQL*Plus? Are you suggesting UTL_FILE?
-Jamie On Mon, 04 Oct 2004 20:05:56 +0200, Morten Egan <meg@xxxxxxxxxxxx>[3]
wrote: What version of oracle are you using? The reason is that if you're
using 9.2. and above,you can check it from within the database instead of
having to create shell scripts (also portable to windows then) /morten Seema
Singh wrote: Hello, I want to setup scripton linux whenever alert log >1GB
wehave to get alert.Does anyone send similar kidn of script ? when I'm
executing following command its show error frees1=`du -sk /alert
logdestination/alert_sid.log| tail -1 | awk '{printf "%s\n",$2}'`; export
frees1 thanks -Seema
_________________________________________________________________ Express
yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/[4] --
//www.freelists.org/webpage/oracle-l[5] --
//www.freelists.org/webpage/oracle-l[6]
--- Links ---
1 mailto:myemail@xxxxxxxxxxxxxxxxxxxxx
2 mailto:mydbaemail@xxxxxxxxxxxxx
3 mailto:meg@xxxxxxxxxxxx
4 http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
5 //www.freelists.org/webpage/oracle-l
6 //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
