Oracle: Full database (user) restore requires drop

impdporacle

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?

Best Answer

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.

Related Question