Issue:

ORA-24247: network access denied by access control list (ACL) error accorded after db upgrade to 11gr2 from 9.2.0.8 in EBS Environment.

Error:

ORA-24247: network access denied by access control list (ACL)

Impact:

Unable to sent mail through database.

Reason:

I had ignored pre-upgrade tool report warning:-

WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER APPS has dependent objects.

Solution:

1. Please check whether the below files exist:

/appsutil/install/<$CONTEXT_NAME>/txkcreateACL.sh

/appsutil/install/<$CONTEXT_NAME>/txkcreateACL.sql

2. If the above files exist , then run 'Autoconfig' on the DB Tier and check if the issue resolves..

3. If the issue does not resolve, then you can check the below steps.

 Create a ACL if one does not exist by referring the bellow command. You can use the scripts mentioned below to check the available ACLs and the related privileges..

SQL> select * from DBA_NETWORK_ACLS;

SQL> select * from DBA_NETWORK_ACL_PRIVILEGES;

Assign the specific Users or Roles to the ACL list.

BEGIN

  -- Only uncomment the following line if ACL "network_services.xml" has already been created

  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

    acl => 'network_services.xml',

    description => 'FTP ACL',

    principal => 'APPS',

    is_grant => true,

    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

    acl => 'network_services.xml',

    principal => 'APPS',

    is_grant => true,

    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

    acl => 'network_services.xml',

    host => '*');

  COMMIT;

END;

Assign the ACL to the required Hosts including the Mail Server

connect apps/apps;

DECLARE

conn utl_smtp.connection;

begin

conn := utl_smtp.open_connection('mail1.indiandba.com', 25);

end;

/

Check the configuration:

select utl_inaddr.get_host_address('mail1.indiandba.com') from dual;

Now retest the issue.