FlashBack Database In Oracle

Overview

Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database.

Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. Flashback Technology revolutionizes recovery by operating just on the changed data. The time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle 10g Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.

Oracle Database 11g introduces an innovative method to manage and query long-term historical data with Flashback Data Archive.

Introduction

Oracle Flashback Database lets you quickly bring your database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error.

Oracle Flashback Drop provides a way to restore accidentally dropped tables.

Oracle Flashback Table lets you quickly recover a table to a point in time in the past without restoring a backup.
Oracle Flashback Query lets you view data at a point-in-time in the past. This can be used to view and reconstruct lost data that was deleted or changed by accident. Developers can use this feature to build self-service error correction into their applications, empowering end-users to undo and correct their errors.

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

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

Note: Internally Oracle uses SCNs to track changes so any flashback operation that uses a timestamp must be translated into the nearest SCN which can result in a 3 second error.

Flashback Database

FLASHBACK DATABASE provides the ability to quickly revert an Oracle database to a previous time-without restoring datafiles and performing media recovery. When you enable the flashback functionality, Oracle automatically creates, deletes, and manages flashback database logs inside the flash recovery area. When you run the FLASHBACK DATABASE command, Oracle applies the flashback logs as well as the archived redo logs to return the database to the specified time. Its a rewind button for the Oracle database.Flashback provides a way to recover only logical data corruptions not a media corruption.

During normal database operation, Oracle occasionally logs past block images in flashback logs.

Flashback logs are written sequentially not archived Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area.

A new background process RVWR introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks

Configuration

Initialization Parameters required,

DB_RECOVERY_FILE_DEST (dynamically modifiable) --> Physical location where RVWR background process writes flashback logs.
DB_RECOVERY_FILE_DEST_SIZE (dynamically modifiable) --> Maximum size flashback logs can occupy in DB_RECOVERY_FILE_DEST.
DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) --> upper limit in minutes on how far back .One can flashback the database.Specified in minute and Default value is 1440 minutes (24 hours)

After setting these parameters in parameter file(init.ora) or spfile enable flashback feature by

SQL> Alter database flashback on;

Note: In oracle 10g we need to put database in mount mode in order to enable flashback mode. In Oracle 11gR2, this process was simplified, allowing flashback database to be enabled/disabled without the need for a database restart.

Flashbacking at database level

Using SCN

SQL> flashback database to SCN 100;

Using Timestamp

SQL> flashback database to TIMESTAMP(sysdate - 2/24); --> flash back to 2 hr
SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12); --> Flashback 5 minutes.
SQL> flashback database to timestamp to_date('27-JUN-2016 19:50:00','DD-MON-YYYY HH24:MI:SS'); --> flash bakc to a specific time period

Using Log sequence Number and RMAN

SQL> flashback database to sequence=50 thread=1;

To check oldest SCN that can be flashed back to use,

SQL> SELECT oldest_flashback_scn FROM v$flashback_database_log;

To check oldest time that can be flashed back to use,

SQL> ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SQL> SELECT oldest_flashback_time FROM v$flashback_database_log;

Note: When an archivelog is deleted, it would also delete the flashback logs that are dependent on the archived log.

Flashback Drop

Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle will place it in a Recycle Bin. Objects in the Recycle Bin will remain there until user decides to permanently remove them or the space pressure is placed on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users can look in the Recycle Bin and undrop the dropped table and its dependent objects.

THE RECYCLE BIN

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created.If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$.It stays in the same tablespace, with the same structure as that of the original table.

Any dependent sources such as procedures are invalidated. You can continue to access the data in a dropped table or even use Flashback Query against it.

Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view.

Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command.

The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if,

A user creates a new table or adds data that causes their quota to be exceeded.
The tablespace needs to extend its file size to accommodate create/insert operations.

There are no issues with dropping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace/schema after the drop.

In order to have FLASHBACK DROP functionality a recyclebin is provided to every oracle user.

Recyclebin does not work for SYS owned objects

Usefull commands

SQL>show recyclebin

SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

The recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table.

SQL>SELECT SUBSTR(object_name,1,50) as object_name,object_type,owner FROM dba_objects WHERE object_name LIKE '%RECYCLEBIN%';

OBJECT_NAME	OBJECT_TYPE	OWNER
RECYCLEBIN$	TABLE	SYS
RECYCLEBIN$_OBJ	INDEX	SYS
RECYCLEBIN$_TS	INDEX	SYS
RECYCLEBIN$_OWNER	INDEX	SYS
USER_RECYCLEBIN	VIEW	SYS
DBA_RECYCLEBIN	VIEW	SYS
CDB_RECYCLEBIN	VIEW	SYS
USER_RECYCLEBIN	SYNONYM	PUBLIC
RECYCLEBIN	SYNONYM	PUBLIC
DBA_RECYCLEBIN	SYNONYM	PUBLIC
CDB_RECYCLEBIN	SYNONYM	PUBLIC

PURGING

In order to completely remove the table from the DB and to release the space the new PURGE command is used.

SQL>PURGE TABLE table_name;

SQL>PURGE INDEX index_name;

SQL>PURGE recyclebin; (The current users entire recycle bin.)

SQL>PURGE dba_recyclebin; (Purge whole objects / only SYSDBA can)

SQL>PURGE TABLESPACE users; (Purge all objects of the tablespace)

SQL>PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)

For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.

We can DROP and PURGE a table with a single command

Eg: DROP TABLE t1 PURGE;

On 10gR2 and higher; recyclebin is a initialization parameter and bydefault its ON.We can disable recyclebin by using the following commands,

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.

Note: When a table is dropped and moved to the recycle bin, a system generated name is used to identify the table and dependents which looks like 'BIN$xxxx'. When a table is restored from the recycle bin, we restore the table and rename it with its original name. Even dependent objects like indexes, constraints, or triggers are also restored, but their original names are not restored, which means their names still look like 'BIN$xxxx'. Once the restore operation is completed, there is no way in the database where we can get the original name of these dependent objects. The user has to make sure to remember / note down their original names and run the relevant ALTER command with the RENAME option to get the original names to the dependents. (from note 433768.1)

Tables with Fine Grained Access policies aer not protected by the recycle bin.
The recycle bin does not preserve referential integrity.

Flashback Table

Flashback Table is a new Oracle Database 10g feature that enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Flashback Table alleviates the need for administrators to perform more complicated point in time recovery operations. Even after a flashback, the data in the original table is not lost. You can later revert it back to the original state.

Automatically restores all of the table attributes, such as indexes, triggers, and the likes that are necessary for an application to function with the flashed-back table.
Maintains data integrity as specified by constraints.

Note: You must be using automatic undo management to use the flashback table feature. It is based on undo information stored in an undo tablespace. In addition, set the UNDO_RETENTION initialization parameter to an interval large enough to include the oldest data you anticipate needing.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

To use the FLASHBACK TABLE statement you must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table. Additionally, you must have SELECT, INSERT, DELETE, and UPDATE privileges on the table.

The table that you are performing the flashback operation on must have row movement enabled.

Usage

SQL>flashback table TABLE_NAME to scn 332348;
SQL>flashback table xxx to timestamp to_timestamp('2012-09-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS') ;

In the following example, we'll pick a time just two minutes previously,

SQL>FLASHBACK TABLE t TO TIMESTAMP SYSTIMESTAMP - INTERVAL '2' MINUTE;

Note

By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.

Having created our table, we should wait a few minutes to allow the SCN to advance. Attempting to flashback a new table straight away can result in ORA-01466: unable to read data - table definition has changed.

Flashback Query

A database query, by default, always shows a consistent set of data containing most recently committed changes. With Flashback Query, introduced in Oracle9i, users are able to query a consistent version of the database as it was at some point in the past either by specifying a wall clock time or a System Change Number (SCN).Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause (9i R2 onwards) . Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle 9i R1, but in a more convenient form.

Requirements

To be able to use flashback query, we require the following system elements,

undo_management=auto (set in pfile/spfile);
undo_retention=n (set in pfile/spfile, where n is a positive number of seconds);
undo_tablespace=[undo tablespace name] (set in pfile/spfile);
FLASHBACK or FLASHBACK ANY system privilege; and
EXECUTE on DBMS_FLASHBACK.

Use case

SELECT COUNT(*) FROM Table_name AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

Recovering data – Using flashback query

The 9i Release 2 flashback query syntax makes it much easier to recover data. Using the AS OF syntax, we can either update the table from the flashback query source or we can delete the current data and insert the flashback data. Consider following simple example

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

134253464

SQL> DELETE FROM t;

5 rows deleted.

SQL> COMMIT;

Commit complete.

Now we will recover this table

SQL> INSERT INTO t SELECT * FROM t AS OF SCN 134253464;

5 rows created.

SQL> COMMIT;

Commit complete.

One more example,

Time 10:30: one row where ename = "YASH" is deleted from table emp

Time 11:30: Detected that it was a mistake and the row is required back and it needs to be put back to the table.Now, this feature of "Oracle Flashback Query" can be used to query the state of a table at a previous time. The contents of the table can be queried as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the "AS OF" clause of the SELECT statement.For example, the following query retrieves the state of the employee record for 'YASH' at 10:00AM, March 19, 2004,

SQL> SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2003-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'YASH';

Restoring YASH's information to the table EMP requires the following update,

SQL> INSERT INTO emp
(SELECT * FROM emp AS OF TIMESTAMP
TO_TIMESTAMP('2004-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')WHERE name = 'YASH');

Flashback Versions

Flashback Versions Query is an extension to SQL that allows you to retrieve the different versions of rows in a given table that existed in a specific time interval. It returns a row for each version of the row that existed in the time interval you specify. For any given table, a new row version is created every time the COMMIT statement is executed. You specify the flashback versions query using the VERSIONS BETWEEN clause of the SELECT statement.Like flashback query ,flashback version also utilze undo data.

Here is an example,

SQL> CREATE TABLE flashback_version_query (id NUMBER(10), description VARCHAR2(25));

Table created.

SQL> INSERT INTO flashback_version_query (id, description) VALUES (1, 'Mahesh');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
134269619 2016-07-01 18:56:42

SQL> UPDATE flashback_version_query SET description = 'Bhaskar' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> UPDATE flashback_version_query SET description = 'Anita' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from flashback_version_query;

ID DESCRIPTION
---------- -------------------------
1 Anita

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
134269750 2016-07-01 18:57:42

SQL> COLUMN versions_startscn FORMAT 99999999999999999
SQL> COLUMN versions_starttime FORMAT A24
SQL> COLUMN versions_endscn FORMAT 99999999999999999
SQL> COLUMN versions_endtime FORMAT A24
SQL> COLUMN versions_xid FORMAT A16
SQL> COLUMN versions_operation FORMAT A1
SQL> COLUMN description FORMAT A11
SQL> SET LINESIZE 200
SQL>
SQL>
SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 description
5 FROM flashback_version_query
6 VERSIONS BETWEEN
7 TIMESTAMP TO_TIMESTAMP('2016-07-01 18:56:42', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2016-07-01 18:57:42', 'YYYY-MM-DD HH24:MI:SS')
9 WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
134269709 01-JUL-16 06.57.23 PM 0A00100015850500 U Anita
134269677 01-JUL-16 06.57.08 PM 134269709 01-JUL-16 06.57.23 PM 0A000F0075870500 U Bhaskar
134269677 01-JUL-16 06.57.08 PM Mahesh

SQL>

The available pseudo columns are,

VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.
VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
VERSIONS_XID - ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION - Operation performed by the transaction, e.g. (I)nsert, (U)pdate or (D)elete)

Flashback Transaction

Flashback Transaction Query provides a way for you to view all changes made to the database at the transaction level. When used in conjunction with Flashback Versions Query, it allows you to easily recover from user or application errors. Flashback Transaction Query shows all 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 like this, the DBA and application developer can precisely diagnose and correct logical problems in the database or application.

So Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY. The UNDO_SQL column contains SQL text that is the logical opposite of the DML operation performed by the given transaction.

SQL> col UNDO_SQL for a110
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A000F0075870500'); 2

XID OPERATION START_SCN COMMIT_SCN LOGON_USER
---------------- -------------------------------- ---------- ---------- ------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------
0A000F0075870500 UPDATE 134269671 134269677 MAHI
update "MAHI"."FLASHBACK_VERSION_QUERY" set "DESCRIPTION" = 'Mahesh' where ROWID = 'AABB0QAAEAAAAFEAAA';

0A000F0075870500 BEGIN 134269671 134269677 MAHI

SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A00100015850500'); 2

XID OPERATION START_SCN COMMIT_SCN LOGON_USER
---------------- -------------------------------- ---------- ---------- ------------------------------
UNDO_SQL

--------------------------------------------------------------------------------------------------------------
0A00100015850500 UPDATE 134269705 134269709 MAHI
update "MAHI"."FLASHBACK_VERSION_QUERY" set "DESCRIPTION" = 'Bhaskar' where ROWID = 'AABB0QAAEAAAAFEAAA';

0A00100015850500 BEGIN 134269705 134269709 MAHI