Transferring huge amount of data

Hi folks,

I need to design a process which will transfer approximately 100,000,000 rows between two databases across two servers.
The data is transformed CDRS which should be transferred to a staging area in the target database. After the transferring they will be moved to the real tables by EXCHANGE PARTITION command.

Both databases version is 10.2.
The source database is hosted on Red Hat Enterprise Linux AS release 3 (Taroon Update 8).
The target database is hosted on Red Hat Enterprise Linux AS release 4 (Nahant Update 4)

My question is : what is the best way to transfer this huge amount of data between the servers?
The possibilities I thought :
1. Data pump
2. Transportable tablespace
3. Create table/insert as select over DB link

What do you think?
Any advice will be helpful.

Thanks in advance,
Nir

All options depend on network bandwith.

If you want something elegant and semi-transparent, consider dblink IF your nodes are LAN connected. DBLink over WAN is a nightmare...

Options 1 and 2 may use offline compression, in order to reduce sending time.

Option 1 may be benefited with parallel load/multiple DB writers, direct-path loading which gives less disk usage (if you compress receiving tables), thus lowering IO when executing EXCHANGE PARTITION.

Option 2 seems like 'almost' transparent, kind of plug and play... I've not tested it.

Thanks for your comments!

My favorite option is using the data pump utility.
To avoid transferring the dump file between the nodes I thought running expdp and impdp from the target database host,as the following example:

Code:

---

expdp user/pwd  tables=T1,T2,T3,....,Tn  NETWORK_LINK=source_database_link directory=CDRS_DIR dumpfile=CDRs.dmp logfile=expdpCDRs.log parallel=4
impdp user/pwd tables=T1,T2,T3,....,Tn directory=CDRS_DIR dumpfile=CDRs.dmp logfile=impdpCDRs.log parallel=4

---

What do you think?

We used to do data transfer between servers using exp/imp for mis purpose.
but started to take too long as data went on increasing.
so we did following.

1) on source db create a flat file with comma seperated data for tables which you want.
2) ftp file to target db
3) map a external table on that flat file
4) pull data from that external table to required table .
5) or if you have partition table then just pull data in temp table in 4th stage and do exchange partition.

you need script for this. its a bit head cracking job but once done it works smoothly...

What is the avg row length?

100M rows is not such an impresive amount of data, large but not huge.

If servers are linked in a high-capacity network I'll go with the most simple option which is a DBLink.

If servers are not liked in a high-capacity network I'll exp/compress - ftp - uncompress/imp. Be sure you do exp/compress and uncompress/imp concurrently, on-the-fly; not as sequential steps.

Quote:

---

Originally Posted by PAVB

100M rows is not such an impresive amount of data, large but not huge.

---

well, rather than going by just numbers.. one should look at the size of the table (determined by avg row length * number of rows).. after all one is transfering data and size of movement is all it matters..

lets say avg row length is too low (<100), then db link would be the answer irrespective of LAN settings (assuiming atleast 1MBPS which any LAN would have more than this)..

if avg row length is too high i would conisder transportable tablespace or expdp.

rgds
abhay

Quote:

---

Originally Posted by PAVB

... Be sure you do exp/compress and uncompress/imp concurrently, on-the-fly; not as sequential steps.

---

Can you explain why, PAVB? It's not something I'm familar with. :confused:

Quote:

---

Originally Posted by JMac

Can you explain why, PAVB? It's not something I'm familar with. :confused:

---

Just to save 1) Space, 2) I/O and, 3) FTP bandwidth

1- exp writes to a pipe and compress writes to the filesystem a already compressed file.

2) because of #1, you do a lot less I/O

3) because you get a compressed dump file you save network bandwidth during FTP

Also, during uncompress/imp you will save a lot of I/O.

Thanks guys for your advices and suggestions!
I don't know yet the avg row length. I assume that some tables will be below 100 and the others above.
I'll test soon both DB link and data pump options and I'll let you know.

Best regards,
Nir

Quote:

---

Originally Posted by PAVB

Just to save 1) Space, 2) I/O and, 3) FTP bandwidth

1- exp writes to a pipe and compress writes to the filesystem a already compressed file.

2) because of #1, you do a lot less I/O

3) because you get a compressed dump file you save network bandwidth during FTP

Also, during uncompress/imp you will save a lot of I/O.

---

Well point 1 & particularly 3 is all that is most important and techinically right too.

Point 2 may be incorrect, as you will still need to do LIO + lots of CPU to compress/uncompress.

Rgds
Abhay

Quote:

---

Originally Posted by abhaysk

Point 2 may be incorrect, as you will still need to do LIO + lots of CPU to compress/uncompress.

---

I'm in agreement with your assesment of increased CPU utilization for compress/uncompress purposes.

In regards to LIO -respectfully- I don't care about it, I care about physical I/O. Remember, we are doing exp/compress and uncompress/imp on-the-fly therefore in the first case exp is writting to an Unix pipe then compress reads from the Unix pipe and writes to disk; in the second case uncompress reads from the compressed file and writes to an Unix pipe from where imp reads.

PAVB - don't you think that running expdp/impdp from the target server is better than exp-compress-ftp-uncompress-imp?

Quote:

---

Originally Posted by nir_s

PAVB - don't you think that running expdp/impdp from the target server is better than exp-compress-ftp-uncompress-imp?

---

Just to be clear, I'm not suggesting exp-compress-ftp-uncompress-imp.

What I've suggested is a three steps process, not a five steps process:

Step #1: Export/Compress on-the-fly in a single step.
Step #2: FTP compressed file
Step #3: Uncompress/Import on-the-fly in a single step.

The expdb over a NETWORK_LINK and them impdb will move uncompressed data over the network -which is probably your slower component- while proposed strategy will move compressed data.

I will love to see the results of a test, would you mind in testing both alternatives for a medium size table and post results?

Thanks PAVB.
I'll post the results when we finish testing.

Regards,
Nir

Quote:

---

Originally Posted by PAVB

I'm in agreement with your assesment of increased CPU utilization for compress/uncompress purposes.

In regards to LIO -respectfully- I don't care about it, I care about physical I/O. Remember, we are doing exp/compress and uncompress/imp on-the-fly therefore in the first case exp is writting to an Unix pipe then compress reads from the Unix pipe and writes to disk; in the second case uncompress reads from the compressed file and writes to an Unix pipe from where imp reads.

---

I dont think you got the GIST.. i meant LIO + CPU Time for compression is nearly equal to time required for PIO to local file system.. all it matters is how much data your moving over network, that time vs data over netwrok is exponential.

however you should also note that OS will be making lots of LIO+PIO while doing a compression/uncompression (plus a added over head of CPU due to extensive LIO + processing the compressing algorithm).. the point 2 of yours hardly matters.. all it matters is point 3, which is obivious and evident.

Rgds
Abhay.

Quote:

---

Originally Posted by abhaysk

LIO + CPU Time for compression is nearly equal to time required for PIO to local file system

---

That's not true, you are wrong.
If you do not believe me, test it using a reasonable large table.

u try to test your self with 2GB file..

test 1: copy the file so that time elapsed will be representative of PIO.

test 2: compress the file and capture the elapsed time.

Quote:

---

Originally Posted by abhaysk

u try to test your self with 2GB file..

test 1: copy the file so that time elapsed will be representative of PIO.

test 2: compress the file and capture the elapsed time.

---

So... you want to test in a cat to know how a dog will behave? LMAO

We have extensively tested the real deal -test in a dog to know how a dog will behave- and I know what I'm talking about. This thread is getting boring, you are not listening.

ok fine.. i will not talk more on this.. i dont want to get personal.. if i am wrong i would suerly change myself.. (i think you know the algorithm for compression)

Well i did a test using exp with compress running in background using pipes..

the table had 6million records and was 3G big..

export (exp) + compression (using pipe) took 4.5 mins
export (exp) + cpmpression (serial task) took 6.0 mins

hardly 1.5 mins difference and that compression took about 2 mins of this total time..

PS, I should admit that i missed the gist of pipe.. your actalluy right..

i had said before

Quote:

---

i meant LIO + CPU Time for compression is nearly equal to time required for PIO to local file system..

---

indeed this is the case, from above test.. the time difference seen is solely because the process is a serial one and former is virtual parallelism..

if we have the table of 20G, then there will still be a max difference of 5-10 mins (required for compression)

I mentioned in my other post that point 2 was not very significant.. as was point 3.. and indeed it is.. your point 2 would be significant if it were a table to 100-200G.. i really suspect if that is the case..

Hi folks,

We tested yesterday transferring of 33,000,000 rows between two servers.
We transferred one table.
AVG_ROW_LEN=286
total space=9GB

We issued create table as select over DB link and it took 14 minutes.
I think it's a good results.
What do you say?

Nir

had you done in parallel dml with no-logging then you could do still better..


rgds
abhay

thanks abhay!