Unable to Create ACL with dbms_network_acl_admin.create_acl after Database Upgrade from 10g to 11.1.0.7 due to Error ORA-29861 (Doc ID 1144893.1)

Last updated on MAY 13, 2013

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.
Checked for relevance on 07-Aug-2012

Symptoms

Upgraded database from 101.0.3 to 11.1.0.7 following <Note 429825.1> and was unable to create ACL as explained in Step 33 due to following error:

SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'acl_icnv.xml',
description => 'acl for the usage op utl_ packages',
principal => 'IKMO2001',
is_grant => TRUE,
privilege => 'connect');
end;
/

begin
*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 226
ORA-06512: at line 2


After reviewing the upgrade logfile noticed some errors while creating XDS_ACL view:

SQL> create or replace view XDS_ACL
2 (ACLID, SHARED, DESCRIPTION, SECURITY_CLASS_NS,
3 SECURITY_CLASS_NAME, PARENT_ACL_PATH, INHERITANCE_TYPE)
4 as
5 select a.object_id,
6 substr(extractvalue(a.object_value, '/acl/@shared',
7 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"'),
8 1, 5),
9 extractvalue(a.object_value, '/acl/@description',
10 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"'),
11 xmlquery('declare namespace a="http://xmlns.oracle.com/xdb/acl.xsd"; fn:namespace-uri-from-QName(fn:data(/a:acl/a:security-class))' PASSING OBJECT_VALUE returning content),
12 xmlquery('declare namespace a="http://xmlns.oracle.com/xdb/acl.xsd"; fn:local-name-from-QName(fn:data(/a:acl/a:security-class))' PASSING OBJECT_VALUE returning content),
13 CASE existsNode(a.object_value, '/acl/extends-from',
14 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"') WHEN 1
15 THEN extractvalue(a.object_value, '/acl/extends-from/@href',
16 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
17 ELSE (CASE existsNode(a.object_value, '/acl/constrained-with',
18 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
19 WHEN 1
20 THEN extractvalue(a.object_value, '/acl/constrained-with/@href',
21 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
22 ELSE NULL END) END,
23 CASE existsNode(a.object_value, '/acl/extends-from',
24 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"') WHEN 1
25 THEN 'extends-from'
26 ELSE (CASE existsNode(a.object_value, '/acl/constrained-with',
27 'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
28 WHEN 1
29 THEN 'constrained-with'
30 ELSE NULL END) END
31 FROM XDB.XDB$ACL a;
FROM XDB.XDB$ACL a
*
ERROR at line 31:
ORA-01403: no data found


SQL>
SQL> create or replace public synonym XDS_ACL for XDS_ACL;

Synonym created.

SQL>
SQL> grant select on XDS_ACL to PUBLIC;
grant select on XDS_ACL to PUBLIC
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms