Oracle Database - Flash back

Now,We are going to see Flashback Details are
  • Flashback introduced in 9i
  • It’s like rewind
  • Lot of enhancement in oracle 10g
  • Flashback is only for logical level corruption
  • We can use RMAN, if physical level corruption
  • RVWR (recovery writer) it is one of the background process, it is used to write flashback logs.

    • Types of Flashback :
    1. Flashback query
    2. Flashback version query
    3. Flashback transaction query
    4. Flashback table
    5. Flashback drop
    6. Flashback database
    Step 1 : To check flashback enabled or not


    Step 2 : Enable the flashback



    Step 3 : Stop the flashback


    Two methods to take flashback:
    1. SCN (System change number)
    2. Timestamp

    Step 1 : To check current scn number


    Step 2 : Identify timestamp from scn number


    Step 3 : Identify scn number from timestamp


    Flashback query:

  • Recovering lost data or undoing incorrect committed changes
  • Comparing data at current time to past time
  • Checking transactional data at specific time
  • Provide self service error correction for an application
  • Enabling users undo and correct their errors

    • Step 1 : Connect to the user then create a table




    Step 2 : Insert the values to a1 table


    Step 3 : Connect to Sys user to check current scn number


    Step 4 : To view data in a1 table


    Step 5 : Insert the values to a1 table


    Step 6 : To check current scn number


    Step 7 : Delete all the values from a1 table


    Step 8 : To view old values by using scn method of flashback query


    Table recreate as different name by using scn number:

    Flashback version query:
    Pseudo columns :
  • Versions_start scn
  • Versions_end scn
  • Versions_start timestamp
  • Versions_end timestamp
  • Versions_operation
  • Versions_xid

    • Step 1 : To view flashback versions query between two scn numbers

















    Flashback transaction query:

    In 12c :
  • To check archive log enable/disable and archive log should be enable
  • To enable supplemental log, (it is extra storage)

    • Step 1 : To view deleted values from flashback transaction query




    Flashback Table :

    Step 1 : To create table with f1 user


    Step 2 : Insert the values to d2 table


    Step 3 : To check current scn number


    Step 4 : Delete the values in d2 table with f1 user


    Step 5 : Flashback table by using scn method


    Flashback drop:
    Step 1 : Drop the table


    Step 2 : Create a table


    Step 3 : Connect to f1 user then flashback the dropped table



    Flashback Database :
    Step 1 : To enable the flashback


    Step 2 : To check current scn number


    Step 3 : Connect to user check how many tables in the user


    Step 4 : Drop the table in f1 user


    Step 5: Connect to sys user & close the database


    Step 6 : Start the database in mount stage


    Step 7 : Flashback the database by using scn number


    Step 8 : To View Table



    (Oracle Database - Flash back)