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
>>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
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
>>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 at email@example.com.