February 10, 2019

Oracle Flashback feature

Flashback Technology in Oracle

Oracle has a number of products and features that provide high availability in cases of unplanned or planned downtime. These include Fast-Start Fault Recovery, Real Application Clusters (RAC), Recovery Manager (RMAN), backup and recovery solutions, Oracle Flashback, partitioning, Oracle Data Guard, LogMiner, multiplexed redolog files and online reorganization.

To correct problems caused by logical data corruptions or user errors, we can use Oracle Flashback.

Flashback is possible only in Locally Managed Tablespace(LMTS).

Following are the Flashback options we have in Oracle database:
  • Flashback Query (from Oracle 9i)
  • Flashback Table (from Oracle 10g)
  • Flashback Drop (from Oracle 10g)
  • Flashback Version Query (from Oracle 10g)
  • Flashback Transaction Query (from Oracle 10g)
  • Flashback Database (from Oracle 10g)
  • Flashback Data Archive (from Oracle 11g)
  • Flashback Transaction (from Oracle 11g)


Flashback Query useful to view the data at a point-in-time in the past. This can be used (only) to view and reconstruct lost data that was deleted or changed by accident.

Flashback Table useful to recover a table to a point-in-time in the past without restoring a backup. Flashback Table is a push button solution to restore the contents of a table to a given point-in-time. An application on top of Flashback Query can achieve this, but with less efficiency.

Flashback Drop provides a way to restore accidentally dropped tables. This will be done with the help of Recyclebin feature.

Flashback Version Query uses undo data stored in the database to view the changes to one or more rows along with all the metadata of the changes.

Flashback Transaction Query useful to examine changes to the database at the transaction level. As a result, we can diagnose problems, perform analysis and audit transactions.

Flashback Database useful to bring database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because we do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Flashback Database applies to the entire database. It requires configuration and resources, but it provides a fast alternative to performing incomplete database recovery.

Flashback Data Archive - from Oracle 11g, flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.


Rules in order to Flashback
  1. DBA must have enabled undo tablespace, with appropriate retention period.
  2. User must have realized the mistake before the retention period.
  3. User must not exited from the session.
  4. Even frontend must be enabled with flashback option (package).

When to Use Oracle Flashback
(Flashback Database Vs Flashback Table)

Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability, and faster restoration.

Flashback Database and Flashback Table differ in granularity, performance, and restrictions. For a primary database, consider using Flashback Database rather than Flashback Table in the following situations:

  • A user error affected the whole database.
  • A user error affected a table or a small set of tables, but the impact of reverting this set of tables is not clear because of the logical relationships between tables.
  • A user error affected a table or a small set of tables, but using Flashback Table would fail because of its DDL restrictions.
  • Flashback Database works through all DDL operations, whereas Flashback Table does not. Flashback Database moves the entire database back in time, constraints are not an issue, whereas they are with Flashback Table. Flashback Table cannot be used on a standby database.

Other Oracle Articles:  RMAN Incremental Backups     Profiles in Oracle


4 comments:

  1. Very informative blog thanks.

    ReplyDelete
  2. Ohayo,



    11/10!! Your blog is such a complete read. I like your approach with Flashback Technology in Oracle. Clearly, you wrote it to make learning a cake walk for me.



    I think you should have a quick look at
    this Unix article something
    that helps me all the time?

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).


    Thank you,
    Ajeeth

    ReplyDelete

  3. Thanks for sharing this post. Your post is really very helpful its students. Oracle Soa Online course Bangalore

    ReplyDelete