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?
-
A user and a database are different entities in Oracle.David Aldridge– David Aldridge2013年04月11日 17:57:06 +00:00Commented Apr 11, 2013 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– Phil Sumner2014年06月26日 15:26:56 +00:00Commented Jun 26, 2014 at 15:26
1 Answer 1
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.