In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc.

We are using expdp/impdp to perform our backup/restores.

For back-ups we run...

expdp 'sys/password@orcl as sysdba' schemas=CurrentUser directory=DirLabel Dumpfile=MyDatabase.dmp logfile=MyDatabase.log

For restores we have been dropping the user (database) manually, and then running

impdp 'sys/password@orcl as sysdba' directory=DirLabel Dumpfile=MyOldDatabase.dmp logfile=MyRestore.log remap_schema=OLDUser:CurrentUser

We are now trying to automate this process and have a question.

Do we have to drop the current user (database) prior to the restore or is there some option/flag on impdb that can be used to force a complete restore? I looked at TABLE_EXISTS_ACTION=REPLACE, but I noticed I still get the same "errors" during restore if I leave the option off the command-line.


migrated from stackoverflow.com Apr 11 '13 at 20:26

This question came from our site for professional and enthusiast programmers.

  • A user and a database are different entities in Oracle. – David Aldridge Apr 11 '13 at 17:57
  • Just to clarify what David said, I guess that you mean you were dropping the schema before importing it again. One instance can only ever host one database, but that may (will) contain many schema. – Phil Sumner Jun 26 '14 at 15:26

I haven't found any impdp commands that would allow you to drop existing schema prior to importing data. But you should know that Data Pump command-line utilities are based around PL/SQL packages DBMS_DATAPUMP and DBMS_METADATA. Thus you may well write your own PL/SQL scripts to automate your data move calling programs in DBMS_DATAPUMP directly. And since this is PL/SQL, you may just invoke DROP USER statement in your scripts prior to calls to programs in DBMS_DATAPUMP and DBMS_METADATA.

I think this approach is more flexible, and to further automate your tasks you can employ Oracle Scheduler.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy