Is it possible to make Export and Import at the same time..

Printable View

10-15-2003, 07:19 PM
skdas

Is it possible to make Export and Import at the same time..

Hi,

Have tablespace 20 GB of data. I want to export it and import it to another database.

I want to have less down time..

Export itself takes long time. Can export and import be done at the same time. Any way out ..?
10-15-2003, 07:27 PM
stecal

Perhaps if you used files?
10-15-2003, 07:47 PM
skdas

Please elaborate a little bit more..

The databases, both are in same server.. I want to send compressed file with PIPE and at the same time also I want to import to other database from the same compressed file....

Is it possible..?
10-15-2003, 08:22 PM
stmontgo

Quote:

Originally posted by skdas
Please elaborate a little bit more..

The databases, both are in same server.. I want to send compressed file with PIPE and at the same time also I want to import to other database from the same compressed file....

Is it possible..?

you want to import into one db from the same file that the export is in the middle of producing?
10-16-2003, 12:24 AM
pando

you can but if the process interrrupts you have to start all over again which can be longer than doing export then import seperately!
10-16-2003, 06:10 AM
julian

Re: Is it possible to make Export and Import at the same time..

Quote:

Originally posted by skdas
Hi,

Have tablespace 20 GB of data. I want to export it and import it to another database.

I want to have less down time..

Export itself takes long time. Can export and import be done at the same time. Any way out ..?

Why don't you clone the database to the other server (with RMAN). That's very fast method.
10-16-2003, 09:55 AM
tamilselvan

Export and Import can be done simultaneously using unix pipes. This is the faster than any other method I had used.

Tamil
10-16-2003, 12:54 PM
slimdave

Quote:

Originally posted by tamilselvan
Export and Import can be done simultaneously using unix pipes. This is the faster than any other method I had used.

Tamil

Do you mean directly through a pipe, or through a fifo file/node?
10-16-2003, 01:25 PM
tamilselvan

Through pipes and rsh.

Tamil
10-17-2003, 04:01 AM
abhaysk

copy is the best & fastest.
10-17-2003, 10:10 AM
tamilselvan

We are discussing about export/import not copy command.

Tamil
10-17-2003, 10:33 AM
slimdave

So what? Nothing wrong with mentioning other methodologies. If the original question is "how do i move data only as fast as possible", then COPY is a good solution -- particularly if it can be done with multiple sessions simultaneously. I've often found it to be much faster than export/import, and more robust also.
10-17-2003, 10:44 AM
abhaysk

Quote:

Originally posted by tamilselvan
We are discussing about export/import not copy command.

Tamil

I duuno why ppl tend to look for harder methods when we have simple & the best methods :rolleyes: :rolleyes:
10-17-2003, 12:16 PM
SANJAY_G

Can anybody elaborate how to do export and import simulataneously using unix pipes or whatever ??
10-17-2003, 01:51 PM
tamilselvan

Steps are:

1 On the Source machine (Let me call it SRC) create unix pipe export
$ mknod /dev/p_exp p

mknod is a command in Solaris to create named pipe.

2 On the Target machine create unix pipe for import
$ mknod /dev/p_imp p

3 On the Target run rsh (remote shell) to copy the file from Source in background and send the output to import pipe
$ rsh SRC dd if=/dev/p_exp > /dev/p_imp

4 On the Source, run the export
$ exp / file=/dev/p_exp full=Y

Wait for 30 Seconds.

5 On the Target, run the import
$ imp / file=/dev/p_imp full=Y

Here order of the steps is very important. Also you should have priv to run rsh. You can also use rcp instead of rsh.

Tamil

To Slimdave,
When I saw copy command as suggested by abhask, I did not think Oracle COPY command, I thought unix cp. That is why I said "We are discussing about exp/imp". Sorry to all.
But I still feel COPY command will be slow, because of dblink is involved.

Tamil
10-17-2003, 03:46 PM
slimdave

Quote:

Originally posted by tamilselvan
[B]Steps are:

1 On the Source machine (Let me call it SRC) create unix pipe export
$ mknod /dev/p_exp p

mknod is a command in Solaris to create named pipe.

2 On the Target machine create unix pipe for import
$ mknod /dev/p_imp p

3 On the Target run rsh (remote shell) to copy the file from Source in background and send the output to import pipe
$ rsh SRC dd if=/dev/p_exp > /dev/p_imp

4 On the Source, run the export
$ exp / file=/dev/p_exp full=Y

Wait for 30 Seconds.

5 On the Target, run the import
$ imp / file=/dev/p_imp full=Y

Here order of the steps is very important. Also you should have priv to run rsh. You can also use rcp instead of rsh.

Tamil

If you have spare cpu cycles, it might be worth compressing the data on the way into p_exp, and uncompressing it on the way out of p_imp

Quote:

Originally posted by tamilselvan

To Slimdave,
When I saw copy command as suggested by abhask, I did not think Oracle COPY command, I thought unix cp. That is why I said "We are discussing about exp/imp". Sorry to all.
But I still feel COPY command will be slow, because of dblink is involved.

Tamil

Oh OK, but SQL*Plus copy command doesn't use db_links -- the data gets read by sqlplus from the source db and written to the target db through regular SQL*Net connection.

Therefore you really want the sqlplus session to be running on either the source or the target host to avoid doubling-up on your network traffic
10-17-2003, 04:02 PM
marist89

Quote:

Originally posted by tamilselvan
Steps are:

1 On the Source machine (Let me call it SRC) create unix pipe export
$ mknod /dev/p_exp p

mknod is a command in Solaris to create named pipe.

2 On the Target machine create unix pipe for import
$ mknod /dev/p_imp p

3 On the Target run rsh (remote shell) to copy the file from Source in background and send the output to import pipe
$ rsh SRC dd if=/dev/p_exp > /dev/p_imp

4 On the Source, run the export
$ exp / file=/dev/p_exp full=Y

Wait for 30 Seconds.

5 On the Target, run the import
$ imp / file=/dev/p_imp full=Y

Here order of the steps is very important. Also you should have priv to run rsh. You can also use rcp instead of rsh.

Tamil

I bow down to the master of the pipes...
10-20-2003, 02:40 AM
abhaysk

Quote:

Originally posted by tamilselvan
But I still feel COPY command will be slow, because of dblink is involved.

Tamil

Tamil :

It will be worth testing of Simultaneous Exp/Imp Vs COPY ( as suggested by Dave, the SQLPLUS session being open on either Source Or Traget Server ) and dont forget keeping Arraysize 5000 and copycommit your choice preferablly < 50..

Abhay.
10-20-2003, 08:30 AM
marist89

Quote:

Originally posted by abhaysk
Tamil :

It will be worth testing of Simultaneous Exp/Imp Vs COPY ( as suggested by Dave, the SQLPLUS session being open on either Source Or Traget Server ) and dont forget keeping Arraysize 5000 and copycommit your choice preferablly < 50..

Abhay.

Either way, you're network will be the limiting factor. Unless you are running multiplexed GB adapters or your disks are really slow, I doubt either method has a significant advantage over the other.
10-20-2003, 08:56 AM
abhaysk

Quote:

Originally posted by marist89
Either way, you're network will be the limiting factor. Unless you are running multiplexed GB adapters or your disks are really slow, I doubt either method has a significant advantage over the other.

Well Network will be limiting factor, but does Import do any better Over Insert using COPY will be the question..So isnt it better to perform a test?

PS I have no idea about "multiplexed GB adapters", if you could let me know about it, would be better (ofcourse I can STW, thas different issue)

Abhay.
10-20-2003, 09:44 AM
marist89

Quote:

Originally posted by abhaysk
Well Network will be limiting factor, but does Import do any better Over Insert using COPY will be the question..So isnt it better to perform a test?

If you've got the kind of time to chase after a dime when there's thousands of dollars on the table, go for it.

Quote:

PS I have no idea about "multiplexed GB adapters", if you could let me know about it, would be better (ofcourse I can STW, thas different issue)

Two Gigabit Ethernet adapters on each box bound as one. (ie. lots of bandwidth between servers)
10-20-2003, 11:22 AM
slimdave

Quote:

Originally posted by abhaysk
... dont forget keeping Arraysize 5000 ...

Sounds a little high to me -- i would expect that you'd be getting collisions on the network. I'd keep it to 500.
10-20-2003, 11:41 AM
abhaysk

Quote:

Originally posted by slimdave
Sounds a little high to me -- i would expect that you'd be getting collisions on the network. I'd keep it to 500.

Collisions, It would not be as you expected.. IMHO and used many times it quite faster than defualt ARRAYSIZE 15 or even 500 as you said...

Well it will be as any other copy over N/W, but in the bundles of the sizes due to 5000 rows !! & which will not be a problem as/when a Copy of over a Gig Bytes will not..

Abhay.