Oracle Flashback – A quick review / Cheat Sheet

Here you will find some quick reviews of the various Flashback technology in Oracle. Flashback Query, Flashback Transaction Query, Flashback Table, and Flashback Data Archive.

Flashback Query

Flashback Query allows you write a query that looks at the data as it appeared in the past. Flashback Query relies on the Oracle UNDO functionality that is what gives Oracle it’s best of breed Concurrency.
Writing a flashback query is very simple, just add the “as of …” clause. You can use either “as of timestamp” or “as of SCN”, with timestamp being the more likely option for general use.
Here is an example:

SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> create table fb_query (col1 number);
Table created.
SQL> insert into fb_query values (1);
1 row created.
SQL> insert into fb_query values (2);
1 row created.
SQL> insert into fb_query values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2010/02/16 13:13:17
SQL> insert into fb_query values (4);
1 row created.
SQL> insert into fb_query values (5);
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2010/02/16 13:14:08
SQL> select * from fb_query order by col1;
COL1
----------
1
2
3
4
5
SQL> select * from fb_query as of timestamp to_date('2010/02/16 13:13:20','yyyy/mm/dd hh24:mi:ss') order by col1;
COL1
----------
1
2
3
SQL> select * from fb_query order by col1;
COL1
----------
1
2
3
4
5

Flashback Transaction Query

Just like with Flashback Query, you can go back to a certain SCN or time.
As long as there is enough UNDO space, you can find DML statements on particular tables by querying the FLASHBACK_TRANSACTION_QUERY view.  Here are the columns in that view:

Name                                       Type
 ------------------------------------------ --------------------------------------
 XID                                        RAW(8)
 START_SCN                                  NUMBER
 START_TIMESTAMP                            DATE
 COMMIT_SCN                                 NUMBER
 COMMIT_TIMESTAMP                           DATE
 LOGON_USER                                 VARCHAR2(30)
 UNDO_CHANGE#                               NUMBER
 OPERATION                                  VARCHAR2(32)
 TABLE_NAME                                 VARCHAR2(256)
 TABLE_OWNER                                VARCHAR2(32)
 ROW_ID                                     VARCHAR2(19)
 UNDO_SQL                                   VARCHAR2(4000)

If the goal is to fix a user error (deleted rows, bad update, etc), then you need to find the START_SCN (or START_TIMESTAMP if you prefer) so that you can flashback to the appropriate time.
To do this, use this SQL statement:

col format start_scn 9999999999999999999
select start_scn, operation, logon_user, undo_sql from flashback_transaction_query;

Once you find the SQL and corresponding SCN, you can either run the SQL that is in the undo_sql column, or use flashback table to scn.

Flashback Table

Just like with Flashback Query, you can go back to a certain SCN or time.  This flashback technology uses UNDO space, so you may need to increase UNDO_RETENTION and the UNDO tablespace.
Login to SQLPlus and use the following commands:

alter table XXXX enable row movement;
 select count(1) from XXXX;
 col CURRENT_SCN format 99999999999999999
 SELECT CURRENT_SCN FROM V$DATABASE;
 delete / update / insert command.....
 FLASHBACK TABLE XXXX to scn #########;
 alter table XXXX disable row movement;

Flashback Data Archive

Oracle’s Flashback Data Archive (formerly known as Total Recall) is a long-term flashback technology that simply stores all of the undo data for an object in a tablespace.  To set it up, follow these steps:

create tablespace myts_fbda datafile '/oradata/MYDB/myts_fbda_01.dbf' size 10g autoextend off;
create flashback archive default fbda tablespace myts_fbda quota 10G retention 1 year;

Once the flashback area is created, then assign a table to it.  Here are some sample commands to enable or disable using a Data Archive tablespace:

CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10)) FLASHBACK ARCHIVE;
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10)) FLASHBACK ARCHIVE fbda;
ALTER TABLE employee FLASHBACK ARCHIVE;
ALTER TABLE employee FLASHBACK ARCHIVE fbda;
ALTER TABLE employee NO FLASHBACK ARCHIVE;

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:
*    ALTER TABLE statement that does any of the following:
*    Drops, renames, or modifies a column
*    Performs partition or subpartition operations
*    Converts a LONG column to a LOB column
*    Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
*    DROP TABLE statement
*    RENAME TABLE statement
*    TRUNCATE TABLE statement
Static Data Dictionary Views for Flashback Data Archive Files
View                         Description
*_FLASHBACK_ARCHIVE          Displays information about Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TS       Displays tablespaces of Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TABLES   Displays information about tables that are enabled for Data Flashback Archive files.

Dropping a Flashback Data Archive:

 drop flashback archive fbda;

One response to “Oracle Flashback – A quick review / Cheat Sheet

  1. Pingback: Oracle Flashback – A quick review | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s