9 Replies Latest reply: Aug 13, 2008 4:16 PM by 616055 RSS

    Data Pump Import Order of Operations

    616055
      Hi all,

      I successfully did a schema only export of one of our schemas and want to now use data pump import to install that schema on another db. It runs but with errors; The first thing it does is try to issue grants but reports errors that the object does not exist. Why does it start with grants? Grants should be one of the LAST things it does, not the very first!

      Is there some way to correct this or do I need to do the export differently?

      Thanks,
      Dave

      Message was edited by:
      Gib2008

      Message was edited by:
      Gib2008
        • 1. Re: Data Pump Import Order of Operations
          591186
          ?It runs but with errors

          what are the errors?
          What is the impdp parameters used?
          • 2. Re: Data Pump Import Order of Operations
            616055
            ORA-39083: Object type ROLE_GRANT failed to create with error:
            ORA-01919: role 'FE_WEB_PAGE' does not exist
            Failing sql is:
            GRANT "FE_WEB_PAGE" TO "JAVA" WITH ADMIN OPTION

            ORA-39083: Object type ROLE_GRANT failed to create with error:
            ORA-01919: role 'REPORT' does not exist
            Failing sql is:
            GRANT "REPORT" TO "JAVA" WITH ADMIN OPTION


            directory=arch_dir
            dumpfile=java_meta.dmp
            schemas=java
            logfile=java_meta_import.log
            transform=segment_attributes:n
            • 3. Re: Data Pump Import Order of Operations
              616055
              I think part of the problem is that the users didn't get re-created from the other db. I thought, logically, their definitions would get zipped up in the export since Im granting scema objects to them. However, I will make sure they exsist first and try it again.
              • 4. Re: Data Pump Import Order of Operations
                591186
                Use SQLFILE option to generate the SChema DDL and execute it against the Database and proceed with import.

                While import, use TABLE_EXISTS_ACTION (equivalen to ignore=y in imp) to avoid errors.
                • 5. Re: Data Pump Import Order of Operations
                  616055
                  Thanks for the info Anantha, I will give that a try and post back.
                  • 6. Re: Data Pump Import Order of Operations
                    247514
                    ORA-39083: Object type ROLE_GRANT failed to create
                    with error:
                    ORA-01919: role 'FE_WEB_PAGE' does not exist
                    Failing sql is:
                    GRANT "FE_WEB_PAGE" TO "JAVA" WITH ADMIN OPTION

                    ORA-39083: Object type ROLE_GRANT failed to create
                    with error:
                    ORA-01919: role 'REPORT' does not exist
                    Failing sql is:
                    GRANT "REPORT" TO "JAVA" WITH ADMIN OPTION


                    directory=arch_dir
                    dumpfile=java_meta.dmp
                    schemas=java
                    logfile=java_meta_import.log
                    transform=segment_attributes:n
                    As the error suggested, role REPORT and FE_WEB_PAGE do not exist in target database. You need to create them separately. As role is not part of schema object so it's not included in schema level export.
                    • 7. Re: Data Pump Import Order of Operations
                      616055
                      Anantha,

                      I tried making sqlfile but that just does the same thing and is adding unnecessary complexity. All the SQLFILE is doing is telling it to write out the commands to a file instead of actually playing the commands. This is not what I want do to. I want to be able to call the impdp command and have it play them. Thank-you for your help though. I also want table_exists_action=replace (not ignore) since this will be a weekly refresh of a post testing enviornment that we load with some of the prod data so our batch processes can be tested with live data.

                      Thanks,
                      Dave
                      • 8. Re: Data Pump Import Order of Operations
                        591186
                        As posted by Yinkguan, You have to create and Grant the ROLES and Privileges for clean import.

                        SQLFILE is equivalent show=y &indexfile options in normal import utility.

                        Have the Schema created with Roles and Grants and let the impdp to import the data.

                        How To Copy Database Schemas To A New Database With Same Login Password ? - Note:336012.1.
                        • 9. Re: Data Pump Import Order of Operations
                          616055
                          Anantha,

                          Thanks. I saw his reply and I corrected the roles and user but I get other errors:

                          Processing object type SCHEMA_EXPORT/TABLE/TABLE
                          ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE:"JAVA"."AQ$_X12_837_QUEUE_TABLE_T"]
                          ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
                          ORA-00955: name is already used by an existing object


                          I find it interesting that my parms say to replace the object if exists. I also find it interesting that datapump refuses to compile QUEUE_TABLE's that have names longer than 24 characters. After all, 30 characters is the oracle limit on object name and obviously we created this que table fine on prod since...

                          Any ideas on that?

                          I also read note 336012.1 and I'm doing option 1 exactly.