Re: Oracle multi Tb refresh for UAT from Prod

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Sat, 17 Nov 2018 02:39:24 -0600
Message-ID: <CAP79kiT2ET=2CqkY9OW00NkzpuBza3SD9fvgXWbBkjtzxNsQCA_at_mail.gmail.com>



Mladen,

We use point in time snapshots of a protection group. A protection group is the set of Prod volumes presented to the Production server.

We refresh 6 databases from Prod within every 2 weeks where Prod is ~80 TB currently. We do not keep the snapshots current (in-sync as you say) with the Production volumes. When we want a refresh we basically "snapshot" the data at that point in time, by snapping the protection group, blowing away the existing dev/qa/staging volumes and represent them as fresh copies.

The only overhead on the storage are the size of the snapshots (the deltas generated in dev/qa/staging).

Our change data rate on Prod approaches 0.5 TB / hour during nightly processes and we generate (on average) 5 TB (with peaks of 6 TB) of archivelogs per 24 hours.

Our Pure M70 array maintains latencies at .4/.5 ms and we see very little impact on performance (I'm not on my work computer to give you exact numbers for the utilization and IOPS).

Thanks,
Chris

On Sat, Nov 17, 2018 at 1:36 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Chris!
>
> You are aware that by using snapshot, you will burden your production
> disks? Snapshot is an array of pointers. The algorithm for snapshot is
> optimized CoW ("Copy on Write", not the main ingredient in a burger). When
> the source database changes, the blocks are copied to a spare location, so
> that SAN can keep the snapshot consistent. If the data doesn't change, you
> are reading your original data through a pointer. Most of the good SAN
> equipment also has "clone" possibility (SnapVault, SRDF, HUR). In
> particular, Pure also has the ability to create a full volume from snapshot.
>
> Regards
> On 11/16/18 5:08 PM, Chris Taylor wrote:
>
> Sanjay,
>
> What is your storage device under your primary db? (or even your standby
> db?)
>
> We snap clone our Prod (or Standby) db on Pure storage but the same
> technology exists for EMC etc.
>
> Snapclones are bad fast and appear to be full size clones to the
> user/dba. But is a fraction of the space on the storage device.
>
> Chris
>
> On Fri, Nov 16, 2018, 12:56 PM Sanjay Mishra <dmarc-noreply_at_freelists.org
> wrote:
>
>> Tim
>>
>> Thanks for sharing the script and providing all detailed information.
>> This is very good starting point and can be to add any extra stuff like you
>> mentioned for dropping prod user etc or any masking.
>>
>> thanks again for your time.
>> Sanjay
>>
>> On Friday, November 16, 2018, 12:51:11 PM EST, Sanjay Mishra <
>> dmarc-noreply_at_freelists.org> wrote:
>>
>>
>> Upendra
>>
>> I cannot have UAt on NFs with delphix as it has to be on same platform
>> architecture like Prod with same configuration. We are using delphix for
>> Dev environment and also these prod don't have any Data masking requirement
>> which can make the process more complex. Currently doing expdp for set or
>> subset of table but now looking for full DB to match for some Critical new
>> interface where multiple UAT env has to be tested with prod equivalent data
>> for all application schema.
>>
>> Tx
>> Sanjay
>> On Friday, November 16, 2018, 11:58:43 AM EST, Upendra nerilla <
>> nupendra_at_hotmail.com> wrote:
>>
>>
>> Are you looking at any 3rd party products? If so, you may want to look
>> into a product Delphix.
>> It appears to be a candidate for what you are looking for. It can do data
>> mask as well as refresh environments quickly.
>> Tim Gorman is in the list - works for Delphix - reach out to him for any
>> technical details..
>>
>>
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org>
>> *Sent:* Friday, November 16, 2018 11:21 AM
>> *To:* Oracle-L Freelists
>> *Subject:* Oracle multi Tb refresh for UAT from Prod
>>
>> Can someone share the process used in your experience/organization where
>> you have several multi TB database and need to frequently refresh UAT for
>> performance testing ? I am looking not much from Masking the data which are
>> sometimes required but based on dropping prod user and adding Test user and
>> application schema back ? Appreciate If someone can share any script used
>> for sync user/password which can be main challenge as other Registration
>> with OEM/Catalog can be handled easily.
>>
>> Tx
>> Sanjay
>>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 17 2018 - 09:39:24 CET

Original text of this message