SEO

Hi all,
is there a way to undo changes in a database (e.g. those made because of
a recent human error, an erroneous delete/update query)

For example reversing all transactions made in the last N days?

On any database? In particular I am most interested in postgres and
mysql... secondarily Oracle.

Thank you

On Jan 23, 11:51 am, kunt <k...@no.net> wrote:
> Hi all,
> is there a way to undo changes in a database (e.g. those made because of
> a recent human error, an erroneous delete/update query)
>
> For example reversing all transactions made in the last N days?
>
> On any database? In particular I am most interested in postgres and
> mysql... secondarily Oracle.
>
> Thank you

You can do "rollback" in Oracle I know. I think in MySQL as well if
the table is InnoDB I believe. This will only allow you to do so if a
"commit" has not been issued after the statement. It would typically
apply if you did something like this from a command line....

> DELETE * FROM tableName;

oh shit...

> ROLLBACK

If you're looking for data that's days old, I think you'll need a
backup strategy in place or redundant tables...

D.

kunt wrote:
> Hi all,
> is there a way to undo changes in a database (e.g. those made because of
> a recent human error, an erroneous delete/update query)
>
> For example reversing all transactions made in the last N days?
>
> On any database? In particular I am most interested in postgres and
> mysql... secondarily Oracle.
>
> Thank you

delete from xyz;
rollback;
and there cannot be an autocommit (if so you are hosed) -- if not, the
rollback.

If it is recent - as in an hour ago -- it is called RESTORE FROM BACKUP

There is no "recycle bin" in Mysql.


Oracle has a "flashback area" not sure how extensive it can be, but no
other DB has that level of recovery.

Oracle also has logminer which allows you to restore from backup up to
the point of "doing something stupid" and then extract all other
transactions except the one that caused your problem.

On Jan 23, 9:45 pm, Michael Austin <maus...@firstdbasource.com> wrote:
[...]
> Oracle has a "flashback area" not sure how extensive it can be, but no
> other DB has that level of recovery.
>

Really? What's wrong with Recovery Expert for DB2?

/Lennart

kunt wrote on 23.01.2008 18:51:
> Hi all,
> is there a way to undo changes in a database (e.g. those made because of
> a recent human error, an erroneous delete/update query)
>
> For example reversing all transactions made in the last N days?
>
> On any database? In particular I am most interested in postgres and
> mysql... secondarily Oracle.
>

Oracle comes with a "Flashback" Query where you can query the
data as of xxx minutes in the past.

How far you can travel into the past depends on the system configuration and the
activity on the table.

Thomas

One way I use:

You can always create a datetime stamp on all your database tables to
know when a record was created/updated/deleted.

If you have a status column to indicate Live/Archive or in some cases I
use a value 0 = Live, 9 = Very old with each delete incrementing the
status value with value + 1. If value is 9 the record can be removed
from the table. Depending your User Permissions you are allowed to
view/update archived records then.

Create triggers on your table:

DELETE TRIGGER that will trap the delete and only do a UPDATE on the
Status column.

UPDATE TRIGGER trap the Update, INSERT a record in database with old
values but with Status of "Archive", then do the update.

Only downside to this is that you need to do more work on keeping your
relationships in place and a performance penalty in high volume
transaction databases.

To rollback then is just a matter of deleting records created with a
datetime stamp > supplied date and change status flag of last row found
with datetime stamp < supplied date.

kunt wrote:
> Hi all,
> is there a way to undo changes in a database (e.g. those made because of
> a recent human error, an erroneous delete/update query)
>
> For example reversing all transactions made in the last N days?
>
> On any database? In particular I am most interested in postgres and
> mysql... secondarily Oracle.
>
> Thank you

Johan Nel wrote:

I also suggest on this subject

http://www.amazon.com/Developing-Tim.../dp/1558604367

> http://www.amazon.com/Developing-Time-Oriented-Database-Applications-...<

This book is out of print, but can be downloaded for free from the
author's web site:

http://www.cs.arizona.edu/~rts/publications.html

brgds

Philipp POst