Flashback in Oracle 10g
This feature was first introduced in Oracle 9i and it had some limitations. With 10g and now 11g it has many more features helpful in my ways. Flashback helps us in point in recovery, to see past data, to see past data transaction wise, to restore accidently dropped table and to restore database itself up to a certain SCN or Timestamp. Restoring database is beyond the coverage of this blog. J
The base of flash back is UNDO information. What UNDO does is it keeps the before change image of data and keeps it until due to some parameters setting it is overwritten by another transaction. We also need FLASH_RECOVERY_AREA configured for Database.
FRA stores files related to RMAN backups. Optionally we can use it to store archived logs. Oracle itself manages space in this. If required Oracle drops the unnecessary files from directory. Parameters to considers for configuration of FAST_RECOVERY_AREA are as follows
DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST
DB_FLASHBACK_RETENTION_TARGET
The available options we have to use Flashback features are as follows:
Flash Back query
:- helps us to see contents of table with reference to a specific timestamp or SCN. Lets check this out.
SQL> create table FB_Qry_Tab (id1 number);
Table created.
1* SELECT current_scn SCN, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')time FROM v$database
SQL> /
CURRENT_SCN TIME
----------- -------------------
2355660 2012-12-11 03:59:57
SQL> insert into FB_Qry_tab values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select id1 from fb_qry_tab;
ID1
----------
1
Flashback using timestamp
1* Select * from fb_qry_tab as of timestamp to_timestamp('2012-12-11 03:59:57','YYYY-MM-DD HH24:MI:SS')
SQL> /
no rows selected
Flashback using SCN
SQL> select * from fb_qry_tab as of SCN 2355660;
no rows selected
Flash Back Version Query
Flash Back query version allows you to see during specified time period using VERSION BETWEEN clause.
1* Create table FB_Ver_Tab (id1 number, info varchar2(20))
SQL> /
Table created.
SQL> insert into fb_Ver_tab values (1,'this is one');
1 row created.
SQL> commit;
Commit complete.
1* SELECT current_scn SCN, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')time FROM v$database
SQL> /
SCN TIME
---------- -------------------
2357901 2012-12-11 04:28:52
SQL> Update FB_Ver_Tab set info='This is two' where id1=1;
1 row updated.
SQL> commit;
Commit complete.
1* update FB_Ver_Tab set info='This is three' where id1=1
SQL> /
1 row updated.
SQL> commit;
Commit complete.
1
SELECT id1, info FROM FB_Ver_TAb VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2012-12-11 04:28:52', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2012-12-11 04:39:52', 'YYYY-MM-DD HH24:MI:SS') WHERE id1 = 1
SQL> /
ID1 INFO
---------- --------------------
1 This is three
1 This is two
1 this is one
Flashback Transaction Query
We can get additional information about Flash back query versions using FLASHBACK_TRANSACTION_QUERY tables. It provides plenty of information about any transaction done on your table(s).
desc flashback_transaction_query
Name Null 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)
12 rows selected
Flashback table
We can do point-in-time recovery of a table using this flashback feature. To do that followings are the requirements:
1:- ROW Movement must be enabled
2:-Enough information in UNDO
3:- Flashback any table privilege
4:- DML including select privilege on table.
SQL> create table fb_tab (id1 number, info varchar2(20));
Table created.
SQL> alter table fb_tab enable row movement;
Table altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2489521
SQL> insert into fb_tab values (1, 'first');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2489593
SQL> insert into fb_tab values (2, 'second');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2489605
SQL> Flashback table fb_tab to scn 2489521;
Flashback complete.
SQL> select * from fb_tab;
no rows selected
SQL> flashback table fb_tab to scn 2489593;
Flashback complete.
SQL> select * from fb_tab;
ID1 INFO
---------- --------------------
1 first
SQL> flashback table fb_tab to scn 2489605;
Flashback complete.
SQL> select * from fb_tab;
ID1 INFO
---------- --------------------
1 first
2 second
We can also user timestamp instead of SCN.
Flashback Drop
SQL> drop table fb_tab;
Table dropped.
SQL> drop table fb_tab;
Table dropped.
SQL> select * from fb_tab;
select * from fb_tab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table fb_tab to before drop;
Flashback complete.
SQL> select * from fb_tab;
ID1 INFO
---------- --------------------
1 first
2 second
Flashback to drop uses recycle bin feature of Oracle database.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FB_QRERY_TEST BIN$0Ib3iNYVqT7gQAIKUgIlbQ==$0 TABLE 2012-12-11:03:50:03
FB_QUERY_TEST BIN$0IgYPbF6/9bgQAIKUgIpKQ==$0 TABLE 2012-12-11:03:59:12FB_TAB BIN$0Jybt3fs9nrgQAIKUgJj6Q==$0 TABLE 2012-12-12:05:42:09
if we use PURGE option of drop table than we are not in position to flashback table to drop. so keep this in mind that before flashback you should check recyclebin.
any suggestion,correction,question is most welcome.
