ORA-29278: SMTP transient error: 421 Service not available

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

One of our production database I saw the below error message

ORA-29278: SMTP transient error: 421 Service not available

Db: 10.2.0.4.0 (cpu jan-09 applied)
OS: Win32bit

This error occur when SMTP SERVER IS NOT REACHABLE so it is not oracle error.
when UTL_SMTP package not able to connect SMTP server then above error will occur.


Suggestion:
1. First need to check without involving oracle we are able to connect smtp server through TELNET command

for eg:
C:\>telnet SMTP_SERVERNAME.LOCAL
Connecting To SMTP_SERVERNAME.LOCAL...Could not open connection to the host, on
port 23: Connect failed

You will see the above error it means system is not able to connect smtp server that is why the above error occur.

Check the Metalink Note for more details: 604763.1

Email Notification: Export Log Error

Steps

1. Create directory
2. Create table in database for exportlog through external table
3. Create procedure for sent to email or check err msg In exportlog table
4. Create sql script which daily import data from exportlog file to exportlog table.
5. Schedule SQL script to run after export command.

SQL> conn system/oracle
SQL> create directory log as 'c:\log';
SQL> grant read,write on directory LOG to username;

SQL> -- sql script
Create sql script which create exportlog table and import exportlog file to database

DROP TABLE EXPORTLOG;

CREATE TABLE exportlog (msg varchar2(200))
organization external (
type oracle_loader
default directory log
access parameters (
records delimited by newline
)
location ('exportlog.log')
)
reject limit 1000;


exec pro_exportlog;

SQL>-- Create procedure which check error msg in exportlog table and sent email

Procedure: PRO_EXPORTLOG


CREATE OR REPLACE PROCEDURE "SCOTT"."PRO_EXPORTLOG"
AS
SendorAddress Varchar2(30) := 'xxx.xxxx.local';
ReceiverAddress varchar2(30) := 'm.taj@fakhruddin.ae';
EmailServer varchar2(30) := 'xxx.xxxxxx.local';
Port number := 25;
conn UTL_SMTP.CONNECTION;
v_result varchar2(2000);

BEGIN
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress );
utl_smtp.open_data (conn);
utl_smtp.write_data(conn, chr(13));
utl_smtp.write_data(conn, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )||chr(13));
utl_smtp.write_data(conn, 'From:'||SendorAddress||chr(13));
utl_smtp.write_data(conn, 'To: '||ReceiverAddress ||chr(13));
utl_smtp.write_data(conn, 'Subject: Mail Through ORACLE Database!' ||chr(13));
utl_smtp.write_data(conn, chr(13));
FOR rec IN (select msg from exportlog
where msg LIKE 'EXP-%'
OR msg LIKE '%ORA-%'
OR msg LIKE '%warning%')
LOOP

v_result := rec.msg;
utl_smtp.write_data( conn,v_result||chr(13) );
END LOOP ;
UTL_SMTP.close_data(conn);
UTL_SMTP.quit(conn);
END;

----------------------------------------------------
4. Create bat file and schedule this sql script


I tested above all and the result:

Date: 05 Feb 08 11:26:24
From:xxx.xxxxx.local
To: m.taj@xxxxxxxx.xx
Subject: Mail Through ORACLE Database!

Export terminated successfully without warnings.
----------------------------------------------------
Date: 05 Feb 08 11:23:47
From:xxxx.xxxxxx.local
To: m.taj@xxxxx.xx
Subject: Mail Through ORACLE Database!

EXP-00011: FGC.YYYYY does not exist
Export terminated successfully with warnings.

Any query or comment are welcome.