Flashback database to restore point

I use this procedure when it is needed to test some DDL and DML statements on a test database. If changes did not work properly, you can flashback database to a point in time before changes started.

SQL> SELECT INST_ID, flashback_on, log_mode FROM gv$database;

   INST_ID FLASHBACK_ON       LOG_MODE
---------- ------------------ ------------
         1 NO                 NOARCHIVELOG

So, it is needed to activate flashback and archivelog mode

to do that, please see Activate archivelog mode on a database

Second step is to enable flashback:

SQL> alter database flashback on;

Database altered.

And after that, we can see RVWR oracle process is started.

[oracle@ODIGettingStarted flash_recovery_area]$ ps -ef | grep -i rvwr
oracle   10163     1  0 06:04 ?        00:00:00 ora_rvwr_orcl

To make sure we can rollback database to restoration point, we make a guarantee restore point, witch means that database will stop in case flashback destination becomes full..

SQL> create restore point my_restore_point guarantee flashback database;

Restore point created.

SQL> select scn,name, TIME,PRESERVED  from v$restore_point;

       SCN NAME                                     TIME                                                                        PRE
---------- ---------------------------------------- --------------------------------------------------------------------------- ---
   5676692 MY_RESTORE_POINT                         15-SEP-17 05.53.41.000000000 AM                                             YES

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

sql> select flashback_size, estimated_flashback_size from v$flashback_database_log;

We should monitor filesystem occupation during this by;

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      4.88                         0               2
FOREIGN ARCHIVED LOG                  0                         0               0

SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;

NAME                                           LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIM TYPE
---------------------------------------- ---------- ---------- ---------- ---------- ------------- --------- ---------
/u01/app/oracle/flash_recovery_area/ORCL          1          1          1   52428800       5676654 15-SEP-17 NORMAL
/flashback/o1_mf_dvq8q83j_.flb

/u01/app/oracle/flash_recovery_area/ORCL          2          1          1   52428800             0           RESERVED
/flashback/o1_mf_dvq8qfdy_.flb

And using df command on unix.

[oracle@ODIGettingStarted flash_recovery_area]$ df -h /u01/app/oracle/flash_recovery_area/ORCL/flashback/
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb               15G   13G  1.1G  93% /u01

If we want to rollback database to restore point, we should to:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             822084488 bytes
Database Buffers          239075328 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT MY_RESTORE_POINT;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Or, finally, if it is not needed to flashback database, we should drop guarantee restoration point, in order stop allocating flashback logs.

SQL> drop restore point MY_RESTORE_POINT;

Restore point dropped.