Thanks for the question, Michael.
Asked: January 21, 2019 - 4:59 pm UTC
Last updated: January 22, 2019 - 12:33 am UTC
Version: 12c
Viewed 10K+ times! This question is
You Asked
Hi,
We have an existing 11g instance on a lab/dev server.
We have build a new server with 12c and exported and imported the data using data pump.
That was all done by an external DBA, however, he's currently unavailable and I would like to have a go myself.
We need to move to the new lab server after doing some testing with it.
So to refresh/replace the data on the new instance what would I have to do?
Can i do a imp system/manager@abc FULL=Y FILE=FULL.DMP and this will replace all of the data?
Or do I need to drop everything and then impdp? If so what's the best way to do this?
Thanks for the help.
and Connor said...
If you have just a few schemas (accounts), and they don't have a lot of cross-schema dependencies I'd probably do it at schema level, eg
Source:
expdp myuser/mypass schemas=MYUSER directory=DUMPDIR dumpfile=MYUSER.dmp logfile=export.log
Target:
1) drop all objects owned by MYUSER
2) impdp myuser/mypass schemas=MYUSER directory=DUMPDIR dumpfile=MYUSER.dmp logfile=import.log
and repeat for each schema of interest.
If you have a lot of schemas with a lot of interdependencies, then you can opt for database level copy
Source:
expdp dbauser/dbapass full=Y directory=DUMPDIR dumpfile=full.dmp logfile=export.log
Target:
1) drop all non-oracle schemas
2) impdp dbauser/dbapass full=Y directory=DUMPDIR dumpfile=full.dmp logfile=import.log
Note that for full imports, you'll typically see warnings because we'll try to import the existing *oracle* schemas as well, but these warnings can be reviewed and dismissed.
And as always, if you're doing for the first time
a) do it on a testing/throwaway system first
b) take a backup of your target environment before blowing it away
Is this answer out of date? If it is, please let us know via a Comment