Oracle数据库邮件未触发排查及ORA-29278错误求助

一、通用排查步骤 #

• 验证SMTP配置正确性：检查WS_PMNT_CONFIG表中MAIL_HOST和MAIL_PORT的配置值是否与目标环境SMTP服务器一致，确认无拼写错误或端口不匹配。
• 测试网络连通性：从Oracle数据库服务器执行telnet <MAIL_HOST> <MAIL_PORT>命令，验证是否能正常连接SMTP服务，排查防火墙或网络策略是否阻止访问。
• 确认数据库权限：确保执行存储过程的用户拥有UTL_SMTP、UTL_TCP、UTL_RAW等包的执行权限，以及访问WS_PMNT_CONFIG、APIPMT_MAIL_RECEPIENTS等相关表的权限。
• 检查存储过程逻辑：
  • 确认APIPMT_MAIL_RECEPIENTS表中存在有效的发件人（FROM_TO='F'）和收件人（FROM_TO='T'）邮箱地址。
  • 验证游标REJ和SUCC是否能查询到数据，确认邮件内容及附件生成逻辑正常。
  • 检查PKG_SEND_MAIL.SEND_MAIL调用参数是否完整，特别是SMTP主机、端口参数是否正确传递。

二、数据库日志追踪方法 #

• 自定义错误日志：当前代码仅通过DBMS_OUTPUT输出错误，建议新增自定义日志表（如MAIL_ERROR_LOG），将错误时间、错误信息、执行参数等写入表中，便于后续排查。
• 数据库告警日志：查看Oracle数据库告警日志（alert.log），排查是否存在与UTL_SMTP相关的权限错误、网络异常或资源限制信息。
• SMTP服务器日志：联系邮件管理员查看SMTP服务器日志，确认数据库服务器的连接请求是否被拒绝或触发限流策略，这对定位SMTP层错误至关重要。

三、针对ORA-29278: SMTP transient error: 421 Service not available的具体排查 #

该错误表示SMTP服务器返回临时不可用响应，常见原因及解决方法：

• SMTP服务器不可达：数据库服务器无法连接配置的SMTP主机/端口，需重新确认网络连通性、防火墙规则及SMTP服务器运行状态。
• SMTP服务器限流或拒绝未授权连接：部分SMTP服务器会对陌生IP或频繁请求限流，需确认数据库服务器IP是否在SMTP白名单中；若SMTP要求身份验证，当前代码未实现UTL_SMTP.AUTH逻辑，需补充认证步骤。
• 环境配置不一致：目标环境SMTP服务器地址、端口或认证方式与测试环境不同，需重新核对WS_PMNT_CONFIG中的配置值。

相关代码参考 #

存储过程PMNT_API_MAILER #

create or replace PROCEDURE PMNT_API_MAILER(PMNT_DATE IN DATE)

AS

BEGIN

DECLARE

strMailHost   VARCHAR2(50);

strMailPort   VARCHAR2(4);

strHeader    VARCHAR2(4000);

strMessage   VARCHAR2(4000);

nPos      NUMBER;

strFrom     VARCHAR2(100);

strSendTo    VARCHAR2(1000);

strCCTo     VARCHAR2(1000);

strSubject   VARCHAR2(500);

strSendTo_BAF  VARCHAR2(1000);

---------------------------------------------------------------------------------------------



P_TEXT     clob;--VARCHAR2(4000);

P_HTML     clob;

V_SUM_LOAN_PRIN_ADV  NUMBER := 0;

V_Count     Number;

V_BusinessDate VARCHAR(20);

v_error varchar2(1000);

P_DATE     DATE := PMNT_DATE;

v_cnt_cu    number := 0;

attachments PKG_SEND_MAIL.ARRAY_ATTACHMENTS := PKG_SEND_MAIL.ARRAY_ATTACHMENTS();

V_REJECT_DATA CLOB;

V_SUCCESS_DATA CLOB;



CURSOR REJ IS

  SELECT a.agreementid,a.downloadid,b.chequeid,b.BANK_REJ_REASON

  FROM lea_h2hpayment_hdr a, lea_h2hpayment_dtl b

  where a.downloadid = b.downloadid

  and a.download_date = P_DATE

  and a.pmnt_method = 'API'

  and a.status = 'A'

  and b.BANK_REJ_REASON is not null;



CURSOR SUCC IS

  SELECT a.agreementid,a.downloadid,b.chequeid,b.BANK_TXN_REF_NO

  FROM lea_h2hpayment_hdr a, lea_h2hpayment_dtl b

  where a.downloadid = b.downloadid

  and a.download_date = P_DATE

  and a.pmnt_method = 'API'

  and a.status = 'A'

  and b.BANK_TXN_REF_NO is not null;





 BEGIN

 v_error :=null;

  
BEGIN



 select CONF_VALUE

 into strMailHost

 from WS_PMNT_CONFIG

 WHERE CONF_KEY = 'MAIL_HOST'

 and status = 'A';



  select CONF_VALUE

 into strMailPort

 from WS_PMNT_CONFIG

 WHERE CONF_KEY = 'MAIL_PORT'

 and status = 'A';



  EXCEPTION WHEN OTHERS THEN

  v_error := 'Error while fetching data from WS_PMNT_CONFIG' || sqlerrm;

END;



BEGIN

 SELECT EMAIL

 INTO strFrom

 FROM APIPMT_MAIL_RECEPIENTS

 WHERE FROM_TO ='F'

 AND ROWNUM = 1;



  EXCEPTION WHEN OTHERS THEN

  v_error := 'Error while fetching sender mail from APIPMT_MAIL_RECEPIENTS' || sqlerrm;

  return;

END;



strHeader  := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') ;

strSubject := 'Payment records processed via API on ' || to_char(P_DATE,'dd-MON-yyyy');



P_HTML := '<HTML><BODY bgcolor="white">

      <TABLE BORDER=0>

      <font color="black" face="Tahoma" SIZE="2" point-size=100 weight=600>

      <TR><TD colspan="2">Dear Team,

      <BR>

      <BR>

      </TD></TR>

      <TR><TD colspan="2">Please find below information related to Payment Disbursement :<BR></TD></TR>

       <TR></TR>

       <BR>

      <TR><TD colspan="2">1. Success file<BR></TD></TR>

      <BR>

      <TR><TD colspan="2">2. Reject file<BR></TD></TR>

      </font></TABLE>';



P_TEXT := '';



P_TEXT := '<BR>';



P_HTML := P_HTML || P_TEXT;



P_TEXT := '';



P_TEXT := '<TABLE BORDER=0>

      <font color="black" face="Tahoma" SIZE="2" point-size=100 weight=600>

      <TR><TD colspan="2">Thanks & Regards</TD></TR>

      <TR><TD colspan="2">IT Team</TD></TR></font></TABLE>';



P_HTML := P_HTML || P_TEXT ;



P_HTML := P_HTML || '</BODY></HTML>';





SELECT LISTAgg(EMAIL,',') WITHIN GROUP (ORDER BY EMAIL)

into strSendTo

FROM APIPMT_MAIL_RECEPIENTS

where status = 'A'

and from_to = 'T';



V_SUCCESS_DATA := 'LAN ID,CHQ ID,BATCH ID,UTR'||chr(13)||chr(10);

FOR S IN SUCC LOOP

  V_SUCCESS_DATA := V_SUCCESS_DATA ||

           S.AGREEMENTID||','||S.CHEQUEID||','||S.DOWNLOADID||','||S.BANK_TXN_REF_NO||chr(13)||chr(10);

END LOOP;

V_REJECT_DATA := 'LAN ID,CHQ ID,BATCH ID,Reject Reason'||chr(13)||chr(10);

FOR R IN REJ LOOP

  V_REJECT_DATA := V_REJECT_DATA ||

           R.AGREEMENTID||','||R.CHEQUEID||','||R.DOWNLOADID||','||R.BANK_REJ_REASON||chr(13)||chr(10);

END LOOP;



attachments.extend(2);



attachments(1).attach_name := 'Reject_'||PMNT_DATE||'.csv';

attachments(1).data_type := 'text/plain';

attachments(1).attach_content := V_REJECT_DATA;



attachments(2).attach_name := 'Success_'||PMNT_DATE||'.csv';

attachments(2).data_type := 'text/plain';

attachments(2).attach_content := V_SUCCESS_DATA;



PKG_SEND_MAIL.SEND_MAIL ( strFrom,strSendTo,strSubject,P_HTML,strCCTo,attachments ,'text/html', strMailHost, strMailPort);



trap('End SEND_MAIL');



  EXCEPTION



  WHEN UTL_SMTP.INVALID_OPERATION THEN

  v_error:='Invalid Operation in SMTP transaction.'||SQLERRM;



WHEN UTL_SMTP.TRANSIENT_ERROR THEN

  v_error:='Temporary problems in sending email - try again later.'||SQLERRM;



WHEN UTL_SMTP.PERMANENT_ERROR THEN

  v_error:='Errors in code for SMTP transaction.'||SQLERRM;



WHEN OTHERS THEN

  v_error:=' Error Occured while sending Mail.'||SQLERRM;

   

   DBMS_OUTPUT.put_line(v_error);

 END;



 END;

包体PKG_SEND_MAIL #

create or replace PACKAGE BODY PKG_SEND_MAIL AS



PROCEDURE SEND_MAIL(

v_from_name  VARCHAR2,

v_to_name   VARCHAR2,

v_subject   VARCHAR2,

v_message_body VARCHAR2,

v_cc_name   VARCHAR2 DEFAULT '',

attachments  array_attachments DEFAULT NULL,

v_message_type VARCHAR2 DEFAULT 'text/plain',

v_smtp_ser  varchar2 ,

v_smtp_port   varchar2

) AS

 v_smtp_server    VARCHAR2(20) := v_smtp_ser;

 n_smtp_server_port NUMBER    := v_smtp_port;

 conn        utl_smtp.connection;

 v_boundry      VARCHAR2(20) := 'SECBOUND';

 n_offset      NUMBER    := 0;

 n_amount      NUMBER    := 1900;

 v_final_to_name   CLOB     := '';

 v_final_cc_name   CLOB     := '';

 v_mail_address   VARCHAR2(100);

 v_error_msg     varchar2(4000 byte);



  BEGIN

  conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);

   utl_smtp.helo(conn, v_smtp_server);

   utl_smtp.mail(conn, v_from_name);



  -- Add all recipient

  v_final_to_name := v_to_name;

  v_final_to_name := replace(v_final_to_name, ' ');

   v_final_to_name := replace(v_final_to_name, ',', ';');

  LOOP

   n_offset := n_offset + 1;

  v_mail_address := regexp_substr(v_final_to_name, '[^;]+', 1, n_offset);

   EXIT WHEN v_mail_address IS NULL;

  utl_smtp.rcpt(conn, v_mail_address);

  END LOOP;



  -- Add all recipient

  v_final_cc_name := v_cc_name;

   v_final_cc_name := replace(v_final_cc_name, ' ');

   v_final_cc_name := replace(v_final_cc_name, ',', ';');

   n_offset := 0;

    LOOP

    n_offset := n_offset + 1;

      v_mail_address := regexp_substr(v_final_cc_name, '[^;]+', 1, n_offset);

      EXIT WHEN v_mail_address IS NULL;

      utl_smtp.rcpt(conn, v_mail_address);

      END LOOP;



        -- Open data

       utl_smtp.open_data(conn);



        -- Message info

         utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_final_to_name || UTL_TCP.crlf));

          utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Cc: ' || v_final_cc_name || UTL_TCP.crlf));

            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.crlf));

            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || UTL_TCP.crlf));

             utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || UTL_TCP.crlf));

             utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || UTL_TCP.crlf));

             utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="' || v_boundry || '"' || UTL_TCP.crlf || UTL_TCP.crlf));



              -- Message body

              utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || UTL_TCP.crlf));

               utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || UTL_TCP.crlf || UTL_TCP.crlf));

               utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || UTL_TCP.crlf));



                -- Attachment Part

               IF attachments IS NOT NULL

                THEN

                 FOR i IN attachments.FIRST .. attachments.LAST

                 LOOP

                -- Attach info

                  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type

            || ' name="'|| attachments(i).attach_name || '"' || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'

            || attachments(i).attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf));



-- Attach body

  n_offset := 1;

  WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)

  LOOP

    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));

    n_offset := n_offset + n_amount;

  END LOOP;

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || UTL_TCP.crlf));

        END LOOP;

         END IF;

        -- Last boundry

        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || '--' || UTL_TCP.crlf));



          -- Close data

           utl_smtp.close_data(conn);

            utl_smtp.quit(conn);



           EXCEPTION

            WHEN OTHERS THEN

             v_error_msg := 'Error in PKG_SEND_MAIL. '||sqlerrm;

              DBMS_OUTPUT.PUT_LINE(v_error_msg);



                END;



                END;

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