Re: oracle total recall
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Tue, 3 Apr 2012 15:54:39 +0200
Message-ID: <4F7B011F.5090601_at_nordea.com>
I just looked at the events generated by a sesion with flashback table. It seems as if it went through a table and check the undo data for every row in it. Is it really so?
This would explain the slowness of it - some tables are historical logs, so rather large.
>
> 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.
>
> Regards
> Remigiusz
>
Date: Tue, 3 Apr 2012 15:54:39 +0200
Message-ID: <4F7B011F.5090601_at_nordea.com>
I just looked at the events generated by a sesion with flashback table. It seems as if it went through a table and check the undo data for every row in it. Is it really so?
This would explain the slowness of it - some tables are historical logs, so rather large.
I would expect the flashback operation to go only through UNDO data (from undo tablespace or flashback archive, whatever) and apply undo statements from blocks after SCN I flashback to to tables without checking every row.
Regards
Remigiusz
W dniu 03.04.2012 15:11, Remigiusz Sokolowski pisze:
> 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.
>
> Regards
> Remigiusz
>
-- Pole nakazi ---------------------------------------------------------------------- Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> pos : DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77 Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia, wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000021828, dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku, VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych, NIP: 586-000-78-20, REGON: 190024711-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 03 2012 - 08:54:39 CDT