Face it, we’ve all been there – that moment when we’ve done something a wee bit too fast and then realize we probably shouldn’t have done it. Then we often generally say something like “stupid database” or “stupid TOAD’ or “stupid whatever. But there are ways to recover without having to go hat in hand to the DBA and beg forgiveness and obtain a recovery. More recent versions of Oracle offer several wonderful “Flashback” options that can often save the day. I’ve included a summary of all those options with an excerpt from my book “Advanced Oracle Utilities: The Definitive Reference”.

 

DBMS_FLASHBACK

 

At the beginning of this chapter when discussing 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 we just examined doing restructures where a backup or before image might be handy in case of problems 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. And few applications are generally worthy of their own backup and recovery strategy using the physical tools to build logical or application based restore points.

 

But 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 you application code such that you could rollback to an application based logical point in time. This was a useful technique, but never really saw extensive usage. But it was a good concept nonetheless – if it only it would have extended to database objects and/or even the database level itself. Well now it does – we have Oracle’s flashback technology.

 

Oracle flashback technology essentially lets you create SAVEPOINT like bookmarks to restore to for either objects or the entire database. In some respects it’s 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’s sure to see heavy usage as time goes on. It’s truly a key new must-have tool for the DBA’s tool belt.

 

There are six flashback technologies (in chronological order of their appearance) whose topics we’ll examine in more detail:

 

  • Flashback Queries (9i)
  • Flashback Drop (10g)
  • Flashback Tables
  • Flashback Database
  • Flashback Transaction (11g)
  • Flashback Archives (i.e. Oracle Total Recall)

 

Furthermore, you’ll see that unlike other features discussed in this chapter – you’ll want 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 thus to perceive that each step was built on the foundations of those prior.

 

Flashback Queries

 

Oracle 9i introduced the concept of the flashback query. I like to call this the “Back to the Future” or time machine type query – where Oracle lets us 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 that 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 (not guaranteed). You can even force that availability via the UNDO tablespace RETENTION GUARANTEE option.

 

In its simplest form, we 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 (i.e. thus 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

 

We also can enable the entire Oracle session to enter a “time tunnel” or “time warp” so that we don’t have to add anything at all to the SELECT command to see such historical data. In that case we 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.

 

Flashback Drop

 

Oracle 10g introduced the flashback drop concept – which is built off the new recycle bin in the database (think of it as much like the Windows recycle bin). Now when you drop an object, it’s moved first to the recycle bin (assuming that the recycle bin has been enabled via the RECYCLEBIN=ON init.ora parameter). The recycle bin simply retains the dropped objects under a new unique name.

 

SQL> drop table junk;

 

Table dropped.

 

SQL> select object_name, original_name, type from recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME                    TYPE

------------------------------ -------------------------------- ---------------

BIN$oP2i2G1STvita2AHhmFdVw==$0 JUNK                             TABLE

 

Then when we want to un-drop a table, the process is very easy – we simply use the FLASHBACK TABLE command to restore the table as it was before the drop. When the table flashback is invoked, not only are the table and its data brought back – but so are any dependent objects and grants. It’s that simple.

 

SQL> select * from junk;

 

        C1         C2

---------- ----------

1          2

 

SQL> drop table junk;

 

Table dropped.

 

SQL> select * from junk;

select * from junk

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> flashback table junk to before drop;

 

Flashback complete.

 

SQL> select * from junk;

 

        C1         C2

---------- ----------

         1          2

 

For those who prefer a graphical interface, OEM has a rather easy screen for doing object level complete recoveries (i.e. un-drop).

 

xyz1.png

Figure 4: OEM Object Level Recovery Screen

 

Prior to the flashback drop capability, the best method for doing object level recoveries was the logical backup method discussed earlier in this chapter (i.e. export/import). But handling referential integrity and dependencies was a problematic manual effort requiring significant investment to get 100% right.

 

Flashback Tables

 

The next logical step in the flashback progression is to do more than to undo a simple table drop – but rather to permit the table to return to its prior state as if making a flashback query permanent. Once again we have yet another key new requirement for this latest flashback feature to work – tables must have row movement enabled (feature that permits Oracle to change the ROWID of a row, otherwise they are usually immutable). Now we can use the prior section’s flashback syntax to return a table to some prior specified time or system change number – as shown here.

 

SQL> create table junk (c1 int, c2 int) enable row movement;

 

Table created.

 

SQL> insert into junk values (1,2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  362096

 

SQL> insert into junk values (3,4);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> flashback table junk to scn 362096;

 

Flashback complete.

 

SQL> select * from junk;

 

        C1         C2

---------- ----------

         1          2

 

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time table recoveries (i.e. flashbacks).

 

xyz2.png

Figure 5: OEM Table Screen

 

xyz3.png

Figure 6: OEM Table Flashback Screen

 

Flashback Database

 

Continuing with our flashback technology progression, the next big step is to permit one to flashback an entire database. In essence we now want to perform a point in time recovery at the database level – and all very simply and without actually doing anything in RMAN. Once again we have yet another key new requirement for this latest flashback feature to work – the database must be running in ARCHIVELOG mode. Plus, we can only flashback to whatever online redo logs are immediately available via disk – any further back in time requires a traditional point in time recovery via RMAN. But for many cases even that short time span may suffice as the first-level basic point-in-time recovery strategy. The next example may look quite a bit like the last for the flashback table, we now simply have to take the database to a mounted status and issue the FLASHBACK DATABSE command as shown. All that’s changed is the scope of the operation.

 

SQL> create table junk (c1 int, c2 int) enable row movement;

 

Table created.

 

SQL> insert into junk values (1,2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  365991

 

SQL> insert into junk values (3,4);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> connect sys/mgr as sysdba

 

Connected.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  627732480 bytes

Fixed Size                  1334996 bytes

Variable Size             398459180 bytes

Database Buffers          222298112 bytes

Redo Buffers                5640192 bytes

Database mounted.

SQL> flashback database to scn 365991;

 

Flashback complete.

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> connect bert/bert

 

Connected.

 

SQL> select * from junk;

 

        C1         C2

---------- ----------

         1          2

 

We’ve now wrapped up the flashback technology historical progression as of Oracle 10g release 2 – so now it’s onto even bigger and better flashback capabilities with Oracle 11g. However whereas the progression and granularities up until this point were probably fairly obvious, we’re now moving into less obvious but critically useful flashback techniques – hopefully with a decent background and understanding now to appreciate just how we got here.

 

Flashback Transaction

 

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and all its dependent transactions. As with all the other flashback technologies discussed so far, the transaction back out operation uses UNDO to create and execute the compensating or opposite transactions that return the affected data to its original state. So in some respects the granularity of this flashback operation is somewhere between our last two cases: table and database. We’re now flashing back a subset of the database that represents some logical collection of tables and queries. It essentially implements the prior mentioned SAVEPOINT concept in the database now rather than the application code. In fact, flashback transactions mostly eliminate the need for the next section on redo log file mining – as it’s now transparently and more easily done as shown here.

 

First, we query the FLASHBACK_TRANSACTION_QUERY view to see what transactions exist for whatever objects and/or users we think may have done something that need undone. This view can return a lot of information in even a mildly busy database, so filtering is highly recommended. Here I’ve asked to see what transactions have occurred in the past day by logon user BERT and on tables owned by BERT. Note that this view offers us the UNDO SQL code.

 

SQL> select xid, start_scn, operation, table_name, undo_sql from flashback_transaction_query where start_timestamp>=sysdate-1 and username='BERT' and table_owner='BERT';

XID               START_SCN OPERATION    TABLE_NAME

---------------- ---------- ------------ ------------

UNDO_SQL

--------------------------------------------------------------

0200030052030000     475697 DELETE       JUNK

insert into "BERT"."JUNK"("C1","C2") values ('5','6');

 

0200030052030000     475697 DELETE       JUNK

insert into "BERT"."JUNK"("C1","C2") values ('3','4');

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAD';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAC';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAB';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAA';

 

If I now want to simply undo the two delete commands (whose undo action to re-insert the data that was deleted), here’s the PL/SQL code for doing that.

 

SQL> select * from bert.junk;

 

        C1         C2

---------- ----------

         1          2

         7          8

 

SQL> declare

   trans_arr XID_ARRAY;

begin

   trans_arr := xid_array('0200030052030000','0200030052030000');

   dbms_flashback.transaction_backout (

        numtxns         => 1,

        xids            => trans_arr,

        options         => dbms_flashback.cascade

   );

end;

/

 

SQL> select * from bert.junk;

 

        C1         C2

---------- ----------

         1 2

         3          4

         5          6

         7          8

 

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time transaction recoveries (i.e. flashbacks).

 

xyz4.png

Figure 7: OEM Table Screen

 

xyz5.png

Figure 8: OEM Transaction Flashback Screen

 

Flashback Archives

 

The final piece of the puzzle in the flashback progression is the new Oracle 11g flashback archive. You define an area that provides the ability to automatically track and archive transactional data changes to specified database objects. These flashback archives become user named and somewhat then managed persistence of UNDO at the specified object level. So when you need to do a SELCT with an AS OF, you can rely on the object being in your chosen container for the specified duration and competing for space only with the objects you so choose. Thus it’s merely a named are to support all the prior flashback features we’ve just examined.

 

SQL> create tablespace flash_archive

datafile 'c:\oracle\oradata\ordb1\flash_archive.dbf' size 50M;

 

 

Tablespace created

 

SQL> create flashback archive default flash_archive tablespace flash_archive retention 30 day;

 

Flashback archive created.

 

SQL> create table bert.junk (c1 int, c2 int) flashback archive flash_archive;

 

Table created.