Flashback – Recover without Recovery

Introduction

You may know the situation, your databases are running fine, the backups are done regularly and without any issues. But there is one fact that brakes the whole thing, other humans called “users”. And a special kind of “users”, the “developers”. Sometimes they just do things by accident which lead to incorrect updates or deletes. The administrators take care of the physical structure of a database. But that does not protect us from human errors leading to data inconsistencies. That means in turn that recovering from these human errors is time-intensive. Ok, there are enhancements in 12c like the RMAN “recover table” command, but that is a different topic.

Flashback – An Overview

Oracle uses “FlashbacK” for a couple of techniques (SE = Standard Edition, EE = Enterprise Edition):

  • SE: Flashback Query – using UNDO data
  • EE: Flashback Version Query – using UNDO data
  • EE: Flashback Transaction – using Redo data (Logminer)
  • SE: Flashback Drop – using Recyclebin
  • EE: Flashback Database – using Flashback Logs
  • SE: Flashback Data Archive – using special data archives

The interresting thing is, that some powerful features come for free even with the Standard Edition of the database. Even Flashback Data Archive (without history table optimization) is included in all editions starting with 11.2.0.4.

Preparations

In order to use all the UNDO related features we need to prepare some things. With the default configuration the datafiles of UNDO tablespaces are created with “autoexend on” and the UNDO_RETENTION parameter is set to 900 seconds. That means Oracle will keep only these 15 minutes and overwrite older commited data even if there might be enough space available. But if we configure the UNDO datafiles to have a fixed size, Oracle ignores the value of UNDO_RETENTION and starts using all the space that is available and keeps commited transactions as long as there is space left in UNDO. That is visible in V$UNDOSTAT:

SQL> select * from (
  2  select TUNED_UNDORETENTION
  3  from   V$UNDOSTAT
  4  order by END_TIME desc
  5  )
  6  where ROWNUM = 1;

TUNED_UNDORETENTION
-------------------
              36356

Flashback Query

This feature utilizes the read consistency that Oracle provides. It just does not use the current SCN, it uses a SCN from the past. You simply use the “select … from … as of …” syntax the query data as it was at the given point in time.

SQL> create table emp_old
  2  as 
  3  select * 
  4  from emp as of timestamp 
  5  to_timestamp(‘2015-06-14 11:00:00’, 
  6  ‘yyyy-mm-yy hh24:mi:ss’);

If this ends up with an “ORA-1555: snapshot too old”, then it the undo data has already been overwritten. But what can we do with this data? We should materialize it so that we no longer rely on UNDO.

SQL> create table emp_old
  2  as 
  3  select * 
  4  from emp as of timestamp 
  5  to_timestamp(‘2015-06-14 11:00:00’, 
  6  ‘yyyy-mm-yy hh24:mi:ss’);

Now we check for differences:

SQL> select * from emp_old
  2  minus
  3  select * from emp;


Oracle allows us to use the "as of"-clause everywhere and mix it with current data, so we could also use it like this:


SQL> select * from emp as of timestamp
  2                to_timestamp('2015-06-14 11:00:00',
  3                'yyyy-mm-dd hh24:mi:ss')
  4  minus
  5  select * from emp;


But again, this only works as long as the UNDO information is not being overwritten.

Now, assume that some records were deleted by accident. We can re-insert these deleted records easily:


SQL> insert into emp
  2  select * from emp as of timestamp
  3                to_timestamp('2015-06-14 11:00:00',
  4                'yyyy-mm-dd hh24:mi:ss')
  5  minus
  6  select * from emp;

SQL> commit;

Or, if some records were modified unintentionally, we can revert these changes:

SQL> update emp e_live
  2  set sal = (select sal
  3             from emp as of timestamp
  4                      to_timestamp('2015-06-14 11:00:00',
  5                      'yyyy-mm-dd hh24:mi:ss') e_orig
  6             where e_orig.empno = e_live.empno
  7            )
  8  ;

SQL> commit;

These are only simple examples to outline the possibilities provided by flashback query. You may use all flavors of SQL DML to cope with your issue.

Flashback Export

A special use case for Flashback Query is Flashback Export. The legacy "exp" as well as the Datapump Export utility can create dumps for a given point in time. That is most useful if there are dependecies between tables that cannot be handled easily. We create an export representing the data from the past and import the data into another database or schema in the same database and give the user/developer access to it.

$ expdp system/Oracle-1 dumpfile=scott_flashback.dmpdp  \ 
        logfile=scott_flashback.expdp.log \
        directory=data_pump_dir \
        flashback_time='2015-06-14-11:00:00' schemas=scott
$ exp system/Oracle-1 file=/tmp/scott_flashback.dmp \
      log=/tmp/scott_flashback.exp.log \
      flashback_time='2015-06-14-11:00:00' owner=scott


<h3>Flashback Drop</h3>
What can we do if a table got dropped by accident? It is time for the Recyclebin. 


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME                OBJ. TYPE  DROP TIME
------------- ------------------------------ ---------- -------------------
EMP           BIN$yxLVQQOqC6rgQBAKtBUKlA==$0 TABLE      2015-06-14:13:29:19

Were indexes defined on that table? To see that we need to query the *_recyclebin views:

SQL> select ORIGINAL_NAME, OBJECT_NAME, OPERATION, DROPTIME 
  2  from USER_RECYCLEBIN;

ORIGINAL_NAME OBJECT_NAME                    OPERATION DROPTIME
------------- ------------------------------ --------- -------------------
EMP           BIN$yxLVQQOqC6rgQBAKtBUKlA==$0 DROP      2015-06-14:13:29:19
PK_EMP        BIN$yxLVQQOpC6rgQBAKtBUKlA==$0 DROP      2015-06-14:13:29:19
IX_ENAME      BIN$yxLVQQOoC6rgQBAKtBUKlA==$0 DROP      2015-06-14:13:29:19
IX_MGR        BIN$yxLVQQOnC6rgQBAKtBUKlA==$0 DROP      2015-06-14:13:29:19


Let's restore the table:


SQL> flashback table emp to before drop;

Flashback done.

SQL> select ORIGINAL_NAME, OBJECT_NAME, OPERATION, DROPTIME 
  2  from USER_RECYCLEBIN;

No rows selected.

Ok, no more indexes in the recyclebin. Let's crosscheck that:

SQL> select index_name from user_indexes
  2  where table_name='EMP';

INDEX_NAME
------------------------------
BIN$yxLVQQOoC6rgQBAKtBUKlA==$0
BIN$yxLVQQOnC6rgQBAKtBUKlA==$0
BIN$yxLVQQOpC6rgQBAKtBUKlA==$0

The index names were not restored to the original ones. We need to rename the indexes on our own, good that we queried the names before:

SQL> alter index "BIN$yxLVQQOoC6rgQBAKtBUKlA==$0" rename to IX_ENAME;

Note that we use double-quotes for the recyclebin-name since it has some special characters and mixed upper/lowercase in it.

And also note, that foreign key constraints are not restored. We need to re-create them manually.

Flashback Version Query

This is an Enterprise Edition feature, so be sure to be licensed properly before you use it. Flashback Version Query provides some pseudo columns and a special from-clause to show the evolution of rows over time. Let's see what happened to Mr. Millers salary:

SQL> select
  2    versions_starttime, versions_endtime,
  3    versions_operation, versions_xid,
  4    sal
  5  from
  6    emp versions between timestamp
  7        systimestamp - interval '1' hour and
  8        systimestamp
  9  where
 10    ename = 'MILLER';

VERSIONS_STARTTIME VERSIONS_ENDTIME   V VERSIONS_XID           SAL
------------------ ------------------ - ---------------- ---------
14.06.15 14:35:41                     U 07000B00F7480000      1800
14.06.15 14:29:29  14.06.15 14:35:41  U                       1300
                   14.06.15 14:29:29                       1504,91

We see two updates of the row and the timestamps when to change took place. Insert and Deletes can also be queried using this technique as long as you know what to look for:

SQL> select
  2    versions_starttime, versions_endtime,
  3    versions_operation,
  4    sal
  5  from
  6    emp versions between timestamp
  7        systimestamp - interval '1' hour and
  8        systimestamp
  9  where
 10    ename = 'FOO';

VERSIONS_STARTTIME VERSIONS_ENDTIME   V        SAL
------------------ ------------------ - ----------
14.06.15 15:30:05                     D       5046
14.06.15 14:57:08  14.06.15 15:30:05  U       5046
14.06.15 14:45:05  14.06.15 14:57:08  I       4711

We see the insert followed by an update and at the end the delete. Nice.

Conclusion

The flashback features offer a nice and easy way to recover from human errors causing unintended changes to the data. My advice is to keep UNDO as long as possible, sometime you will appreciate it. Your users or developers do not ask for help immediately. They try on their own to recover the data and only if they are not successful they come and ask. That may be hours or even days after the change happened....

Advertisements