Manage

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.

Brian Peasland

Published: 23 Nov 2009

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.

Dig Deeper on Oracle database backup and recovery

Choosing between Red Hat Enterprise Linux backup utilities

How to precreate Oracle table extents and define extent size

How to precreate tablespaces in Oracle before a new database import

Using exp and imp for Oracle Database when switching platforms

Related Q&A from Brian Peasland

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.