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)

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

For ex-
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;

Limitations:
· 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.

SQL> FLASHBACK TABLE test TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Flashback Drop:

The Oracle 10g provides the ability to reinstating an accidentally dropped table from recyclebin.
SQL> FLASHBACK TABLE test TO BEFORE DROP;

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.

User needs the FLASHBACK ARCHIVE ADMINISTER, FLASHBACK ARCHIVE privileges

SQL> CREATE FLASHBACK ARCHIVE flash_back_archive

TABLESPACE flash_back_archive QUOTA 10G RETENTION 5 YEARS;

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)

2. DB_RECOVERY_FILE_DEST (Location of 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.

Regards
Satishbabu Gunukula, Oracle ACE

117 comments:

  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

    ReplyDelete
  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

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

    ReplyDelete
  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.
    PINOY TV SHOWS
    THE VIDEOS WHICH
    THE GMA NETWORK

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

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

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

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

    ReplyDelete
  9. This comment has been removed by the author.

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

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

    ReplyDelete
  12. This comment has been removed by the author.

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

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

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

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

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

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





    DATA SCIENCE COURSE MALAYSIA

    ReplyDelete
  19. 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.
    www.technewworld.in
    How to Start A blog 2019

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

    ReplyDelete
  21. 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"

    ReplyDelete
  22. 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"

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

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

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

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

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

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

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

    ReplyDelete
  30. Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates.

    Data Science Training

    ReplyDelete
  31. I would like to say that this blog really convinced me to do it! Thanks, very good post.
    Data Science Course in Bangalore

    ReplyDelete
  32. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.
    Data Science Training in Bangalore

    ReplyDelete
  33. This comment has been removed by the author.

    ReplyDelete
  34. This comment has been removed by the author.

    ReplyDelete
  35. 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.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    spoken english classes in chennai | Communication training


    ReplyDelete

  36. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    about us

    ReplyDelete
  37. Very interesting article. Many articles I come across these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome. I will instantly grab your rss feed to stay informed of any updates you make and as well take the advantage to share some latest information about

    CREDIT CARD HACK SOFTWARE which many are not yet informed of, the recent technology.

    Thank so much for the great job.

    ReplyDelete
  38. This is a great post I saw thanks to sharing. I really want to hope that you will continue to share great posts in the future.
    data science courses in delhi

    ReplyDelete
  39. Very good article, I liked reading your post, very pleasant to share, I want to tweet this to my followers. Thank you!.

    Data Analytics Course in Bangalore

    ReplyDelete
  40. I am very happy with your good work. Visit this post Boys Attitude Status

    ReplyDelete
  41. Top quality article with valuable information found very useful and enjoyed reading it thanks for sharing.
    Data Analytics Course Online

    ReplyDelete
  42. when we are talking about data, how can we miss the opportunity to show some data related to the profession of a Data Scientist? According to a report published by an online education portal, there has been a dramatic rise noticed in the listing and application for jobs related to Data Science. data science course syllabus

    ReplyDelete
  43. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    bag of words

    ReplyDelete
  44. Data scientists are being labeled the "Sexiest Job people of the 21st century" and the "Rock Stars of the IT world". It becomes the most sought-after profile, gifting individuals with the career of admiration and achievement and a handsome pay package. Join ExcelR and get data science certification to get your dream data science course syllabus

    ReplyDelete
  45. so happy to find good place to many here in the post, the writing is just great, thanks for the post.
    Best Digital Marketing Institute in Hyderabad

    ReplyDelete
  46. It fully emerged to crown Singapore's southern shores and has undoubtedly put it on the world residential monument map. Still, I scored more points than I have in one season for GS. I think it would be hard to find someone with the same consistency that I have had over the years, so I'm happy.
    Data Analytics Course in Bangalore

    ReplyDelete
  47. Thanks for Sharing this Valuable Information with us: this is very useful for me. Keep it Up.
    data scientist online course

    ReplyDelete
  48. Thanks For your post. This is rally helpful for Data science for beginner.
    machine learning course aurangabad

    ReplyDelete
  49. This post is very simple to read and appreciate without leaving any details out. Great work!
    data science training

    ReplyDelete
  50. Thanks for sharing of your important information.Thank you so much .dehumidifier supplier in banglades

    ReplyDelete
  51. https://www.techbadoo.com/2015/07/increase-your-blog-domain-authority-fast.html?showComment=1483425023567#c4238570396485038802
    https://www.google.com/search?q=%22thanks+for+giving+that+type+of+information.digital+marketing+company+in+delhi%22&rlz=1C1YTUH_enIN979&sxsrf=AOaemvJLoF-dVeVc82tDWs4M6dTgiobwyw%3A1636974262674&ei=tj6SYbzLKNivyAOq54bQBg&oq=%22thanks+for+giving+that+type+of+information.digital+marketing+company+in+delhi%22&gs_lcp=Cgdnd3Mtd2l6EANKBAhBGABQAFjgCmCtEGgAcAB4AIABvQGIAboDkgEDMC4zmAEAoAEBoAECwAEB&sclient=gws-wiz&ved=0ahUKEwi8_rqrnJr0AhXYF3IKHaqzAWoQ4dUDCA4&uact=5&shem=ssmd
    https://futureofcio.blogspot.com/2017/01/well-prepare-digital-storm-to.html?showComment=1636974826732#c3422097579455605323
    https://rrusin.blogspot.com/2016/03/learning-sinus-function-using-neural.html?showComment=1636974853012#c1850333578107917666
    https://seofreelancer-in-india.blogspot.com/2015/09/digital-marketing.html?showComment=1636974882133#c908754094912403483
    https://toopoorforgradschool.blogspot.com/2015/09/book-review-great-leader-and-fighter.html?showComment=1636974907406#c1048648583502867427
    https://aimotion.blogspot.com/2014/08/mip-proposal-high-performance-pipeline-whole-exome-dna-sequencing.html?showComment=1636975021153#c7664308355607796165
    http://blog.trewgrip.com/2015/03/10-are-better-than-one-will-healthcare.html?showComment=1636975040629#c1661112706959807743

    ReplyDelete
  52. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.

    Data Scientist Training and Placement Bangalore

    ReplyDelete
  53. Very good message. I came across your blog and wanted to tell you that I really enjoyed reading your articles.

    Cloud Computing Course in Bangalore

    ReplyDelete
  54. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .
    <a href="https://360digitmg.com/india/data-analytics-certification-training-course-in-bangalore>Data Analytics training in Bangalore</a>

    ReplyDelete
  55. Thank you for your post. I sincerely thank you for your post. If you are interested in purchasing our products, you can contact us through this website.You can visit,Online shopping in Bangladesh

    ReplyDelete
  56. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. data scientist course in delhi

    ReplyDelete
  57. This post is so interactive and informative.keep update more information…
    CCNA course in Anna Nagar
    ccna course in Chennai

    ReplyDelete
  58. Business Analytics Course in Patna

    I have read your article, it is very informative and useful to me, I admire the valuable information you offer in your articles. Thanks for posting it ...

    Business Analytics Course in Patna

    ReplyDelete
  59. Very informative message! There is so much information here that can help any business start a successful social media campaign!


    Data Analytics Course in Nashik

    ReplyDelete

  60. Really it is a very interesting blog site. And your posts are very beautiful, I am very impressed for your post. And thank you so much for sharing this post web development company in Bangladesh

    ReplyDelete
  61. Genuinely very charming post. I was looking for such an information and thoroughly enjoyed examining this one. Keep on posting. An obligation of appreciation is for sharing.business analytics course in bhubaneswar

    ReplyDelete
  62. It's like you've got the point right, but forgot to include your readers. Maybe you should think about it from different angles.
    Business Analytics Course in Erode

    ReplyDelete
  63. Very informative message! There is so much information here that can help any business start a successful social media campaign!


    Data Science Course in Erode

    ReplyDelete
  64. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    cyber security course malaysia

    ReplyDelete
  65. This post is so useful and informative. Keep updating with more information.....
    Simple Hacking
    How To Be An Ethical Hacker

    ReplyDelete
  66. People are impressed with this technology, and the experts have predicted a bright future of data science.
    data science course in lucknow

    ReplyDelete
  67. This article will present a closer look at data science courses that give you a comprehensive look at the field. So let's get started.
    data science course in borivali

    ReplyDelete
  68. Data Analytics is an interesting domain with great career growth, start your career in a Data Analytics course with 360DigiTMG and bag a handsome paying job.

    Data Science Course in Bangalore with Placement

    ReplyDelete
  69. Are you not ready to risk your and your family's health this pandemic time by joining an offline Data Analyst course. we have a solution for you, enroll in an online Data Analyst course that will equip you with all the knowledge needed for a job in just 6 months.


    Data Scientist Course in Delhi

    ReplyDelete
  70. When you start learning the discovery of data insight courses, you will have to develop an understanding of detecting complex behaviors and patterns of data.
    data science training in lucknow

    ReplyDelete
  71. Embark on a journey to achieve your professional goals by enrolling in the Data Scientist course in London. Learn the skills of collecting, extracting, analyzing, preparing, visualizing, and presenting results to make valuable decisions. Master the concepts of data science through hands-on projects and case studies to learn the latest trends and skills in this field.
    data science course in london

    ReplyDelete
  72. Great post on Oracle Flashback Technology and features, thanks for valuable information, keep posted Software Testing Training in Pune

    ReplyDelete
  73. I found the section on exploratory data analysis in this post to be informative and well-explained. 360DigiTMG provides data analyst course in bangalore

    ReplyDelete