Hi all,

Is it possible to configure Oracle 9iR1 to send an e-mail whenever the
instance shuts down? I know that I could create a shell script to shut
the instance down and send a mail, but I can't be sure that folks will
always use the script. I'm wondering whether there's an Oracle file
somewhere that always gets read during an instance shutdown, to which I
could add a line that would send the mail. Maybe there's a PL/SQL
procedure that would handle this... what's the best way to get the
system to send this e-mail?

TIA
Tarby

Tarby777 wrote:
> Hi all,
>
> Is it possible to configure Oracle 9iR1 to send an e-mail whenever the
> instance shuts down? I know that I could create a shell script to shut
> the instance down and send a mail, but I can't be sure that folks will
> always use the script. I'm wondering whether there's an Oracle file
> somewhere that always gets read during an instance shutdown, to which I
> could add a line that would send the mail. Maybe there's a PL/SQL
> procedure that would handle this... what's the best way to get the
> system to send this e-mail?
>
> TIA
> Tarby

Oracle Enterprise Manager (comes free with Oracle) can do that provided
you
- set up an OEM repository (= an Oracle database)
- enable the 'database up/down event' for the database you wantg to
monitor
- OEM can email an alert and/or page it to your cellphone

Please do NOT reinvent the wheel to replace functionality you already
have.

--
Sybrand Bakker
Senior Oracle DBA

Tarby777 wrote:
> Hi all,
>
> Is it possible to configure Oracle 9iR1 to send an e-mail whenever the
> instance shuts down? I know that I could create a shell script to shut
> the instance down and send a mail, but I can't be sure that folks will
> always use the script. I'm wondering whether there's an Oracle file
> somewhere that always gets read during an instance shutdown, to which I
> could add a line that would send the mail. Maybe there's a PL/SQL
> procedure that would handle this... what's the best way to get the
> system to send this e-mail?
>
> TIA
> Tarby

What operating system? On a Unix system you could monitor the Oracle
processes periodically.

[root@MY_SERVER ~]# ps -eaf|grep MY_DB
oracle 5561 1 0 Jul29 ? 00:27:32 ora_pmon_MY_DB
oracle 5563 1 0 Jul29 ? 00:29:00 ora_psp0_MY_DB
oracle 5565 1 0 Jul29 ? 00:11:02 ora_mman_MY_DB
oracle 5567 1 0 Jul29 ? 00:29:27 ora_dbw0_MY_DB
oracle 5569 1 0 Jul29 ? 00:42:10 ora_lgwr_MY_DB
oracle 5571 1 0 Jul29 ? 01:04:15 ora_ckpt_MY_DB
oracle 5573 1 0 Jul29 ? 00:24:20 ora_smon_MY_DB
oracle 5575 1 0 Jul29 ? 00:00:13 ora_reco_MY_DB
oracle 5577 1 0 Jul29 ? 00:44:24 ora_cjq0_MY_DB
oracle 5579 1 0 Jul29 ? 00:35:19 ora_mmon_MY_DB
oracle 5581 1 0 Jul29 ? 01:03:16 ora_mmnl_MY_DB
oracle 5583 1 0 Jul29 ? 00:00:42 ora_d000_MY_DB
oracle 5585 1 0 Jul29 ? 00:01:08 ora_s000_MY_DB
oracle 5589 1 0 Jul29 ? 00:03:54 ora_qmnc_MY_DB
oracle 5602 1 0 Jul29 ? 00:07:14 ora_q000_MY_DB
oracle 12901 1 0 15:04 ? 00:00:00 ora_q003_MY_DB
oracle 13094 1 1 15:18 ? 00:00:00 ora_q002_MY_DB
oracle 13108 1 6 15:19 ? 00:00:01 ora_j000_MY_DB

[root@ACBristol105 ~]# ps -eaf|grep dbmon | grep -v grep |wc -l
17
[root@MY_SERVER ~]#

The above database has 17 processes with no-one connected. If that
number falls, I know there's an issue.

HTH

-g

Tarby777 wrote:
> Hi all,
>
> Is it possible to configure Oracle 9iR1 to send an e-mail whenever the
> instance shuts down? I know that I could create a shell script to shut
> the instance down and send a mail, but I can't be sure that folks will
> always use the script. I'm wondering whether there's an Oracle file
> somewhere that always gets read during an instance shutdown, to which I
> could add a line that would send the mail. Maybe there's a PL/SQL
> procedure that would handle this... what's the best way to get the
> system to send this e-mail?
>
> TIA
> Tarby
>

I agree with Sybrand's suggestion to use OEM to monitor this condition
for you. That would be choice #1.

Choice #2 (IMHO) is to create a BEFORE SHUTDOWN trigger to implement
this functionality. The trigger will only fire on "normal" shutdowns and
will not capture crashes. That is why OEM is preferable.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Brian Peasland wrote:
> Tarby777 wrote:
>> Hi all,
>>
>> Is it possible to configure Oracle 9iR1 to send an e-mail whenever the
>> instance shuts down? I know that I could create a shell script to shut
>> the instance down and send a mail, but I can't be sure that folks will
>> always use the script. I'm wondering whether there's an Oracle file
>> somewhere that always gets read during an instance shutdown, to which I
>> could add a line that would send the mail. Maybe there's a PL/SQL
>> procedure that would handle this... what's the best way to get the
>> system to send this e-mail?
>>
>> TIA
>> Tarby
>>
>
> I agree with Sybrand's suggestion to use OEM to monitor this condition
> for you. That would be choice #1.
>
> Choice #2 (IMHO) is to create a BEFORE SHUTDOWN trigger to implement
> this functionality. The trigger will only fire on "normal" shutdowns and
> will not capture crashes. That is why OEM is preferable.
>
> HTH,
> Brian
>

Forgot the link....here is the documentation on creating said trigger:

http://download-east.oracle.com/docs...4.htm#i2235611


Cheers,
Brian


--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown