Creating a dump and restoring an Oracle database

These days I am involved in testing a migration tool which demands in testing the application's migration against several databases. In the process it is very useful if a loaded database can be backed-up to restore later.

This is how you create a database dump of an oracle database and restore it.

1. First you need to connect to oracle as sysdba, create a user and grant him required privileges. Command line steps for creating a user and granting privileges are as below;

C:\app\Administrator\OraHome_1\BIN>sqlplus sys as sysdba;

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jan 10 17:28:47 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user yumani cascade;

User dropped.

SQL> Create user yumani identified by yumani account unlock;

User created.

SQL> grant create session, dba to yumani;

Grant succeeded.

SQL> grant connect to yumani;

Grant succeeded.

SQL> exit

2.  Fill in your database with data.

3. Next, lets create a database dump.

$ expdp yumani/yumani schemas=yumani dumpfile=yumani100112_1657.dmp

An oracle dump with the name yumani100112_1657.dmp will be created in the location that you ran the above command.

4. After backing-up if you need to restore the dump, do as below.

$ impdp yumani/yumani schemas=yumani dumpfile=YUMANI100112_1640.DMP