Re: SCN and User Sessions...

jhy <jhy_at_earthling.net> wrote in article <35DA3733.1A564259_at_earthling.net>...
> Wouldn't point in time recovery be a little more appropriate to your
example?
>

Point-in-time is one option, but it could be a little imprecise....

Let's say the user realises their mistake and trys to call the DBA. The DBA is away from their desk. Ten minutes elapse before the DBA is notified. The DBA then asks the user at what time the error occured. The user didn't know to record to this information, so guesses at around 15 minutes....

We would want to save all transactions prior to the one that caused the problem. The recovery in this scenario could take forever. Recovering the backup and then rolling forward to an aproximate time, checking if the data is intact, rolling forward again until we can zero in on the offending transaction.

Likewise I can get an approximate SCN from the v$database view, but I could still be looking a few hundred SCNs for the one that caused the problem.

I think there must be a way to accurately restore the database to the very transaction before the one that caused the problem, but right now all I can think off is to create a trigger that records in a journal table the SCN and user-id for each update, just in case someone goofs.

Alternatively I can check v$log for the current log file, then force a log switch so I can copy the logfile to the PC. I can then use a binary browser to scan the file for the offending statement, and pull the SCN directly from the logfile. If anybody else is interested in trying this, note that on an Alpha system the SCN is stored in reverse hexadecimal - that is SCN 4471909 is stored in the log file as 0x653C44.

Surely there must be a better way than this?

Thanks again.

Graham. Received on Wed Aug 19 1998 - 09:19:36 CDT