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@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
