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

We were often seeing following network ACL error.

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

Query to check for existing ACLs.
SELECT NACL.ACLID,ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE NACL.ACLID = ACE.ACLID ;

We had to run following steps to resolve the issue.


EXEC DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'mails.xml' );


EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'mails.xml', description => 'Mail ACL', principal => 'SCOTT', is_grant => TRUE, PRIVILEGE => 'connect');
Principal ==> says which schema will primarily own the ACL
is_grant => TRUE will allow other schema in DB to access this ACL (this line needs verification)

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl          => 'mails.xml',
                                         principal    => 'SCOTT', --Schema name
                                         is_grant     => TRUE, --same as above
                                         PRIVILEGE    => 'connect',
                                         position     => NULL,
                                         start_date   => SYSTIMESTAMP,
                                         end_date     => NULL);

   COMMIT;
END;
/


EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'mails.xml', HOST => 'mailhost', lower_port => 21, upper_port => 30);

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl          => 'mails.xml',
                                         principal    => 'SCHEMA2',
                                         is_grant     => TRUE,
                                         PRIVILEGE    => 'connect',
                                         position     => NULL,
                                         start_date   => SYSTIMESTAMP,
                                         end_date     => NULL);

   COMMIT;
END;
/

For more details: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm

Parallel Hint

SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;

Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.