1. Flashback Query,
2. Flashback Database,
3. Flashback Drop
4. Flashback Table.
1. Flashback Query –
Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the employees table, and you know that at 9:30AM the data for Chung was
correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost.
If appropriate, you can restore the lost data.
Below query retrieves the state of the record for Chung at 9:30AM, April 4, 2004.
Retrieving a Lost Row with Oracle Flashback Query
SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP(‘2004-04-04 09:30:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE last_name = ‘Chung’;
In case you need to restore the lost record, you combine the above select with an insert as mentioned in the below insert query
INSERT INTO employees (
SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP(‘2004-04-04 09:30:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE last_name = ‘Chung’
);
2. Flashback Database –
Flashback Database allows you to restore your database to a prior point in time by undoing all the changes that have taken place since that time. This is different than a Point-In-Time Recovery in that you don’t restore a backup and then roll forward, instead you “rewind” the database by applying flashback logs. This operation can be very fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Keep in mind that this is NOT a replacement for regular backups (preferably rman hot backups) as it DOES NOT protect you in the case of media failure, filesystem corruption, accidental deletion or anything else that effects the physical data files. It is used to undo logical data corruption, such as from a failed application upgrade. It can also be very useful in Test databases where you want to test a process repeatedly, starting with an identical data set each time. There is overhead associated with generating and managing the flashback logs but for many databases, this is well worth it.
In order to enable Flashback Database, put the database is mount mode and run “alter database flashback on”. The flashback logs will be written to your FRA as defined by the db_recovery_file_dest parameter. The db_flashback_retention_target parameter defines how long you wish to save your flashback logs.
Here is an example of enabling Flashback Database, creating a restore point and then flashing back to that restore point:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
YES
SQL> create restore point flashback_test;
Restore point created.
SQL> select name,time from v$restore_point;
NAME TIME
——————– —————————————————————————
FLASHBACK_TEST 17-APR-12 09.17.46.000000000 AM
SQL> create table flashback_test as select * from all_objects;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point flashback_test;
Flashback complete.
NOTE: You can also flashback to a given time or SCN as well as to a restore point
SQL> alter database open resetlogs;
Database altered.
SQL> select * from flashback_test;
select * from flashback_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
The table we created after the restore point is no longer there; the database was “rewound” to a prior point in time.
Related to this is the guaranteed restore point, which can be created even if Flashback Database has not been enabled.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO
SQL> create restore point restore_point_test guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point restore_point_test;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> drop restore point restore_point_test;
Once you are confident that you won’t be rolling back, make sure to drop the restore point or you will continue to generate flashback logs.
3. Flashback Drop –
It allows you to recover a non-SYS table that was dropped by making use of the Recycle Bin.
This is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created.
You can view your dropped tables by querying the new RECYCLEBIN view.
Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command.
SQL> drop table drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
DROP_TEST BIN$veGmdD1Yy9/gQKjAYIkPIg==$0 TABLE 2012-04-17:10:21:10
SQL> flashback table drop_test to before drop;
Flashback complete.
SQL> drop table drop_test purge;
Table dropped.
SQL> show recyclebin;
SQL> flashback table drop_test to before drop;
flashback table drop_test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
4. Flashback Table –
This can be used to restore data in a table to past point in time, based upon time or SCN.
This is similar to the Flashback Query in that it relies on the data being present in the UNDO tablespace.
SQL> create table flashback_test (col1 number);
Table created.
SQL> alter table flashback_test enable row movement;
Table altered.
–insert a bunch of rows
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1048161
SQL> select count(*) from flashback_test;
COUNT(*)
———-
192
SQL> delete from flashback_test;
192 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table flashback_test to scn 1048161;
Flashback complete.
SQL> select count(*) from flashback_test;
COUNT(*)
———-
192
