Re: VLDB Refresh
Date: Mon, 24 Feb 2020 22:53:18 +0000 (UTC)
Message-ID: <1884003211.1069004.1582584798454_at_mail.yahoo.com>
Jonathan
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
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
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
--
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
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
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
Sanjay
--
http://www.freelists.org/webpage/oracle-l
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 24 2020 - 23:53:18 CET