Re: Time required for export/import vs. "copying tables"

A copy of this was sent to jeremy_jameson_at_my-deja.com (if that email address didn't require changing) On Mon, 10 Jan 2000 18:09:54 GMT, you wrote:

>Let me preclude this post by saying that I am not a DBA but rather an
>application developer.
>
>Can someone please comment on the use of export/import vs. "copying
>tables" for transferring data from one instance to another? We
>currently have three instances of Oracle (development, test, and
>production) that we need to keep relatively in sync on a periodic basis
>(e.g. once a week replicate the data from production to test and
>development).
>
>In my opinion, these databases are quite small -- around 5 GB -- but
>our DBAs insist that it takes about 4-6 hours to replicate the
>production data into another instance. I realize that the test and
>development servers may not be running on state of the art equipment
>(Sparc 10's) but 4-6 hours for 5 GB of data just seems absurd.
>
>I was also told that the preferred process is to "copy the tables"
>(which I am assuming means to run an INSERT operation using a database
>link) rather than performing an export/import. Doesn't it seem logical
>that an export/import would be significantly faster?
>
>Could someone please provide a time estimate for replicating 5 GB of
>data from one Oracle instance to another? I understand that the time
>will vary widely on the hardware, but I just need a ballpark figure.
>

4-6 hours seems long but it really depends on the disks and the ram. It could be that long.

If it is the whole database, why not just backup and restore? take your last production backup and restore it (rolling forward if need be) to build your test/development -- take a hot backup of production and apply it to development and test and roll em forward a little to make them consistent. Would be a good test of your recoverability capabilities as well.

If you are using Oracle8i, you can also look at transportable tablespaces to 'steal' datafiles from one instance and attach them to another as well. be faster then dumping and reloading all of your data.

>Thanks,
>
>Jeremy Jameson
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 10 2000 - 13:15:33 CST