7 Replies Latest reply: Nov 11, 2012 8:31 PM by 925887 RSS

    Upgrade error

    925887
      Hi,

      Version 11.2.0.3
      Os: Aix 6

      We recently migrated the database from 11.2.0.1 to 11.2.0.3.After migration,we got the invalid object in OLAPSYS schema.

      SQL> select object_name ,owner,object_type from dba_objects where status='INVALID';

      OBJECT_NAME OWNER OBJECT_TYPE
      ----------------------- ------------------------------ -------------------
      CWM2_OLAP_OLAPAPI_ENABLE OLAPSYS PACKAGE BODY


      I tried to compile but getting error

      SQL> alter PACKAGE OLAPSYS.CWM2_OLAP_OLAPAPI_ENABLe compile BODY;

      Warning: Package Body altered with compilation errors.


      SQL> show error
      Errors for PACKAGE BODY OLAPSYS.CWM2_OLAP_OLAPAPI_ENABLE:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      93/30 PL/SQL: SQL Statement ignored
      93/57 PL/SQL: ORA-01775: looping chain of synonyms
      491/33 PL/SQL: SQL Statement ignored
      491/64 PL/SQL: ORA-01775: looping chain of synonyms
      635/32 PL/SQL: SQL Statement ignored
      635/63 PL/SQL: ORA-01775: looping chain of synonyms
      637/27 PL/SQL: SQL Statement ignored
      637/58 PL/SQL: ORA-01775: looping chain of synonyms
      639/32 PL/SQL: SQL Statement ignored
      639/63 PL/SQL: ORA-01775: looping chain of synonyms
      1141/34 PL/SQL: SQL Statement ignored

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      1141/66 PL/SQL: ORA-01775: looping chain of synonyms
      1277/33 PL/SQL: SQL Statement ignored
      1277/65 PL/SQL: ORA-01775: looping chain of synonyms
      1279/31 PL/SQL: SQL Statement ignored
      1279/63 PL/SQL: ORA-01775: looping chain of synonyms
      1732/33 PL/SQL: SQL Statement ignored
      1732/64 PL/SQL: ORA-01775: looping chain of synonyms
      1913/34 PL/SQL: SQL Statement ignored
      1913/66 PL/SQL: ORA-01775: looping chain of synonyms
      SQL>

      Any suggestion


      Thanks
        • 1. Re: Upgrade error
          mseberg
          Hello

          Please refer to this MOS note :

          OLAPSYS.CWM2_OLAP_OLAPAPI_ENABLE Package Body Is Invalid [ID 731574.1]

          Best Regards

          mseberg
          • 2. Re: Upgrade error
            Niket Kumar
            http://www.dba-oracle.com/sf_ora_01775_looping_chain_of_synonyms_bc1.htm
            • 3. Re: Upgrade error
              925887
              Hi,

              Thanks

              I got the following error while execute cwm2awen.plb


              SQL> @cwm2awen.plb

              Warning: Package Body created with compilation errors.

              Errors for PACKAGE BODY CWM2_OLAP_OLAPAPI_ENABLE:

              LINE/COL ERROR
              -------- -----------------------------------------------------------------
              0/0 PL/SQL: Compilation unit analysis terminated
              1/14 PLS-00304: cannot compile body of 'CWM2_OLAP_OLAPAPI_ENABLE'
              without its specification



              SQL> select owner,status,object_type from dba_objects where object_name =
              'CWM2_OLAP_OLAPAPI_ENABLE' 2 ;

              OWNER STATUS OBJECT_TYPE
              ------------------------------ ------- -------------------
              SYS INVALID PACKAGE BODY
              PUBLIC VALID SYNONYM
              OLAPSYS VALID PACKAGE
              OLAPSYS INVALID PACKAGE BODY

              Could i follow 464499.1(it is related to 9i to 10g) ?but we migrated from 11.2.0.1 to 11.2.0.3

              Any suggestion

              Thanks
              • 4. Re: Upgrade error
                973636
                How you are recompiling using utlrpt.sql or manually.I guess manually so try it using utlrpt.sql and if it doesn't work then
                If you are not using OLAP then remove OLAP api deinstalll it's components then re compile everything.

                --Remove OLAP catalog
                SQL>@catnoamd.sql

                --Remove OLAP API
                SQL>@olapidrp.plb
                SQL>@catnoxoq.sql

                --Deinstall OLAP-AW components
                SQL>@catnoaps.sql
                SQL>@cwm2drop.sql

                --Recompile everything
                SQL>@?\rdbms\admin\utlrp.sql

                --Check for invalids
                SQL> select owner, object_name, object_type, status from dba_objects where
                status='INVALID';

                --You will see several invalids in SYS(around 4) and PUBLIC(around 97) schemas.

                --Re-install OLAP

                SQL>@olap.sql SYSAUX TEMP
                • 5. Re: Upgrade error
                  mseberg
                  I don't like to guess. That said I ran into a similar problem with an upgrade from 11.2.0.2 to 11.2.0.3.

                  I had kept notes and in my case I had removed several permissions from public. My work around was to grant these back OLAPSYS as follows :
                  GRANT EXECUTE ON DBMS_LOB TO OLAPSYS;
                  
                  GRANT EXECUTE ON UTL_FILE TO OLAPSYS;
                  
                  GRANT EXECUTE ON SYS.DBMS_JOB to  OLAPSYS;
                  
                  GRANT EXECUTE ON SYS.UTL_SMTP TO OLAPSYS;
                  
                  GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO OLAPSYS;
                  
                  GRANT EXECUTE ON SYS.UTL_HTTP TO OLAPSYS;
                  
                  GRANT EXECUTE ON SYS.UTL_TCP TO OLAPSYS;
                  
                  GRANT EXECUTE ON DBMS_RANDOM TO OLAPSYS;
                  Then I was able to recompile the invalids.

                  Before considering this you might check these objects and their current permissions and only add the least that you need.

                  Best Regards

                  mseberg
                  • 6. Re: Upgrade error
                    Srini Chavali-Oracle
                    Pl identify the method used for the upgrade - did it complete successfully without any errors ? Pl post the output of the following -
                    SQL> select COMP_ID, COMP_NAME, STATUS, VERSION from DBA_REGISTRY;
                    Is this the only invalid object ? If not, can you post a full listing of all of the invalids ?

                    This MOS Doc may be relevant (per mseberg)

                    Invalid Objects In OLAPSYS Schema and Unable To Recompile [ID 820783.1]

                    HTH
                    Srini
                    • 7. Re: Upgrade error
                      925887
                      Hi,

                      Thanks
                      COMP_ID COMP_NAME STATU VERSION
                      --------------- -------------------------------- ----- ------------------------------
                      OWB OWB VALID 11.2.0.1.0
                      APEX Oracle Application Express VALID 3.2.1.00.10
                      EM Oracle Enterprise Manager VALID 11.2.0.3.0
                      OLS Oracle Label Security OPTION OFF 11.2.0.1.0
                      AMD OLAP Catalog VALID 11.2.0.3.0
                      SDO Spatial VALID 11.2.0.3.0
                      ORDIM Oracle Multimedia VALID 11.2.0.3.0
                      XDB Oracle XML Database VALID 11.2.0.3.0
                      CONTEXT Oracle Text VALID 11.2.0.3.0
                      EXF Oracle Expression Filter VALID 11.2.0.3.0
                      RUL Oracle Rules Manager VALID 11.2.0.3.0
                      OWM Oracle Workspace Manager VALID 11.2.0.3.0
                      CATALOG Oracle Database Catalog Views VALID 11.2.0.3.0
                      CATPROC Oracle Database Packages and Types VALID 11.2.0.3.0
                      JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.3.0
                      XML Oracle XDK VALID 11.2.0.3.0
                      CATJAVA Oracle Database Java Packages VALID 11.2.0.3.0
                      APS OLAP Analytic Workspace VALID 11.2.0.3.0
                      XOQ Oracle OLAP API VALID 11.2.0.3.0
                      19 rows selected.
                      Invalid objects in db


                      After (once followed 820783.1) i run utlrp, invalid objects are increasing
                      OBJECT_NAME STATUS OWNER
                      -------------------------------- --------------- ------------------------------
                      CWM2_OLAP_OLAPAPI_ENABLE INVALID SYS
                      SQL_SHARED_CURSOR INVALID SYS
                      DBMS_XSLPROCESSOR INVALID XDB
                      DBMS_XMLPARSER INVALID XDB
                      SDO_ROUTER_PARTITION INVALID MDSYS
                      SDO_NET INVALID MDSYS
                      SDO_NET_PARTITION INVALID MDSYS
                      SDO_NET_PARTITION INVALID MDSYS
                      CWM2_OLAP_OLAPAPI_ENABLE INVALID OLAPSYS
                      Thanks

                      Edited by: 922884 on Nov 11, 2012 2:50 PM