Re: VLDB Refresh

From: Sanjay Mishra <"Sanjay>
Date: Tue, 3 Mar 2020 17:25:59 +0000 (UTC)
Message-ID: <>

 I did use the Exadata Snapshot feature with 12.1 with ASM Diskgroup with Sparse Disk setup but the issue was that it will be good if we are refreshing on a daily basis or need multiple environments. If I am correct that if I create the Snapshot then Standby cannot be refreshed or if Standby need to be refreshed, we need to drop the Snapshot and recreate after standby refresh along with the recreation of all User created earlier in Snapshot setup. I saw some good features in 18c but cannot be used in the existing 12.1 or 12.2 setups.  I will check some of the links shared for more details too. TxSanjay

    On Tuesday, February 25, 2020, 07:28:26 PM EST, Jack van Zanen <> wrote:  

Whilst that is great for functional testing and creating multiple copies with less storage, one of the questions he asked if it was possible to reduce space...If you only have one test copy you would still need a full standby copy as your master data copy so to speak... so you are not saving space until you are using it to create several copies...

Jack van Zanen

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

On Tue, Feb 25, 2020 at 7:23 PM Noveljic Nenad <> 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,





From: <>On Behalf Of Sent: Dienstag, 25. Februar 2020 08:50
Cc: Oracle-L Freelists <>; Jonathan Lewis <> Subject: Re: VLDB Refresh


The Oracle feature that matches your use case is called Exadata Storage Snapshots - the documentation is at and does require you to be on database 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 a helpful presentation as well.   


On Mon, Feb 24, 2020 at 10:55 PM Sanjay Mishra <> wrote:



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 <> 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.

Jonathan Lewis

From: <> on behalf of Sanjay Mishra <> 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?






Niall Litchfield
Oracle DBA

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   

-- Received on Tue Mar 03 2020 - 18:25:59 CET

Original text of this message