This discussion is archived
4 Replies Latest reply: Jul 31, 2012 5:49 AM by 716192 RSS

expdp / impdp - Private synonyms using DB link not imported

716192 Explorer
Currently Being Moderated
Hi

I'd appreciate any help with this.

We are taking an export (expdp) of a database on Oracle EE 10.2.0.3 in Prod and then importing it into an identical DB on 10.2.0.5 in Dev.

I doubt the minor version difference is an issues, but I mention it for completeness.

Our expdp in prod used to look like this:
expdp agdba/x directory=DATA_PUMP_DIR dumpfile=X.DMP logfile=X.LOG schemas=A,B,C,D

We have changed it to:
expdp agdba/x directory=DATA_PUMP_DIR dumpfile=X.DMP logfile=X.LOG full=y EXCLUDE=SCHEMA:"IN ('E', 'F')"

( so basically do a full export, but exclude schemas E & F )

The impdp in dev has not changed (we import one schema at a time):

impdp agdba/x DUMPFILE=X.DMP LOGFILE=imp_X.LOG SCHEMAS=A DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=SKIP

DB user AGDBA has been granted the DBA role...


The Issue:_

Private synonyms that use a DB link are NOT imported, but private synonyms that do NOT use a DB link ARE imported.

I can fix the issue by simply recreating the private synonyms using the DB link - e.g.:

CREATE SYNONYM EVENTLOGTBL FOR EVENTLOGTBL@FCISTOSMS;

Things were working fine until we change the expdp to use FULL. I see a few posts about synonyms and FULL=Y issues, but nothing quite like our problem.

Any ideas?

Thanks,
Andreas
  • 1. Re: expdp / impdp - Private synonyms using DB link not imported
    User286067 Journeyer
    Currently Being Moderated
    Andreas Hess wrote:
    We have changed it to:
    expdp agdba/x directory=DATA_PUMP_DIR dumpfile=X.DMP logfile=X.LOG full=y EXCLUDE=SCHEMA:"IN ('E', 'F')"
    ( so basically do a full export, but exclude schemas E & F )
    If I may, do not exclude if you want full backup unless you are constrained by space. Keep all, and then while impdp, exclude those.
    The impdp in Dev has not changed (we import one schema at a time):
    impdp agdba/x DUMPFILE=X.DMP LOGFILE=imp_X.LOG SCHEMAS=A DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=SKIP
    May I ask why you impdp one schema at a time? What is the purpose? What specific gain/advantage you get? Do you import only one schema? or you import all, but one-at-a time? My recommendation is to import all schema you need in one command. That way impdp can make intelligent decisions and creates objects in a proper order, so as to minimize errors related to cross-schema dependencies.
    The Issue:_
    Private synonyms that use a DB link are NOT imported, but private synonyms that do NOT use a DB link ARE imported.
    1. Who (which schema) owns the db link?
    2. Which schema owns the synonym using the db link?
    3. At what sequence do you import this schema that owns dblink? Before or after the schema that needs synonym?
    4. Do you drop existing schema before importing?

    If you impdp all necessary schema in one go, you may be able to eliminate this error, but until you attempt that, it remains uncertain.
  • 2. Re: expdp / impdp - Private synonyms using DB link not imported
    716192 Explorer
    Currently Being Moderated
    Hi

    Thanks for your reply.

    - The schemas we want to exclude are about 200GB and are exported separately once a month - so we do want to exclude them in the daily exports
    - As for one schema at a time, this is to allow the script to import one or more schemas as not all are required every time we refresh Dev

    I ran another test where I created just the SQLFILE (textfile only) using impdp to determine whether it is the expdp that does not export the synonyms or the impdp that just does not import them:

    impdp agdba/x sqlfile=dumpfile_contents.sql dumpfile=X.DMP directory=data_pump_dir

    The result was that expdp does NOT export any private synonyms for the schema (call it 'A') if those synonyms refre to table via a DB link.

    So it's the change to expdp FULL=Y that is causing the issue.

    Some answers to your other questions:

    1. Let's say schema 'A' (non DBA schema, but has the permissions to create synonyms)
    2. Schema 'A' owns the synonym
    3. The DB link is never dropped, but most other objects (including all private synonyms) are dropped
    4. Schemas do not get dropped, but most object they own are

    The problem isn't the sequence as you can create a synonym irrespective of whether an object it refers to exists or not...

    So the problem is expdp FULL=Y for some reason does not export synonyms that refer to objects via DB links. Busy reading up on DATABASE_EXPORT_OBJECTS view...

    Cheers,
    Andreas
  • 3. Re: expdp / impdp - Private synonyms using DB link not imported
    User286067 Journeyer
    Currently Being Moderated
    Andreas Hess wrote:
    So the problem is expdp FULL=Y for some reason does not export synonyms that refer to objects via DB links.
    I doubt that ... More than few times a week I refresh non-prod databases from a prod db, (it is schema level export not FULL=Y), and two of the schema own db links, and others have synonyms pointing to them. I have never encountered synonym problem (except that if db_links are invalid then impdp takes extremely long time to timeout while compiling pl/sql code.). We normally change db_links after the impdp is done, or (while it is importing table via another session).

    I just re-ran sqlfile option with include=db_link,synonym option and i can see statements that create synonyms and create db links.

    However i think the order in which impdp runs might be the source of your problem ... see this
    Starting "ME"."SYS_SQL_FILE_SCHEMA_01":  ME/******** dumpfile=xxxx.%u.dmp directory=xxxx_exp schemas=schema1,schema2,schema3 sqlfile=schema.sql include=db_link,synonym
    Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM       <<<< Synonyms are created first
    Processing object type SCHEMA_EXPORT/DB_LINK               <<<< db_links come later
    Job "ME"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 08:32:02
    So, it is conceivable that if you drop synonyms/db_links from your schemas before impdp, some synonyms will fail to create properly. This could be a version specific issue, mine is 11.2 and I don't see failures for synonym creation, i.e. no error messages while impdp. Next time you could try to drop objects except db_links and see if you still have the same issue.

    Raj
  • 4. Re: expdp / impdp - Private synonyms using DB link not imported
    716192 Explorer
    Currently Being Moderated
    Hi

    Thanks for your feedback.

    But the problem is definitely the expdp, not the impdp.

    As I mentioned, when I generated a SQLFILE with impdp, I could see that the export file contains the commands to create DB links as well as private synonyms, but it does NOT create private synonyms if these refer to a table via a DB link.

    I tested this against one of our older export files which were not taken with FULL=Y. There I could very clearly see the commands that create synonyms that refer to tables via DB links...

    I have logged a call with Oracle, so let's see what they say...

    A.

Legend

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