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
Solved

Need code to migrate data from one server to another

Posted on 2006-10-31
5
579 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
  • 3
5 Comments
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 100 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 400 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

809 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