Solved

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

Posted on 2006-07-23
7
1,022 Views
Last Modified: 2008-02-01
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
Comment
Question by:angelsky
7 Comments
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 25 total points
ID: 17165241
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
 
LVL 3

Accepted Solution

by:
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
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
 
LVL 16

Expert Comment

by:MohanKNair
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Assisted Solution

by:sathya_s
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.

Regards,
Sathya.S
0
 

Author Comment

by:angelsky
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?

Thanks,
0
 
LVL 3

Expert Comment

by:hardikbeit
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
0
 

Author Comment

by:angelsky
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!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now