Configuring mail alerts via PLSQL – Troubleshot ORA-29278

Configuring mail alerts via PLSQL - Troubleshot ORA-29278

Troubleshot ORA-29278: SMTP transient error: 421 Service not available.

Using utl_smtp:

The obselete utl_smtppackage was first introduced in Oracle 8i to give access to the SMTP protocol from PL/SQL.  The package is dependent on the JServer option  which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the sys user if it is not already present.

Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting.

Assume:

>> My SMTP server IP is : 170.1.1.1

>> We will use ALERT user to send test mail

-- To create alert user:

SQL> create user ALERT identified by xwdkjdlc13ns default tablespace USERS temporary tablespace TEMP profile DEFAULT;

SQL> grant CONNECT to ALERT;

SQL> grant execute on UTL_MAIL to ALERT;

SQL> grant MGMT_USER to ALERT;

SQL> grant SELECT_CATALOG_ROLE to ALERT;

-- Necessary configuration:

-- in spfile set

SQL> alter system set smtp_out_server = '170.1.1.1' scope=spfile;

Now, bounce the database;

-- before set

SQL> show parameter smtp;

NAME                TYPE        VALUE

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

smtp_out_server     string

SQL>

-- after set

SQL> show parameter smtp;

NAME                TYPE        VALUE

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

smtp_out_server     string      170.1.1.1

SQL>

-- give grants

SQL> grant execute on UTL_MAIL to public;

OR

SQL> grant execute on UTL_MAIL to ALERT;

SQL> ALTER SESSION SET smtp_out_server = '170.1.1.1';

SQL> exec UTL_MAIL.send(sender => 'oracle.com', recipients => '

[email protected]

', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii');

If you are facing "ORA-29278: SMTP transient error: 421 Service not available" error, the read the below steps.

The Problem:

You are trying to use the UTL_MAIL package to send email from your database. When attempting to send a mail, you recieve the following error:

SQL> exec utl_mail.send('oracle.com','

[email protected]

','test mail','Hello World',mime_type => 'text; charset=us-ascii');

ORA-29278: SMTP transient error: 421 Service not available

ORA-06512: at "SYS.UTL_MAIL", line 654

ORA-06512: at "SYS.UTL_MAIL", line 671

ORA-06512: at line 2

Troubleshoot methods:

The Cause:

If this fails it could be a result of many things, so please check all of the items below:

1) SMTP_OUT_SERVER

Check the value for the initialisation parameter SMTP_OUT_SERVER. This should be set to the SMTP server IP. If not, run:

ALTER SYSTEM SET SMTP_OUT_SERVER=”<IP>” scope=both;

2) Recent Upgrade to Oracle 11g

Are you running Oracle database 11g? If so, you will need to have XMLDB & Java installed in order to configure fine grained auditing and enable it there for each user explicitly. You can run through these checks to confirm you have everything in place:

col COMP_NAME format a40;

col VERSION format a12;

col STATUS format a12;

SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

COMP_NAME                                VERSION         STATUS

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

Oracle Enterprise Manager                11.2.0.3.0      VALID

Oracle XML Database                      11.2.0.3.0      VALID

Oracle Expression Filter                 11.2.0.3.0      VALID

Oracle Rules Manager                     11.2.0.3.0      VALID

Oracle Workspace Manager                 11.2.0.3.0      VALID

Oracle Database Catalog Views            11.2.0.3.0      VALID

Oracle Database Packages and Types       11.2.0.3.0      VALID

JServer JAVA Virtual Machine             11.2.0.3.0      VALID

Oracle XDK                               11.2.0.3.0      VALID

Oracle Database Java Packages            11.2.0.3.0      VALID

10 rows selected.

If the “JServer JAVA Virtual Machine” and “Oracle XML Database” components are not there then you need to install them to get this working.

The following scripts are what you would use to install Java and XML DB

Note: These instructions are taken directly from Oracle Metalink, but I would recommend double checking the notes on there just to make sure there have been no updates to them since I wrote this.

3. UTL_MAIL Package & Grants:

You will need to ensure that the UTL_MAIL package exists and that the required users have permission to execute it. You can install it and grant execute privileges on it to a user with the following commands:

SQL/> connect as sysdba

SQL> @?/rdbms/admin/utlmail.sql

SQL> @?/rdbms/admin/prvtmail.plb

SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO USER;

e.g.,

SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;

When executed:

SQL> @?/rdbms/admin/utlmail.sql

Package created.

Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;

Grant succeeded.

4. Access Control List (ACL) Configuration:

Have you configured your Access Control List (ACL)? If not, you can do it with the code below. This is required for any Oracle 11g database where you want to send email using Access Control Lists (ACLs) and is a very common error to encounter after upgrading your database from 10g to 11g.

SQL>

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

    acl => 'UTL_SMTP.xml',

    description => 'Granting privs to required users for UTL_SMTP.xml',

    principal => 'ALERT',

    is_grant => TRUE,

    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (

    acl => 'UTL_SMTP.xml',

    principal => 'ALERT',

    is_grant => TRUE,

    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

    acl => 'UTL_SMTP.xml',

    host => '170.1.1.1');

END;

/

COMMIT;

SQL>

PL/SQL procedure successfully completed

Commit complete

SQL>

Now I am testing the above:

SQL> exec utl_mail.send('oracle.com','

[email protected]

','test mail','Hello World',mime_type => 'text; charset=us-ascii');

PL/SQL procedure successfully completed

SQL>

Now it is succeeded.

Hopefully that has worked for you, but it may not…You might now be be getting a different error reporting ORA-24247: network access denied by access control list (ACL) if you have not configured your ACLs for the user running the package. if that’s the case, check out that article. It also covers the error whereby you have refreshed your environment from your production environment and receive the ORA-24247: network access denied by access control list (ACL).

Hopefully one of the suggestions above should help you to resolve the issue.

Note:

Limitations on sending e-mail in Oracle with utl_mail. There are several limitations in utl_mail for sending e-mail messages from inside Oracle. The utl_mail package can only handle a RAW datatype, and hence a maximum value of 32k for a 32k mail message.