Solved

Need code to migrate data from one server to another

Posted on 2006-10-31
5
570 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 34

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 34

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 34

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

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

707 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

19 Experts available now in Live!

Get 1:1 Help Now