There are many reasons you might be getting the ORA-29278 error message. Below you will find the 4 most common and how to resolve them.
Let’s get stuck in…
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:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 29
ORA-06512: at “SYS.UTL_SMTP”, line 108
ORA-06512: at “SYS.UTL_SMTP”, line 150
ORA-06512: at “SYS.UTL_MAIL”, line 395
ORA-06512: at “SYS.UTL_MAIL”, line 608
ORA-06512: at line 2
If the code is embedded in a larger package, you can test the email sending functionality very quickly using the code below:
begin sys.utl_mail.send(sender => 'email@example.com' ,recipients => 'firstname.lastname@example.org' ,subject => 'test' ,message => 'test' ); end; /
If this fails it could be a result of many things, so please check all of the items below:
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:
SET LINES 120 SET PAGES 1000 COl COMP_NAME FOR A60 SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
The result of this query should show which components are installed in your database, and should look somehting like this:
COMP_NAME VERSION STATUS -------------------------------------- -------------- -------- Oracle Database Catalog Views 22.214.171.124.0 VALID Oracle Database Packages and Types 126.96.36.199.0 VALID JServer JAVA Virtual Machine 188.8.131.52.0 VALID Oracle XDK 184.108.40.206.0 VALID Oracle Database Java Packages 220.127.116.11.0 VALID Oracle XML Database 18.104.22.168.0 VALID
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.
SPOOL full_jvminst.log; SHUTDOWN IMMEDIATE SET ECHO ON STARTUP MOUNT ALTER SYSTEM SET "_system_trig_enabled" = false scope=memory; ALTER DATABASE OPEN; select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; @D:\Oracle\Product\11.2.0\dbhome_11203\javavm\install\initjvm.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @D:\Oracle\Product\11.2.0\dbhome_11203\xdk\admin\initxml.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @D:\Oracle\Product\11.2.0\dbhome_11203\xdk\admin\xmlja.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\catjava.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; SHUTDOWN IMMEDIATE SET ECHO OFF SPOOL OFF EXIT -- End of File full_jvminst.sql -- Start of XML DB Install STARTUP SPOOL xmldb_install.log @D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\catqm.sql change_on_install SYSAUX TEMP NO SPOOL OFF -- End of XML DB Install
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:
GRANT EXECUTE ON SYS.UTL_MAIL TO USER;
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.
BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'UTL_SMTP.xml', description => 'Granting privs to required users for UTL_SMTP.xml', principal => 'USER', is_grant => TRUE, privilege => 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'UTL_SMTP.xml', principal => 'USER', is_grant => TRUE, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'UTL_SMTP.xml', host => '<SMTP SERVER IP ADDRESS>'); END; / COMMIT;
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.
Just quick before you go…
Check out my latest post about which Oracle books are a must read for any serious Oracle DBA. Learn from the best…
Like it, share it...
Category: Database Config