Oracle Flashback

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Flashback feature was introduced in Oracle9i; it was limited to Flashback Query only
Great improvements have been made in the Flashback functions in Oracle Database 10g.
Flashback functionalities provide fast and flexible data recovery

TYPES OF FLASHBACK :-

1. flashback query ---we can show only the past state of the table

2. flashback version query ---show the the table transaction time and scn

3. flashback transaction query ---it show what operation is done and which user is performed

4. flashback table --- to flash back a table to an earlier scn or timestamp

5. flashback drop --- flashback table to before drop using recylebin

6. flashback database --- to flash back a table to an earlier scn,timestamp or restore point

Flashback Works On Two Parameter

1. scn

2. timestamp

FLASHBACK STATUS:-

Flashback status of a database can be checked from the below query and system parameters.

SQL> select NAME, FLASHBACK_ON from v$database;

SQL> archive log list

SQL> show parameter undo_retention

SQL> show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file

FLASHBACK ENABLE:-

The Database must be started through SPFILE

SQL > show parameter spfile

NAME TYPE VALUE

Step to Flashback Practical

1)undo_retention seconds, set to one hour

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.

2)create test1 table and insert some data’s

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.

3)step to set flashback_on in database

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.
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

4) 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

5)add some values in the tables and just show the scn

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

6)flashback query -- we can only show the past state of the table

 SQL> flashback DATABASE as of scn 29034095;
 Flashback complete.
SQL> SELECT * FROM nijam.test1;
        ID DESCR
---------- --------------
         1 One
         2 Two
         3 Three

7) flashback table --- to flash back a table to an earlier scn or timestamp

Enabling row movement for your test table is mandatory for flashback table:

SQL> ALTER TABLE test1 enable ROW movement;
 TABLE altered.

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 nijam.test1;
           ID DESCR
---------- --------------
         1 One
         2 Two
         3 Three

8) Inserting few test rows and performing a “wrong” update:

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

9) 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> 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.

10) Using flasback technology will create SYS_TEMP_FBT in your schema:

SQL> SELECT table_name FROM user_tables;
 TABLE_NAME
------------------------------
SYS_TEMP_FBT
TEST1

11) flashback drop --- flashback table to before drop using recylebin

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

12) 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.

13) flashback query ---we can show only the past state of the table

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.

14) flashback version query ---show the the table transaction time and scn


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

Note:

Please note the commit after each update to generate multiple transactions.

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

Note:

Same as DBMS_FLASHBACK package you may work with timestamp or SCN

15) flashback transaction query ---it show what operation is done and which user is performed

First strange thing is missing information for one of the DML statement… After a while if you again select the existing one:

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

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]

Note:

Oracle 11gR2 extend this functionality with flashback transaction backout by flashing back a specific transactions and all its dependent transactions.

16) 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.

Note:

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

Search This Blog

TutorialDBA - Support | Training | Consultant

Oracle Flashback

Comments

Post a Comment

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Configuration UDEV SCSI Rules In Oracle Linux 5, 6 and 7

Linux ctime,mtime,atime,cmin,amin,mmin

aspell