RE: Refreshing a multi TB database

Alexander,

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?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alexander Fatkulin Sent: Sunday, March 18, 2007 10:48 PM
To: veeeraman_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Refreshing a multi TB database

Ram,

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 <veeeraman_at_gmail.com> wrote:
> Hi all,
>
> We have a huge production database that is currently 2TB and expected
to
> grow to 3TB soon. We have the corresponding test database that is only
a
> 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
up
> with some suggestions. Can you please share your opinions on this.
Thanks.
>
> 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
there
> anything else.
>
> Ram.
>

-- 
Alexander Fatkulin
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l