Copying 100 billion records per day to Oracle - how?

Question

I have received a request to make a 100 billion records database available on Oracle, too. Optimizing using replication is not an option, unfortunately, so I guess the easiest solution is to create a new Oracle database, and copy all the data, and do this once per day.

What kind of Oracle server would do this well? Is there anything specific that I need to take care of in this regard?

Answer 1

Not enough detail to give a quality answer, but I think 'server' is going to be 'servers'.

If you have 100,000,000,000 records at 100 bytes each, that's 9,536,743 MB per day without any incidental I/O for indexing, etc. Divide that by the number of seconds in a day and you get 110 MB per second. Even that is assuming even distribution and a full 24 hours. That's right about at the theoretical max for GigE.

In other words, you're going to be maxing 'normal' bandwidth and disk I/O even with these simple assumptions.

Something tells me that you really want to think this design through.

Answer 2

I think for that sort of job it's worth calling Oracle..

Answer 3

I would buy the following;

HP DL795 G6 with 8 x 6-core Opterons, 64GB+ memory, 64-bit RedHat, dual 10Gbps ethernet NICs, 2 x HP P800 SAS controllers connected locally to eight MSA70s each filled with 25 x 146GB 2.5" 6G 15krpm SAS disks.

I like to fix performance issues with hardware :)

If this doesn't do it then you're going to have to go to something MUCH more expensive and/or shard the thing.

Answer 4

Looks like exadata v2 should more than easily do the job. See http://www.oracle.com/us/corporate/press/033684 and http://faisalgh.blogspot.com/2009/10/embarrassingly-fast-exadata-v2.html

Answer 5

1. Datapump. Parallelize.

2. If not replication, what about a standby database? You make a copy once, then apply changes in an asynchronous manner so if standby crashes, primary doesn't feel anything. You get it read-only, thou, but it may be sufficient.

3. Get to know more about the data - are they all/any updated or only added? Try to avoid copying records which didn't change if the condition is easy enough and record aren't dispersed...

Just some ideas...

Answer 6

When you do bulk processing it is about bytes and NOT about the number of rows. Your just pushing bytes around.

This question is a bit vague. My guess from this is you need to move data from one DB to another right?

This is what I would do (without know the actual size of your database and other stuff going on )

1. create a database and link. CREATE TABLE AS NO LOGGING across the link and see how long it takes. if that is good enough. your done. This is generally the fastest way to move the data. make sure to use NO LOGGING or it will be slower. if the table youa re reading is partitioned then query it with parallel option, if not don't (won't do anything if you use it if its not partitioned).

2. Transportable tablespace. Google it. your table will need to be a schema that is fully contained in a tablespace. You also need to sysdba access (so has to be done by the oracle user). this requires shell scripting and more code than option 1.

3. data pump

In that order. Materialized views suck for large amounts of data and full refreshed. Creating the table is MUCH faster. only use option #3 if you can't meet the requirements of a transportable tablespace.

I have loaded large amounts of data/day. in the terabyte range.