RE: sync up production to UAT for a 500G+ database

From: Mark W. Farnham <>
Date: Sat, 15 Mar 2008 14:40:50 -0400
Message-ID: <00ee01c886cc$18d8f060$>

How big a window do you have each night? I believe you're saying you want to discard anything that took place in testing mode and completely resync.  

One simple way to do that is instantiate (either manually or with Dataguard if you want to pay license fees for a case where this is not part of your recovery and reliability thread) a set of database files on the test server from either a hot or cold backup, and then begin shipping archived redo logs. (Again, either manually or with Dataguard). Then simply recover this copy of "PROD" to an arbitrary end of an archived log near the end of night (or some daily business event if you want functional consistency). Then you: Shut down "TEST", cancel recovery on "PROD",shut down the "PROD" instance, copy "PROD" files over the top of TEST, and start up up the new generation of TEST renaming and resetting the online redo logs. That gives you a new
"TEST". Then you mount and start recovery on "PROD" again.

Figuring out needs to be licensed to facilitate testing I leave up to you. Of course this means you will have to supply roughly 2X the disk acreage of your current setup, but if you align stripe sets reasonably you should be able to accommodate the local disk-to-disk copies in non-interfering sets of parallel i/o. If you're not formally in dataguard mode you may of course from time to time have to re-instantiate parts or all of your shadow of
"PROD" on the test server due to unlogged transactions or use the
appropriate force logging switches (together with the attendant overhead) on the real PROD database.  

Still, this is usually less of a time requirement than a complete fresh copy of the database every day. There are many, many ways to skin this particular cat depending on your choices of hardware and software to augment your options. This is just one simple way to do it.  



From: [] On Behalf Of Prasad
Sent: Saturday, March 15, 2008 12:15 PM
Subject: sync up production to UAT for a 500G+ database  


I have a 500GB production database running on Oracle 9i on Solaris . and now the Users Testing group wants to keep it in sync with production every night . So I am looking for any suggestions from List's experience .

Please advise.


Received on Sat Mar 15 2008 - 13:40:50 CDT

Original text of this message