George Mauer is on the Net

Backing up and Restoring Databases in Oracle

This article is specifically about how to use oracle’s exp and imp utilities to create a dump file of all objects contained within an Oracle User(s) including the schema, procedures, package, constraints, sequences, jobs, and data; pretty much everything except DB Links. When you are done, you should be able to back up a database and restore it at a future date.

What you will need for this:

  • Oracle installed on your machine (You can download Oracle XE for free here)
  • A Database to back up (Sample schemas here, but then again you probably have one if you’re reading this)
  • Windows (maybe?) – I have no clue how this process works on a Linux or Mac system, though I imagine it’s exactly the same.

Creating a Backup

An Oracle install typically comes with the excellent exp command line utility (also there’s similar the exp data pump) to see if it’s installed and its manual type at the command prompt:

C:\Temp>exp -?

There are many articles online explaining this utility. The important parameters here for a simple, straightforward backup of a user are OWNER and FILE. Since the command line utility is not within oracle you don’t have to mess around with oracle directories or permissions or anything like that. To backup a database type:

C:\Temp>exp scott/tiger@XE owner=(scott) file=scott.dmp

Break it down:

  • exp – start our utility, don’t be silly
  • scott/tiger – the username/password of the account we will be using to pull out the information. It can be any account with the ability to access the user schema you’re trying to save, I usually just use the same schema
  • @XE – An ‘@’ symbol followed by the TNS name for the oracle instance the database you’re backing up is in, look in your tnsnames.ora for installed names. Do not omit this, as the utility might have a default and you might end up backing up the wrong database.
  • owner=(scott) – a comma separated list of users you want to back up. We could have used owner=(user1,user2) or – when there is only one user – owner=user1.
  • file=scott.dmp – the file that the backup will be stored in. The above set-up will create c:\Temp\scott.dmp with the contents of everything that is owned by the scott User in the XE instance of Oracle.

Restoring the Backup

This part is (slightly) more difficult. The command line utility to use is called imp

C:\Temp>imp -?

The important parameters are FILE, TOUSER, FROMUSER

Because you exported everything owned by a user, you must first ensure the user exists in the garget database. You can do a CREATE USER SQL statement or, if using the Toad for Oracle client you can use the Create User pop-up.

If the user already exists however and you want to restore them to the saved state you must first remove all of the user’s objects. The easiest way to do this is to drop and recreate your user. In Toad you can select the user from the Users view and click on the Script tab to see a script for dropping and restoring the user. In the case of the Scott Schema you would see








— 2 Roles for SCOTT




— 1 System Privilege for SCOTT


Notice that there are actually six SQL statements here (count the semi-colons), you must run them all in order. (First drop the user, then create the user, then grant roles, etc.). Also notice that the password is stored as an encrypted hash value; ‘F894844C34402B67’ is the encoding of the ‘tiger’ password. If you would like to change the password just take out the values keyword: IDENTIFIED BY ‘MyNewPassword’.  Otherwise, keep the line as is.  Finally, since you are dropping and recreating the user notice that you must be logged in from a different user account – one with the permissions to do so. If you have access to the System user it will do nicely, this is the only time that you need to be logged in as a different user, make sure you get in and get out as quickly as possible to minimize potential damage.

Now with the Scott user created and cleared of any objects that might conflict with the ones we are trying to restore, we are ready to run the import.

C:\Temp>imp scott/release1@XE file=scott.dmp fromuser=(scott) touser=(scott)

Break Down:

  • imp scott/release1@XE – just like explained above, what user should the utility log in as when doing the import and at what Oracle instance. The user should have permissions to create tables, packages, etc. and to insert data.
  • file=scott.dmp – a file created by the exp command described above
  • fromuser=(scott) – since the .dmp file can create objects for multiple users, will search the file for all objects owned by this user(s) and import those. When there is only one user we can also omit the paretheses.
  • touser=(scott) – the objects we’re importing will be created need to be owned by a user already in the database, this is the user they will be created under. If our target database had two empty users scott and allan, and we did something like imp scott/release1@XE file=scott.dmp fromuser=scott touser=allan then the end result is that scott will still be empty but all of the objects saved to the file from the scott user will now have been imported to the allan user.

And you’re done. Don’t forget to check for invalid objects if you are transferring data between two different oracle instances or users. Enjoy!


Errors That I’ve Commonly Run Into

· You are attempting to run imp and you get IMP-00015: following statement failed because the object already exists scrolled to the screen. Generally this means that you forgot to empty the target user. Drop and recreate them or manually drop all the objects the import is complaining about.

· You are attempting to run imp and you get IMP-00010: not a valid export file, header failed verification. This in my experience is caused not so much by a mismatch in the versions of Oracle as Google would suggest but by a mismatch in the versions of the exp and imp utilities. For example if I go to my Oracle Server I can check the version of the export utility

C:\Documents and Settings\Administrator.WESTWAY-AD1>exp

Export: Release – Production on Wed Jun 25 16:50:19 2008


And if I try the same thing on my Desktop I get:


Export: Release – Production on Wed Jun 25 16:49:27 2008

You will have to find and download the same version of each. Sorry about that.


June 25, 2008 Posted by | Album | Leave a comment