Re: Oracle Backward Recovery

David J DeWolfe <sxdjd_at_orca.alaska.edu> wrote:

>Hi all;
 

>I would presume the scenario the original poster was wondering about
>would be one where you would read backwards through the last few
>online/archive redo logs undoing transactions, as opposed to restoring the
>necessary datafiles and rolling forward through *many* archive log files. We
>have this capability with our IDMS databases running on our MVS machine, and

Hold on there. Surely you don't mean rolling back *commited* transactions (and don't call me "Shirley"). Oracle, like any other real-world SQL database (Sybase, Ingres, Informix, DB2, DMIV/TP (an old Honeywell GCOS product), etc.) does allow for rollback of *uncommited* transactions.

As long as you have not commited a series of transactions, whether explicitly via the "commit" command, or implicitly via a normal job termination, you can most certainly roll back to your last commit point.

For example, suppose I want to delete employ records for retired employees.

SQL> select count(*) from employ_record; 2375 records counted.

SQL> delete employ_record;
2375 records deleted.

SQL> select count(*) from employ_record; 0 records counted.

(Oops, I just deleted *everybody* from the company. Guess I better fix that.)

SQL> rollback;
Rollback completed.

SQL> delete employ_record where status='RETIRED'; 34 records deleted.

SQL> select count(*) from employ_record; 2341 records counted.

(My job is saved.)

Now if I panic, and instead of typing "rollback" I exit out of sqlplus, then I have just made an implicit commit, which now means the records are *really* gone. Which means I now must restore from the last backup, and if archivelogs are maintained, do a partial roll-forward up to my mistaken delete.

This same scenario applies to all other databases I've ever heard of.

>the few times we needed to roll backward, it was always fun to watch the
>tapes fast-forward to the EOT marker, and then start reading backwards
>through the journal (equivalent to oracle logs) entries.

Still working with 9-track tape? Hmmmm. As I recall, those old tape drives did indeed have the ability to read tapes backwards. The phone company used to do that with central office switch tapes, because it made toll charge calculations easier.

You really do mean rolling back commited transactions, don't you?

                --Bolen Coogler Received on Mon Aug 05 1996 - 00:00:00 CEST