Flashback Tech

In Oracle 10g, the flashback functionality has been greatly extended.

Flashback Database

Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle databa se server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

RVWR Background Process

When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.



RVWR Background process and Flashback Database Logs,

The list below shows all the background processes for the 'grid' instance.

$ ps -ef | grep grid

oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
Enabling Flashback Database

You can enable Flashback Database using the following steps:

1. Make sure the database is in archive mode.

2. Configure the recovery area by setting the two parameters:

- db_recovery_file_dest

- db_recovery_file_dest_size

3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:

SQL> STARTUP MOUNT EXCLUSIVE;

SQL> ALTER DATABASE FLASHBACK ON;

4. Set the Flashback Database retention target:

- db_flashback_retention_target

5. Determine if Flashback Database is enabled:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
YES

Flashback Drop

Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.

This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

Recycle Bin

A recycle bin contains all the dropped database objects until,

- You permanently drop them with the PURGE command.

- Recover the dropped objects with the UNDROP command.

- There is no room in the tablespace for new rows or updates to existing rows.

- The tablespace needs to be extended.


You can view the dropped objects in the recycle bin from two dictionary views:

- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects

Example :

QL> show user
USER is "SCOTT"
SQL>
SQL> create table tst (a int);

Table created.

SQL> select object_name,original_name,operation,type from user_recyclebin;

no rows selected

SQL> drop table tst;

Table dropped.

SQL> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE

SQL> create table tst (a int);

Table created.

SQL> drop table tst;

Table dropped.

SQL> select object_name,original_name,operation,type from user_recyclebin

SQL> /

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
BIN$NPgvUqA422DgQKjAAwIuMw==$0 TST DROP TABLE

Restoring a Dropped Object

SQL> flashback table TST to before drop;

Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
TST TABLE

6 rows selected.

SQL> select object_name,type from user_recyclebin;

OBJECT_NAME TYPE
------------------------------ -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE

Dropping a Table Permanently

SQL> DROP TABLE TST PURGE;

Table dropped.

SQL> purge table tst;

Table purged.


SQL> purge recyclebin;

Recyclebin purged.


SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-01031: insufficient privileges

This statement purges all objects from tablespace users in the recycle bin

SQL> purge tablespace users;

Tablespace purged.

SQL>


Flashback Table

Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.

Flashback Table operations are not valid for the following object types:

- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)

Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.

However, if the following DDL commands are issued, the flashback table command does not work:

- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE
- ALTER TABLE ... MOVE

Flashback versions
Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.

You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.

Example:

SQL> CREATE TABLE BABU (S INT);

Table created.

SQL> INSERT INTO BABU VALUES (123);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
575002

SQL> UPDATE BABU SET S=321;

1 row updated.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
575090

SQL> DELETE FROM BABU WHERE S=321;

1 row deleted.

SQL> L
1* DELETE FROM BABU WHERE S=321
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
575105

SQL>
SQL> INSERT INTO BABU VALUES (258);

1 row created.

SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
575112

SQL> UPDATE BABU SET S=456;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
575120


SQL> select versions_startscn as startscn, versions_endscn as endscn, versions_xid as xid,
2* versions_operation as oper, s from babu versions between scn 575002 and 575120
SQL> /

STARTSCN ENDSCN XID O S
---------- ---------- ---------------- - ----------
575118 09000E003A010000 I 456
575118 09000E003A010000 D 123
575118 123

Flashback Transaction

The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.

You can retrieve the transaction history from dba_transaction_query view:

Example :

SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3* where xid= '09000E003A010000'

SQL> /

START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
574990 575118 SCOTT UPDATE BABU
update "SCOTT"."BABU" set "S" = '258' where ROWID = 'AAAM0ZAAEAAAAGEAAB';

574990 575118 SCOTT INSERT BABU
delete from "SCOTT"."BABU" where ROWID = 'AAAM0ZAAEAAAAGEAAB';

574990 575118 SCOTT DELETE BABU
insert into "SCOTT"."BABU"("S") values ('321');