This discussion is archived
12 Replies Latest reply: Feb 26, 2013 3:40 AM by 986381 RSS

dbms_network_acl_admin error

986381 Newbie
Currently Being Moderated
After installing xml_db, different bodys (f.e. dbms_network_acl_admin) are not valid.

If I try to compile, I get the error PL/SQL: ORA-01775: looping chain of synonyms.

What can I do to fix.
  • 1. Re: dbms_network_acl_admin error
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    What did you do ("...After installing xml_db...") and/or how did you do it?
    Which database version are we talking about (report all digits please) ?
  • 2. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    I use Release 11.2.0.1.0.

    First I tried to install using dbca. I got different errors.

    Then I run catqm.sql (with required 4 parameters) as sysdba.

    select comp_name "Component" from dba_registry shows me that XML_DB was installed.

    But I got an error using the package dbms_network_acl_admin. The body of the package was not valid.

    I set the XMLDB Tablespaces offline, drop them and run catnoqm do deinstall.
    Then I tried a second time with the same results as above.

    Finally I run utlrp.sql with the result "XDB now invalid, could not find xdbconfig".
  • 3. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    The Bodys of the needed packages fortunately are wrapped. So I can't check what is wrong.

    We migrated from 10g to 11g and use UTL_Mail from inside the database.
    In 10g that was no problem.

    Is there any other solution in 11g without defining ACLs using UTL_Mail ?
  • 4. Re: dbms_network_acl_admin error
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    user2627033 wrote:
    The Bodys of the needed packages fortunately are wrapped. So I can't check what is wrong.
    With good reason, they implement enhanced security in 11g
    We migrated from 10g to 11g and use UTL_Mail from inside the database.
    In 10g that was no problem.
    The background of this and quick & dirty workaround: http://www.liberidu.com/blog/2007/08/14/oracle-11g-security-xmldb-is-here-to-stay/
  • 5. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    Ok I understand the background .

    But sorry, I don't see the workaround using UTL_Mail without installing XML DB ?

    Without XML DB I don't have the packages dbms_network_acl_admin or dbms_xdb.

    My main problem is, that it's not posssible to install a valid XML DB and I don't no why.
  • 6. Re: dbms_network_acl_admin error
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    AFAIK UTL_MAIL has no dependance with XMLDB functionality. Its a wrapper around Java stuff and worked in older version even before version 9.2

    DBMS_NETWORK_ACL_ADMIN and DBMS_XDB are related by not mutually exclusive

    Does the following help:

    http://razikahamed.blogspot.nl/2011_09_01_archive.html
    http://www.orafaq.com/forum/t/176431/0/
    http://www.liberidu.com/blog/2009/07/13/howto-clean-xml-db-install/

    Check the following 3 scripts regarding DBMS_NETWORK_ACL_ADMIN and how and when to execute.

    In the end verify with @?/rdbms/admin/utlrp.sql

    Edited by: Marco Gralike on Jan 25, 2013 1:39 AM
  • 7. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    After running all scripts

    utlrp.sql shows

    XDB now invalid, invalid objects found
    object_name object_type
    DBMS_RESCONFIG PACKAGE_BODY

    Then I tried:

    BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'utlmail.xml',
    description => 'Permissions to access UTL_MAIL',
    principal => 'SYSADM',
    is_grant => TRUE,
    privilege => 'connect');
    COMMIT;
    END;
    Fehlerbericht:
    ORA-04063: package body "SYS.DBMS_NETWORK_ACL_ADMIN" enthält Fehler
    ORA-06508: PL/SQL: aufgerufene Programmeinheit : "SYS.DBMS_NETWORK_ACL_ADMIN" konnte nicht gefunden werden
    ORA-06512: in Zeile 2
    04063. 00000 - "%s has errors"
    *Cause:    Attempt to execute a stored procedure or use a view that has
    errors. For stored procedures, the problem could be syntax errors
    or references to other, non-existent procedures. For views,
    the problem could be a reference in the view's defining query to
    a non-existent table.
    Can also be a table which has references to non-existent or
    inaccessible types.
    *Action:   Fix the errors and/or create referenced objects as necessary.


    I look at the body of DBMS_NETWORK_ACL_ADMIN and try to compile and get following errors

    Fehler(84,5): PL/SQL: SQL Statement ignored
    Fehler(85,27): PL/SQL: ORA-01775: Zyklische Kette von Synonymen (looping chain of synonyms)
  • 8. Re: dbms_network_acl_admin error
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Is this a production environment?
  • 9. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    Yes, it's a production environment. That's the problem.

    I'm not so familiar with XML, but i think, i need a xdbconfig.xml in a special path.
    It's right or it's not relevant for me?

    Furthermore I read that the views RESOURCE_VIEW and PATH_VIEW has to be available.
    There is no PATH_VIEW in my database.

    Edited by: user2627033 on 25.01.2013 01:44
  • 10. Re: dbms_network_acl_admin error
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Okay. I think then that makes it very easy regarding the next step:

    1) Stop fiddling around on this production system.
    2) Log an SR with support.oracle.com for help to get this straight again.

    You should never just have executed those mentioned dbms_network_acl_admin database scripts on a production system.
  • 11. Re: dbms_network_acl_admin error
    User294075 Newbie
    Currently Being Moderated
    Have you tried yet by reloading XDB? Check ID 1429943.1. Solve common common invalid XDB obj issues.
  • 12. Re: dbms_network_acl_admin error
    986381 Newbie
    Currently Being Moderated
    The solution in my special case:

    Remove xmldb with catnoqm.sql.

    Compile all invalid objects.
    Check and remove the remaining invalid objects.

    Run utlrp.sql.

    All components and all objects was now valid.

    Run catqm.sql

    The installation was now successful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points