Getting below errors while sending mail from one of DB using UTL_SMTP.mail
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 641
ORA-06512: at "SYS.TESTMAIL", line 30
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.TESTMAIL", line 6
ORA-06512: at line 5
solution :
1. install mailx & postfix and enable the services
rpm -qa | grep mailx
rpm -qa | grep postfix
yum install -y postfix
yum install -y mailx
systemctl status postfix
systemctl start postfix
systemctl enable postfix
systemctl status postfix
2. Try to telnet the email relay server, if you failed reach to network admin and open the relay server port
eg.
db ip - source - 192.xx.xx.35
mail relay ip - 192.xx.xx.25
mail port - 587
$ telnet 192.xx.xx.35 587
3. Telnet output :
[root@node1 ~]# telnet 192.xx.xx.25 587
Trying 192.xx.xx.25...
Connected to 192.xx.xx.25.
Escape character is '^]'.
Connection closed by foreign host.
[root@node1 ~]#
** if Telnet output is "Connection closed by foreign host." then there is issue, you should reach to email admin and ask to allow source ip in email relay server
after that, if you telnet then below output should receive
[root@node1 ~]# telnet 192.xx.xx.25 587
Trying 192.xx.xx.25...
Connected to 192.xx.xx.25.
Escape character is '^]'.
220 relay.mydomain Microsoft ESMTP MAIL Service, Version: 8.5.9600.16384 ready at Thu, 6 Jan 2022 09:56:05 +0300
now, type
helo domain
250 relay.mydomain Hello [192.xx.xx.35]
4.
REFER : https://stackoverflow.com/questions/41377078/ora-29278-smtp-transient-error-service-not-available-when-running-utl-mail
Create proper ACL :
Give grant to corresponding schema name for utl_tcp,utl_smtp and utl_http.
grant execute on utl_tcp to myprod_schema;
grant execute on utl_smtp to myprod_schema;
grant execute on utl_http to myprod_schema;
CREATE_ACL using DBMS_NETWORK_ACL_ADMIN sys package:-
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => '/sys/acls/myorg.xml',
description => 'Allowing SMTP Connection',
principal => 'myprod_schema
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
ADD_PRIVILEGE to schema using DBMS_NETWORK_ACL_ADMIN package:-
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '/sys/acls/myorg.xml',
principal => 'myprod_schema',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/
ASSIGN_ACL to mail server using DBMS_NETWORK_ACL_ADMIN package:-
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => '/sys/acls/myprod_schema.xml',
host => '*');
COMMIT;
END;
/
5. CREATE TESTMAIL PROC AND TEST IT
REFER : Check SMTP Server Availability for ORA-29278 or ORA-29279 errors using UTL_SMTP to Send Email. (Doc ID 604763.1)
CREATE OR REPLACE NONEDITIONABLE PROCEDURE TESTMAIL(fromm VARCHAR2,too VARCHAR2,sub VARCHAR2,body VARCHAR2,port NUMBER)
IS
objConnection UTL_SMTP.CONNECTION;
vrData VARCHAR2(32000);
BEGIN
objConnection := UTL_SMTP.OPEN_CONNECTION('192.xx.xx.25',PORT);
UTL_SMTP.HELO(objConnection, '192.xx.xx.25');
UTL_SMTP.MAIL(objConnection, fromm);
UTL_SMTP.RCPT(objConnection, too);
UTL_SMTP.OPEN_DATA(objConnection);
UTL_SMTP.WRITE_DATA(objConnection, 'From: '||fromm || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(objConnection, 'To: '||too || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF);
UTL_SMTP.WRITE_DATA(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_SMTP.WRITE_DATA(objConnection, 'Content-Type: ' || 'text/html;');
UTL_SMTP.WRITE_DATA(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<HTML>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<BODY>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'<FONT COLOR="red" FACE="Courier New">'||body||'</FONT>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</BODY>');
UTL_SMTP.WRITE_DATA(objConnection,UTL_TCP.CRLF||'</HTML>');
UTL_SMTP.CLOSE_DATA(objConnection);
UTL_SMTP.QUIT(objConnection);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.QUIT(objConnection);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
WHEN OTHERS THEN
UTL_SMTP.QUIT(objconnection);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END TESTMAIL;
/
Test by executing this ---
SQL>
DECLARE
Vdate Varchar2(25);
BEGIN
Vdate := to_char(sysdate,'dd-mon-yyyy HH:MI:SS AM');
TESTMAIL('source@mail.com', 'destination@mail.com', 'TESTMAIL','This is a UTL_SMTP-generated email at '|| Vdate,587);
END;
/
--PL/SQL procedure successfully completed
Cheers !!!
No comments:
Post a Comment