Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Flashback

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

Ø Time base flashback
Ø SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

     a) SQL> Select *from student;
          -- This will display all the rows
     b) SQL> Delete student;
     c) SQL> Commit;              -- this will commit the work.
     d) SQL> Select *from student;
         -- Here it will display nothing
     e) Then execute the following procedures
         SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)
     f) SQL> Select *from student;
         -- Here it will display the lost data
         -- The lost data will come but the current system time was used
     g) SQL> Exec dbms_flashback.disable
          -- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

     a) Declare a variable to store SCN
          SQL> Variable s number
     b) Get the SCN
          SQL> Exec :s := exec dbms_flashback.get_system_change_number
     c) To see the SCN
         SQL> Print s
     d) Then execute the following procedures
          SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

          SQL> Exec dbms_flashback.disable
Share this article :

3 comments:

  1. Hi Dayakar,

    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    We have received request from messaging team to create new SQL database for implement the below step in SQL server. Kindly help us.

    Data Export
    1. From Proofpoint archives, export mail items by date (YYYYMM) or date range
    1. Archive may be exported into PSTs or EDRMs
    2. Time duration = 15 minutes for 600 MB
    3. Deposited into FTP site
    4. FTP site mapped to folder accessible from the extraction server
    1. PS script to read mail item content of the PST file
    5. Lock the PST object to the PS1 instance
    6. Evaluate whether the mail item is --
    1. i. A DSN, receipt notification
    2. ii. An archived mail item
    3. iii. A journal envelope
    7. If 2.b.iii. (journal envelope), extract the enclosed MSG file
    8. Collect the following data
    1. i. Date Sent
    2. ii. Sender
    3. iii. Subject
    4. iv. Recipients
    5. v. Message ID
    6. vi. Forwarding
    7. vii. Parent ID
    9. Copy the MSG file to the file folder(s) -- sort by custodian?
    1. i. Or we can add to the SQL DB also??
    10. Write all data collected in 2.d. in SQL database, plus --
    1. i. Status (duplicate or single item or error)
    2. ii. Litigation hold status -- date = 12/31/2099
    3. iii. If not on lit-hold -- set retention due date


    Awesome! Thanks for putting this all in one place. Very useful!


    Gracias
    Irene Hynes

    ReplyDelete
  2. Szia,


    What you’re saying is absolutely correct SQL Flashback , but this isn’t the exact situation everywhere. Where most smart folk work on a project - why can’t you do this the Boss asks :).

    I would much appreciate to get a sample of data travelling via these steps.

    Source data to Staging server(tables) then PLSQL for data cleansing and transferring them to tbl_cleanABC or tbl_error_ABC. Checking the accuracy of data and finally PLSQL for loading to data warehouse.

    Follow my new blog if you interested in just tag along me in any social media platforms!


    Gracias

    ReplyDelete
  3. Szia,


    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Flashback I agree with you on lot of points!


    I posted before regarding using oracle
    UDT stored procedure parameter type to pass a list of records for multiple insert using .net c#.
    I had the below example where a class definition is needed at .net side to map to oracle object. Unfortunately, I did not apply this due to the extra mapping code that is needed.
    http://appsjack.blogspot.com.eg/2010/09/pass-custom-udt-types-to-oracle-stored.html

    My question is: did this change with oracle 12c? Can we now use:

    parameter.UdtTypeName = directOracleUDTName; //without the need for .net definition and mapping classes





    Once again thanks for your tutorial.


    Best Regards,

    Ajeeth

    ReplyDelete