1 2 Previous Next 16 Replies Latest reply: Apr 3, 2012 10:07 AM by 928233 RSS

    11gR2 Upgrade and Invalid Objects

    808131
      Trying to upgrade from 10gR2 to 11gR2 using the expdp / impdp method. I've got an 11gR2 database created (with the standard schemas). I took a full database export of my 10gR2 database and moved the file over to the new server. I import the dumpfile using the impdp full=y method. And I'm getting several thousand errors. Mostly tables/objects already exists because they were created during the database creation. But when I run the utlrp.sql script i get:
      OBJECTS WITH ERRORS
      -------------------
      0

      DOC>#

      ERRORS DURING RECOMPILATION
      ---------------------------
      0

      when there really is invalid objects... A bunch of invalid objects:

      SQL> select count(*) from dba_objects where status!='VALID';

      COUNT(*)
      ----------
      43
      My source database only had one invalid object ... So i'm not sure why these are all invalid. When I try to compile them they don't want to compile either ... So I'm not sure if something is happening during my full datatabase import or whats going on.
        • 1. Re: 11gR2 Upgrade and Invalid Objects
          Srini Chavali-Oracle
          Pl provide a list of the 43 invalid objects. Are these seeded Oracle objects or custom objects ? What happens if you compile these objects manually ? Can you post the errors from the compile ?

          HTH
          Srini
          • 2. Re: 11gR2 Upgrade and Invalid Objects
            808131
            SQL> select owner, object_name, object_type, status from dba_objects where status!='VALID' order by 1;

            OWNER OBJECT_NAME OBJECT_TYPE STATUS
            ------------------------------ ------------------------------ ------------------- -------
            BANINST1 BWFKTDEL PACKAGE BODY INVALID
            BANINST1 BWPKFJDT PACKAGE BODY INVALID
            BANINST1 BWGKJPAY PACKAGE BODY INVALID
            BANINST1 BWPKFJOB PACKAGE BODY INVALID
            BANINST1 GB_EVENT PACKAGE BODY INVALID
            BANINST1 GOKDBMS PACKAGE BODY INVALID
            BANINST1 GOKOUTD PACKAGE BODY INVALID
            BANINST1 GOKOUTP PACKAGE BODY INVALID
            BANINST1 GOKSSSO PACKAGE BODY INVALID
            BANINST1 NOKEPAF PACKAGE BODY INVALID
            BANINST1 PXKW2TS PACKAGE BODY INVALID
            BANINST1 SFKPREQ PACKAGE BODY INVALID
            BANSECR GSPCRPT PACKAGE BODY INVALID
            BANSSO GOKCSSO PACKAGE BODY INVALID
            DMSYS DBMS_DM_UTIL_INTERNAL PACKAGE BODY INVALID
            DMSYS DBMS_DM_UTIL PACKAGE BODY INVALID
            DMSYS DBMS_DM_IMP_INTERNAL PACKAGE BODY INVALID
            DMSYS DBMS_DM_EXP_INTERNAL PACKAGE BODY INVALID
            DMSYS DBMS_DATA_MINING_INTERNAL PACKAGE BODY INVALID
            DMSYS DBMS_DATA_MINING PACKAGE BODY INVALID
            DMSYS DMP_SYS PACKAGE INVALID
            DMSYS DMP_SYS PACKAGE BODY INVALID
            DMSYS ODM_ABN_MODEL PACKAGE BODY INVALID
            DMSYS ODM_ASSOCIATION_RULE_MODEL PACKAGE BODY INVALID
            DMSYS ODM_ATTRIBUTE_IMPORTANCE_MODEL PACKAGE BODY INVALID
            DMSYS ODM_CLUSTERING_UTIL PACKAGE BODY INVALID
            DMSYS ODM_MODEL_UTIL PACKAGE BODY INVALID
            DMSYS ODM_NAIVE_BAYES_MODEL PACKAGE BODY INVALID
            DMSYS ODM_OC_CLUSTERING_MODEL PACKAGE BODY INVALID
            DMSYS ODM_UTIL PACKAGE BODY INVALID
            DMSYS DBMS_JDM_INTERNAL PACKAGE BODY INVALID
            DMSYS DMP_SEC PACKAGE BODY INVALID
            DMSYS DBMS_PREDICTIVE_ANALYTICS PACKAGE BODY INVALID
            PUBLIC GSVAPDT SYNONYM INVALID
            PUBLIC GSVCADT SYNONYM INVALID
            PUBLIC GSVAPST SYNONYM INVALID
            SATURN ST_STVTERM_AR_LDI TRIGGER INVALID
            STREAMSADMIN GSVAPDT VIEW INVALID
            STREAMSADMIN GP_STREAMS_UTIL PACKAGE BODY INVALID
            STREAMSADMIN GP_STREAMS_RULES PACKAGE BODY INVALID
            STREAMSADMIN GP_STREAMS_ERROR PACKAGE BODY INVALID
            STREAMSADMIN GSVCADT VIEW INVALID
            STREAMSADMIN GSVAPST VIEW INVALID

            43 rows selected.
            • 3. Re: 11gR2 Upgrade and Invalid Objects
              Helios-GunesEROL
              Hi;

              Pelase see:
              PLS-00201: identifier 'DBMS_RANDOM' must be declared When Compiling Invalid Data Mining Objects [ID 727867.1]

              Regard
              Helios
              • 4. Re: 11gR2 Upgrade and Invalid Objects
                808131
                It seems like a lot of my users had grant execute on XXXXX; but they weren't carried over during the export and import process. Is there an easy way to fix that? OR did I miss something in the export/import process?

                Thanks.
                • 5. Re: 11gR2 Upgrade and Invalid Objects
                  Srini Chavali-Oracle
                  GRANTs should be carried over - can you pl post the expdp and impdp commands used ? Are there any grant related errors in either the expdp or impdp logs ?

                  Srini
                  • 6. Re: 11gR2 Upgrade and Invalid Objects
                    808131
                    expdp userid=system/******** dumpfile=test.dmp logfile=test.log directory=dumpfile full=y

                    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
                    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
                    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
                    Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
                    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4110 error(s) at 11:13:24


                    Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
                    ******************************************************************************
                    Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
                    /u02/oradata/dumpfile/test.dmp
                    Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 0 error(s) at 08:31:49


                    impdp userid=system/******** directory=dumpfile dumpfile=test.dmp logfile=test.log full=y




                    All of the errors are for objects/tables/tablespaces/grants/views already exist.

                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_COLL_METRIC" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_MNTR_CA" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_MNTR_COLLECTION" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_PDP_COLUMN_META" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_PDP_SETTING_META" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_PDP_PARAM_META" already exists
                    ORA-31684: Object type TYPE_BODY:"SYSMAN"."MGMT_PDP_META" already exists
                    • 7. Re: 11gR2 Upgrade and Invalid Objects
                      808131
                      I've got the list down to:

                      OWNER OBJECT_NAME OBJECT_TYPE STATUS
                      -------------------- ------------------------------ ------------------------------ -------
                      BANINST1 BWFKTDEL PACKAGE BODY INVALID
                      BANSSO GOKCSSO PACKAGE BODY INVALID
                      DMSYS DBMS_DATA_MINING_INTERNAL PACKAGE BODY INVALID
                      DMSYS DMP_SYS PACKAGE BODY INVALID
                      DMSYS DMP_SEC PACKAGE BODY INVALID
                      DMSYS DBMS_DATA_MINING PACKAGE BODY INVALID
                      DMSYS DBMS_DM_IMP_INTERNAL PACKAGE BODY INVALID
                      PUBLIC GSVAPST SYNONYM INVALID
                      PUBLIC GSVAPDT SYNONYM INVALID
                      PUBLIC GSVCADT SYNONYM INVALID
                      SATURN ST_STVTERM_AR_LDI TRIGGER INVALID
                      STREAMSADMIN GSVAPDT VIEW INVALID
                      STREAMSADMIN GSVAPST VIEW INVALID
                      STREAMSADMIN GSVCADT VIEW INVALID
                      STREAMSADMIN GP_STREAMS_ERROR PACKAGE BODY INVALID
                      STREAMSADMIN GP_STREAMS_RULES PACKAGE BODY INVALID
                      STREAMSADMIN GP_STREAMS_UTIL PACKAGE BODY INVALID

                      17 rows selected.


                      I'm manually trying to compile the objects and most of them aren't compiling because a table doesn't exist. So i'm still looking into that.

                      Errors for PACKAGE BODY DMP_SEC:

                      LINE/COL ERROR
                      -------- -----------------------------------------------------------------
                      40/3 PL/SQL: SQL Statement ignored
                      40/10 PL/SQL: ORA-00942: table or view does not exist
                      57/9 PL/SQL: SQL Statement ignored
                      57/21 PL/SQL: ORA-00942: table or view does not exist
                      77/21 PL/SQL: Item ignored
                      77/21 PLS-00201: identifier 'DM$P_MODEL.ALGORITHM_NAME' must be
                      declared

                      82/3 PL/SQL: SQL Statement ignored
                      83/10 PL/SQL: ORA-00942: table or view does not exist
                      85/3 PL/SQL: Statement ignored
                      85/10 PLS-00320: the declaration of the type of this expression is
                      incomplete or malformed

                      91/21 PL/SQL: Item ignored
                      91/21 PLS-00201: identifier 'DM$P_MODEL.FUNCTION_NAME' must be declared
                      96/3 PL/SQL: SQL Statement ignored
                      97/10 PL/SQL: ORA-00942: table or view does not exist
                      99/3 PL/SQL: Statement ignored
                      99/10 PLS-00320: the declaration of the type of this expression is
                      incomplete or malformed

                      105/18 PL/SQL: Item ignored
                      105/18 PLS-00201: identifier 'DM$P_MODEL.MODEL_SIZE' must be declared
                      110/3 PL/SQL: SQL Statement ignored
                      111/10 PL/SQL: ORA-00942: table or view does not exist


                      I feel like i'm missing something ...
                      • 8. Re: 11gR2 Upgrade and Invalid Objects
                        Srini Chavali-Oracle
                        Do you use the Data Mining option ? What is the missing table ? Is manually reinstalling the DMSYS schema an option ?

                        How To Manually Install Data Mining In Oracle 11g? (Doc ID 818314.1)

                        Srini
                        • 9. Re: 11gR2 Upgrade and Invalid Objects
                          808131
                          I really don't think we use it, but I'm not sure. Is there a way to tell?
                          • 10. Re: 11gR2 Upgrade and Invalid Objects
                            Srini Chavali-Oracle
                            I have not used DM, so not sure how to tell if it is being used. Pl check with your Dev team and ask if they are using any features of DM

                            Srini
                            • 11. Re: 11gR2 Upgrade and Invalid Objects
                              808131
                              I guess I'm not quite so concerned about the DMSYS user objects as I am about this whole process in general. I mean why are there 40ish objects that needed to be compiled after the import? And why am I having to go through and manually apply grant execute on XXX to user; to be able to get these to compile. Is there something I'm missing in the import / export process?
                              • 12. Re: 11gR2 Upgrade and Invalid Objects
                                Srini Chavali-Oracle
                                805128 wrote:
                                I guess I'm not quite so concerned about the DMSYS user objects as I am about this whole process in general. I mean why are there 40ish objects that needed to be compiled after the import? And why am I having to go through and manually apply grant execute on XXX to user; to be able to get these to compile. Is there something I'm missing in the import / export process?
                                The export/import process does port grants - you do not have to grant them explicitly on the target. You will have to look thru your expdp and impdp logs to see what the issue is. Are these seeded grants ? or custom grants ?

                                Srini
                                • 13. Re: 11gR2 Upgrade and Invalid Objects
                                  808131
                                  Does it bring over system grants too?
                                  • 14. Re: 11gR2 Upgrade and Invalid Objects
                                    Srini Chavali-Oracle
                                    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1006790

                                    Srini
                                    1 2 Previous Next