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.
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.
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