1 2 Previous Next 17 Replies Latest reply: Sep 17, 2012 10:22 AM by 918424 RSS

    54 Invalid Objects when running utlrp.sql script

    918424
      Hi all,
      I am migrating database from Windows Vista to Linux OEL5 using RMAN convert. When running utlrp.sql script as final step I am getting 54 invalid objects. I don't know how to rectify those objects. Can you give me an idea to resolve this?

      Thanks and regards,
      Vimal.

      All those invalid objects belong to any of the following category: Synonyms, Views and Package body. What should I do?

      Edited by: 915421 on Sep 14, 2012 6:49 AM
        • 1. Re: 54 Invalid Objects when running utlrp.sql script
          Hussein Sawwan-Oracle
          Please post the details of the application release, database version and OS.

          What are those invalid objects? Under what schema?

          What is the error you get when you compile those invalid objects manually?

          Thanks,
          Hussein
          • 2. Re: 54 Invalid Objects when running utlrp.sql script
            918424
            Hi Hussein,

            The database version is Oracle 10.2.0.1(32 bit). Migration is from Windows to Linux. Windows version is Vista-64 bit and Linux version is OEL5- 32 bit.


            The owner, object_name, object_type are as follows:


            SQL> select object_name,object_type,owner from dba_objects where status='INVALID';
            6
            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$OLAP2_AW_CATALOGS
            VIEW OLAPSYS

            ALL_OLAP2_AW_CATALOGS
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CATALOG_MEASURES
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_OLAP2_AW_CATALOG_MEASURES
            SYNONYM PUBLIC

            ALL$OLAP2_AW_PHYS_OBJ
            VIEW OLAPSYS

            ALL_OLAP2_AW_PHYS_OBJ
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$OLAP2_AW_PHYS_OBJ_PROP
            VIEW OLAPSYS

            ALL_OLAP2_AW_PHYS_OBJ_PROP
            SYNONYM PUBLIC

            ALL$OLAP2_AW_DIMENSIONS
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_OLAP2_AW_DIMENSIONS
            SYNONYM PUBLIC

            ALL$OLAP2_AW_ATTRIBUTES
            VIEW OLAPSYS

            ALL_OLAP2_AW_ATTRIBUTES
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$OLAP2_AW_CUBES
            VIEW OLAPSYS

            ALL_OLAP2_AW_CUBES
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CUBE_DIM_USES
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_OLAP2_AW_CUBE_DIM_USES
            SYNONYM PUBLIC

            ALL$AW_DIM_ENABLED_VIEWS
            VIEW OLAPSYS

            ALL_AW_DIM_ENABLED_VIEWS
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$AW_CUBE_ENABLED_VIEWS
            VIEW OLAPSYS

            ALL_AW_CUBE_ENABLED_VIEWS
            SYNONYM PUBLIC

            ALL$AW_CUBE_ENABLED_HIERCOMBO
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_AW_CUBE_ENABLED_HIERCOMBO
            SYNONYM PUBLIC

            ALL$OLAP2_AW_DIM_LEVELS
            VIEW OLAPSYS

            ALL_OLAP2_AW_DIM_LEVELS
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$OLAP2_AW_DIM_HIER_LVL_ORD
            VIEW OLAPSYS

            ALL_OLAP2_AW_DIM_HIER_LVL_ORD
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CUBE_MEASURES
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_OLAP2_AW_CUBE_MEASURES
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CUBE_AGG_SPECS
            VIEW OLAPSYS

            ALL_OLAP2_AW_CUBE_AGG_SPECS
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL$OLAP2_AW_CUBE_AGG_MEAS
            VIEW OLAPSYS

            ALL_OLAP2_AW_CUBE_AGG_MEAS
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CUBE_AGG_LVL
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            ALL_OLAP2_AW_CUBE_AGG_LVL
            SYNONYM PUBLIC

            ALL$OLAP2_AW_CUBE_AGG_OP
            VIEW OLAPSYS

            ALL_OLAP2_AW_CUBE_AGG_OP
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            MRAC_OLAP2_AW_DIMENSIONS_V
            VIEW OLAPSYS

            MRAC_OLAP2_AW_ATTRIBUTES_V
            VIEW OLAPSYS

            MRAC_OLAP2_AW_CUBES_V
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            MRAC_OLAP2_AW_CUBE_MEASURES_V
            VIEW OLAPSYS

            MRV_OLAP2_AW_DIMENSIONS
            SYNONYM PUBLIC

            MRV_OLAP2_AW_ATTRIBUTES
            SYNONYM PUBLIC


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            MRV_OLAP2_AW_CUBES
            SYNONYM PUBLIC

            MRV_OLAP2_AW_CUBE_MEASURES
            SYNONYM PUBLIC

            CWM2_OLAP_METADATA_REFRESH
            PACKAGE BODY OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            OLAP_SYS_AW_ACCESS_DIM_VIEW
            VIEW OLAPSYS

            OLAP_SYS_AW_ACCESS_CUBE_VIEW
            VIEW OLAPSYS

            OLAP_SYS_AW_ENABLE_ACCESS_VIEW
            VIEW OLAPSYS


            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE OWNER
            ------------------- ------------------------------
            OLAP_SYS_AW_ACCESS_CUBE_VIEW
            SYNONYM PUBLIC

            OLAP_SYS_AW_ACCESS_DIM_VIEW
            SYNONYM PUBLIC

            OLAP_SYS_AW_ENABLE_ACCESS_VIEW
            SYNONYM PUBLIC


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

            DBMS_AWM
            PACKAGE BODY OLAPSYS

            CWM2_OLAP_CUBE
            PACKAGE BODY OLAPSYS


            54 rows selected.



            When I try to compile it manually, I get:

            SQL> alter package CWM2_OLAP_OLAPAPI_ENABLE compile body;
            alter package CWM2_OLAP_OLAPAPI_ENABLE compile body
            *
            ERROR at line 1:
            ORA-04043: object CWM2_OLAP_OLAPAPI_ENABLE does not exist


            I get same error for other invalid objects also.

            Thanks.
            • 3. Re: 54 Invalid Objects when running utlrp.sql script
              Hussein Sawwan-Oracle
              Please connect as apps user and compile those invalid objects manually.

              Or, use the schema name as a prefix when you issue the alter command.
              ORA-04043: object CWM2_OLAP_OLAPAPI_ENABLE does not exist
              For example, instead of:
              SQL>  alter view OLAP_SYS_AW_ACCESS_DIM_VIEW compile;
              Issue:
              SQL> alter view OLAPSYS.OLAP_SYS_AW_ACCESS_DIM_VIEW compile;
              Thanks,
              Hussein
              • 4. Re: 54 Invalid Objects when running utlrp.sql script
                918424
                Hi Hussein,

                I did as you said. But I get Warning: View altered with compilation errors.+

                After this command I checked with total invalid objects again, I get same as before: 54 invalid objects.

                Thanks.
                • 5. Re: 54 Invalid Objects when running utlrp.sql script
                  Hussein Sawwan-Oracle
                  915421 wrote:
                  Hi Hussein,

                  I did as you said. But I get Warning: View altered with compilation errors.+

                  After this command I checked with total invalid objects again, I get same as before: 54 invalid objects.

                  Thanks.
                  Type "show error" from SQL*Plus after you issue the alter command.

                  Thanks,
                  Hussein
                  • 6. Re: 54 Invalid Objects when running utlrp.sql script
                    Srini Chavali-Oracle
                    Was OLAP option installed on the target database ? Pl see this MOS Doc

                    Invalid OLAP Objects When Upgrading An Oracle 10.2.0.1 Installation, without OLAP, to 10.2.0.2 and Higher [ID 455839.1]

                    HTH
                    Srini
                    • 7. Re: 54 Invalid Objects when running utlrp.sql script
                      Hussein Sawwan-Oracle
                      If this is an Oracle E-Business Suite, please make sure you follow the steps in these docs (according to your application release).

                      10g Release 2 Export/Import Process for Oracle Applications Release 11i [ID 362205.1] -- 9. Install other required components
                      Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2 [ID 454616.1]

                      Thanks,
                      Hussein
                      • 8. Re: 54 Invalid Objects when running utlrp.sql script
                        918424
                        Sorry for the late reply since I was out of my home yesterday....

                        Srini I have OLAP option already installed in my DB.

                        Hussein after compilation, when I ask it to show the errors I get the following result...

                        SQL> show errors
                        Errors for PACKAGE BODY OLAPSYS.CWM2_OLAP_CUBE:

                        LINE/COL ERROR
                        -------- -----------------------------------------------------------------
                        588/28 PL/SQL: SQL Statement ignored
                        588/55 PL/SQL: ORA-04063: view "OLAPSYS.OLAP_SYS_AW_ENABLE_ACCESS_VIEW"
                        has errors

                        Thanks.
                        • 9. Re: 54 Invalid Objects when running utlrp.sql script
                          Srini Chavali-Oracle
                          I was referring to the OLAP option on the target installation (Linux)- pl see docs referred by Hussein above

                          How To Manually Install Oracle OLAP In 9i, 10g Or 11g Database After The DB Has Been Created [ID 296187.1]

                          HTH
                          Srini
                          • 10. Re: 54 Invalid Objects when running utlrp.sql script
                            918424
                            Srini,

                            Yes, I have OLAP installed already in Linux as well.

                            After getting the above error(shown in previous post), I have decided to start everything from the beginning. At this time, the utlrp.sql script didn't even succeed, saying the error that "not connected to Oracle". So, I uninstalled the OLAP option from my Linux and re-ran the utlrp.sql script again and successfully completed. But the problem now is that I get the error like:


                            SQL> show error
                            Errors for PACKAGE BODY SYS.CWM2_OLAP_INSTALLER:

                            LINE/COL ERROR
                            -------- -----------------------------------------------------------------
                            5/3 PL/SQL: Item ignored
                            7/5 PL/SQL: Statement ignored
                            7/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.CREATE_MEASURE' must be
                            declared

                            10/5 PL/SQL: Statement ignored
                            10/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.DROP_MEASURE' must be
                            declared

                            12/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                            be declared

                            20/3 PL/SQL: Item ignored
                            22/5 PL/SQL: Statement ignored
                            22/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.CREATE_CUBE' must be
                            declared

                            25/5 PL/SQL: Statement ignored
                            25/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.DROP_CUBE' must be declared
                            27/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                            be declared

                            35/3 PL/SQL: Item ignored
                            37/5 PL/SQL: Statement ignored
                            37/5 PLS-00201: identifier
                            'CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE' must be
                            declared

                            40/5 PL/SQL: Statement ignored
                            40/5 PLS-00201: identifier
                            'CWM2_OLAP_LEVEL_ATTRIBUTE.DROP_LEVEL_ATTRIBUTE' must be declared

                            42/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                            be declared

                            52/5 PL/SQL: Statement ignored
                            52/5 PLS-00201: identifier 'CWM2_OLAP_LEVEL.CREATE_LEVEL' must be
                            declared

                            SQL>

                            Regarding the metalink ID, I have no access to use Oracle metalink unfortunately..

                            Kindly, guide me please...

                            Edited by: 915421 on Sep 16, 2012 5:50 AM
                            • 11. Re: 54 Invalid Objects when running utlrp.sql script
                              Srini Chavali-Oracle
                              Pl post exact database version. OLAP option is required for EBS install - pl indicate how you enabled/disabled it. Access to MOS is required to follow the steps in the MOS Doc indicated by Hussein.

                              HTH
                              Srini
                              • 12. Re: 54 Invalid Objects when running utlrp.sql script
                                918424
                                Hi Srini,

                                The database version is Oracle 10.2.0.1(32 bit) installed on Windows Vista 64 bit. Migration is from Windows to Linux. Linux version is OEL5- 32 bit. I wonder if it is possible to migrate with these features.

                                I disabled OLAP with:

                                SQL> conn / as sysdba
                                SQL> @? / Olap / admin / catnoamd.sql
                                SQL> @? / Olap / admin / olapidrp.plb
                                SQL> @? / Olap / admin / catnoaps.sql
                                SQL> @? / Olap / admin / catnoxoq.sql

                                I can enable with olap.sql script.

                                But after disabling it I can successfully run the utlrp.sql script, but with invalid objects.

                                SQL> show error
                                Errors for PACKAGE BODY SYS.CWM2_OLAP_INSTALLER:

                                LINE/COL ERROR
                                -------- -----------------------------------------------------------------
                                5/3 PL/SQL: Item ignored
                                7/5 PL/SQL: Statement ignored
                                7/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.CREATE_MEASURE' must be
                                declared

                                10/5 PL/SQL: Statement ignored
                                10/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.DROP_MEASURE' must be
                                declared

                                12/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                                be declared

                                20/3 PL/SQL: Item ignored
                                22/5 PL/SQL: Statement ignored
                                22/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.CREATE_CUBE' must be
                                declared

                                25/5 PL/SQL: Statement ignored
                                25/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.DROP_CUBE' must be declared
                                27/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                                be declared

                                35/3 PL/SQL: Item ignored
                                37/5 PL/SQL: Statement ignored
                                37/5 PLS-00201: identifier
                                'CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE' must be
                                declared

                                40/5 PL/SQL: Statement ignored
                                40/5 PLS-00201: identifier
                                'CWM2_OLAP_LEVEL_ATTRIBUTE.DROP_LEVEL_ATTRIBUTE' must be declared

                                42/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
                                be declared

                                52/5 PL/SQL: Statement ignored
                                52/5 PLS-00201: identifier 'CWM2_OLAP_LEVEL.CREATE_LEVEL' must be
                                declared

                                SQL>

                                What must I do to compile them all?
                                • 13. Re: 54 Invalid Objects when running utlrp.sql script
                                  Hussein Sawwan-Oracle
                                  915421 wrote:
                                  Sorry for the late reply since I was out of my home yesterday....

                                  Srini I have OLAP option already installed in my DB.

                                  Hussein after compilation, when I ask it to show the errors I get the following result...

                                  SQL> show errors
                                  Errors for PACKAGE BODY OLAPSYS.CWM2_OLAP_CUBE:

                                  LINE/COL ERROR
                                  -------- -----------------------------------------------------------------
                                  588/28 PL/SQL: SQL Statement ignored
                                  588/55 PL/SQL: ORA-04063: view "OLAPSYS.OLAP_SYS_AW_ENABLE_ACCESS_VIEW"
                                  has errors

                                  Thanks.
                                  What is the ouput of this query?
                                  SQL> select comp_name, version, status 
                                  from dba_registry
                                  where comp_name like '%OLAP%';
                                  Thanks,
                                  Hussein
                                  • 14. Re: 54 Invalid Objects when running utlrp.sql script
                                    Hussein Sawwan-Oracle
                                    Regarding the metalink ID, I have no access to use Oracle metalink unfortunately..

                                    Kindly, guide me please...
                                    You need to have access to MOS to review those docs.

                                    I would also suggest you review (Can Not Use OLAP After Upgrading To OLAP 10.2.0.3.0 Due to INVALID OLAP Option And Errors Like ORA-04063 [ID 421671.1]) once you get an access.

                                    Please note that posting the contents of MOS doc is not an option since this violates Oracle support agreement policy.

                                    Thanks,
                                    Hussein
                                    1 2 Previous Next