How to backup and restore whole(complete) database using Import and Export Utilities using Oracle 9i

Posted on 2006-07-23
Last Modified: 2008-02-01
How would I export the FULL database using Oracle 9i Export utilities to a dump file and restore the FULL database again using Import utility into another DB in a different machine.  Is there any way that I can restore the same/complete database state and I do not have to create the users one by one?  

Question by:angelsky
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Assisted Solution

rbrooker earned 25 total points
ID: 17165241

sounds to me like you want to do a backup restore.  much easier than doing a complete export / import and much much more reliable.  this also has the advantage of taking everything, including users ( plus their passwords are maintained ).

there are plenty of examples of how to do this throughout experts exchange...

good luck.


Accepted Solution

hardikbeit earned 50 total points
ID: 17165347

Hi angelsky,

You can do full export with EXPORT utility of Oracle and restore with IMPORT utility.

Some export/import command for your reference:

With use of EXP command you can backup
-      Entire database
-      User wise database
EXP filename.dmp it creates dump file
Examples: (For entire backup use 'sys' userid with sysdba rights (sys/pass@string as sysdba)
Exp user/pass file = a.dmp rows= yes indexes = no;
Exp user/pass file = a.dmp full=yes
Exp user/pass file = a.dmp tables = working_lines query =\’where phone_no = 272721\’;

IMP command is used to import database from dump file.
      With use of IMP command you can restore
-      Entire database
-      Particular user
-      Particular table
IMP user/pass@domain filename
IMP user/pass file = a.dmp fromuser = fromusername touser= tousername tables = (working_lines, closed_lines);

Let me know in case of any query.

With regards,
LVL 16

Expert Comment

ID: 17165473
A full database export and import can be a good way to replicate or clean up a database. A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before the full import. A full import creates any undefined tablespaces using the same datafile names as the exported database.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Assisted Solution

sathya_s earned 50 total points
ID: 17165504
For Exporting a full database:
exp <username1>/<password1> full=y file=<filename> statistics=NONE consistent=Y log=<log_filename>

Username1/password1 :  Username  & Password of the user who is going to do the export

Filename : Specifies the name of the export file.

Statistics : Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE.

In some cases, Export will place the calculated statistics in the export file as well as the ANALYZE statements to regenerate the statistics.
The calculated optimizer statistics are flagged as questionable at export time if:
•      There are row errors while exporting
•      The client character set or NCHAR character set does not match the server character set or NCHAR character set
•      A QUERY clause is specified
•      Only certain partitions or subpartitions are exported
It is always better to specify statistics = NONE, which will avoid the warnings generated.

Consistent : Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.

Log: Error log

For Importing a full database:

imp <username1>/<password1> full=y file=<filename> log=<log_filename>

Username1/password1 :  Username  & Password of the user who is going to do the import.

Filename : Specifies the name of the import file

Log: Error log

Create a new database before importing.


Author Comment

ID: 17166422
Hi Guys,
I tried the export but it truncated my user names and schemas to only 30 characters, is there a way to configure it not to truncate the data but restore everything with its original length?


Expert Comment

ID: 17166510

Hi angelsky,

This is due to Oracle naming restriction, max length is 30 characters. I am thinking that How can you create your usernames with more than 30 characters. As per my point of view it's not possible to exceed 30 characters.

With regards, Hardik

Author Comment

ID: 17172733
Hi Hardik,
The database I am trying to backup is connected to our test management tool, TestDirector and it is the one that creates the users and schemas automatically when we use it.

Is there any way we can lengthen the names :-)

Many thanks to all of you!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question