This content has been marked as final. Show 4 replies
Andreas Hess wrote: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.
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):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.
impdp agdba/x DUMPFILE=X.DMP LOGFILE=imp_X.LOG SCHEMAS=A DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=SKIP
The Issue:_1. Who (which schema) owns the db link?
Private synonyms that use a DB link are NOT imported, but private synonyms that do NOT use a DB link ARE imported.
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.
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...
Andreas Hess wrote: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).
So the problem is expdp FULL=Y for some reason does not export synonyms that refer to objects via DB links.
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
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.
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
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...