Re: Moving large amounts of data from one Oracle Server to another

From: Bob Fazio <rfazio_at_home.com.nospam>
Date: 2000/05/04
Message-ID: <T27Q4.170691$Tn4.1255819_at_news1.rdc2.pa.home.com>#1/1


Most of the suggestions are about copying the datafiles. If you are planning on staying with 7.3.., this is probably the best approach. If it is possible to go with 8.1.5, then there is a much better way.

A standby database with 8.1.5 (8i) can be used to query against for reporting purposes. It is basically the same as the above suggestion, but it will constantly keep itself in recovery mode, with the ability to query against the tables.

Another option is to actually do the export/import at the same time. We do this with some databases that we need to refresh.

Start this on the primary database, on an NFS mounted filesystem. mknod expdat.dmp p
gzip -c <expdat.dmp >expdat.dmp.gz &
exp system/manager direct=y ... # whatever you need to export.

On the other node/machine start this process shortly after the expdat.dmp.gz file starts to get filled.
gunzip -c <expdat.dmp.gz >expdat.dmp &
imp system/manager ... # whatever you want to import.

This works because a pipe is only really local to the machine, and the .gz file will be read until it is closed, plus an import will always take longer.

The advantage of this process is that you will be able to run both the import and the export at the same time removing at least 2-3 hours out of your 10 hour process. If the import fails for any reason you will still have the expdat.dmp.gz file to run it again.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Bobc" <bobchaytor_at_hotmail.com> wrote in message
news:9vaH4.143011$Hq3.3361229_at_news2.rdc1.on.home.com...

> Here is the situation:
> There is 10 gb of data on a production server (Oracle 7.3.?.?) that I need
> to move to a second server on a quarterly basis (Same version of Oracle).
> Currently this data is bundled up in a full oracle export, sent to the
> second Unix box, an import is performed and then the quarterly processing
> takes place on the second box. The whole process from the start of the
> export to the end of the import is taking 8-10 hours!!! This is way to
> long. The export processing is taking place on a large parallel siemens
box
> the import is being done on a smaller siemens machine.
>
> Does anyone out there have an suggestions on a different approach to
moving
> this data to the second machine? I am open to any and all ideas. I can
> only assume, since I have not had a chance to look at the database tuning
> issues that both databases are well tuned, if you know of any tuning issue
> specific to import/export I would really like to hear about them.
>
> One requirement of any suggestion is that it can not heavily impact
> performance on the main production server.
>
> If it is not to much trouble, could you email me directly.
>
> Thanks in advance,
> Bob.
>
>
Received on Thu May 04 2000 - 00:00:00 CEST

Original text of this message