In the first in this series of articles on the tools in the
Oracle Flashback Technology armoury, I discussed what is available to
application developers, the various means by which they can rewind the clock
and correct recently-past data errors.
However, if this was a world in which the worst thing that could happen
was a small mix-up with data, what a happy world it would be. However, this is
not Disneyland, and data errors are not the big, bad wolf at the door; it is
errors that raze whole database objects that database administrators fear. But
fortunately, Oracle Flashback can come to our rescue in those situations too.
First a quick recap: Oracle Flashback Technology is actually
a clutch of different Oracle tools that enable developers and administrators
reach into the immediate past of their database to recover from errors, without
having to resort to point-in-time recovery options. For this reason, they are
relatively fast and incur a relatively low logistical overhead (since the
database does not need to suffer any downtime to implement them).
FLASHBACK TABLE:
Flashback table enables you revert a table to its state at a
specified timestamp or SCN. Any rows of data created after the parameterized
timestamp disappear and any deleted rows zombie back to existence; in addition,
associated attributes such as triggers, indexes and constraints remain
unviolated. And all this magic occurs without even the slightest blip of
interference affecting the rest of the database; it stays up and available.
Flashback table is able to sidestep the unpleasantness of
restoring from backups by using information in the undo tablespace to restore
the table.
Imagine this scenario: some catastrophe has befallen the EMP
table and the developers are unable to unpick the data. All eyes turn to you:
Save us, they beseech, wave your wand and erase the past hour. This is where
you would find Flashback Table useful:
FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;
And it’s done. Day saved. Simple.
To use flashback table, however, there are a few prerequisites
that need to be in place:
- You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.
- You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.
- Row movement must be enabled on the table.
- Finally - and crucially - the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.
No comments:
Post a Comment