• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

Oracle data pump import to import full database

I have an Oracle data pump full database dump and I want to import in a blank database. Please help.
  • 4
  • 4
  • 2
1 Solution
- step1: register a database directory. if you already have a directory you can straight away go to step 2:
CREATE DIRECTORY dmpdir AS 'c:\folderlocation\';

- step2: place the dump file in that folder location you have just registered

- step3:
IMPDP id/password DIRECTORY=dmpdir DUMPFILE=dumpfilename.dmp

. and you should also refer to the docs for other import options:
baberaminAuthor Commented:
what about tablespaces, users. Can data pump import can create by themselves?
- you need to create the tablespace first, no for users.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Geert GOracle dbaCommented:
it depends on the user doing the import
>> for a full database import use system account
baberaminAuthor Commented:
yes system account can be used but is it only the tablespaces we need to create first? and nothing else.
What if we dont know the tablespace names? or number of tablespaces is high? so how to tackle that?
Anything to see what tablespaces were in that dump file?
Geert GOracle dbaCommented:
use sqlfile parameter to see what the import will do
baberaminAuthor Commented:
i checked that and its even having "CREATE TABLESPACE " command.
Does that mean it will create any tablespace or anything by itself?
Geert GOracle dbaCommented:
yes, that's the idea

analyzing the import sql is about 80% of the work
if you don't want it to create some tablespaces, use the exclude feature:

or the transform if you want to change things:
baberaminAuthor Commented:
see its very difficult this way to recover from dump file.

Any thing which can make life easy?
Geert GOracle dbaCommented:
recovery is always difficult
what you actually need to have as experience:

being successful for recovery requires proficiency in
> creating sufficient backups before a go-live
  >> this requires a rather indept knowledge of the upcoming changes
> being able to restore from those backups

possible backup techniques: (not all listed)
1: copy cold database
2: copy hot database in backup mode
3: create full rman backup
4: create incremental level 0 and level 1 backup of database, config, redologs, etc
5: save one of above to tape (have someone store tape off-site)
5: maintain a standby database (or more than 1)
6: setup flashback (query or database)
7: export data before it is changed
8: etc, etc

for all scenario's you need to be able to do a restore/import or whatever on a test database (and be able to setup a new test database)

now for an answer to your question:
"anything which can make your life easier ?"
knowledge and experience
>> practice, practice and ... more practice
>> call an external resource (oracle dba) for help or advice
>> this site can help a little (we can't *see* the problems ... you are the*eyes*)
>> the paycheck at the end of the month soothes the headache ... a little
>> tutorials, courses, etc

did someone say "oracle dba" was easy ??? >>> they must have been joking ! :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now