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.
No comments:
Post a Comment