Sunday, January 01, 2006

Oracle Flashback Technology is an absolutly fabulous feature

As you know, we're living in "Information Era", so data and informatiom is one of the most wealthy thing of us. Consequently, Backup and Recovery of this data is one of the major problem of DBAs. Oracle 10g represent a new facility to solve this problem easily, it is called "Oracle Flashback Technology". It faster and more efficient than Point_in_Time recovery. This technology consists of 6 types:
• Oracle Flashback Query
• Oracle Flashback Version Query
• Oracle Flashback Transaction Query
• Oracle Flashback Table
• Oracle Flashback Drop
• Oracle Flashback Database

Oracle Flashback Query: Recovering at the Row Level
If you know 'JOHN' was at 09:30:00, but it is deleted, you can find it as below:
SQL> SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP ('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
You can insert deleted 'JOHN' to EMPLOYEE as below:
SQL> 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');

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

Oracle Flashback Drop: Undo a DROP TABLE Operation
SQL> show recyclebin;
SQL> SELECT object_name as recycle_name, original_name, object_type
FROM recyclebin;

Oracle Flashback Database
RMAN> FLASHBACK DATABASE
TO TIME to_timestamp ('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
SQL> FLASHBACK DATABASE
TO TIME to_timestamp ('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');