- 21 Nov, 2021
- 0 Comments
- 5 Mins Read
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>