?
Solved

Need code to migrate data from one server to another

Posted on 2006-10-31
5
Medium Priority
?
589 Views
Last Modified: 2012-08-13
we are trying to migrate oracel database from one server to another.

1.First we need to do export/imp with no rows
2. Then disable constraints
3. then ex/import  with data
4. then enable with no validate

Need script for that, its urgent for data migration..

right now using this scripts and taking more time, need better way

 
sql> select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ' ; ' from user_constraints where constraint_type='R';
 
sql> select 'drop table ' || table_name || ' cascade constraints ; ' from user_tables ;
 
sql>select 'create synonym '  table_name ||
 
gzexp userid=REJECT_MONITOR/REJECT_MONITOR@DTLG01C file=REJECT_MONITOR.dmp log=REJECT_MONITOR.log grants=no direct=y consistent=y
 
gzimp userid=REJECT_MONITOR/REJECT_MONITOR@PREJ01A file=IMP_REJECT_MONITOR.dmp log=IMP_REJECT_MONITOR.log ignore=Y full=Y buffer=5000000 direct=Y
0
Comment
Question by:shihabf
[X]
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
  • 3
5 Comments
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 300 total points
ID: 17845258
Hi,

dont do an export / import, do a restore from a backup.  this is much faster and less error prone.
create a dummy database on the target server and replace this database with the most recent backup of the database on the source server.

much safer than an export / import.

good luck :)
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1200 total points
ID: 17845296
Yes, you can use export and import to migrate an Oracle dataabse from one server to another *IF* the version of Oracle in the destination server is at least as high as the version of Oracle in the source server.

No, you do not need to disable constraints.  And no, you do not need to build scripts to help with any of the constraints, grants, etc.

You can simply use export and import to do this all for you.

I would say your steps should be:
1. install the Oracle software on the destination server
2. create a starter database on the destination server
3. create the tablespaces that you need in this starter database.
4. run export on the source server with "grants=N" and "constraints=N", and copy the *.dmp file to the destination server
5. run import with "ignore=Y" so it does not quit when it sees that the tablespaces are already there, and creates the user accounts/schemas, the tables, and the PL\SQL objects.
6. run export again on the source server, but with: "rows=N,constraints=Y,grants=Y" and copy the (quite small) *.dmp file to the destination server
7. run import with "ignore=Y, grants=Y,constraints=Y"

0
 

Author Comment

by:shihabf
ID: 17845301
I need the script thats ok, my supervisor wants it the way i explained..


sf
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17845447
Should you use export/import or backup/restore?  That depends on many things:
1. Do the servers have the same O/S? If not you cannot use backup/restore.
2. Do the servers have the same Oracle version?  If not, you cannot use backup/restore.
3. Do the servers have the same disk/directory structure?  If not, you may be able to use backup/restore, but you will need to make some changes in the database before you can open it.
4. Do you want to recover freespace from deleted records or objects, or defragment objects?  If so, export/import may give you a huge performance boost.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17845482
If your supervisor says you need scripts to do this, then your supervisor does not understand how to use Oracle's export/import features to help you.  Sure, you can do this the hard way with scripts, but why when you've paid for Oracle?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

764 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