Q
Manage Learn to apply best practices and optimize your operations.

How to restore a dump file with the Oracle SHOW=Y option

Oracle expert Brian Peasland explains how to restore a dump file with the Oracle SHOW=Y option before precreating tablespaces.

We often receive dump files from clients to develop applications and provide support for those applications, and often we have problems restoring those dump files. If we know a user where we can restore the database using "from user, to user" switch then the process becomes easier after several attempts while identifying the user or roles missing.

The process seems to become impossible when the customer does not know a user to use for a restore and we try to...

do a full restore. One issue I run into is that there is a different file structure at which the import fails. Another problem is not knowing objects that need to be precreated, and also, the full restore seems to overwrite the password of default DBA accounts, making the database inaccessible.

There must be a better way. What do you recommend? Must we require specific information or can you give us a list of specific steps and commands that will ensure a good restore?

If I do not know the contents of the dump file, I use the SHOW=Y option with the LOG parameter to capture the large output. The SHOW option will "show" you the contents of the dump file without actually performing the import. So try something like the following:

 

 imp file=my_dump.dmp show=y log=import_show.txt full=y

Make sure you do the entire dump file (FULL=Y). The output will scroll by really fast. After it is done running, open the text file in any text editor. You will be able to see all CREATE commands, etc. This way, you can see what the file structure is and precreate the necessary tablespaces. You will be able to precreate any needed users and roles so that your real import will not fail.

This was last published in November 2009

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close