12 Replies Latest reply on Feb 26, 2013 11:40 AM by 986381

    dbms_network_acl_admin error

      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
          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
            I use Release

            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
              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
                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
                  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
                    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:


                    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
                      After running all scripts

                      utlrp.sql shows

                      XDB now invalid, invalid objects found
                      object_name object_type
                      DBMS_RESCONFIG PACKAGE_BODY

                      Then I tried:

                      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
                      acl => 'utlmail.xml',
                      description => 'Permissions to access UTL_MAIL',
                      principal => 'SYSADM',
                      is_grant => TRUE,
                      privilege => 'connect');
                      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
                        Is this a production environment?
                        • 9. Re: dbms_network_acl_admin error
                          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
                            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
                              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
                                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.