Manual flashback implementation

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 Tue Nov 19 2002 - 13:16:10 CST