Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476055] |
Tue, 21 September 2010 02:56  |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
I checked the server and port in Telnet and I am able to send mails in that.
But while using UTL_SMTP or UTL_MAIL in Oracle, I get the 421 transient error as below:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_MAIL", line 395
ORA-06512: at "SYS.UTL_MAIL", line 608
ORA-06512: at line 3
Please help.
|
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476068 is a reply to message #476066] |
Tue, 21 September 2010 03:56   |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
Please find the details below
Following is the telnet connection:

Connection Established:
220 FSMPCHHUBCAS01.FS.company.COM Microsoft ESMTP MAIL Service ready at Tue, 2
Sep 2010 14:22:09 +0530
helo fs.company.com
250 FSMPCHHUBCAS01.FS.company.COM Hello [10.32.13.111]
mail from:ramkumar.n@fs.company.com
250 2.1.0 Sender OK
rcpt to: ramkumar.n@fs.company.com
250 2.1.5 Recipient OK
SQL> declare
2 lConnection UTL_SMTP.CONNECTION;
3 begin
4 --lConnection := UTL_SMTP.OPEN_CONNECTION('FSMPCHHUBCAS01.FS.company.COM',25);
5 lConnection := UTL_SMTP.OPEN_CONNECTION('10.32.16.54',25);
6 DBMS_OUTPUT.PUT_LINE('Opened ok');
7
8 UTL_SMTP.HELO(lConnection, 'OSSWKS2R2YQ1S');
9 DBMS_OUTPUT.PUT_LINE('HELO ok');
10
11 UTL_SMTP.MAIL(lConnection, 'ramkumar.n@fs.company.com');
12 UTL_SMTP.RCPT(lConnection, 'ramkumar.n@fs.company.com');
13 DBMS_OUTPUT.PUT_LINE('Addressing ok');
14 end;
15 /
declare
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 5
Kindly let me know what the problem could be.
Thanks and Regards,
Ramkumar
|
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476073 is a reply to message #476071] |
Tue, 21 September 2010 04:12   |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
I am executing the code on my local machine. This is a client.
The telnet session was also on my local machine.
Is there any configuration in Oracle server that needs to be checked?
I have set up the SMTP_OUT_SERVER as below:
SQL> BEGIN
2 EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = "10.32.16.54:25"';
3 END;
4 /
PL/SQL procedure successfully completed.
Following is another code, getting the same 421 service error
SQL> DECLARE
2 --l_mailhost VARCHAR2(64) := 'FSMPCHMBX01.FS.company.COM';
3 l_mailhost VARCHAR2(64) := '10.32.16.54';
4 l_from VARCHAR2(64) := 'ramkumar.n@fs.company.com';
5 l_to VARCHAR2(64) := 'ramkumar.n@fs.company.com';
6 l_mail_conn UTL_SMTP.connection;
7 BEGIN
8 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
9 UTL_SMTP.helo(l_mail_conn, l_mailhost);
10 UTL_SMTP.mail(l_mail_conn, l_from);
11 UTL_SMTP.rcpt(l_mail_conn, l_to);
12 UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13))
13 UTL_SMTP.quit(l_mail_conn);
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 8
Regards,
Ramkumar
|
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476218 is a reply to message #476081] |
Wed, 22 September 2010 02:37   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ramkum wrote on Tue, 21 September 2010 12:04I checked just now...the Telnet connection between the DB Server and port 25 on the Mail server could not be established.
Is the blocking of Port 25 on the DB Server a required feature? Does it have to be unblocked or is there a workaround for this?
Regards,
Ramkumar
Typically, database servers are as shut off from the outside world as possible. This means that firewalls block access to most any ports.
Talk to your systems administrator if you need this port opened, and be prepared to have your request denied.
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476401 is a reply to message #476400] |
Thu, 23 September 2010 02:02   |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
Please correct me if I am wrong:
The Port no. 25 is the SMTP PORT which listens to incoming requests. This means we should be interested only if Port 25 on the mail server is unblocked. We have verified this through telnet (local PC to mail server).
The issue now is what prevents the DB Server from communicating with the mail server. On the DB Server, there is no point in checking Port 25 (realised this after checking it through PortQry and finding that it was LISTENING). Then, what exactly needs to be checked on the DB Server?
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476410 is a reply to message #476403] |
Thu, 23 September 2010 02:43   |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
ramoradba,
I used your function and following was the result:
SQL> set serverout on;
SQL> declare
2 msg varchar2(100);
3 begin
4 msg := ping_host('10.32.16.54','25');
5 dbms_output.put_line(msg);
6 end;
7 /
OK
PL/SQL procedure successfully completed.
Then I removed the exception handling:
SQL> create or replace FUNCTION PING_server(p_HOST_NAME VARCHAR2, p_PORT NUMBER DEFAULT 1000) RETURN
VARCHAR2
2 IS
3 tcpConnection UTL_TCP.CONNECTION; --TCP/IP connection to the server
4 C_PING_OK CONSTANT VARCHAR2(10) := 'OK';
5 C_PING_ERROR CONSTANT VARCHAR2(10) := 'ERROR';
6 BEGIN
7 tcpConnection := UTL_TCP.open_connection(remote_host => p_HOST_NAME, remote_port => p_PORT)
;
8
9 UTL_TCP.close_connection(tcpConnection);
10 --Que raro...el host tiene abierto el puerto 1000...
11 RETURN C_PING_OK||p_port;
12 END PING_server;
13
14 /
Function created.
SQL> set serverout on;
SQL> declare
2 msg varchar2(100);
3 begin
4 msg := ping_server('10.32.16.54','25');
5 dbms_output.put_line(msg);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.UTL_TCP", line 28
ORA-06512: at "SYS.UTL_TCP", line 257
ORA-06512: at "PREMIAUAE.PING_SERVER", line 7
ORA-06512: at line 4
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476438 is a reply to message #476431] |
Thu, 23 September 2010 05:15   |
ramkum
Messages: 15 Registered: September 2010 Location: Chennai
|
Junior Member |
|
|
Sriram,
You are taking us back to the beginning..
Like we saw 2 days ago, using Telnet works from my PC to mail server, but not from DB Server to mail server.
Like Michel said, either there is an outward block on the DB server or inward block on the mail server.
It will be very useful if you can add to that or help us narrow down, rather than disputing our findings or quoting from others.
Appreciate your willingness to help.
Regards,
Ramkumar
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476440 is a reply to message #476438] |
Thu, 23 September 2010 05:26   |
John Watson
Messages: 8981 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Hello again. How about some lateral thinking? You said thisQuote:On the DB Server, there is no point in checking Port 25 (realised this after checking it through PortQry and finding that it was LISTENING). That suggests that there is in fact an smtp server running on your database server node. Perhaps you could adjust your smtp_out_server parameter to point to that, and let it handle the mail? It may be configured (and permitted) to relay mail on to your corporate smtp server. If it isn't, perhaps your mail administrators can do that - without having to punch holes in any firewalls.
|
|
|
|
|
|
|
|
|
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476574 is a reply to message #476561] |
Fri, 24 September 2010 01:22   |
John Watson
Messages: 8981 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I have done this several times: your mail administrators create a normal user account, and you write PL/SQL code to send mail messages to it. There are two problems with Exchange, though: first, many Exchange administrators do not enable POP3 or IMAP4, and you must persuade them to do so. Second, if they do enable POP or IMAP, the way Exchange handles security is a bit odd. Heaven help you if they use TLS. Basically, Exchange is harder to talk to than any other email server.
Of course, you will have to make sure that the POP or IMAP ports are accessible from your database server. Can you telnet to ports 110 or 143?
|
|
|
|
|
|