How to Flashback Database to Guaranteed Restore Point in Oracle

I will explain How to Flashback Database to Guaranteed Restore Point in this article. Let’s review the Oracle Flashback Restore Point.

 

Oracle Flashback Restore Point

If you don’t know what is the Flashback in Oracle database, read the following articles.

Oracle Flashback Architecture

Oracle Flashback Database

 

 

 

 

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;

 

You can use the Restore point features in the Snapshot standby in Dataguard as follows.

Snapshot Standby ( Read Write ) in Oracle Dataguard

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/