Inside Oracle dbms_flashback
The System Change Number is captured from
the packaged function
dbms_flashback.get_system_change_number.
Get the current SCN at the source database.
Next, use the Oracle dbms_flashback package to get
the current SCN. This value will be used
during instantiation at the destination
site, as well as by rman when duplicating
the database.
SET
SERVEROUTPUT ON
DECLARE
until_scn NUMBER;
BEGIN
until_scn:=
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
Before
Oracle dbms_flashback Query functionality can be used by
ordinary users, some actions are required
from the database administrators:
Alter system set UNDO_MANAGEMENT=AUTO;
Alter system set UNDO_RETENTION=86400;
Grant execute on DBMS_FLASHBACK to username;
Oracle
dbms_flashback Query is enabled and disabled
using the DBMS_FLASHBACK package. The point
in time of the flashback can be specified
using the SCN or the actual time.
EXECUTE dbms_Flashback.Enable_At_Time('28-AUG-02
11:00:00');
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.
|
and include the URL for the page.
