Re: oracle total recall
Date: Tue, 3 Apr 2012 15:11:47 +0200
Message-ID: <4F7AF713.5020004_at_nordea.com>
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:11:47 CDT