I've recently received the dump file of the soon to be migrated Oracle Database. I've installed Oracle 10g and now I need to restore the original database.
Can anyone give me a step by step procedure for how to restore it from the *.dmp file ?
Add a comment |
4 Answers
It depends what you are trying to do but one short answer is:
imp system/password@sid file=(filename) log=imp.log fromuser=(existing user) touser=(new user) The last 3 here: log, fromuser, touser are optional. You might give some more details about what you are doing if that doesn't help...
- So basicly this should import all the tables / views / triggers / procedures in the default tablespace and in the default "System" schema ?Paul– Paul2009-06-19 19:17:18 +00:00Commented Jun 19, 2009 at 19:17
- the short anser is yes but it really depends on what was exported and how.Ciaran De Buitlear– Ciaran De Buitlear2009-06-19 19:33:45 +00:00Commented Jun 19, 2009 at 19:33
It depends on the type of export (dump) that was done from the original database. Oracle does four types of exports (called modes):
- Full
- User
- Table(s)
- Tablespace(s)
Assuming that a Full export was done, you'd do something like:
SET ORACLE_SID=dbsid imp system/manager FULL=y FILE=database.dmp LOG=import.log STATISTICS=recalculate Having said that, you should read through the Oracle documentation on imp.
Keep in mind a .dmp file could mean it was exported using exp or expdp. Run imp help=y or impdp help=y if you run into any issues.
I usually only restore the schemas that I want:
imp userid=SYSTEM/ART@cscdap1 fromuser=MDSBI touser=MDSBI grants=y indexes=y commit=y ignore=y buffer=10240000 file=MDSBI.dmp log=imp_MDSBI.log imp userid=SYSTEM/ART@cscdap1 fromuser=MDS_GRS touser=MDS_GRS grants=y indexes=y commit=y ignore=y buffer=10240000 file=MDS_GRS.dmp log=imp_MDS_GRS.log imp userid=SYSTEM/ART@cscdap1 fromuser=ODM touser=ODM grants=y indexes=y commit=y ignore=y buffer=10240000 file=ODM.dmp log=imp_ODM.log imp userid=SYSTEM/ART@cscdap1 fromuser=ODIWORK touser=ODIWORK grants=y indexes=y commit=y ignore=y buffer=10240000 file=ODIWORK.dmp log=imp_ODIWORK.log imp userid=SYSTEM/ART@cscdap1 fromuser=ODIMASTER touser=ODIMASTER grants=y indexes=y commit=y ignore=y buffer=10240000 file=ODIMASTER.dmp log=imp_ODIMASTER.log imp userid=SYSTEM/ART@cscdap1 fromuser=EPADMRT touser=EPADMRT grants=y indexes=y commit=y ignore=y buffer=10240000 file=EPADMRT.dmp log=imp_EPADMRT.log imp userid=SYSTEM/ART@cscdap1 fromuser=OEMUSER touser=OEMUSER grants=y indexes=y commit=y ignore=y buffer=10240000 file=OEMUSER.dmp log=imp_OEMUSER.log imp userid=SYSTEM/ART@cscdap1 fromuser=HERNA236 touser=HERNA236 grants=y indexes=y commit=y ignore=y buffer=10240000 file=HERNA236.dmp log=imp_HERNA236.log imp userid=SYSTEM/ART@cscdap1 fromuser=MAURYAKO touser=MAURYAKO grants=y indexes=y commit=y ignore=y buffer=10240000 file=MAURYAKO.dmp log=imp_MAURYAKO.log imp userid=SYSTEM/ART@cscdap1 fromuser=SAWANTRO touser=SAWANTRO grants=y indexes=y commit=y ignore=y buffer=10240000 file=SAWANTRO.dmp log=imp_SAWANTRO.log imp userid=SYSTEM/ART@cscdap1 fromuser=ABDERHOB touser=ABDERHOB grants=y indexes=y commit=y ignore=y buffer=10240000 file=ABDERHOB.dmp log=imp_ABDERHOB.log imp userid=SYSTEM/ART@cscdap1 fromuser=CHECK_DB touser=CHECK_DB grants=y indexes=y commit=y ignore=y buffer=10240000 file=CHECK_DB.dmp log=imp_CHECK_DB.log imp userid=SYSTEM/ART@cscdap1 fromuser=SABATAJA touser=SABATAJA grants=y indexes=y commit=y ignore=y buffer=10240000 file=SABATAJA.dmp log=imp_SABATAJA.log 