How to send email from Oracle server.

When we configure Oracle E-MAIL notification method through OEM then it is not include notification for DB shutdown or startup events. For these two events we have to configure manually.


Connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password:
Connected.
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'smtp_exchange_server_name' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

Create two trigger for startup and shutdown event on database which send us email notification

SQL> create or replace trigger scott.db_shutdown
2 before shutdown on database
3 begin
4 sys.utl_mail.send (
5 sender =>'xyz@xyz.com',
6 recipients =>'xyz@xyz.com',
7 subject => 'Oracle Database DOWN',
8 message => 'May be DB Down ’||
‘ but also contact to DBA for further details. ’
9 );
10 end;
11 /

Trigger created.

SQL> create or replace trigger scott.db_startup
2 after startup on database
3 begin
4 sys.utl_mail.send (
5 sender =>'xyz@xyz.com',
6 recipients =>'xyz@xyz.com',
7 subject => 'Oracle Database UP',
8 message => 'DB OPEN .'
9 );
10 end;
11 /

Trigger created.