Re: Manual flashback implementation

Assuming you can implement with Oracle9i Release 2, take a look at Oracle Streams. Streams provides both an automated capture and apply mechanism. In addition to the default apply engine, you can customize the apply procedure to do just about anything you want. Oracle Streams is available in the Enterprise Edition of 9iR2 as an integrated feature. There is no additional charge for this feature. More information on Oracle Streams is available on OTN at http://otn.oracle.com/products/dataint/ This site also includes the URL for the Streams documentation and a worked demo.

Richard Kuhler wrote:

> I'm looking for design ideas to manually implement the equivalent of
> Oracle's flashback query. In other words, I need to be able to retrieve
> the data as it looked at any point in the past. Since I need a 100%
> guarantee that I can do this forever, I don't see how I can safely rely
> on Oracle's flashback mechanism.
> Here's what I've come up with...
>
> 1. Triggers on all base tables record all changes to a history table
> 2. Any time a commit occurs, the SCN and time of the commit must be
> recorded in my own table
> 3. All rows that were modified during that commit must have the SCN
> recorded in the history tables rows
>
> It's step 3 that I hate the most. This will result in lots of
> additional updates in the system. Actually, it's not even clear to me
> yet how I can implement steps 2 and 3 (is there some way to get this
> information from Oracle?). I suspect I'm going to have to create my own
> SCN mechanism and implement a special commit procedure for all processes
> that manipulate data. There's also a delay between steps 2 and 3 when
> queries would not be able to retrieve the data as of the current time
> (although a delay of as much as 24 hours would be acceptable). I have a
> feeling that it's probably possible to process the redo logs to build
> the history table information with the necessary SCN in one step but I'm
> not sure how best to proceed with that.
>
> Anybody have any ideas?
>
> Thanks,
> Richard Kuhler
Received on Sat Nov 23 2002 - 22:34:55 CST