Creating a dump file and restoring an Oracle database

Creating a dump file and restoring an Oracle database

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 sumit cascade;

User dropped.

SQL> Create user sumit identified by sumit account unlock;

User created.

SQL> grant create session, dba to sumit;

Grant succeeded.

SQL> grant connect to sumit;

Grant succeeded.

SQL> exit

2.  Fill in your database with data.

3. Next, lets create a database dump.

$ expdp sumit/sumit schemas=sumit dumpfile=sumit100112_1657.dmp

An oracle dump with the name sumit100112_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 sumit/sumit schemas=sumit dumpfile=SUMIT100112_1640.DMP