My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.