3

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.

Thoughts?

1 Answers1

3

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.

Yasir Arsanukayev
  • 3,155
  • 3
  • 23
  • 30