Tuesday, April 10, 2018

Oracle Flashback Technology and features

Oracle Flashback Technology is a group of Database features that that will help you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:
· Queries that return past data
· Recover rows or tables to a previous point in time
· Track and archive transactional data changes
· Roll back a transaction and its dependent transactions while the db online

Oracle flashback uses automatic undo management (AUM) for all flashback transactions.

Here are few Oracle flashback features.
· Flashback Database
· Flashback Table
· Flashback drop
· Flashback query
· Flashback Version/Transaction Query
· Flashback Data Archive (From Oracle 11g)
· Flashback Recovery Area:-

Flashback Database:- The FLASHBACK DATABASE is a fast alternative to performing an incomplete recovery. The database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET- length of the desired flashback window in minutes (1440 min)


For ex-

· It cannot be used to repair media failures, or to recover from accidental deletion of data files.
· You cannot use Flashback Database to undo a shrink data file operation.
· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded
· Flashback database cannot be used against block corruptions.

Flashback Table The FLASHBACK TABLE used to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system and cannot restore a table to an earlier state across any DDL operation.


Flashback Drop:

The Oracle 10g provides the ability to reinstating an accidentally dropped table from recyclebin.

Flashback Query The feature allows the DBA to see the value of a column as of a specific time, as long as the before-image copy of the block is available in the undo segment.

For ex:-
SQL> SELECT comments FROM employee AS OF TIMESTAMP TO_TIMESTAMP ('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

SQL> SELECT comments FROM employee AS OF SCN 722452;

Flashback Version/Transaction Query Flashback Query only provides a fixed snapshot of the data as of a time. Use Flashback Version Query feature to see the changed data between two time points.

Ex: - The following query shows the changes made to the table:

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, rate from rates versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries using FLASHBACK_TRANSACTION_QUERY view. The UNDO_SQL column in the table shows the actual SQL Statement.

For Ex:-
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query

Oracle 11g has more enhancements in Flashback Transaction and LogMiner. The LogMiner Viewer has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions. Flashback Transaction allows the changes made by a transaction to be undone.

Flashback Data Archive (From Oracle 11g) : Flashback data archive allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive.




Flashback Recovery Area:-
Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)


Performance Guidelines for Oracle Flashback Technology
· For Oracle Flashback Version Query, use index structures.

· Not to scan entire tables and use indexes to query small set of past data. If you need to scan a full table then use parallel hint to the query

· Keep the statistics current as cost-based optimized relies on statistics and use the DBMS_STATS package to generate statistics for tables involved in a Oracle Flashback Query.

· In a Oracle Flashback Transaction Query, the xid column is of the type RAW(8). Use the HEXTORAW conversion function: HEXTORAW(xid)to take advantage of inbuilt function

· The I/O performance cost is mainly paging in the data and undo blocks I the buffer cache. The CPU performance cost is to apply undo.

· A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

Satishbabu Gunukula, Oracle ACE


  1. The article is very good, I like it very much.Here I learned a lot, then I will pay more attention to you.I am impressed by the quality of information on this website .
    abcya | hooda

  2. Keep sharing your blog with updated and useful information.I am very pleased that I stumbled across this in my search for something relating to this sites.I have also bookmarked you for checking out new posts. windows 10 pro product key

  3. Amazing Blog With A lot of worthy articles. kindly check also this romantic whatsapp status collection

  4. This is profoundly informatics, fresh and clear. I imagine that everything has been portrayed in deliberate way so peruser could get most extreme data and realize numerous things.

  5. https://tanvirhossain.com.bd

  6. Thank you for your great information it is useful .Tableau Online Training

  7. Thanks for sharing such an amazing blog. It is really helpful for me and I get my lots of solution with this blog.
    Best Offshore VPS

  8. Thanks for sharing this information with us. I like this blogs so much, it is relative as same as I need.
    Hong Kong Honeymoon Packages

  9. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!

    Data Science Training in Chennai

    Data science training in bangalore

    online Data science training

    Data science training in pune

    Data science training in kalyan nagar

    Data science training in Bangalore

    Data science training in tambaram

  10. Lifestyle Magazine India; Lifestyle is a elite magazine circulated to the elite at page parties. Lifestyle Magazine also circulated to the elite at clubs, lounge bars etc.
    Lifestyle Magazine India

  11. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    java training in chennai | java training in bangalore

    java online training | java training in pune

    selenium training in chennai
    selenium training in bangalore

  12. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    Amazon Web Services Training in Pune | Best AWS Training in Pune

  13. This is beyond doubt a blog significant to follow. You’ve dig up a great deal to say about this topic, and so much awareness. I believe that you recognize how to construct people pay attention to what you have to pronounce, particularly with a concern that’s so vital. I am pleased to suggest this blog.

    java training in annanagar | java training in chennai

    java training in marathahalli | java training in btm layout

    java training in rajaji nagar | java training in jayanagar

  14. This comment has been removed by the author.


  15. I would like to thank you for your nicely written post, its informative and your writing style encouraged me to read it till end. Thanks

    angularjs-Training in annanagar

    angularjs Training in chennai

    angularjs Training in chennai

    angularjs Training in bangalore

  16. nice work keep it up thanks for sharing the knowledge.Thanks for sharing this type of information, it is so useful.Laminated Doors manufacturer in hubli

  17. Greetings. I know this is somewhat off-topic, but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform like yours, and I’m having difficulty finding one? Thanks a lot.

    Amazon Web Services Training in Tambaram, Chennai|Best AWS Training in Tambaram, Chennai

    Amazon Online Training

    AWS Training in JayaNagar | Amazon Web Services Training in jayaNagar

  18. Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here.
    python training in tambaram
    python training in annanagar
    python training in jayanagar

  19. nice topic which you have choose.
    second is, the information which you have provided is better then other blog.
    so nice work keep it up. And thanks for sharing.
    outdoor led wall lights in delhi

  20. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Devops Training in pune

  21. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa interview questions and answers
    automation anywhere interview questions and answers
    blueprism interview questions and answers
    uipath interview questions and answers
    rpa training in chennai

  22. Amazon has a simple web services interface that you can use to store and retrieve any amount of data, at any time, from anywhere on the web. Amazon Web Services (AWS) is a secure cloud services platform, offering compute power, database storage, content delivery and other functionality to help businesses scale and grow.For more information visit.
    aws online training
    aws training in hyderabad
    amazon web services(AWS) online training
    amazon web services(AWS) training online

  23. This comment has been removed by the author.

  24. Best App development company visit here website more information. Thanks you
    Mobile app development company in Gurgaon

  25. Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.

  26. Thanks for sharing this blog, this blog is very helpful information for every one.
    Mobile app Development Company in Mumbai

  27. Most impressive Topic and Blog, this is very helpful Information. thanks for sharing.
    School Management Software in Delhi

  28. Very Informative blog, Thank you for this Information.

  29. Its is good and very informative. I would like to appreciate your work.
    Best Machine Learning Training Coaching

  30. بسم الله الرحمن الرحيم تقدم لكم شركة الكمال جميع خدمات رش المبيد يجميع انحاء المملكة بافضل انواع

    المبيدات للقضاء على جميع الحشرات الطائرة والزاحفه كالصراصير والفائران والنمل الابيض والبق

    والذباب والناموس
    شركة رش مبيدات بالطائف
    شركة رش مبيدات بجازان
    شركة رش مبيدات بحائل
    والسلامه عليكم ورحمة الله وبركاته

  31. Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
    AWS Training in Chennai
    Data Science Training in Chennai
    Python Training in Chennai
    RPA Training in Chennai
    Digital Marketing Training in Chennai

  32. And indeed, I’m just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective I’ve had.

  33. Excellent Blog, I have read all your blogs. Thanks for sharing important information. Such a nice post.
    Devops Training in Chennai | Devops Training Institute in Chennai

  34. Nice blog, good post, informative and helpful post and you are obviously very knowledgeable in this field. Very useful and solid content. Thanks for sharing

    Data Science Courses in Bangalore

  35. Its as if you had a great grasp on the subject matter, but you forgot to include your readers. Perhaps you should think about this from more than one angle.
    date analytics certification training courses
    data science courses training
    data analytics certification courses in Bangalore
    ExcelR Data science courses in Bangalore

  36. I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place
    Data Science Course in Pune

  37. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.


  38. Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well i really get amazed to read this. Its really good.
    How to Start A blog 2019

  39. https://www.gergstore.com

    Gergstore - Online Shopping India

    Golden Era Software Solutions

    Buy handicrafts online in India

    Golden Era Property

  40. Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live.
    angularjs training in chennai | angularjs course in chennai | angularjs training institute in chennai | angularjs training institutes in chennai

  41. Learning more about copyright in the music industry will help avoid tons of problems later on. It would also be great to include the basics of copyright laws in the teaching program at school.

    if you want more just look here "human rights assignments"

  42. Awesome..I read this post so nice and very imformative information...thanks for sharing
    Click here for data science course

  43. You have a real ability for writing unique content. I like how you think and the way you represent your views in this article. We are Best CRM Software in Mumbai.

  44. Browse job depository for thousands of job offers and find the best suitable position. Fill up your profile with your education and experience, then just apply for a job with one click.

    if you want more just look here "jobdepository"

  45. Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    Get SAP S4 HANA Training in Bangalore from Real Time Industry Experts with 100% Placement Assistance in MNC Companies. Book your Free Demo with Softgen Infotech.

  46. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.

    sap abap training in bangalore

    sap abap courses in bangalore

    sap abap classes in bangalore

    sap abap course syllabus

    best sap abap training

    sap abap training center

    sap abap training institute in bangalore

  47. Your article is worth reading! You are providing a lot of valid information.This'll be really helpful for my reference. Do share more such articles.
    AWS Training center in Chennai
    AWS Classes in Chennai
    AWS training fees in Chennai
    R Training in Chennai
    AWS Training in Anna nagar
    AWS Training in OMR
    AWS Training in Porur

  48. Appslure is a high rated Mobile Game development company in Delhi. We are no 1 company for IOS & Android games.
    Game Development company in Delhi

  49. Thanks for sharing this useful information. Keep doing regularly...
    AWS Course in Bangalore

  50. Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Scientist Course

  51. Appslure is a Brilliant mobile Application Development Company in Gurgaon. Our app developers team are expert in iOS, Android, iPhone
    Mobile app development company in gurgaon

  52. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Digital marketing course

  53. Thanks for giving me the time to share such nice information. Thanks for sharing.
    Data Science Course

  54. Attend The Data Science Courses From ExcelR. Practical Data Science Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses.
    Data Science Courses
    Data Science Interview Questions

  55. Study Machine Learning Course Bangalore with ExcelR where you get a great experience and better knowledge.
     Machine Learning Course Bangalore

  56. Study Artificial Intelligence Course with ExcelR where you get a great experience and better knowledge.
     Artificial Intelligence Course

  57. Study Machine learning course bangalore with ExcelR where you get a great experience and better knowledge. Machine learning course bangalore

  58. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data science Interview Questions

  59. It's very useful blog post with inforamtive and insightful content and i had good experience with this information.I have gone through CRS Info Solutions Home which really nice. Learn more details About Us of CRS info solutions. Here you can see the Courses CRS Info Solutions full list. Find Student Registration page and register now. Go through Blog post of crs info solutions. I just read these Reviews of crs really great. You can now Contact Us of crs info solutions. You enroll for Pega Training at crs info solutions.