Dec 08 2013

Oracle Flashback Technology

Category: AdministrationFatih Acar @ 11:20

When authorized people make mistakes, you need the tools to correct these errors. Oracle Database provides a family of human error correction technology called Flashback. Flashback revolutionizes data recovery. In the past, it might take minutes to damage a database but hours to recover it. With Flashback technology, the time to correct errors equals the time it took to make the error. It is also easy to use a single, short command to recover the entire database instead of following some complex procedure.

Flashback is unique to Oracle Database and provides:

  • A SQL interface to quickly analyze and repair human errors.
  • Fine-grained surgical analysis and repair for localized damage, such as when the wrong customer order is deleted.
  • Correction of more widespread damage yet does it quickly to avoid long downtime, such as when all of this month’s customer orders have been deleted.
  • Recovery at all levels including the row, transaction, table, tablespace, and database wide.


Flashback Technology Various

Flashback Query

Oracle Flashback Query enables you to query any data at some point in time in the past. You can use Flashback Query to view and reconstruct lost data that may have been deleted or changed by accident. For example:
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP(’19-APR-05 02:00:00 PM’) WHERE …
This statement displays rows from the employee table as of 2:00pm on the specified date. Developers can use this feature to build self-service error correction into their applications, empowering end users to undo and correct their errors without delay rather than burdening administrators to perform this task. Flashback Query is simple to manage, because the database automatically keeps the necessary information to reconstruct data for a configurable time into the past.

Flashback Versions Query

The Flashback Versions Query provides a way to view changes made to the database at the row level. It is an extension to SQL and enables you to retrieve all of the different versions of a row across a specified time interval. For example:
SELECT * FROM employee VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(’19-APR-05
02:00:00 PM’) AND TIMESTAMP TO_TIMESTAMP(’19-APR-05 03:00:00 PM’) WHERE …
This statement displays each version of the row, with each row changed by a different transaction between 2:00 and 3:00 p.m. on 19 April. This helps you to pinpoint when and how data is changed and trace it back to the user, application, or transaction, and tracks down the source of a logical corruption in the database and correct it. Flashback Versions Query also helps application developers debug code.

Flashback Transaction

Oracle Flashback Transaction backs out a transaction and its dependent transactions. The DBMS_FLASHBACK.TRANSACTION_BACKOUT() procedure rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and execute the compensating transactions that return the affected data to its original state. You can query the DBA_FLASHBACK_TRANSACTION_STATE view to see the current state of a transaction with respect to whether the transaction has been backed out using dependency rules or forced out by either:

  • Backing out nonconflicting rows
  • Applying undo SQL

Oracle Flashback Transaction increases availability during logical recovery by easily and quickly backing out a specific transaction or set of transactions and their dependent transactions, with one command while the database remains online.

Flashback Transaction Query

Flashback Transaction Query provides a way to view changes made to the database at the transaction level. It is an extension to SQL that enables you to see all changes made by a transaction. For example:
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = ‘000200030000002D’;
This query shows all of the resultant changes made by this transaction. In addition, compensating SQL statements are returned and can be used to undo changes made to all rows by this transaction. Using a precision tool of this kind, the database administrator and application developer can precisely diagnose and correct logical problems in the database or application.

Flashback Database

To bring an Oracle database to a previous point in time, the traditional method is to do point-in-time recovery. However, point-in-time recovery can take hours, or even days, because it requires the whole database to be restored from backup and recovered to the point in time just before the error was introduced into the database. With the size of databases constantly growing, it will take hours or even days just to restore the whole database.
Flashback Database is a strategy for doing point in time recovery. It quickly rewinds an Oracle database to a previous time to correct any problems caused by logical data corruption or user error. It uses flashback logs to capture old versions of changed blocks. This is similar to a continuous backup or storage snapshot. When you must perform recovery, the Flashback logs are quickly replayed to restore the database to a point in time before the error and just the changed blocks are restored. Flashback Database is fast and reduces recovery time from hours to minutes. For example, issue the following command to recover a database to 2:05 p.m.:

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(’19-APR-05 02:05:00 PM’);

Flashback Table

Flashback Table recovers a table or a set of tables to a specified point in time in the past. In many cases, Flashback Table alleviates the need to perform more complicated point-in-time recovery operations. For example:
FLASHBACK TABLE orders, order_items TIMESTAMP TO_TIMESTAMP(’07-APR-2005 02:33:00 PM’);
This command rewinds any updates to the orders and order_items tables that were made between the current time and the specified timestamp in the past. Flashback Table performs this operation online and in place and it maintains any referential integrity constraints between the tables. Flashback Table is very similar to having a rewind or undo button for a table, or a set of related tables.

Flashback Drop

Dropping, or deleting, database objects by accident is a mistake users sometimes make when they think they are connected to a test database when actually connected to the production database. Users soon realize their mistake but by then it is too late and there is no way to easily recover the dropped tables and its indexes, constraints, and triggers. Objects once dropped are lost forever. Indexes can be rebuilt, but for important tables or other objects (such as partitions or clusters), you must perform a point-in-time recovery, which may be very time consuming and lead to loss of recent transactions.
Flashback Drop provides a safety net when dropping objects in Oracle Database. When a user drops a table, Oracle Database places it in a recycle bin. The recycle bin is a virtual container where all dropped objects reside. Objects remain in the recycle bin until you decide to permanently remove the objects or until the space pressure is placed on the tablespace containing the table. You can undrop the dropped table and its dependent objects from the Recycle Bin. For example, the table employee and all of its dependent objects would be undropped using the following command:

FLASHBACK TABLE employee TO BEFORE DROP;

Flashback Restore Points

When an Oracle database point-in-time recovery operation is required, you must determine a time or System Change Number (SCN or transaction time) to which the data must be rolled back. Oracle Database uses restore points, which is a user-defined label that can be substituted for an SCN or clock time when used in conjunction with Flashback Database, Flashback Table, and Recovery Manager (RMAN). Restore points provides the ability to bookmark a known time when the database was in a good state, allowing quick and easy rewind of an inappropriate action done to the database. It also provides the ability to flashback through a previous database recovery and open resetlogs. Guaranteed restore points allow major database changes to be quickly undone (for example, database batch job, upgrade, or patch) by ensuring that the undo required to rewind the database is retained. Restore points provide an easy way to rewind back to a known time.
In an Oracle Data Guard environment, this capability also allows a physical standby database that has been opened read/write to later flash back the changes and later convert the database back to a physical standby database that is synchronized with the production database. If a logical error is discovered after a switchover operation, the primary and standby databases can be flashed back to an SCN or a point in time prior to the switchover operation. In addition, to quickly synchronize the standby database with the production database, you can apply an incremental backup of the production database to the standby database instead of applying all the redo data generated since the two databases diverged. This can significantly reduce the time to resynchronization the two databases.

Block recovery using flashback logs

Block recovery can optionally retrieve a more recent copy of a data block from the flashback logs to reduce recovery time. Furthermore, a corrupted block encountered during instance recovery does not result in instance recovery failing. The block is automatically marked as corrupt and added to the RMAN corruption list in the V$DATABASE_BLOCK_CORRUPTION table. You can subsequently issue the RMAN RECOVER BLOCK command to fix the associated block.

Flashback Data Archive

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports. See or complete information.

Source : Oracle Offical Documents

24,342 total views, 10 views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.