Forum Stats

  • 3,854,219 Users
  • 2,264,340 Discussions
  • 7,905,613 Comments

Discussions

Export dump & import warnings

rthampi
rthampi Member Posts: 291 Bronze Badge

Hi guys

We've a legacy database 10g Release 1 & the vendor has configured export dump as the sole backup that happens everyday midnight. As the hardware started giving some issues those cannot be fixed, we are planning to move the database to new hardware (Windows environment)

We were able to successfully import the objects using import, however noticed that many warnings about non-existing objects (tables especially on which currently imported schema having read/write accesses belonging to other schemas). Subsequently all the schemas are imported & my question is, should we exclusively re-assign the rights on the objects those were not existing while a particular schema was imported or as all the related schemas were imported successfully, the rights will be implemented automatically?

Example scenario

We have five schemas fin, om, lc, payroll and fa (standing for finance, order management, letter of credit, fixed assets & payroll). The import parameter file was structured the same way I listed the objects & we start seeing warnings about non-existing tables those are referred by finance owned by other schemas.

Thanks in advance

rajesh

Best Answer

  • Mudasir Hakak
    Mudasir Hakak Member Posts: 135 Bronze Badge
    edited Apr 5, 2017 10:06AM Answer ✓
    Example scenarioWe have five schemas fin, om, lc, payroll and fa (standing for finance, order management, letter of credit, fixed assets & payroll). The import parameter file was structured the same way I listed the objects & we start seeing warnings about non-existing tables those are referred by finance owned by other schemas.

    Oracle will take care of the object privileges while importing provided and there is no need to explicitly grant the permissions again.

    Assumption-1: all the 5 mentioned schemas in the dumpfile had been exported completely and not only the specific tables/objects of those schemas.

    Assumption-2: grant doesn't fail to the object outside these 5 schemas[example grant to a user/object that doesn't exist in your target database] in that case you need to create the object post import and run failed grants.

    Hope this helps

    rthampi
«1

Answers

  • SUPRIYO DEY
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited Apr 5, 2017 6:28AM

    how did you import the objects?

    FULL or schema-wise?

  • Ever Santoro
    Ever Santoro Member Posts: 10
    edited Apr 5, 2017 7:10AM

    Backup and restore by rman, it will be best for you

  • rthampi
    rthampi Member Posts: 291 Bronze Badge
    edited Apr 5, 2017 7:12AM

    I am importing the schemas in a sequence, provided within an import parameter file.

  • SUPRIYO DEY
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited Apr 5, 2017 7:41AM

    try importing with a DBA USER and see what happens.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Apr 5, 2017 7:46AM

    Ill echo Ever Santoro - why not just backup and restore the database using RMAN - simple and straightforward.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Apr 5, 2017 7:52AM
    rthampi wrote:I am importing the schemas in a sequence, provided within an import parameter file.

    Does that mean you have 5 different export dumps? How does that work with regards to consistency? Does the vendor provide any instruction as to how to use these as backups? If not then they are not backups, they are false promises.

    RMAN makes backup and restore real simple, it should certainly be something you consider.

  • rthampi
    rthampi Member Posts: 291 Bronze Badge
    edited Apr 5, 2017 7:54AM

    @John/@Ever

    Well, My question was specific.

    "my question is, should we exclusively re-assign the rights on the objects those were not existing while a particular schema was imported or as all the related schemas were imported successfully, the rights will be implemented automatically?

    I'm dealing with a situation where the database is 10g R1 and all I am left with is a dump export file, which is intact and I can import the objects without any issues, other than the warning messages I noticed during the import activities.

    Please don't read me wrong, I am pretty experienced with RMAN backup/restore approaches (minimum from an Oracle developer's role)

    regards,

  • MsJ
    MsJ Member Posts: 936
    edited Apr 5, 2017 8:27AM

    A schema level export would not export roles.

    Pre-create it either by executing DDLs or using metadata import by metadata export from the source database.

    FULL=Y

    INCLUDE=ROLE:"IN (select distinct role from role_tab_privs where owner in ('fin',....' ')"

    CONTENT=METADATA_ONLY

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Apr 5, 2017 8:27AM
    Please don't read me wrong, I am pretty experienced with RMAN backup/restore approaches (minimum from an Oracle developer's role)regards,

    so what prevents you from using it for this migration?  Just because "the vendor" only configured exp/imp as their only backup, doesn't mean you can't use rman -- not only for this migration but for a real backup going forward.

  • rthampi
    rthampi Member Posts: 291 Bronze Badge
    edited Apr 5, 2017 8:35AM

    As I mentioned with the initial post, the old hardware is almost non-usable & all I have in my custody is a dump export file. Once I build the database server, RMAN will be configured.

This discussion has been closed.