JDN
asked on
The best way to transfer a database?
Hi Oracle Experts,
I like to know if there's an easy way to transfer an Oracle database from our server to the Personal Oracle on my notebook. I normally make an export on the server using then EXP.EXE tool, and import the database dump in my notebook using the IMP.EXE tool. This export/import works, but I always have to delete all tables in my Personal Oracle database (using the Orcale DBA Studio) before I use the import tool. If I don't delete all tables, the import tool is displaying a lot of scrolling text while importing data. Due to this scrolling text, the import takes a very long time. When I delete all tables in the destination table first, the import is a lot faster.
In the DBA Studio on my notebook there is a menu option Export, Import, Backup and Recovery, but all these menu's generate an error message "This wizard can only be launched when the application is connected to the Oracle Management Server"....?
So, my question is: is there a way to import a database dump without deleting all tables in the destination database first, and without waiting a long time for the many text lines scrolling over the screen?
Thanks for any help!
I like to know if there's an easy way to transfer an Oracle database from our server to the Personal Oracle on my notebook. I normally make an export on the server using then EXP.EXE tool, and import the database dump in my notebook using the IMP.EXE tool. This export/import works, but I always have to delete all tables in my Personal Oracle database (using the Orcale DBA Studio) before I use the import tool. If I don't delete all tables, the import tool is displaying a lot of scrolling text while importing data. Due to this scrolling text, the import takes a very long time. When I delete all tables in the destination table first, the import is a lot faster.
In the DBA Studio on my notebook there is a menu option Export, Import, Backup and Recovery, but all these menu's generate an error message "This wizard can only be launched when the application is connected to the Oracle Management Server"....?
So, my question is: is there a way to import a database dump without deleting all tables in the destination database first, and without waiting a long time for the many text lines scrolling over the screen?
Thanks for any help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One option is to truncate all tables and run import with "ignore=Y" so it doesn't complain about the tables and indexes already existing. I don't know if either of those options are easy (or even possible) through Oracle's GUI tools, they certainly are available through SQL*Plus or Server Manager (for the truncate) and from a DOS prompt (to run import with a parameter file).
If you have any referential integrity constraints (foreign keys) these will also have to be disabled before doing the truncates. Also, any table triggers will need to be disabled. You can do all of these with script files that are not that difficult to write or with PL\SQL and the "execute immediate" command.
If you have any referential integrity constraints (foreign keys) these will also have to be disabled before doing the truncates. Also, any table triggers will need to be disabled. You can do all of these with script files that are not that difficult to write or with PL\SQL and the "execute immediate" command.
ASKER
Can you give me an example of using a parmfile with IMP? I am using IMP interactive, and answered the "Ignore..." question with "Yes". But then there's also a lot of scrolling text, which slows down the import.
Dbrower, I tried to use Backup/Restore from the DBA Studio, but as I said: there's always an error message "This wizard can only be launched..." (see my topic).
Thanks,
JDN
Dbrower, I tried to use Backup/Restore from the DBA Studio, but as I said: there's always an error message "This wizard can only be launched..." (see my topic).
Thanks,
JDN
imp parfile=<parfile_name> will do it.
Check out the ORACLE SERVER UTILITIES book. This book has a description of all parms and their meaning. Hhere is an example:
imp parfile=my_parms
my_parms
userid=username/password
file=/dump_file_name
log=/import.log
COSTRAINTS=N
DESTROY=N
ignore=y
indexes=n
commit=y
full=y
recordlength=10240000
feedback=10000
imp parfile=my_parms
my_parms
userid=username/password
file=/dump_file_name
log=/import.log
COSTRAINTS=N
DESTROY=N
ignore=y
indexes=n
commit=y
full=y
recordlength=10240000
feedback=10000
ASKER
Dbrower,
I used your parfile, but there's still a lot of text scrolling in the DOS box. I see text lines like:
Column 12:
Column 13:
Column 14:
etc
It takes a very long time to import the dump file this way. Is this really the only way to do the job with Oracle. I want to transfer the database daily to my notebook.
I know in the Enterprise Manager of MS SQL Server, there's a simple way to export and import a database dump. The only thing you see moving is a progress bar. Is'nt there in Oracle a similar way, perhaps using a third party tool?
JDN
I used your parfile, but there's still a lot of text scrolling in the DOS box. I see text lines like:
Column 12:
Column 13:
Column 14:
etc
It takes a very long time to import the dump file this way. Is this really the only way to do the job with Oracle. I want to transfer the database daily to my notebook.
I know in the Enterprise Manager of MS SQL Server, there's a simple way to export and import a database dump. The only thing you see moving is a progress bar. Is'nt there in Oracle a similar way, perhaps using a third party tool?
JDN
"imp parfile=<para_file_name>" will do it.
The other way (faster) to do it is to use SQL*Loader instead of export and import. Export the tables you require in a fixed format ASCII file. Truncate them in the target database. Then, use SQL*Loader Direct approach (this bypasses inserts, etc) to load the data and then use the index script to re-index later. This will be the fastest way to do a daily job.
Sorry, one of the (few?) advantages to MS SQL Server is the GUI tools it comes with. Oracle did not get to be popular because of its GUI tools - many of them are far from user-friendly or complete. You may find the command-line approach will work better with Oracle. Most experienced Oracle DBA's use command-line tools extensively, even for Oracle on Windows.
Yes, the command to run import from a DOS prompt is as dpd0809 indicated, but you still need to do the work of truncating the tables, disabling constraints and triggers (and another task I just thought of: dropping sequences) *BEFORE* running import. Otherwise you will still have all of those error messages to deal with when import runs.
Yes, the command to run import from a DOS prompt is as dpd0809 indicated, but you still need to do the work of truncating the tables, disabling constraints and triggers (and another task I just thought of: dropping sequences) *BEFORE* running import. Otherwise you will still have all of those error messages to deal with when import runs.
If you need this data every day, you may also consider writing a script to:
1) Truncate all the tables.
2) Disable all constraints.
3) Drop all indexes.
4) Use SQL*Net (parallely) to populate all the data.
5) Recreate indexes with UNRECOVERABLE option.
6) Enable the constraints.
This may be faster if you have a fast network, and not in Archivedlog mode (you should not be on your laptop).
1) Truncate all the tables.
2) Disable all constraints.
3) Drop all indexes.
4) Use SQL*Net (parallely) to populate all the data.
5) Recreate indexes with UNRECOVERABLE option.
6) Enable the constraints.
This may be faster if you have a fast network, and not in Archivedlog mode (you should not be on your laptop).
ASKER
I'am very familiar with SQL Server, Markgeer, Oracle is new for me. That's why I asked for help.
I understand from all your reactions that the Oracle command line tools are the only way to do the job.
Anyway, I will continue to import my database this way. Dpd0809 was the first with a complete answer, so I'll give him the points...
Thanks all.
I understand from all your reactions that the Oracle command line tools are the only way to do the job.
Anyway, I will continue to import my database this way. Dpd0809 was the first with a complete answer, so I'll give him the points...
Thanks all.
you can copy your database to your notebook without exp/imp.
on your Production database run this script:
alter database bakup controlfile to trace;
this will create a controlfile script.
on your notebook:
restore the database to your notebook
change the controlfile script to meet your configuration on your notebook
after that run the script
befoor you open your database with
alter database open resetlogs;
recover your database if it is in archivemode using archives.
ADAM
on your Production database run this script:
alter database bakup controlfile to trace;
this will create a controlfile script.
on your notebook:
restore the database to your notebook
change the controlfile script to meet your configuration on your notebook
after that run the script
befoor you open your database with
alter database open resetlogs;
recover your database if it is in archivemode using archives.
ADAM
Have you tried to restore the database to your notebook? I have moved many databases from one server to another by restoring the database. You may find this quicker yet.
good luck