ORA-29278: The 4 Most Common Issues & How to Fix Them

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…

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:

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 => 'check@testing.com'
,recipients => 'testing@testing.com'
,subject => 'test'
,message => 'test'
);
end;
/

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:

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          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
Oracle XML Database                    11.2.0.3.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:

@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb

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…

Hopefully that helped you out and resolved your Oracle error. While you’re here, what steps are you taking to improve your Oracle knowledge?

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