CODEWITHSUNDEEP
oracle-databaseplsql
Migs
Migs

Reputation: 1480

ORA-29278: SMTP transient error: Service not available when running UTL_MAIL

We are planning to install the UTL_MAIL Package and we're currently testing the installation steps in our Development Environment. After sucessfully installing the UTL_MAIL Package Scripts and creating the sufficient PUBLIC Synonyms and Grants, we are getting the error ORA-29278 when running the test Anonymous Block below:

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.

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?

Oracle Database Version:

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.

Upvotes: 6

Views: 67343

Answers (2)

Sumit Jangir
Sumit Jangir

Reputation: 81

I came up with SMTP email challenges for 19c database. I was able to solve it. Below is the complete solution:-

  • Give grant to corresponding schema name for utl_tcp,utl_smtp and utl_http.

            grant execute on utl_tcp to schemaname;
        grant execute on utl_smtp to schemaname;
        grant execute on utl_http to schemaname;

  • CREATE_ACL using DBMS_NETWORK_ACL_ADMIN sys package:-

        BEGIN
       DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
       acl => '/sys/acls/utl_http.xml',
       description => 'Allowing SMTP Connection',
       principal => 'SCHEMANAME',
       is_grant => TRUE,
       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/utl_http.xml',
     principal => 'SCHEMANAME',
     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/utl_http.xml',
        host => 'mailhostname');
       COMMIT;
      END;
     /

Upvotes: 0

Migs
Migs

Reputation: 1480

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.xxxx since 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.com and this involves an extra step of Whitelisting the External Servers to be sent to.

As i checked in V$PARAMETER, we were using the smtprelay.xxxxx.com server and decided to try the other server mail.xxx.xxx.xxxx.

I issued the Alter command as below:

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;

Upvotes: 2

Related Questions