Flashback Table In Oracle

Flashback Table In Oracle

-we can recover a table to a specific timestamp without downtime.
-we can flashback to the original table.
-we can flashback to a new table
-we can flashback query to identify if undo segments have the old data.

flashback table is enabled by-default.

example:

insert into scott.dept values(&dept,'&ename','&loc');
commit;
alter system checkpoint;

query to check:

select count(*) from scott.dept as of timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');

If above query will shows any data, then we can recover the table till that time.

1.flashback to a different table.

create table scott.dept_temp as select * from scott.dept as of timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');
select * from scott.dept_temp;

2.flashback to a original table.

alter table scott.dept enable row movement;
flashback table scott.dept to timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');
alter table scott.dept disable row movement;

If user drops a table.
drop table scott.dept_temp;

flashback table scott.dept_temp to before drop;

Note: if a user truncte the table, then you cannot use flashback.

Flashback database:

flashback database is not enabled by-default.

To check:
select flashback_on from v$database;

Important parameters of flashback database:

show parameter db_recovery
db_recovery_file_dest
db_recovery_file_dest_size
show parameter flash
db_flashback_retention_target ---by-default 1440 mins

How to enable flashback:

Till 10g version, flashback will be enabled in mount state, but in 11g version we can enabled it open state.
alter database flashback on;

Note: when you enable flashback, one directory is automatically created with flashback name in fra location, and it is pre-requisite to set fra location before we enabled the flashback.

insert into scott.dept values(&dept,'&ename','&loc');
commit;

Note: To performing the flashback database technique, your database should be open in mount state.

shutdown immediate;
startup mount;
rman target /
flashback database to time "TO_TIMESTAMP('27-11-2019 10:01:00','DD-MM-YYYY HH24:MI:SS')";

alter database open resetlogs;
alter database open;

Restore point:

-application release
-upgradation of database
-application upgradation
-ETL

create restore point A;
shutdown immediate;
startup mount;

rman target /
flashback database to restore point A;
alter database open resetlogs;

Note: restore point will be stored based on your retention period.

create restore point B guarantee flashback database;

Note: It will not dependant on retention period, and stored the restore points until you drop them(useful for longer applications)

shutdown immediate;
startup mount;
rman target /
flashback database to restore point B
alter database open resetlogs;
select name, guarantee_flashback_database from v$restore_point;
drop restore point B;

Note: even after you purging the recycebin it will hold the dropped values based on your retention period.