Re: oracle total recall
From: Ilmar Kerm <ilmar.kerm_at_gmail.com>
Date: Tue, 3 Apr 2012 16:54:45 +0300
Message-ID: <CAKnHwtdL7Z9hYiT=f=ONkcsRK+nNAai9pnKh7C1VwkorvcfhLA_at_mail.gmail.com>
On Tue, Apr 3, 2012 at 4:11 PM, Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> wrote:
> W dniu 03.04.2012 14:32, Tim Hall pisze:
>> Hi.
>>
>> I agree with Ilmar, your post does sound a little confusing because
>> you do sound like you are talking about flashback database. :)
>>
>> Ignoring that, what it sounds like you are saying is the more changes
>> you make to the table, the longer a flashback operation takes to
>> complete.
>>
>> Well, that's kinda obvious since the way the undo-based flashback
>> operations work is to use the undo in the undo tablespace (and
>> flashback data archive if present and required) to build up a specific
>> point in time representation of the data. In the case of flashback
>> table, this representation of the data is used to alter the table
>> data, effectively recovering to the previous state. This is a
>> transactional operation though, so the next flashback operation you
>> try will have more data to wade through to get your result. The same
>> way, flashing back 1 minute will be quicker than flashing back 1 week,
>> since the amount of undo processing will be vastly less (assuming even
>> load over time etc.)
>
> I understand this is a transactional operation - that is why I wrote
> about points logically equal to some starting A point, and I understand
> now this was misleading. It is a little hard to wrote about returning
> back when really the database goes always ahead.
>
> So may be more about the implementation.
> We have an application on a relatively large db. This application is not
> connected to other apps on this db, and we would like from time to time
> get a data equal to some time point in the past only for that
> application ie. some number of tables. Those tables are loaded in
> batches, so we may assume that let's say at 8:17:00 today this app was
> logically consistent and if we flashback all tables to scn near 8:17, we
> get what we look for (even if it will not be the same scn, though of
> course it is the simplest to flashback to the same scn).
>
> At 8:20 we do some processing (which changes data) and finish after 30
> minutes. And then we want the data state on 8:17, so we want to apply
> undo statements.
> We use "FLASHBACK TABLE" on flashback-archive-enabled tables - will this
> statement use the UNDO from UNDO tablespace or rather from flashback
> archive tablespace?
> Anyway this is a concept - it is not about getting the database as if we
> never do this processing.
>
> And my initial problem is this:
> - today around 10:30am I purged flashback archives
> - then I run a processing - it went wrong, changing only one table and
> finishing after 1 minute or so (it seems so based upon a 10046 trace
> content)
> - I started flashbacking tables and this goes until now (around 3pm)
> - nobody is working on that database except me (and possibly some
> internal oracle jobs)
>
> Assuming I may use "flashback table" here lying upon flashback archives
> and even if I know that this flashback is transactional in its nature
> and undo is recognized one by one block at a time (which means db file
> sequential read) I wonder why it takes few hours to apply undo changes
> when almost nothing changed since the SCN I flashback to.
Date: Tue, 3 Apr 2012 16:54:45 +0300
Message-ID: <CAKnHwtdL7Z9hYiT=f=ONkcsRK+nNAai9pnKh7C1VwkorvcfhLA_at_mail.gmail.com>
On Tue, Apr 3, 2012 at 4:11 PM, Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> wrote:
> W dniu 03.04.2012 14:32, Tim Hall pisze:
>> Hi.
>>
>> I agree with Ilmar, your post does sound a little confusing because
>> you do sound like you are talking about flashback database. :)
>>
>> Ignoring that, what it sounds like you are saying is the more changes
>> you make to the table, the longer a flashback operation takes to
>> complete.
>>
>> Well, that's kinda obvious since the way the undo-based flashback
>> operations work is to use the undo in the undo tablespace (and
>> flashback data archive if present and required) to build up a specific
>> point in time representation of the data. In the case of flashback
>> table, this representation of the data is used to alter the table
>> data, effectively recovering to the previous state. This is a
>> transactional operation though, so the next flashback operation you
>> try will have more data to wade through to get your result. The same
>> way, flashing back 1 minute will be quicker than flashing back 1 week,
>> since the amount of undo processing will be vastly less (assuming even
>> load over time etc.)
>
> I understand this is a transactional operation - that is why I wrote
> about points logically equal to some starting A point, and I understand
> now this was misleading. It is a little hard to wrote about returning
> back when really the database goes always ahead.
>
> So may be more about the implementation.
> We have an application on a relatively large db. This application is not
> connected to other apps on this db, and we would like from time to time
> get a data equal to some time point in the past only for that
> application ie. some number of tables. Those tables are loaded in
> batches, so we may assume that let's say at 8:17:00 today this app was
> logically consistent and if we flashback all tables to scn near 8:17, we
> get what we look for (even if it will not be the same scn, though of
> course it is the simplest to flashback to the same scn).
>
> At 8:20 we do some processing (which changes data) and finish after 30
> minutes. And then we want the data state on 8:17, so we want to apply
> undo statements.
> We use "FLASHBACK TABLE" on flashback-archive-enabled tables - will this
> statement use the UNDO from UNDO tablespace or rather from flashback
> archive tablespace?
> Anyway this is a concept - it is not about getting the database as if we
> never do this processing.
>
> And my initial problem is this:
> - today around 10:30am I purged flashback archives
> - then I run a processing - it went wrong, changing only one table and
> finishing after 1 minute or so (it seems so based upon a 10046 trace
> content)
> - I started flashbacking tables and this goes until now (around 3pm)
> - nobody is working on that database except me (and possibly some
> internal oracle jobs)
>
> Assuming I may use "flashback table" here lying upon flashback archives
> and even if I know that this flashback is transactional in its nature
> and undo is recognized one by one block at a time (which means db file
> sequential read) I wonder why it takes few hours to apply undo changes
> when almost nothing changed since the SCN I flashback to.
If flashback table is too slow for this, maybe you could try Workspace Manager? http://docs.oracle.com/cd/B19306_01/appdev.102/b14253/long_intro.htm
It gives you hierarchical workspaces to make your data changes. So when you start a new batch you create a new child workspace, if batch is successful, then you can merge workspaces (or delete workspaces?). I have not used it in Oracle, but would be very interested in hearing about performance tests and experiences about it. I know that SmallworldDB uses similar "long transactions" a lot.
-- Ilmar Kerm -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 03 2012 - 08:54:45 CDT