At the beginning of this chapter, various use
case scenarios for the data pump export and
import were mentioned. Namely, that a DBA might
need the ability to restore collections of
tables to some pristine state on a regular
interval. In the prior section, creating
restructures were illustrated where a backup or
before image might be handy in case of problems
during mid-restructuring. The truth is that
there are many cases where the DBA would like
the ability to restore a logical portion of the
database back in time or to a prior SCN. But the
problem is that physical backups and restores
are very complex and operate more at the
physical database level. Few applications are
generally worthy of their own backup and
recovery strategy using the physical tools to
build logical or application based restore
points.
However, a long time ago Oracle introduced a
great concept in the SQL and PL/SQL languages
called the SAVEPOINT. This was the ability to
issue a database state bookmark within the
application code such that one could rollback to
an application based logical point in time. This
was a useful technique but never really saw
extensive usage. Nevertheless, it was a good
concept if only it would have extended to
database objects and/or even the database level
itself. Well, now it does and it is Oracle’s
flashback technology.
Oracle flashback technology essentially lets the
DBA create SAVEPOINT like bookmarks to restore
to either objects or the entire database. In
some respects, it is a great short term
point-in-time recover technique, rather than
going to a full blown backup and restore. Plus,
its usage has been made so integrated, seamless
and easy that it is sure to see heavy usage as
time goes on. It is truly a definite must-have
tool for the DBA’s tool belt.
There are six flashback technologies, in
chronological order of their appearance, whose
topics will be examined in more detail:
Furthermore, unlike other features covered in
this chapter, it will be beneficial to learn how
to utilize these various flashback technology
capabilities via OEM, SQL commands and the
PL/SQL API.
Note: Part
of the reason for covering all the flashback
technologies here, including a recap of
older ones, is to hopefully lead the reader
along the historical path of flashback
technology development and, therefore, to
perceive that each step was built on the
foundations of those prior.
Flashback Queries
Oracle 9i introduced the concept of the
flashback query. This can be called the “Back to
the Future” or time machine type query
where Oracle lets DBAs make some queries
in the present as if from a database state in
the not too distant past.
The black magic that makes this possible are
UNDO tablespaces and automatic UNDO management
and Oracle now treats those UNDO blocks as
first-rate data based upon the
undo_retention parameter. Using
these, Oracle does its best to retain UNDO data.
One can even force that availability via the
UNDO tablespace RETENTION GUARANTEE option.
In its simplest form, merely add an AS OF clause
to the SELECT statement to request the current
execution be performed as if it were run at some
prior specified time or system change number
like looking backwards in time.
SQL> select
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from
dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
353026
SQL> update
movies.customer set zip='99999';
62 rows updated.
SQL> commit;
Commit complete.
SQL> select firstname,
lastname, zip from movies.customer as of scn
353026 where rownum < 5;
FIRSTNAME
LASTNAME
ZIP
--------------------
------------------------------ -----
Flozell
Adams
75063
Troy
Aikman
75063
Larry
Allen
75063
Eric
Bjornson
75063
The entire Oracle session can also be enabled to
enter a “time tunnel” or “time warp” so that
nothing has to be added to the SELECT command to
see such historical data. In that case, simply
enable and disable the flashback effect as shown
here via the PL/SQL packages found in
dbms_flashback.
SQL> select firstname,
lastname, zip from movies.customer where
rownum < 5;
FIRSTNAME
LASTNAME
ZIP
--------------------
------------------------------ -----
Flozell
Adams
99999
Troy
Aikman
99999
Larry
Allen
99999
Eric
Bjornson
99999
SQL>
SQL> execute
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(353026)
PL/SQL procedure
successfully completed.
SQL>
SQL> select firstname,
lastname, zip from movies.customer where
rownum < 5;
FIRSTNAME
LASTNAME
ZIP
--------------------
------------------------------ -----
Flozell
Adams
75063
Troy
Aikman
75063
Larry
Allen
75063
Eric
Bjornson
75063
SQL> execute
DBMS_FLASHBACK.DISABLE
PL/SQL procedure
successfully completed.
