This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 17, 2012 8:22 AM by 918424 RSS

54 Invalid Objects when running utlrp.sql script

918424 Newbie
Currently Being Moderated
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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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

Legend

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