This discussion is archived
11 Replies Latest reply: Mar 21, 2013 4:59 AM by EdStevens RSS

How to setup email notification in oracle 11g using custome OS script.

992402 Newbie
Currently Being Moderated
oracle version 11.2.0.3
os version solaris 10.

i want to configure my oracle 11g database to send output of shell script to email address after every 30minuts.
my script is

#!/bin/sh
DATE=`/usr/bin/date '+%d%m%Y'`
Filetarget=helth.$DATE.txt
col spoolname new_value spoolname
select 'health_'||to_char(sysdate, 'yymmddhh24miss') spoolname from dual;
spool '&spoolname'


prompt**---------------Database General Information-------------------------------**
SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
column "Host Name" format a15;
column "Host Address" format a15;
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;

prompt**-----------------Database SGA Parameters-------------------------------**
set line 200;
col name format a30;
col value format a20;
SELECT name, value from v$parameter
where name in ('sga_max_size', 'shared_pool_size', 'large_pool_size', 'db_cache_size', 'db_block_size', 'log_buffer');

prompt**---------------DB Characterset Information-------------------------------**
Select * from nls_database_parameters;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
col member format A40 heading "Redolog Files";
set line 200;
col archived format a15;
col status format a10;
col first_time format a20;
select a.group#, a.member, b.archived, b.status, b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;

prompt**---------------DB Profile and Default Information----------------------------**

set line 200;
col username format a25;
col profile format a20;
col default_tablespace format a25;
col temporary_tablespace format a25;
Select username, profile, default_tablespace, temporary_tablespace from dba_users;

prompt**---------------Monitoring Schema Growth Rate---------------------------**
select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

prompt**------------------Largest object in Database------------------------------**
SET LINE 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a30;
col BYTES format a30;
col TABLESPACE_NAME FORMAT A30;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;

prompt**--------------Number of Objects Created within 7 days-------------------**
select count(1) from user_objects where CREATED >= sysdate - 7;

prompt**--------------Check any Long job Currently running in Database----------**
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;

prompt**-----------------Monitor DML Lock------------------------------------**
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait_Time"
FROM
v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')
AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;

prompt**-----------------Track Redolog Generation-------------------------------**
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;

prompt**------------------Tablespace Information--------------------------------**
col "Tablespace" for a22
col "SizeMb" for 99,999,999
col "FreeMb" for 99,999,999
select b.tablespace_name "Tablespace" , tbs_size "SizeMb", a.free_space "FreeMb"
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

prompt**---------------Shows Used/Free Space Per Datafile--------------------------**
prompt

set linesize 200
col file_name format a50 heading "Datafile Name"

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

TTI off

prompt**---------------Report Tablespace < 10% free space----------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

prompt**------CPU and I/O consumption----**
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;

prompt**---------------File I/O statistics-------------------------------**

prompt

set linesize 150
col name format a50 heading "Datafile Name"
select name,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg Time" from v$filestat,v$datafil
e where v$filestat.file#=v$datafile.file#;
set feedback on
prompt

rem
rem Filename: sga_stat.sql
rem Purpose: Display database SGA statistics
rem prompt Recommendations:
prompt =======================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
redoent number;
redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
dbms_output.put_line('SGA CACHE STATISTICS');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
spool off
!cp //tmp/oracle/helth.txt /tmp/oracle/$Filetarget
exit

can any body help me.

thanks in advance..
  • 1. Re: How to setup email notification in oracle 11g using custome OS script.
    damorgan Oracle ACE Director
    Currently Being Moderated
    Help you with what?

    There is no need to invoke a shell script ... you can send the email using UTL_SMTP which is a fully documented built-in package assuming your network is so insecure that the required port is open.
  • 2. Re: How to setup email notification in oracle 11g using custome OS script.
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    Just schedule a scheduler job and send an email with attachment of your spool file .

    http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#attachment

    Salman
  • 3. Re: How to setup email notification in oracle 11g using custome OS script.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Why configure the database for that? It does not initiate the shell script check. It does not own the code. If a client runs a process against the database, then the client needs to assume responsibility for that. And seeing that the client writes (spools) to a local file, it makes no sense to expect the database to mail it.

    Two basic architectural choices.

    Make it a database owned process. Stored procedure. Write report data to a GTT (temp table). Then format a mail using GTT data and send the mail. Schedule the server process via DBMS_SCHEDULER or DBMS_JOB.

    Keep it as a client process. Write more shell script code to format a mail and send the mail (refer to the mail command on Solaris). Schedule it via cron.

    There are very clear boundaries between client and server, in client-server architecture. Crossing these boundaries invariable result in less than robust code, with a lot of additional complexities and unnecessary moving parts.
  • 4. Re: How to setup email notification in oracle 11g using custome OS script.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    damorgan wrote:

    There is no need to invoke a shell script ... you can send the email using UTL_SMTP which is a fully documented built-in package assuming your network is so insecure that the required port is open.
    An open port is not what makes a network insecure. It is what application protocol is used, what service is listening on that port, and how that service is configured.
  • 5. Re: How to setup email notification in oracle 11g using custome OS script.
    992402 Newbie
    Currently Being Moderated
    sir billy thanks for your responce.
    i am new to oracle technology so please can you tell me in simple words (step by step procedure) if i want to send output of below simple querry through email and i want it keep repeating after every 30 minuts. i will be realy thankful to you.


    SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
    SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
    FROM sys.v_$instance;
  • 6. Re: How to setup email notification in oracle 11g using custome OS script.
    992402 Newbie
    Currently Being Moderated
    i am new to oracle technology so please can you tell me in simple words (step by step procedure) if i want to send output of below simple querry through email and i want it keep repeating after every 30 minuts. i will be realy thankful to you.

    SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
    SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
    FROM sys.v_$instance;
  • 7. Re: How to setup email notification in oracle 11g using custome OS script.
    EdStevens Guru
    Currently Being Moderated
    989399 wrote:
    oracle version 11.2.0.3
    os version solaris 10.

    i want to configure my oracle 11g database to send output of shell script to email address after every 30minuts.
    my script is

    #!/bin/sh
    DATE=`/usr/bin/date '+%d%m%Y'`
    Filetarget=helth.$DATE.txt
    col spoolname new_value spoolname
    select 'health_'||to_char(sysdate, 'yymmddhh24miss') spoolname from dual;
    spool '&spoolname'


    prompt**---------------Database General Information-------------------------------**
    SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
    SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
    FROM sys.v_$instance;
    column "Host Name" format a15;
    <snip>

    What is this?
    starts off looking like a shell script, but then you throw in a boat load of sql statements and sqlplus directives. The OS shell process isn't going to have a clue as to what to do with it. You haven't even tested this, have you? Or if you have, you would have received a ton of error messages from the shell processor, saying "-bash: .... command not found"

    If you want to do this in a shell script (questionable approach from the beginning), then before you even begin to worry about how to mail the output, you are going to have to learn how to execute sqlplus.

    But as others have said, if you are going to do this from a shell script, you don't need -- and would not want -- to have oracle send the mail. All nix operating systems have their own sendmail capabilities, and that would be the logical way to email the output of a shell script.  Also have you considered how* you are going to have this run "every 30 minutes"?

    The alternative (and many would say, "better") approach would be to scrap the shell script entirely. Write a pl/sql procedure that gathers the data and sends the mail using oracle's own mail procedures. Then schedule that using oracle's scheduler.

    But beyond all that, looking at what that code is attempting to do, I'd question the fundamental premise of emailing that "every 30 minutes". A large amount of the information being gathered is simply not going to be changing that frequently. And when it does change, you are going to know it because it is going to require a specific action on your part. And a most of what is more dynamic doesn't -- or shouldn't -- need to be watched in 30 minute increments. And all of the stuff that is worth watching is already being watched by OEM. All you need to do is configure the notification process.
  • 8. Re: How to setup email notification in oracle 11g using custome OS script.
    992402 Newbie
    Currently Being Moderated
    You are looking very strike DBA. :)
    actully in start i just loaded a shell script and want to email output of it but when i recive replies as i said before i am new in oracle world so that replay was above my level so wn,t undestand than for simplicity i just copy tow commands from scripts and any body tell me step by step procedure how to email output of above mention statments thats why i change my statments to make thing easy for me to undestand. hope you got my msg.. now if you can tell me step by step procdure than i will be realy thankfull.

    thanks.
  • 9. Re: How to setup email notification in oracle 11g using custome OS script.
    992402 Newbie
    Currently Being Moderated
    ???????????????????
  • 10. Re: How to setup email notification in oracle 11g using custome OS script.
    EdStevens Guru
    Currently Being Moderated
    989399 wrote:
    ???????????????????
    Patience, Grasshopper

    This forum is not a chat line, and it is not paid support.

    Everyone here has a job for which they are paid, and this forum is not it.

    No one is responsible for monitoring it and giving a quick response.

    Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

    Your original post went up in the middle of the night for half the world.

    No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
  • 11. Re: How to setup email notification in oracle 11g using custome OS script.
    EdStevens Guru
    Currently Being Moderated
    989399 wrote:
    You are looking very strike DBA. :)
    I have no idea what it means to "look strike".
    actully in start i just loaded a shell script and want to email output of it but when i recive replies as i said before i am new in oracle world so that replay was above my level so wn,t undestand than for simplicity i just copy tow commands from scripts and any body tell me step by step procedure how to email output of above mention statments thats why i change my statments to make thing easy for me to undestand. hope you got my msg.. now if you can tell me step by step procdure than i will be realy thankfull.

    thanks.
    You might want to get your keyboard checked out. There seem to be a lot of keys that are malfunctioning.

    You want "step-by-step" procedure for what, exactly?

    To be able to execute those sql statements and sqlplus commands from a shell script?
    To be able to send an email from a shell script?

    Or the best way (regardless of technique) to do some pro-active monitoring and auditing of your database?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points