Enabling Flashback Database

To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.

1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SELECT STATUS FROM V$INSTANCE;

2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

3. Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE test1 FLASHBACK OFF;


You can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE test1 FLASHBACK ON;


Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.

Requirements for Flashback Database

The requirements for enabling Flashback Database are:

* Your 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.
* For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

Oracle Flashback Table: Returning Individual Tables to Past States

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

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 of data.

Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

* You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
* You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
* Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;

Performing Flashback Table

The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:

FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The employee table is restored to its state when the database was at the time specified by the timestamp.

You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:

FLASHBACK TABLE employee TO SCN 123456;

The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:

FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;

The following scenario is typical of the kind of logical corruption where Flashback Table could be used:

At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;

Source: http://www.stanford.edu

Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table 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 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

Oracle Flashback Technology: Overview

Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.

* Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
* Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
* Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
* Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.

* Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
* Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.

Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.

Flash Back Feature..

1. How to delete a table permanently?
Usage: drop table purge
eg: drop table emp purge;

2.How to delete a table from recycle bin?
Usage: purge < table name >
eg: purge emp;

3. How to recover a table from recycle bin?
Usage: flashback table "HBKLOEKIMO$#^^&777" to before drop;
Usage1:flashback table "HBKLOEKIMO$#^^&777" to before drop rename to emp1;

4.How to remove all objects in recyclebin?
Usage: purge recyclebin

5. How to show all objects in recyclebin?
Usage: show recyclebin