oracle ORA-29278: SMTP 瞬时错误: 运行 UTL_MAIL 时服务不可用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41377078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-29278: SMTP transient error: Service not available when running UTL_MAIL
提问by Migs Isip
We are planning to install the UTL_MAILPackage and we're currently testing the installation steps in our Development Environment.
After sucessfully installing the UTL_MAILPackage Scripts and creating the sufficient PUBLICSynonyms and Grants,
we are getting the error ORA-29278when running the test Anonymous Block below:
我们正计划安装该UTL_MAIL包，目前正在我们的开发环境中测试安装步骤。成功安装UTL_MAIL包脚本并创建足够的PUBLIC同义词和授权后，我们ORA-29278在运行下面的测试匿名块时遇到错误：
BEGIN
UTL_MAIL.SEND(sender => '[email protected]'
, recipients => '[email protected]'
, subject => 'Testmail'
, message => 'Hello');
END;
Full Details of the error Message:
错误消息的完整详细信息：
ORA-29278: SMTP transient error: 421 4.3.2 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 3
29278. 00000 - "SMTP transient error: %s"
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.
As per research from related links (Send Email Using PLSQL), i may need to setup the proper access control list (ACL) for this to work. However, upon executing the script below, i'm still getting the same error.
根据相关链接（使用 PLSQL 发送电子邮件）的研究，我可能需要设置正确的访问控制列表 (ACL) 才能使其正常工作。但是，在执行下面的脚本时，我仍然遇到相同的错误。
DECLARE
-- ACL name to be used for email access reuse the same value for all
-- future calls
l_acl VARCHAR2 (30) := 'utl_smtp.xml';
-- Oracle user to be given permission to send email
l_principal VARCHAR2 (30) := 'APPS';
-- Name of email server
g_mailhost VARCHAR2 (60) := 'smtprelay.xxxxx.com';
l_cnt INTEGER;
PROCEDURE validate_smtp_server
AS
l_value v$parameter.VALUE%TYPE;
l_parameter v$parameter.name%TYPE := 'smtp_out_server';
BEGIN
SELECT VALUE
INTO l_value
FROM v$parameter
WHERE name = l_parameter;
IF l_value IS NULL
THEN
raise_application_error (
-20001
, 'Oracle parameter '
|| l_parameter
|| ' has not been set'
|| UTL_TCP.crlf
|| 'it s/b smtprelay.alorica.com'
);
END IF;
DBMS_OUTPUT.put_line ('parameter ' || l_parameter || ' value is ' || l_value);
END validate_smtp_server;
PROCEDURE create_if_needed (p_acl IN VARCHAR2)
AS
l_cnt INTEGER;
BEGIN
SELECT COUNT (*) c
INTO l_cnt
FROM dba_network_acls a
WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl;
IF l_cnt = 0
THEN
DBMS_OUTPUT.put_line ('creating acl ' || p_acl);
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => p_acl
, description => 'Allow use of utl_smtp'
, principal => l_principal
, is_grant => TRUE
, privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl (acl => p_acl, HOST => g_mailhost);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (p_acl || ' acl already exists');
END IF;
END create_if_needed;
PROCEDURE add_if_needed (
p_principal IN VARCHAR2
, p_acl IN VARCHAR2
)
AS
l_cnt INTEGER;
BEGIN
SELECT COUNT (*) c
INTO l_cnt
FROM dba_network_acl_privileges
WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl
AND principal = p_principal;
IF l_cnt = 0
THEN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml'
, principal => p_principal
, is_grant => TRUE
, privilege => 'connect'
);
COMMIT;
DBMS_OUTPUT.put_line ('access to ' || p_acl || ' added for ' || p_principal);
ELSE
DBMS_OUTPUT.put_line (p_principal || ' already has access to ' || p_acl);
END IF;
END add_if_needed;
BEGIN
EXECUTE IMMEDIATE 'grant execute on utl_mail to ' || l_principal;
create_if_needed (p_acl => l_acl);
add_if_needed (p_principal => l_principal, p_acl => l_acl);
DBMS_OUTPUT.put_line ('Verification SQL:');
DBMS_OUTPUT.put_line (' SELECT * FROM dba_network_acls;');
DBMS_OUTPUT.put_line (' SELECT * FROM dba_network_acl_privileges;');
COMMIT;
validate_smtp_server;
END;
What other steps can i take or what other instructions do i need to provide to the DBAs for this?
为此，我还可以采取哪些其他步骤或需要向 DBA 提供哪些其他说明？
Oracle Database Version:
Oracle 数据库版本：
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Thank you very much.
非常感谢。
采纳答案by Migs Isip
I was able to resolve this by contacting our System Administrator and asking for the details of the Mail Server.
Turns out, if we'll only be sending the email internally, we are advised to use a different server mail.xxx.xxx.xxxxsince its not going to be blocked by the firewall.
On the other hand, if we'll be sending email externally, another server is involved smtprelay.xxxxx.comand this involves an extra step of Whitelisting the External Servers to be sent to.
我能够通过联系我们的系统管理员并询问邮件服务器的详细信息来解决这个问题。事实证明，如果我们只在内部发送电子邮件，我们建议使用不同的服务器，mail.xxx.xxx.xxxx因为它不会被防火墙阻止。另一方面，如果我们smtprelay.xxxxx.com要向外部发送电子邮件，则涉及另一台服务器，这涉及将要发送到的外部服务器列入白名单的额外步骤。
As i checked in V$PARAMETER, we were using the smtprelay.xxxxx.comserver and decided to try the other server mail.xxx.xxx.xxxx.
当我登记入住时V$PARAMETER，我们正在使用该smtprelay.xxxxx.com服务器并决定尝试另一台服务器mail.xxx.xxx.xxxx。
I issued the Alter command as below:
我发出了如下 Alter 命令：
alter system set smtp_out_server = 'mail.xxx.xxx.xxxx';
and ran the anonymous block and was able to recieve the email successfully.
并运行匿名块并能够成功接收电子邮件。
BEGIN
UTL_MAIL.SEND(sender => '[email protected]'
, recipients => '[email protected]'
, subject => 'Testmail'
, message => 'Hello');
END;
