Home » RDBMS Server » Server Utilities » Strategic question export/import of large amount of data
Strategic question export/import of large amount of data [message #263618] Thu, 30 August 2007 09:13 Go to next message
Nasiobo
Messages: 10
Registered: August 2007
Location: Atlanta, GA
Junior Member
Hey all, I got a strategic question that I'd like to get your opinions on if you don't mind. I was not sure where to post this, but, this seemed the correct place.

We have a production database [version is 10.2.0.3.0] (located in New York) with one particular table space that is around 50 gig. On a normal week we have to export this table space and import to staging databases (two of them) and "research" databases (around 10 of them) (same versions), located in Texas. Now, we don't do all 12 systems in the week, but, we may do three or four in a week. So we dealing with a little more than 600 gig in a normal cycle (yes, more space for other table spaces and such .. but, trying to keep this simple). The reason for the export/import to so many different systems is really a "political" in which I have tried to change but just don't have this ability. They use this for different research, such as accounting may use one system to verify all accounting activity outside of production, documents team may load different datasets .. etc, really lots of different reasons.

The normal time for one of these cycles, export, copy from New York to Texas, and import takes around 12 hours or so (give or take a little). This is not a problem right now.

Now, we're in the process of taking on lots of new data. In our test cycles we've shown that this new data will be around 300 gig. So, the total "new" table space will now be 300 gig. Now, instead of the 600 gig normal cycles we'd be looking at around 7.2 tera. This is not going to be acceptable, not only from a space standpoint, but, from a time standpoint. The time it would take to do this export, copy and import would take way too long.

Question:

Any suggestions? Do any of you deal with large data sets that you have to import to test systems? We are looking at the possibility of setting up replication from New York to Texas so that we'd have a system in Texas that we could start grabbing the data from. Then, we also thought that we'd start exporting just limited datasets from certain tables, which looks like something we could do using data pumping. So, instead of exporting all 500+ tables with "all" the data we'd export 500+ tables, and for the majority of the tables we'd take only specific companies (for example).

Any other suggestions? Of course, our first suggestion was to knock of some of these twelve systems, but, as I said, this is real political and these requests are way above our division. In the end we could get the space, but, even with this if we did exactly what we were doing today it would turn into a "timing" issue. Any tips or suggestions are greatly appreciated!

-- Nasiobo
Re: Strategic question export/import of large amount of data [message #263639 is a reply to message #263618] Thu, 30 August 2007 10:15 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Why you don't use Transportable tablespaces feature.?
Re: Strategic question export/import of large amount of data [message #263952 is a reply to message #263618] Fri, 31 August 2007 06:10 Go to previous messageGo to next message
Nasiobo
Messages: 10
Registered: August 2007
Location: Atlanta, GA
Junior Member
Thanks for the suggestion. I have submitted that for research and are now starting some initial testing.

We have also considered exporting for some testers using a sample percent, but, we're not sure how this would work with foreign keys and such. For example, if we say to export a sample of 25% of the data I'm assuming that this would be table by table, so, some foreign key data could be missing.
Re: Strategic question export/import of large amount of data [message #264729 is a reply to message #263618] Tue, 04 September 2007 06:25 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
we say to export a sample of 25% of the data


If you want sample then use data pump.

If not then,
Before using transport tablespaces check out it 's limitation

(like tablespace read only, set of tablespaces self contained or not. ETC...)
Previous Topic: Export only part of data.............
Next Topic: prob with setting DISPLAY env var.
Goto Forum:
  


Current Time: Wed Dec 25 09:41:36 CST 2024