Flashback must be turned on in order to be able to use Guaranteed Restore Point. To enable Flashback, we need to use the FRA parameter like following.
SQL> show parameter db_recovery_file NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /u01/recovery_area db_recovery_file_dest_size big integer 2022043K
Flashback is enabled as follows. This can be done in case in mount mode.
SQL> alter database flashback on; Database altered.
You can create a guaranteed restore point as follows.
SQL> create restore point before_open guarantee flashback database; Restore point created.
When we want to flash back to any point of database, we will use this restore point.
Opened the database in Read write mode, then perform some database operations for Deployment purpose, test operations or Disaster purpose
SQL> create user mehmet identified by salih; User created. SQL> grant dba to mehmet; Grant succeeded.
If Disaster tests or any other tests are completed and you want to flash back database into that point again, then you should perform the following steps.
Open it in Mount mode
SQL> startup mount force; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 163580104 bytes Database Buffers 360710144 bytes Redo Buffers 7942144 bytes Database mounted.
Firstly list the restore points as follows.
select * from v$restore_point;
You can check how long this Flashback operation work, you can see its estimated time as follows.
set pages 100 set line 200 column message format a50 select sid,message from v$session_longops where sofar <> totalwork; set linesize 400 col DB_DATA for 999999999999 col FLASHBACK_SIZE for 999999999999 SELECT TO_CHAR(A.BEGIN_TIME,’DDMMYY HH24:MI’) “FLASH BEGIN”,TO_CHAR(A.END_TIME,’DDMMYY HH24:MI’) “FLASH END”,A.DB_DATA, B.FLASHBACK_SIZE, (A.DB_DATA/B.FLASHBACK_SIZE)*100 “% COMPLETE” FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;
Flash back database to related restore point as follows.
SQL> flashback database to restore point before_test_29032020; Flashback complete.
Now open database in open resetlogs mode.
SQL> alter database open resetlogs;
You can drop the restore point as follows.
SQL> DROP RESTORE POINT before_test_29032020;
