Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Refreshing a multi TB database

RE: Refreshing a multi TB database

From: William Wagman <>
Date: Mon, 19 Mar 2007 11:30:25 -0700
Message-ID: <>


This is an interesting solution and of course, leads to further questions. Our developers have been asking for a 'sandbox' if you will where they can play yet have it refreshed form production on a daily basis. Currently the database is 9i and I use RMAN to clone the test/devel database but haven't really figured out yet how to automate that process so it doesn't happen on a daily basis. So two questions...

  1. Has anyone worked out a means to do this using 9i? If so how?
  2. You mention some restrictions in the solution you provided. I am curious in knowing those restrictions. Are the flashback storage requirements simply the sum of the archive generation in a 24 hour period or are there other issues which must be taken into account? Have you done this and how difficult was it to implement?


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208
-----Original Message-----
[] On Behalf Of Alexander Fatkulin Sent: Sunday, March 18, 2007 10:48 PM
Subject: Re: Refreshing a multi TB database


you can also consider the following scenario if you're on 10G. There is some restrictions evolved but I think it's worth considering.

  1. create a physical standby from production box. We will use it as a future test ground DB.
  2. enable flashback on this newly created standby.
  3. activate it and open in a read write mode.

Now you have fully workable clone from production DB.

At the end of the day when you need a fresh copy:

  1. close the test DB.
  2. mount and flash it back to before activation point.
  3. convert back to physical standby and rollforward using archivelogs from prod.
  4. activate and open it in read write again.

This can (or can not!) be significantly faster than doing a full db restore depending on situation. Say your prod DB doesn't generate huge amount of logs between the refresh points and test DB doesn't generate too many flashback logs (so to roll the whole thing back and then forward takes less time than full restore+recover). You would also consider running prod in a force logging mode perhaps - again this can impact your choice.

HTH. On 3/17/07, Ram Raman <> wrote:
> Hi all,
> We have a huge production database that is currently 2TB and expected
> grow to 3TB soon. We have the corresponding test database that is only
> third of its size and was refreshed a while ago. I would like to see
> refreshed data so we can test things more accurately in test DB. I am
> pressing for more space and better refresh methods, but I want to come
> with some suggestions. Can you please share your opinions on this.
> Some things I can think of: We have to consider the network bandwidth
> between the two servers, the space, the activity in the test DB. Is
> anything else.
> Ram.

Alexander Fatkulin

Received on Mon Mar 19 2007 - 13:30:25 CDT

Original text of this message