Oracle Flashback

1.Flashback query

    Depend on:content in undo tablespace

    1.1Base syntax:

   1: SELECT ... FROM ... AS OF TIMESTAMP TO_TIMESTAMP('...','yyyy-mm-dd hh34:mi:ss')

    1.2Flashback table

    Hints:Row movement must be enabled on the tables needed to be flashback.

   1: ALTER TABLE XXX ENABLE ROW MOVEMENT

    Use Flashback table

   1: FLASHBACK TABLE XXX TO TIMESTAMP('...','yyyy-mm-dd hh34:mi:ss')

    Warning:If there exist foreign key constraint,maybe operator would flashback not only one table

    1.3Flashback version query

    Base:Virtual columns:VERSIONS_STARTSCN,VERSIONS_ENDSCN,VERSIONS_STARTTIME,VERSIONS_ENDTIME,

    VERSIONS_XID(type RAW,transaction ID),VERSIONS_OPERATIONS(INSERT I,DELETE D,UPDATE U)

    Syntax:

   1: SELECT 

   2: column1,column2,...,

   3: VERSIONS_XID,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_OPERATION 

   4: FROM <TABLE_NAME> 

   5: VERSIONS BETWEEN TIMESTAMP TIMESTAMP1 AND 

   6: TIMESTAMP2 

   7: WHERE <conditions>

    1.4Flashback transaction

    Base:Based on flashback version query.

    The dbms will provide the undo SQL commands to flashback the specified transaction operations

    Involved Data Dictionary Table:FLASHBACK_TRANSACTION_QUERY(XID,…,OPERATION,ROWID,UNDO_SQL)

    Flashback transaction manually(Get the undo_sql and execute it yourself):

   1: SELECT OPERATION,UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID=hextoraw('xxxxxxxxxxxxxxx');

   1: execute 

   2: dbms_flashback.transaction_backout

   3: (-numtxns=>2,-xids=sys.xid_array('0900010059100000','02000700920F0000',-options=>dbms_flashback.cascade);

2.Flashback deletion(drop only)

    Depend on:recyclebin(renaming table)

    2.1Performing flashback deletion

    P1:flashback drop feature is enabled default.So tables dropped by users are put into recycle bin.

    You should confirm it so that log in as the owner of table and “show recyclebin”

    P2:

   1: flashback table xxx to before drop

3.Flashback Database

    Depend on:flashback log

    3.1Enable flashback database feature

    P1:Set FRA location

    P2:Set the size of FRA

    P3:Set flashback retention

    P4:Restart Database instance to mount

    P5:

   1: ALTER DATABASE FLASHBACK ON

   1: ALTER DATABASE OPEN

    P1:Restart database instance to mount

    P2:

   1: FLASHBACK DATABASE TO TIMESTAMP/SCN XXX

   1: ALTER DATABASE OPEN RESETLOGS

4.Flashback Data Archive(FDA)/Total Recall

    Base:

    Create a tablespace to store flashback log data so that users can execute flashback query successfully

    during retention period.

    4.1Enable FDA on a table

    P1:Create a tablespace

   1: CREATE TABLESPACE FDA DATAFILE 'fda1.dbf' SIZE 10M

    P2:Create a fda identifier and set retention time

   1: CREATE FLASHBACK ARCHIVE fla1 TABLESPACE FDA RETENTION 7 year;

    P3:Grant appropriate privilege to fdauser

   1: GRANT FLASHBACK ARCHIVE ON fla1 TO <fdauser>

    P4:Enable FDA on tables

   1: ALTER TABLE xx FLASHBACK ARCHIVE fla1

    4.2Disable FDA on a table

   1: ALTER TABLE xxx.xxx NO FLASHBACK ARCHIVE

   1: DROP FLASHBACK ARCHIVE fla1;