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

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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