Table of contents
Preamble
At which level can you flashback figures in Oracle:
| Database level | Data will be modified ? | Technology |
|---|---|---|
| Database | Yes (uses flashback logs and archived logs) | Flashback logs |
| Table | Yes | Undo |
| Drop Table | Yes (obviously you accidentally dropped the table) | Recycle Bin |
| Query | No | Undo |
| Version Query | No | Undo |
| Transaction Query | No | Undo |
| Archive | Yes | Flashback Data Archive |
All testing has been done on Oracle 11.2.0.2.0 on Red Hat Enterprise Linux Server release 5.5 (Tikanga).
To avoid any non-expected results Undo tablespace retention has been set to guaranteed (and so preserved for UNDO_RETENTION seconds, set to one hour):
SQL> SET lines 200 SQL> SELECT tablespace_name,retention FROM dba_tablespaces; TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY SYSAUX NOT APPLY UNDOTBS1 NOGUARANTEE TEMP NOT APPLY USERS NOT APPLY TESTTBS NOT APPLY 6 ROWS selected. SQL> ALTER TABLESPACE UNDOTBS1 retention guarantee; TABLESPACE altered. SQL> SELECT tablespace_name,retention FROM dba_tablespaces; TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY SYSAUX NOT APPLY UNDOTBS1 GUARANTEE TEMP NOT APPLY USERS NOT APPLY TESTTBS NOT APPLY 6 ROWS selected. SQL> ALTER SYSTEM SET undo_retention=3600; SYSTEM altered. |
All testing have been done with below table (created in locally managed tablespace):
SQL> CREATE TABLE test1(id NUMBER, descr VARCHAR2(30)); TABLE created. SQL> INSERT INTO test1 VALUES(1,'One'); 1 ROW created. SQL> INSERT INTO test1 VALUES(2,'Two'); 1 ROW created. SQL> INSERT INTO test1 VALUES(3,'Three'); 1 ROW created. SQL> COMMIT; COMMIT complete. |
Flashback Database
This flashback technology is a complete rewind of the database i.e. no granularity here, the whole database is re-winded…
You must activate it with:
SQL> shutdown IMMEDIATE DATABASE closed. DATABASE dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total SYSTEM Global Area 1068937216 bytes Fixed SIZE 2233336 bytes Variable SIZE 796920840 bytes DATABASE Buffers 260046848 bytes Redo Buffers 9736192 bytes DATABASE mounted. SQL> ALTER DATABASE flashback ON; DATABASE altered. SQL> ALTER DATABASE OPEN; DATABASE altered. |
Can be then controlled with:
SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ YES |
You define the flashback database retention with:
SQL> show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target INTEGER 1440 |
You are now able to rewind your database at maximum db_flashback_retention_target minutes in the past, if you look into your Fast Recovery Area you see creation of below files:
[oracle@server1 fast_recovery_area]$ pwd /oracle/fast_recovery_area [oracle@server1 fast_recovery_area]$ ll total 20 drwx------ 2 root root 16384 May 11 17:05 lost+found drwxr-x--- 7 oracle dba 4096 Aug 15 14:23 TEST [oracle@server1 fast_recovery_area]$ ll TEST total 20 drwxr-x--- 30 oracle dba 4096 Aug 15 00:11 archivelog drwxr-x--- 2 oracle dba 4096 Jul 13 11:22 autobackup drwxr----- 5 oracle dba 4096 Jul 25 17:07 backupset drwxr-x--- 2 oracle dba 4096 Aug 15 14:23 flashback drwxr-x--- 2 oracle dba 4096 Jul 20 12:07 onlinelog [oracle@server1 fast_recovery_area]$ ll TEST/flashback total 16040 -rw-r----- 1 oracle dba 8200192 Aug 15 14:31 o1_mf_74l3ynbt_.flb -rw-r----- 1 oracle dba 8200192 Aug 15 14:23 o1_mf_74l3yoth_.flb |
Remark:
Activating flashback logs is not completely transparent as apparently performance overhead is around 2% (but mainly depends on your workload so expected to be more from what I heard around)…
Let’s test it with a DDL statement:
SQL> SET lines 200 SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29034095 15-AUG-11 02.35.27.000000000 PM SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20)); TABLE altered. SQL> UPDATE test1 SET column1='Temporary'; 3 ROWS updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1; ID DESCR COLUMN1 ---------- ------------------------------ -------------------- 1 One TEMPORARY 2 Two TEMPORARY 3 Three TEMPORARY SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29034142 15-AUG-11 02.36.11.000000000 PM |
Database must be in mount state to flashback it:
SQL> shutdown IMMEDIATE; DATABASE closed. DATABASE dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total SYSTEM Global Area 1068937216 bytes Fixed SIZE 2233336 bytes Variable SIZE 796920840 bytes DATABASE Buffers 260046848 bytes Redo Buffers 9736192 bytes DATABASE mounted. SQL> flashback DATABASE TO scn 29034095; Flashback complete. SQL> ALTER DATABASE OPEN resetlogs; DATABASE altered. SQL> SELECT * FROM yjaquier.test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three |
Remark:
- You may use SCN, timestamp or restore points to flashback a database.
- You can open the database read only instead of resetlogs to only export figures.
Flashback Table
This technology allows you to flashback a table to a previous state after “wrong” DML statements, DDL statements are not flashback-able…
Enabling row movement for your test table is mandatory for flashback table:
SQL> ALTER TABLE test1 enable ROW movement; TABLE altered. |
Inserting few test rows and performing a “wrong” update:
SQL> SET lines 200 SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 28947307 15-AUG-11 09.35.57.000000000 AM SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> INSERT INTO test1 VALUES(4,'Five'); 1 ROW created. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three 4 Five SQL> UPDATE test1 SET descr='Four'; 4 ROWS updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 Four 2 Four 3 Four 4 Four |
Flashing back table to original good state (SCN or timestamp taken in previous step, Oracle suggests to record current SCN before issuing such command):
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 28947426 15-AUG-11 09.38.03.000000000 AM SQL> flashback TABLE test1 TO scn 28947307; Flashback complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three |
You can also work with restore point (refer to official documentation for retention policies):
SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> CREATE restore point before_upgrade; Restore point created. SQL> UPDATE test1 SET descr='Temporary'; 3 ROWS updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 TEMPORARY 2 TEMPORARY 3 TEMPORARY SQL> SET lines 200 SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 28949800 15-AUG-11 10.03.02.000000000 AM SQL> flashback TABLE test1 TO restore point before_upgrade; Flashback complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> DROP restore point before_upgrade; Restore point dropped. |
Remark:
Using flasback technology will create SYS_TEMP_FBT in your schema:
SQL> SELECT table_name FROM user_tables; TABLE_NAME ------------------------------ SYS_TEMP_FBT TEST1 |
Flashback Drop
This technology is no more no less than Windows recycle bin and allows you to restore a wrongly dropped table. In fact objects are not really dropped but just renamed and they remain in tablespace until you need space to create other objects:
SQL> SELECT table_name FROM user_tables; TABLE_NAME ------------------------------ SYS_TEMP_FBT TEST1 SQL> DROP TABLE test1; TABLE dropped. SQL> SELECT table_name FROM user_tables; TABLE_NAME ------------------------------ SYS_TEMP_FBT SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$qogQry1iu6TgQEsKbCUKuA==$0 TABLE 2011-08-15:10:29:44 SQL> SELECT * FROM recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE -------------------------------- --- --- ---------- ----------- ------------ ---------- BIN$qogQry1iu6TgQEsKbCUKuA==$0 TEST1 DROP TABLE USERS 2011-08-15:09:35:12 2011-08-15:10:29:44 28951308 YES YES 71097 71097 71097 8 |
Once the object is dropped you cannot access it but you can still query its recyclebin counterpart and/or restore it:
SQL> SELECT * FROM test1; SELECT * FROM test1 * ERROR AT line 1: ORA-00942: TABLE OR VIEW does NOT exist SQL> SELECT * FROM "BIN$qogQry1iu6TgQEsKbCUKuA==$0"; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> flashback TABLE test1 TO before DROP; Flashback complete. SQL> show recyclebin; SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three |
You can also purge it with:
SQL> purge recyclebin;
Recyclebin purged. |
Flashback Query
This flashback technology has the taste of flashback table except that you can see the past figures without actually restoring them.
Let’s use our test table and update it with “wrong” figures:
SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT table_name FROM user_tables; TABLE_NAME ------------------------------ SYS_TEMP_FBT TEST1 SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS current_time FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME ----------- --------------------------------------------------------------------------- ----------------------------- 28954179 15-AUG-11 11.03.48.000000000 AM 15-aug-2011 11:03:48 SQL> UPDATE test1 SET descr='Temporary'; 3 ROWS updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 TEMPORARY 2 TEMPORARY 3 TEMPORARY |
The flashback query feature works with AS OF SCN and AS OF TIMESTAMP in SELECT statement:
SQL> SELECT * FROM test1 AS OF scn 28954179; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT * FROM test1 AS OF TIMESTAMP TO_TIMESTAMP('15-AUG-11 11.03.48.000000000 AM'); ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT * FROM test1 AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three |
You can also use DBMS_FLASHBACK package for flashback query. This can as well been done with SCN using ENABLE_AT_SYSTEM_CHANGE_NUMBER procedure or timestamp using ENABLE_AT_TIME procedure:
SQL> EXEC dbms_flashback.enable_at_system_change_number(28954179); PL/SQL PROCEDURE successfully completed. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> EXEC dbms_flashback.disable; PL/SQL PROCEDURE successfully completed. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 TEMPORARY 2 TEMPORARY 3 TEMPORARY |
Finally you can flashback your table using flashback table technology (or insert using AS SELECT in a subquery):
SQL> flashback TABLE test1 TO scn 28954179; Flashback complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three |
Remark:
This flashback query technology can also be used in export utility (exp and expdp) using FLASHBACK_SCN and FLASHBACK_TIME parameters.
Flashback Versions Query
This flashback technology is similar to flashback query but you can see different past figures of a column over an interval. Let’s first perform few updates on our test table:
SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29006118 15-AUG-11 12.51.21.000000000 PM SQL> UPDATE test1 SET descr='The one' WHERE id=1; 1 ROW updated. SQL> COMMIT; COMMIT complete. SQL> UPDATE test1 SET descr='The only one' WHERE id=1; 1 ROW updated. SQL> COMMIT; COMMIT complete. SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29006142 15-AUG-11 12.52.03.000000000 PM |
Remark:
Please note the commit after each update to generate multiple transactions.
You can now see past figures:
SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, id, descr FROM test1 VERSIONS BETWEEN SCN 29006118 AND 29006142 WHERE id = 1; VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V ID DESCR ----------------- ------------------------ --------------- ------------------------ ---------------- - ---------- ------------------------------ 29006133 15-AUG-11 12.51.45 PM 0A001500B99F0000 U 1 The only one 29006128 15-AUG-11 12.51.36 PM 29006133 15-AUG-11 12.51.45 PM 060017000A100000 U 1 The one 29006128 15-AUG-11 12.51.36 PM 1 One SQL> SELECT * FROM test1 AS OF scn 29006128; ID DESCR ---------- ------------------------------ 1 The one 2 Two 3 Three SQL> SELECT * FROM test1 AS OF scn 29006118; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 The only one 2 Two 3 Three |
Remark:
Same as DBMS_FLASHBACK package you may work with timestamp or SCN.
Flashback Transaction
Nothing special in this flashback technology except an extension of flashback query to get extra information of transactions. To use it you must have activated database minimal supplemental logging with:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; DATABASE altered. |
If we use flashback query example:
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('0A001500B99F0000'); no ROWS selected SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('060017000A100000'); XID OPERATION START_SCN COMMIT_SCN LOGON_USER ---------------- -------------------------------- ---------- ---------- ------------------------------ UNDO_SQL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 060017000A100000 UPDATE 29006126 29006128 YJAQUIER UPDATE "YJAQUIER"."TEST1" SET "DESCR" = 'One' WHERE ROWID = 'AAARW7AAEAABn6GAAA'; 060017000A100000 BEGIN 29006126 29006128 YJAQUIER |
First strange thing is missing information for one of the DML statement… After a while if you again select the existing one:
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('060017000A100000'); no ROWS selected |
Behavior is really erratic and this is explained by bugs, apparently corrected in 11.2.0.2.2, so not really mature so far:
Bug 10358019 – Queries against FLASHBACK_TRANSACTION_QUERY return wrong results [ID 10358019.8]
Remark:
Oracle 11gR2 extend this functionality with flashback transaction backout by flashing back a specific transactions and all its dependent transactions.
Flashback Data Archive
Flashback Data Archive (FDA) technology is an extension of flashback versions query technology and extend Undo functionality (bypassing UNDO_RETENTION parameter) by keeping figures for fixed pre-defined period (if enough available space obviously). On the paper it’s perfect: transparent, efficient, old figures stored in compressed format, answers to SOX requirements. Small drawback is the cost as you must purchase Oracle Total Recall option to use it…
First start by creating a dedicated tablespace (you may instead use an existing one):
SQL> CREATE TABLESPACE fda datafile '/oracle/data01/test/fda01.dbf' SIZE 100m extent management local SEGMENT SPACE management auto; TABLESPACE created. |
Then create a default flashback data archive (then no need to specify one when activating FDA on your tables), no quota to use whole tablespace and retention set to one month:
SQL> CREATE flashback archive DEFAULT fla1 TABLESPACE fda retention 1 MONTH; Flashback archive created. |
Then modify your table:
SQL> ALTER TABLE test1 flashback archive; TABLE altered. |
Remark:
To see what’s activated and your FDA you may use the following queries (USER and ALL counterparts may also be used)
SELECT * FROM dba_flashback_archive; SELECT * FROM dba_flashback_archive_ts; SELECT * FROM dba_flashback_archive_tables; |
Let’s modify the table and see past figures, same as versions query and you may use SCN or timestamp. From pure SQL standpoint you don’t see difference with versions query, except in retention policy…
SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29045024 15-AUG-11 04.22.20.000000000 PM SQL> UPDATE test1 SET descr='The one' WHERE id=1; 1 ROW updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1; ID DESCR ---------- ------------------------------ 1 The one 2 Two 3 Three SQL> SELECT * FROM test1 AS OF scn 29045024; ID DESCR ---------- ------------------------------ 1 One 2 Two 3 Three SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- --------------------------------------------------------------------------- 29045079 15-AUG-11 04.23.29.000000000 PM SQL> UPDATE test1 SET descr='The only one' WHERE id=1; 1 ROW updated. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM test1 AS OF scn 29045079; ID DESCR ---------- ------------------------------ 1 The one 2 Two 3 Three |
In this example past version of the table will be kept for one month and then automatically deleted.
With only few exceptions DDLs are supported and for unsupported ones you may use DBMS_FLASHBACK_ARCHIVE PL/SQL package:
SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20)); TABLE altered. SQL> SELECT * FROM test1; ID DESCR COLUMN1 ---------- ------------------------------ -------------------- 1 The only one 2 Two 3 Three SQL> SELECT * FROM test1 AS OF scn 29045024; ID DESCR ---------- ------------------------------ 1 One 3 Three 2 Two |
References
- Master Note For Oracle Flashback Technologies [ID 1138253.1]
- Oracle Flashback Technologies
Adnan Ali says:
Worth reading article.
Nice effort.
Yannick Jaquier says:
Thanks for nice comment !
Orkan says:
This has been a very useful information. Examples are very clear. I have noted this page to my reference pages. Thanks for the effort.
Justin says:
Very good explanatory reading. However, the grey background in screen window makes it difficult to read the text.
Yannick Jaquier says:
Thanks for stopping by !
I personally rate the gray background a little bit less aggressive for my eyes than the white one…
vimal says:
very nice article.I practice by seeing these example.lots of thing i have learn,but little bit more explanation you need to add here,such as when i was practicing i logged in as “SYS” user and try to do flashback table ,but it didn’t allow me.So i think it is confusing for new bee or novice user.So you need to explain here that some command will not work for sys user and why it will not work for sys user that also you need to mention.Rest of the article is very nice.Keep it up your good work.
Yannick Jaquier says:
Thanks to you for reading !
Honestly I was not aware of:
ORA-08185: Flashback not supported for user SYSBut I think it’s a general best practice to not use SYS user when performing testing. Your customers will not connect to database using SYS right ? Then coming back to FLASHBACK TABLE, the prerequisites section of the official documentation well described needed grant to use this feature as a “normal user”…
Marcela says:
Really nice!!! Thanks