[Last Call] Learn how to a build a cloud-first strategyRegister Now


Oracle data pump import to import full database

Posted on 2012-09-04
Medium Priority
Last Modified: 2012-09-20
I have an Oracle data pump full database dump and I want to import in a blank database. Please help.
Question by:baberamin
  • 4
  • 4
  • 2
LVL 23

Expert Comment

ID: 38366709
- 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:

Author Comment

ID: 38366820
what about tablespaces, users. Can data pump import can create by themselves?
LVL 23

Expert Comment

ID: 38366853
- you need to create the tablespace first, no for users.
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

LVL 38

Expert Comment

by:Geert Gruwez
ID: 38366868
it depends on the user doing the import
>> for a full database import use system account

Author Comment

ID: 38366882
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?
LVL 38

Accepted Solution

Geert Gruwez earned 2000 total points
ID: 38366914
use sqlfile parameter to see what the import will do

Author Comment

ID: 38366955
i checked that and its even having "CREATE TABLESPACE " command.
Does that mean it will create any tablespace or anything by itself?
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38366972
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:

Author Comment

ID: 38367450
see its very difficult this way to recover from dump file.

Any thing which can make life easy?
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38367508
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 ! :)

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question