Re: VLDB Refresh

That's what I was thinking as well, but wanted a sanity check. Thank you for the info.

Chris

On Tue, Feb 25, 2020, 8:18 AM Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> No, only on file systems with implemented copy-on-write (like ZFS and
> ACFS). If ASM LUNs are on SAN, then you could probably use storage
> copy-on-write features instead. I prefer to have it on OS, because it’s
> more transparent, easier to diagnose and doesn’t cost anything.
>
>
>
> On Exadata you could possibly use ACFS, but I don’t think anyone is doing
> that.
>
>
>
> Mind that copy-on-write inherently performs worse because of
> fragmentation, see excellent Bart Sjerp’s blog about it
> https://bartsjerps.wordpress.com/2013/02/26/zfs-ora-database-fragmentation/
> . So, basically it’s trading some performance for agility. But as already
> mentioned, you can have both maximum performance in the production and the
> agility in test by cloning from a standby.
>
>
>
> Best regards,
>
>
> Nenad Noveljic
>
>
>
> *From:* Chris Taylor <christopherdtaylor1994_at_gmail.com>
> *Sent:* Dienstag, 25. Februar 2020 15:06
> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> *Cc:* niall.litchfield_at_gmail.com; dmarc-noreply_at_freelists.org; Oracle-L
> Freelists <oracle-l_at_freelists.org>; Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk>
> *Subject:* Re: VLDB Refresh
>
>
>
> Is this doable on ASM ? Has anyone done this on Exadata where you don't
> have access to the storage?
>
>
>
> Chris
>
>
>
> On Tue, Feb 25, 2020, 2:22 AM Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> wrote:
>
> Hi Sanjay,
>
>
>
> I’ve been using a copy-on-write file system for more than a decade (mainly
> ZFS, but recently experimenting with ACFS as well) for the (super-)fast
> refreshing of test environments. Any database regardless of its size can be
> cloned within seconds. Prior to the invention of ZFS I was using storage
> shadow copy for the same purpose.
>
>
>
> When a large database has to be cloned to another server, you can run a
> physical standby there and do snapshots/clones from the standby instead
> from the production. Not only is it extremely fast, but it’s also cheap.
> What I mean by that is, you can create multiple test databases which
> literally almost don’t require any additional storage space, as only the
> changed blocks are physically stored.
>
>
>
> Of course, you’d need identical environments when you’re testing for
> performance, but for the functional tests this is OK.
>
>
>
> Owing to robustly automated copy-on-write-based database cloning, the
> environments where we’ve implemented this technology have become “agile”
> even before this word became a buzzword.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
> https://nenadnoveljic.com/blog/
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *niall.litchfield_at_gmail.com
> *Sent:* Dienstag, 25. Februar 2020 08:50
> *To:* dmarc-noreply_at_freelists.org
> *Cc:* Oracle-L Freelists <oracle-l_at_freelists.org>; Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk>
> *Subject:* Re: VLDB Refresh
>
>
>
> The Oracle feature that matches your use case is called Exadata Storage
> Snapshots - the documentation is at
> https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-snapshots.html#GUID-78F67DD0-93C8-4944-A8F0-900D910A06A0 and
> does require you to be on database 12.1.0.2 or above. I've not personally
> used this data virtualization option (we aren't an Exa customer) but there
> are a number of blogs about the feature and you might find
> https://www.doag.org/formes/pubfiles/10819226/2018-Infra-Peter_Brink-Exadata_Snapshot_Clones-Praesentation.pdf
> a helpful presentation as well.
>
>
>
> On Mon, Feb 24, 2020 at 10:55 PM Sanjay Mishra <
> dmarc-noreply_at_freelists.org> wrote:
>
> Jonathan
>
>
>
> Thanks for the update. Problem is that all these Partitions are
> interval-based and each one is several terabytes and so only 3 Bigfile
> tablespaces are in use as one for Lobs, Table Data and for Indexes. Due to
> the size of the database which is now 130Tb, there is already 3 LOB related
> tablespace of each 28-30 TB. Once LOB reached close to 25 TB we change the
> default for Lob to use new bigfile tablespace. We are using Triple
> Redundancy for Production ASM and double for Non-prod and environment are
> on Exadata and so trying to see if there is any way for Oracle to save
> storage in Refresh setup. It is a new setup active from only close to one
> year and currently, it was refreshed once a year and now request is coming
> to every quarter. Every month has 10Tb of growth. Moreover, Retention for
> the database is 4 Year and so old Partitioned were also purged.
>
>
>
> So trying to check if Oracle has any features that can help. Using
> Readonly tablespace is a good point if we want to reduce the refresh time
> but DB will still remain the same and moreover old partitions are purged
> and so might not provide many benefits.
>
>
>
> I really love your Tuning/Optimization BLOGs entries.
>
>
>
> TIA
>
> Sanjay
>
>
>
> On Monday, February 24, 2020, 05:24:13 PM EST, Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
>
>
>
>
> A fairly standard strategy when the bulk of the database is in time-based
> partitions - which seems possible in your case - is to have time-related
> tablespaces and make "older" tablespaces read-only as soon as the data they
> contain stops changing. Then you can use transportable tablespaces to get
> the old tablespaces to the test system as a one-off exercise and only use a
> backup/recover type of strategy for the most recent data.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
>
>
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org>
> Sent: 24 February 2020 22:14
> To: Oracle-L Freelists
> Subject: VLDB Refresh
>
> Do we have any better way to refresh Test with Production when the
> database is very big like close to 100Tb? The requirement is not the
> timing which anyway using Tape backup will take day or more based on Tape
> library But looking for the following points
> 1. Only One Application Schema in the database using Bigfile Tablespace
> 2. Contains multiple Table with LOBs Partitioned
> 3. All Big tables are monthly Partitioned with 2 years of Data in all
>
> So now looking to refresh Test with only last 3-6 months of data using a
> monthly partition. It cannot use Datapump not only due to several Terabytes
> but also cannot put any load on the critical Production environment. Does
> Oracle provide such features using any backup technologies including Oracle
> ZDLRA or any new features that can help to not only refresh the Test
> environment but also take lesser storage?
>
> TIA
> Sanjay
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version. Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by Vontobel
> Group. Unless stated to the contrary and without prejudice to any
> contractual agreements between you and Vontobel Group which shall prevail
> in any case, e-mail-communication is for informational purposes only and is
> not intended as an offer or solicitation for the purchase or sale of any
> financial instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>

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