Problem with ACL and XB on 11gr2

[biotmessiah]

Hello Dear,

It is a pleasure to be here with you.

I would like to know if colleagues can help me with a boring problem I'm trying to solve.

I have a schema on my Oracle 11G R2 server 11.2.0.3 who needs to send emails, but so that he can do this I need to release in the ACL, so far so good, the problem is that When you try to create the ACL I receive the following error:


Error report:

Select all

ORA-29903: erro na execução da rotina ODCIIndexFetch() 
ORA-31001: Handle de recurso ou nome de caminho "" inválido 
ORA-06512: em "XDB.DBMS_RESCONFIG", line 47 
ORA-06512: em "SYS.DBMS_NETWORK_ACL_ADMIN", line 101 
ORA-06512: em "SYS.DBMS_NETWORK_ACL_ADMIN", line 505 
ORA-06512: em line 15 
29903. 00000 -  "error in executing ODCIIndexFetch() routine" 
*Cause:    The execution of ODCIIndexFetch routine caused an error. 
*Action:   Examine the error messages produced by the indextype code and 
           take appropriate action. 

I am using the following syntax :

Select all

BEGIN 
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 
acl => 'UTL_SMTP.xml', 
description => 'Granting privs to required users for UTL_SMTP.xml', 
principal => 'MNT_TESTE', 
is_grant => TRUE, 
privilege => 'connect'); 
 
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( 
acl => 'UTL_SMTP.xml', 
principal => 'MNT_TESTE', 
is_grant => TRUE, 
privilege => 'resolve'); 
 
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 
acl => 'UTL_SMTP.xml', 
host => 'endereçosrv'); 
END; 
/ 
 
COMMIT;

Performing the above command in parts, it seems that the error occurs when executing the command:

Select all

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL

to see the file is created, because when running the drop it is deleted and the second time it says that the file does not exist.

Select all

BEGIN 
  DBMS_NETWORK_ACL_ADMIN.drop_acl (  
    acl         => 'UTL_SMTP.xml'); 
 
  COMMIT; 
END; 
/

The same error occurs to run and check the PATH on the View Resource_View.

Select all

 
SELECT any_path FROM resource_view; 
 
ERRO na linha 1: 
ORA-29903: erro na execuc?o da rotina ODCIIndexFetch() 
ORA-31001: Handle de recurso ou nome de caminho "" invalido

My environment has the version below Oracle running on Oracle Linux 6 Update 1 2.6.39-400.21.1.El6uek.x86_64

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management Options

I have two two instance 11g one of them in the same version and the problem does not occur.

Sincerely,

[fbifabio]

@BiotMessiah,

The article http://www.fabioprado.net/2013/01/envia ... os-de.html may help you.

[biotmessiah]

Fabio,

Thanks for the tip, I had read the article! Very good by the way! Congratulations!!!

My problem occurs when executing the command below:

Select all

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 
acl => 'UTL_SMTP.xml', 
host => 'endereçosrv'); 
END; 
/ 
 
COMMIT;

and also occurs when doing a SELECT NA VIEW RESOUCE_VIEW

Select all

ERRO na linha 1: 
ORA-29903: erro na execuc?o da rotina ODCIIndexFetch() 
ORA-31001: Handle de recurso ou nome de caminho "" invalido

[fbifabio]

I found the following doc in MOS: ORA-31001 USING DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (doc ID 838908.1)
See if the procedure below resolves your problem (make the necessary replacements):

Select all

SOLUTION 
 
Get the DROP_ACL outside the plsql block in its own block: 
 
conn / as sysdba  
 
begin  
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('localhost.xml');  
end;  
/  
 
-- The DROP_ACL will generate error ORA-31001: Invalid resource handle or path name  
"/sys/acls/localhost.xml"  
-- That is expected behaviour because it does not exist  
 
-- Then create_acl  
 
begin  
dbms_network_acl_admin.create_acl(  
acl => 'localhost.xml'  
,description => 'ACL for 127.0.0.1'  
,principal => 'myname'  
,is_grant => true  
,privilege => 'connect');  
dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');  
end;  
/

[biotmessiah]

Good afternoon people,

I solved the problem by reinstalling XDB, as I only used it to create the ACLs, I decided to eliminate and recreat it again.

This is all OK now, sent emails and without any invalid object.

Sincerely,