Home » SQL & PL/SQL » SQL & PL/SQL » Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 (Oracle 10.2.0.10, Windows XP SP 3)
Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476055] Tue, 21 September 2010 02:56 Go to next message
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 #476057 is a reply to message #476055] Tue, 21 September 2010 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
421 Service not available

This is NOT an Oracle error, just your SMPT server is not there.

Regards
Michel
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476065 is a reply to message #476057] Tue, 21 September 2010 03:37 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
Michel, I can send mail using the same server name and port number in Telnet.
I also have grant privilege to UTL_MAIL and UTL_SMTP.

Regards,
Ramkumar
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476066 is a reply to message #476065] Tue, 21 September 2010 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No one is able to debug code that he can't see.
All I can say is:
1/ You think you access the same smtp server but you don't
2/ You do seomthing wrong.
3/ It works for me

Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
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 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
Please find the details below


Following is the telnet connection:

/forum/fa/8284/0/

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 #476069 is a reply to message #476068] Tue, 21 September 2010 04:01 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
I have execute privilege (this has been given to public) on UTL_SMTP.
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476071 is a reply to message #476068] Tue, 21 September 2010 04:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Is your database also run from your local machine?
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476072 is a reply to message #476071] Tue, 21 September 2010 04:12 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
You will get an ora-29278 if you have not set your smtp_out_server parameter.
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 Go to previous messageGo to next message
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 #476077 is a reply to message #476073] Tue, 21 September 2010 04:42 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Can you telnet from the database server to the mailserver as well?
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476081 is a reply to message #476077] Tue, 21 September 2010 05:04 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
I 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
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476089 is a reply to message #476081] Tue, 21 September 2010 05:44 Go to previous messageGo to next message
ram_rocks23
Messages: 6
Registered: September 2010
Location: Bangalore
Junior Member
You need to verify whether the SMTP port is open or not.
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 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ramkum wrote on Tue, 21 September 2010 12:04
I 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 #476257 is a reply to message #476218] Wed, 22 September 2010 06:37 Go to previous messageGo to next message
ram_rocks23
Messages: 6
Registered: September 2010
Location: Bangalore
Junior Member
No you need to unblock. there is no workaround for this.
Contact your System admin. He might help you configuring the mail server port.

Cheers
Ram!
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476400 is a reply to message #476257] Thu, 23 September 2010 01:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Since you are replying to my post, I'd expect some extra info in your reply.
Just as in your previous post, you once again just repeat what I said.

Besides, it is not the mail server's port that needs configuring, it is the database server blocking outgoing traffic on port 25.

[Edit: Added the port-stuff]

[Updated on: Thu, 23 September 2010 01:49]

Report message to a moderator

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 Go to previous messageGo to next message
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 #476402 is a reply to message #476401] Thu, 23 September 2010 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
The issue now is what prevents the DB Server from communicating with the mail server.

You may have a firewall that does not allow:
- a request to get out from your db server
- a request to get into your smtp server from the db one

Regards
Michel
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476403 is a reply to message #476402] Thu, 23 September 2010 02:17 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
If I read it proper

1) Are these on the same session ?
2) You can use ping_host function to know the hand shake through the DB
(I mean access through Db on the host/port working status)
create FUNCTION PING_host(p_HOST_NAME VARCHAR2, p_PORT NUMBER DEFAULT 1000) RETURN VARCHAR2
  IS
    tcpConnection  UTL_TCP.CONNECTION;  --TCP/IP connection to the server
    C_PING_OK           CONSTANT VARCHAR2(10)  := 'OK';
C_PING_ERROR        CONSTANT VARCHAR2(10)  := 'ERROR';
  BEGIN
    tcpConnection := UTL_TCP.open_connection(remote_host => p_HOST_NAME, remote_port => p_PORT);

    UTL_TCP.close_connection(tcpConnection);
    --Que raro...el host tiene abierto el puerto 1000...
    RETURN C_PING_OK||p_port;
  EXCEPTION
    WHEN UTL_TCP.NETWORK_ERROR THEN
      IF( UPPER(SQLERRM) LIKE '%HOST%' )THEN --Host inaccesible
        RETURN C_PING_ERROR;
      ELSIF(UPPER(SQLERRM) LIKE '%LISTENER%' )THEN

        RETURN C_PING_OK;
      ELSE--Mensaje SQLERRM desconocido: este es un error grave!
        RAISE;
      END IF;
  END PING_host;


[The above function created by one the member of Orafaq And its working fine)

Sriram
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 Go to previous messageGo to next message
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 #476431 is a reply to message #476066] Thu, 23 September 2010 04:55 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Tue, 21 September 2010 14:10
No one is able to debug code that he can't see.
All I can say is:
1/ You think you access the same smtp server but you don't
2/ You do seomthing wrong.
3/ It works for me

Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Quote:

ORA-29260: network error: TNS:no listener

It confirmed now.

Quote:
You may have a firewall that does not allow:
- a request to get out from your db server
- a request to get into your smtp server from the db one

Regards
Michel


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:385218740472#2113673500346587006


So use Telnet on that port and show as it is.(Not with your data as above)

And show
1)
 sho parameter smtp_out_server 

run the above at sql prompt


Good Luck

Sriram
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 Go to previous messageGo to next message
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 #476439 is a reply to message #476438] Thu, 23 September 2010 05:24 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
You are taking us back to the beginning..

Yes Thats why I quoted that .
Have you replied to all before ?
Did you Contact your System admin.?

Its not the question of quoting ! Have you read them and answered ?

You have stated that " 'ALTER SESSION SET smtp_out_server = "10.32.16.54:25"' "
Are you on the same session still ? did you get it what I am asking?
At the first 4 replies only Michel said that is some thing wrong with you Db and smtp server connections ?right ...
so if you got any reply you should try that atleast right ? have you replied for any of Michel`s questions/suggestions ?


sriram

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 Go to previous messageGo to next message
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 #476441 is a reply to message #476439] Thu, 23 September 2010 05:28 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And Your function returned with a connection error right ?
And more over have you replied to this
" sho parameter smtp_out_server "

And more over If it would be at your server side configuration (nothing to do with DB) In which way we can help you ?

Hope I am clear

sriram

[Updated on: Thu, 23 September 2010 05:31]

Report message to a moderator

Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476442 is a reply to message #476441] Thu, 23 September 2010 05:36 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And I simply quit from this topic As OP not interested (By reported feature)

sriram
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476443 is a reply to message #476440] Thu, 23 September 2010 05:39 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
Thanks John. SMTP service was enabled in the DB server as part of our trials, and that's how Port 25 may be listening on that.

Right now, I have reached the stage where I am trying to get our IT people to add our Db Server's IP address to the list of valid IP addresses in the receive connector of the exchange server. I will try to get that done and post back if that works.

http://technet.microsoft.com/en-us/library/aa996395.aspx
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476468 is a reply to message #476443] Thu, 23 September 2010 08:38 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
NO WAY OUT.
1. Adding DB Server's IP Address to Exchange Server's receive connector -> Rejected by IT Helpdesk.
2. The DB Server's windows firewall was never turned on.

Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476471 is a reply to message #476468] Thu, 23 September 2010 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unless & until DB server can telnet to port 25 on mail server; no mail messages will ever be delivered.

[Updated on: Thu, 23 September 2010 08:47]

Report message to a moderator

Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #476561 is a reply to message #476471] Fri, 24 September 2010 00:10 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
This is the plan now:
I am going to ask IT to set up a Microsoft Outlook account in the DB Server, that will use the mail server 10.32.16.54. If they agree, let them find a way of doing that and then, communication between the DB Server and Port 25 of the mail server should be possible.
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 Go to previous messageGo to next message
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?
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #477062 is a reply to message #476574] Tue, 28 September 2010 04:08 Go to previous messageGo to next message
ramkum
Messages: 15
Registered: September 2010
Location: Chennai
Junior Member
Thanks to all of you.

It is working now.

McAfee Antivirus on the DB Server was blocking the sending of mails. After unblocking 'Prevent Mass Mailing worms from sending mail....' In VirusScan -> Access Protection, mailing from Oracle is working.


Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #477301 is a reply to message #477062] Thu, 30 September 2010 00:41 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This sounds rather quirky.. Why would a database server need a virus scanner?
This might mean that it has too many open ports.
Either that, or I have grown used to using *nix servers instead of Windows.
Re: Problem in sending mail using Oracle: ORA-29278 SMTP transient error: 421 [message #477343 is a reply to message #477301] Thu, 30 September 2010 05:37 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The Windows people seem to install them religiously on everything they get their hands on (even multiple ones). Have gotten the "Aren't you a Database Guy? Can you look at the performance problems of our SQLServer?" question quite often, only to see one or more virus scanners run mad on the data files. Very Happy
Previous Topic: concatenate a parameter with a column in a sql query
Next Topic: Inserting an Image into a table by Insert statement
Goto Forum:
  


Current Time: Sat Aug 09 13:11:30 CDT 2025