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

Posted on 2006-07-23
Medium Priority
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 100 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 200 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.
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Assisted Solution

sathya_s earned 200 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

752 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