Improve company productivity with a Business Account.Sign Up

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

Need code to migrate data from one server to another

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
  • 3
2 Solutions

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 :)
Mark GeerlingsDatabase AdministratorCommented:
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"

shihabfAuthor Commented:
I need the script thats ok, my supervisor wants it the way i explained..

Mark GeerlingsDatabase AdministratorCommented:
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.
Mark GeerlingsDatabase AdministratorCommented:
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?
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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