Before enable the flashback we need to take our database in archive log mode. To enable the archive log click here
After enabling the archive logs now we need to set 3 paramaters for flashback are
1 db_recovery_file_dest
2 db_recovery_file_dest_size
3 db_flashback_retention_target
>>This parameter "db_recovery_file_dest" is used to set the destination where we want to store
the location of flashback logs. The location of flashback logs may or maybe be same as the location
of archive logs, here i gave different location from archive logs.
>>The parmeter "db_recovery_file_dest_size" is used to define the size that can be used by
flashback directory/folder provided with above parameter. This size can be calculated on the basis
of transactions happened in our database and if archive logs, only when the destination of archive logs
& flashback logs are same.
>>The parameter "db_flashback_retention_target " is used to provide time limit upto which
flashback logs should be in the memory of database, So that database can be flashed up back
with in time limit. Time is provided in minutes, so we set retention time 1440 which is about a day.
>>Set flashback database parameters
alter system set db_recovery_file_dest='E:\app\navneet\FRA' scope=spfile;
alter system set db_recovery_file_dest_size=20G scope=spfile;
alter system set db_flashback_retention_target=1440 ;
>> Turn flashback on for the database
shutdown immediate;
startup mount;
alter database flashback on;
alter database open;
>>Now check if flashback is enabled
select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Now Flashback is ENABLED.
>>To disable flashback
alter system set db_recovery_file_dest=" scope= both sid='*';
Some related queries are as follow
>> To check the flashback logs
select * from v$flashback_database_log;
>>To check the estimated flashback size
SELECT ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024) FROM V$FLASHBACK_DATABASE_LOG;
>>To create a guaranteed restore point
create restore point RESTORE_POINT guarantee flashback database;
select NAME, SCN, TIME from V$RESTORE_POINT;
>>To flashback the database to specific time
flashback database to timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 'DD/MM/YYYY
HH24:MI:SS');
>>To drop a restore point
select NAME, SCN, TIME from V$RESTORE_POINT;
drop restore point RESTORE_POINT;
The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help to find out the current location, disk quota, space in use, space reclaimable by deleting files,
total number of files, the percentage of the total disk quota used by different types of files.
Your suggestions and queries are always warm welcomed.
