This discussion is archived
9 Replies Latest reply: Aug 13, 2008 2:16 PM by 616055 RSS

Data Pump Import Order of Operations

616055 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    ?It runs but with errors

    what are the errors?
    What is the impdp parameters used?
  • 2. Re: Data Pump Import Order of Operations
    616055 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for the info Anantha, I will give that a try and post back.
  • 6. Re: Data Pump Import Order of Operations
    247514 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.