Re: Flashback Recovery

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 24 Apr 2007 13:33:40 -0700
Message-ID: <1177446820.733870@bubbleator.drizzle.com>

Arthernan wrote:
> On Apr 23, 10:50 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> Arthernan wrote:
>>> On Apr 23, 5:39 pm, Arthernan <arther..._at_hotmail.com> wrote:
>>>> On Apr 16, 4:30 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>>>> NetComrade wrote:
>>>>>> 2 questions
>>>>>> a) can someone point to a source that descrives how theflashbackarea
>>>>>> actually works
>>>>>> b) did anyone ever had to 'flash back' a database in production? I
>>>>>> really can't think of a scenario where it would be useful (since it
>>>>>> forces you to lose
>>>>>> .......
>>>>>> We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
>>>>>> remove NSPAM to email
>>>>> FlashbackDatabase is one of the most useful and valuable features
>>>>> added in 10g.
>>>>> Very simply I use it with guaranteed restore points any time we are:
>>>>> 1. Patching applications
>>>>> 2. Patching the database
>>>>> 3. Batch loading
>>>>> Flashbackis not useful because you use it a lot any more than a
>>>>> fire extinguisher is valuable because you use it a lot. They are
>>>>> invaluable because they are available for use when required.
>>>>> As to how theflashbackarea works I would suggest that this is a
>>>>> topic too large for a single paragraph in a usenet group and that
>>>>> you read the docs athttp://tahiti.oracle.com.
>>>>> --
>>>>> Daniel A. Morgan
>>>>> University of Washington
>>>>> damor..._at_x.washington.edu
>>>>> (replace x with u to respond)
>>>>> Puget Sound Oracle Users Groupwww.psoug.org
>>>> I have been looking at adding flashback-like functionality to our
>>>> company system. I can see the benefit from a DBA perspective.
>>>> In our case I am most interested on giving this functionality to the
>>>> end users. But I am hesitant because it seems to require a great deal
>>>> of DB configuration and it makes the task of looking at history very
>>>> different. For example I did not see anywhere where you could do a
>>>> query like this.
>>>> Select Employee_name, Salary, DateAdded, DateRemoved from Employee
>>>> where DateAdded<point_in_time and (DateRemoved>point_in_time or
>>>> DateRemoved is null)
>>>> Now I don't pretend for flashback to work MY way. But for this to work
>>>> for my end users I would need to show them history at the record
>>>> level. So they can then pick a sinlge record/transaction and recreate
>>>> it they need it, or maybe just research changes already made.
>>>> Does anybody have experience using Oracle Flashback in a similar way?
>>> Before I get abused for not reading the manual, I'd like to say that I
>>> did see this
>>> Select Employee_name, Salary from Employee
>>> as of timestamp point_in_time;
>>> But this leaves out the columns DateAdded and DateRemoved. Whatever
>>> pseudocolumns that could exist with the time of the changes are very
>>> important for the end user research.
>>> Of course, the alternative is to use triggers of to improve the
>>> application logic, which is what I currently do. One benefit of
>>> writing your own flashback logic is that it is your own and possibly
>>> not dependent on oracle propietary features.
>> There is little evidence, from what you wrote, that you read the
>> manual. Your statement about "leaves out" is incorrect. Take a look
>> at the Flashback Transaction Query demo in Morgan's Library atwww.psoug.org.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> Thank you, that did help. There are just tooo many manuals. I searched
> for the ROWDEPENDENCIES keyword and finally the right manual came up.
> "Application Developers Guide - Fundamentals" I just did not expect to
> find it there.
>
> So a query similar to what I wrote would look like this
>
> Select Employee_name, Salary, version_starttime, version_endtime from
> Employee versions
>
> Having gone throught some database redesign already, and gone to an
> almost insert only database design. I'm not sure that flashback would
> be useful for our case.
>
> I want to make a distincion bewteen historical data and error data. It
> is different to change a ph# because there was a typo, than if that p#
> truly belonged to the individual and it has been changed. So this
> second type of historical changes would still need to be addressed
> within the database structure, and still benefit from an insert only
> database approach.
>
> There are other database admin considerations that could make
> Flashback useful for us, but I can't justify it at the application
> level. Having said that I would love to hear other opinions on this.

If you are subject to any audit requirement like Sarbanes-Oxley, etc. you can not make a distinction between one reason for changing a value and another ... nor should you. Who is to say that something was a typo rather than a change? And what does that say about the integrity of your data.

If you are going to audit ... audit.

I would be interested in knowing what you consider when you say "other database admin consierations." It isn't at all clear.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Apr 24 2007 - 15:33:40 CDT