Monday, March 8, 2010

Oracle: Flashback Technology – Recovering dropped objects


Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, flashback Table.
When you drop a table by default it is stored in oracles recyclebin like windows. And you can restore the table with flashback command easily;

You can use the flashback technology at the database, table, and transaction levels:
• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data).

What is the Recycle Bin?

The Recycle Bin is a virtual container where all dropped objects reside 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$$.

Flashback Drop:

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

--Create a dummy table for testing purpose
CREATE TABLE test_flashback
AS
SELECT object_name ,object_type
FROM user_objects
WHERE ROWNUM <=100
ORDER BY dbms_random.value
/
SELECT * FROM test_flashback
/
--Drop the table
DROP TABLE test_flashback
/
SELECT * FROM test_flashback
/
--Following ERROR will be displayed
--ORA-00942: table or view does not exist
/
--Search in RECYCLEBIN
SELECT * FROM RECYCLEBIN
WHERE upper(original_name) = 'TEST_FLASHBACK' ;
/
--To recover the recently dropped version of the table from flashback memory
FLASHBACK TABLE test_flashback TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.
/
FLASHBACK TABLE test_flashback TO BEFORE DROP;
/
SELECT * FROM test_flashback
/
--You can retrieve the table and assign a new name using a RENAME TO clause
FLASHBACK TABLE test_flashback TO BEFORE DROP RENAME TO test_flashback_version1;
/
SELECT * FROM test_flashback_version1
/
-- The following command recovers the test_flashback table version to 2:33 PM on July 7.
FLASHBACK TABLE test_flashback TO TIMESTAMP ('JUL-07-2003, 02:33:00')
/

For More information on Oracle Flashback please visit : Oracle Flaback Technology

1 comment:

Salman Badar said...

Awsome, I updated my blog with this article.

Post a Comment