Re: Anyway to estimate how much space flashback logs space will be needed in 11.2.0.4?
Date: Fri, 7 Jul 2017 17:06:30 +0000
Message-ID: <MMXP123MB1037D4884DDC5BB28A02AF9AA5AA0_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>
I'm not convinced any of those documents are particularly helpful.
The volume of flashback log required is essentially dictated by the number of times a block has to be read from disc to be changed - with an additional load for cases where a block is constantly subject to change and stays in memory for a very long time, in which case it is written to the flashback log once every 30 minutes (by default)
This means if you keep changing the same small set of blocks then the volume of flashback generated could be very small but if you update a large number of blocks just once each then your flashback log will hold the previous version of every block. If you update every block in a table with one statement, then go back and update every block with a second statement, and then a third statement (and the table doesn't stay cached the whole time) then the flashback log will be three times the size of the table. You have to think very carefully about how to do the maintenance - try to engineer it so that each step works with tightly focused, constantly cached areas of the database.
A couple of tips:
get rid of as many indexes as you think feasible before you start the job - you don't want to generate flashback for indexes if you can avoid it
create and switch into a new undo tablespace before you enable flashback - this avoids "physical read for flashback new" (and writes to flashback log) for your initial undo usage
create new tablespaces (or shrink to a minimum then resize existing tablespaces) if you are going to create or move objects - again this avoids "physical reads for flashback new", this time for the target tablespace.
when you get to the bit where you rebuild indexes - remember you should have created new empty tablespace for them - again to avoid ...
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Andy Klock <andy_at_oracledepot.com> Sent: 07 July 2017 14:16:31
To: oracledbaquestions_at_gmail.com
Cc: ORACLE-L
Subject: Re: Anyway to estimate how much space flashback logs space will be needed in 11.2.0.4?
On Thu, Jul 6, 2017 at 12:52 PM, Dba DBA <oracledbaquestions_at_gmail.com<mailto:oracledbaquestions_at_gmail.com>> wrote: If I make a guaranteed restore point for a specific point in time. If the DB generates archivelog, is there a way to estimate how much space will be needed? We have limited space and may need to backout a build coming. The build includes a data migration. One option is to take a backout and restore from backup. The other options is a guaranteed restore point. The migration scripts are done by a whole other group. I have no idea how much archivelog will be generated.
From my notes, see:
How To Calculate the Size of the Generated Flashback Logs (Doc ID 761126.1) http://blog.oracle-ninja.com/wp-content/uploads/2012/05/13445-pedregalcolvin-514442.pdf https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV71000
Thanks,
Andy K
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 07 2017 - 19:06:30 CEST