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

46 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. Thank you for your great information it is useful .Tableau Online Training

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

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

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

    ReplyDelete
  12. 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
  13. This comment has been removed by the author.

    ReplyDelete

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

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

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

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

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

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

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

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

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

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

    ReplyDelete
  26. Very Informative blog, Thank you for this Information.

    ReplyDelete