Prerequisites for Flashback recovery:
- Must have FLASHBACK ANY TABLE system privilege or must have FLASHBACK object privilege on the table.
- Must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
- Row movement must be enabled on the table.
SQL>ALTER TABLE ENABLE ROW MOVEMENT;
Flashback Drop:( Reverses the effects of a DROP TABLE statement)
Reverse the Drop table ->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;
Assign a new name to the restored table->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO ;
Flashback Table: (Reverses a table to its state at a previous point in time)
Case:
At 01:00 PM , we discovers that some rows/records is missing from the EMP table. This record was present at 11:00 AM. Someone accidentally deleted the record from EMP table between 11:00 AM to 01.00 PM. We can return the table to it’s stat at 11.00 AM by Flashback table.
Example:
FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP('2009-01-02 14:14:13','YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS; OR
FLASHBACK TABLE EMP TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
Flashback Query: (We can specify a target time and then run queries, viewing results and recover from an unwanted change)
Case:
At 02:00 PM, we discover that some records deleted from the EMP table and we know that at 9:30AM that data was correctly stored in the database, We 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.
Example:
Retrives the state of the record at 9:30AM.
SQL> SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2009-01-01 09:30:00', 'YYYY-MM-DD HH:MI:SS');
Restoring information to the table EMP (Missing Data)
INSERT INTO emp (SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2009-01-01 09:30:00', 'YYYY-MM-DD HH:MI:SS');
Flashback Transaction Query: (We can view changes made by a transaction during a period of time.)
Case:
At 03:00 PM, we discover that some records deleted from the EMP table and we know that at 12:00 PM that data was correctly stored in the database, We could query the contents of the table data between 12:00 PM and 03:00PM
SQL>
SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY WHERE xid IN (SELECT versions_xid FROM emp VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS') and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS') );
Flashback database: (We can revert database at a past time)
FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');
