Re: file alert

From: Morten Egan <meg_at_miracleas.dk>
Date: Mon, 04 Oct 2004 23:11:06 +0200
Message-ID: <4161BC6A.1030602@miracleas.dk>

 

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_at_mydatabasemachine.com[1]'; mail_to varchar2(256) := 'mydbaemail_at_mycompany.com[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_at_miracleas.dk>[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] --
http://www.freelists.org/webpage/oracle-l[5] --
http://www.freelists.org/webpage/oracle-l[6] 

• Links --- 1 mailto:myemail_at_mydatabasemachine.com 2 mailto:mydbaemail_at_mycompany.com 3 mailto:meg_at_miracleas.dk 4 http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ 5 http://www.freelists.org/webpage/oracle-l 6 http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 04 2004 - 16:10:44 CDT