Re: Methods to Create Smaller Test DB from Production?

Let me support Mladen on this 110%.

I use Princeton Softech's "Move for Servers" product and it does exactly what you are after. Let me elaborate on our scenario for you...

Our production instance of over 1TB, from there we use backup tapes to restore a volume testing environment once every so often (this acts as a recovery test of sorts as well). From here we use Move for Servers to create many small test and development databases (about 20 of them currently active). We have no Referential Integrity defined within our database (not my preference) so I had to manually teach the tool about each relationship. Then I select the tables that I want extracted and define limits. For example, I have one extract to get all referential data (perhaps 50 tables), one extract to get data based on customers (about 80 tables) and several other adhoc extracts to meet specific requirements (such as testing a specific defect). Once the initial definitions are created then maintenance is a breeze.

To build a new database I create the structure from DDL (Move for Servers can do this but isn't flexible enough for my preference), then load a Reference extract and then load a 500 Customer extract. To extract all data for 500 customers (from perhaps 500,000 - 1,000,000 customers) from our system takes about 3-4 hours and then to load this into a new environment takes about 15 minutes. I keep the extracts once created so that I can load into multiple destinations quickly.

I could talk for hours about Move for Servers - there is a wealth of functionality in there once you start to understand the tool and their support and pricing are wonderful. So ignore those people suggesting you roll-your-own unless you are a sucker for punishment. Did I also mention that Move for Servers can move data between different RDBMS's - so moving data from Oracle to SQL Server (for example) is possible? That may or may not be a feature of importance to you. If you have any further questions let me know. I can also put you into contact with people from Princeton Softech if you wish.

Regards,

      Mark.

                                                                                                                                     
                      Mladen Gogala                                                                                                  
                      <mgogala_at_adelphia.        To:       oracle-l_at_freelists.org                                                     
                      net>                      cc:                                                                                  
                      Sent by:                  Subject:  Re: Methods to Create Smaller Test DB from Production?                     
                      oracle-l-bounce_at_fr                                                                                             
                      eelists.org                                                                                                    
                                                                                                                                     
                                                                                                                                     
                      02/03/2004 06:59                                                                                               
                      Please respond to                                                                                              
                      oracle-l                                                                                                       
                                                                                                                                     
                                                                                                                                     

It does exist. Take a look at Princeton Softech,

             http://www.princetonsoftech.com

Very good tool, very good company. Alternatively, there is a UK company called DataBee, which does the same thing. I don't have much experience with them, but thweir DBA Tool to extract DDL from an export file is excellent. You can look at them at

             http://www.databee.com

On 03/01/2004 02:54:05 PM, "Thater, William" wrote:
>
>
> -----Original Message-----
> From: Mercadante, Thomas F [mailto:thomas.mercadante_at_labor.state.ny.us]
> Sent: Monday, March 01, 2004 2:48 PM
> To: 'oracle-l_at_freelists.org'
> Subject: RE: Methods to Create Smaller Test DB from Production?
>
>
> David,
>
> Boy, this would be a great tool if it existed.
>
> I think the only way out for you is to roll-your-own. You could easily
> identify code tables (look-up tables) and export all of that data. But
the
> relational data (like your main parent table and all of the subsequent
> children records) you will probably need to move by hand - or by writing
a
> program to select, say, 10,000 master records and all of the subsequent
> child records. It's not an easy task, but once you've done it, it is in
the
> can - but subject to updates when new tables and relationships come
along.
>
> Good Luck!
> [Shrek]
>
> there is a product from Quest that will do that for PeopleSoft, i used it
on
> my previous gig to do just that. but i don't know if it will work with
> databases other than PeopleSoft.
> --
> Bill "Shrek" Thater ORACLE DBA
> "I'm going to work my ticket if I can..." -- Gilwell song
> william.thater_at_carrier.utc.com
> <mailto:william.thater_at_carrier.utc.com>
> ------------------------------------------------------------------------
> Absence is to love what wind is to fire; it extinguishes the small, it
> enkindles the great. - Comte de Bussy-Rabutin
>
>
>

--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------





<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------