• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1044
  • Last Modified:

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

Hi!
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?  

Thanks.
0
angelsky
Asked:
angelsky
3 Solutions
 
rbrookerCommented:
Hi,

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.

:)
0
 
hardikbeitCommented:

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
Examples:
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,
Hardik
0
 
MohanKNairCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sathya_sCommented:
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.

Regards,
Sathya.S
0
 
angelskyAuthor Commented:
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?

Thanks,
0
 
hardikbeitCommented:

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
0
 
angelskyAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now