ORA-29279错误排查：AWS RDS Oracle调用SES发件失败

我在AWS RDS（Oracle SE 19c）上尝试通过已配置并测试过的Amazon SES中继发送邮件——Linux下用SENDMAIL可以正常发件，但用UTL_SMTP包执行时出错。已创建Oracle wallet cwallet.sso并上传到Oracle目录，配置了所有必要参数，执行时出现以下错误栈：

ORA-29279: SMTP permanent error: 504 The requested authentication mechanism is not supported
ORA-06512: at "MMX.SEND_MAIL_HTML3", line 38
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 142
ORA-06512: at "SYS.UTL_SMTP", line 446
ORA-06512: at "MMX.SEND_MAIL_HTML3", line 20
ORA-06512: at line 14

存储过程代码：

create or replace PROCEDURE SEND_MAIL_HTML3 (
    p_recipients  IN VARCHAR2,
    p_cc          IN VARCHAR2 DEFAULT NULL,
    p_bcc         IN VARCHAR2 DEFAULT NULL,
    p_subject     IN VARCHAR2,
    p_message     IN VARCHAR2)
IS
    c UTL_SMTP.CONNECTION;
    PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
    BEGIN
        UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
    END;
BEGIN   
    c := utl_smtp.open_connection(
              host => 'email-smtp.eu-central-1.amazonaws.com',
              port => 587,
              wallet_path => 'file://rdsdbdata/userdirs/01',
              secure_connection_before_smtp => FALSE);
    UTL_SMTP.STARTTLS(c);
    UTL_SMTP.AUTH(
        c => c,
        username => 'AWS_SES_ACCESS_KEY',
        password => 'AWS_SES_SECRET_KEY',
        schemes  => utl_smtp.all_schemes);
    UTL_SMTP.HELO(c, 'xxx.awsapps.com');
    UTL_SMTP.MAIL(c, 'xxx-do-no-reply@xxx.awsapps.com');
    UTL_SMTP.RCPT(c, p_recipients);
    UTL_SMTP.OPEN_DATA(c);
    send_header('From',    'XXX Dev Alert <xxx-do-no-reply@xxx.awsapps.com>');
    send_header('To',      p_recipients);
    send_header('Subject', p_subject);
    UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || p_message);
    UTL_SMTP.CLOSE_DATA(c);
    UTL_SMTP.QUIT(c);
EXCEPTION
  WHEN OTHERS
  THEN
    RAISE;
END send_mail_html3;

问题原因及解决方案 #

核心原因 #

错误504 The requested authentication mechanism is not supported的本质是：Oracle UTL_SMTP请求的认证机制不被Amazon SES支持。Amazon SES SMTP接口仅支持PLAIN和LOGIN两种认证方案，而utl_smtp.all_schemes会尝试Oracle支持的所有认证类型（包括CRAM-MD5等SES不兼容的），导致协商失败。

修复步骤 #

1. 指定兼容的认证方案
   将UTL_SMTP.AUTH中的schemes参数从utl_smtp.all_schemes改为utl_smtp.plain_auth_scheme（推荐，因为STARTTLS已加密连接，PLAIN的明文密码会被加密传输），或utl_smtp.login_auth_scheme。

   修改后的认证代码片段：

   UTL_SMTP.AUTH(
       c => c,
       username => 'AWS_SES_ACCESS_KEY',
       password => 'AWS_SES_SECRET_KEY',
       schemes  => utl_smtp.plain_auth_scheme);
   

2. 验证Wallet有效性（可选）
   执行以下SQL确认wallet状态正常：

   SELECT status FROM v$wallet;
   

   返回OPEN表示wallet配置正确，若返回其他状态需检查路径权限或wallet文件完整性。

3. 调整SMTP命令顺序（可选）
   部分SMTP服务器要求在认证前发送EHLO（而非HELO），建议调整顺序为：OPEN_CONNECTION → STARTTLS → EHLO → AUTH，提升兼容性。

完整修复后的存储过程示例 #

create or replace PROCEDURE SEND_MAIL_HTML3 (
    p_recipients  IN VARCHAR2,
    p_cc          IN VARCHAR2 DEFAULT NULL,
    p_bcc         IN VARCHAR2 DEFAULT NULL,
    p_subject     IN VARCHAR2,
    p_message     IN VARCHAR2)
IS
    c UTL_SMTP.CONNECTION;
    PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
    BEGIN
        UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
    END;
BEGIN   
    c := utl_smtp.open_connection(
              host => 'email-smtp.eu-central-1.amazonaws.com',
              port => 587,
              wallet_path => 'file://rdsdbdata/userdirs/01',
              secure_connection_before_smtp => FALSE);
    UTL_SMTP.STARTTLS(c);
    UTL_SMTP.EHLO(c, 'xxx.awsapps.com'); -- 改用EHLO符合现代SMTP标准
    UTL_SMTP.AUTH(
        c => c,
        username => 'AWS_SES_ACCESS_KEY',
        password => 'AWS_SES_SECRET_KEY',
        schemes  => utl_smtp.plain_auth_scheme);
    UTL_SMTP.MAIL(c, 'xxx-do-no-reply@xxx.awsapps.com');
    UTL_SMTP.RCPT(c, p_recipients);
    -- 补充CC和BCC处理逻辑
    IF p_cc IS NOT NULL THEN
        UTL_SMTP.RCPT(c, p_cc);
    END IF;
    IF p_bcc IS NOT NULL THEN
        UTL_SMTP.RCPT(c, p_bcc);
    END IF;
    UTL_SMTP.OPEN_DATA(c);
    send_header('From',    'XXX Dev Alert <xxx-do-no-reply@xxx.awsapps.com>');
    send_header('To',      p_recipients);
    IF p_cc IS NOT NULL THEN
        send_header('Cc', p_cc);
    END IF;
    send_header('Subject', p_subject);
    UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || p_message);
    UTL_SMTP.CLOSE_DATA(c);
    UTL_SMTP.QUIT(c);
EXCEPTION
  WHEN OTHERS
  THEN
    RAISE;
END send_mail_html3;

内容的提问来源于stack exchange，提问作者Rajarshi Mukherjee