oracle-databaserecoveryflashbackrestore-pointspoint-in-time-recovery

Does a Restore Point Persist after Flashback/Point-in-time?


If I have created a restore point my_restore_point

create restore point my_restore_point;

then flashback to this restore_point

flashback database to restore point my_restore_point;
  1. Will my_restore_point still exist?
select * from v$restore_point where name = 'MY_RESTORE_POINT';
  1. Will my_restore_point still exist if I flashback to the restore point's SCN?
variable my_scn number
exec select scn into :my_scn from v$restore_point where name = 'MY_RESTORE_POINT'
flashback database to scn :my_scn;
  1. Will my_restore_point still exist if I flashback to before the restore point's SCN?
flashback database to before scn :my_scn;
  1. Will my_restore_point still exist if I perform an RMAN point-in-time recovery UNTIL SCN x, where x is the value in :my_scn?

If I had an environment in which to test this I would attempt these tests myself, so my advanced gratitude to this community for the assist.

Using Oracle Database 19c Enterprise Edition Release 19


Solution

  • Restore points need to be available during MOUNT phase, so they are not like tables etc that are "rewound" during a flashback, eg

    SQL> create restore point rp1 guarantee flashback database;
    
    Restore point created.
    
    SQL> create table t1 ( x int );
    
    Table created.
    
    SQL>
    SQL> create restore point rp2 guarantee flashback database;
    
    Restore point created.
    
    SQL> create table t2 ( x int );
    
    Table created.
    
    SQL>
    SQL> select name, scn from v$restore_point;
    
    NAME                                            SCN
    ------------------------------ --------------------
    RP1                                  16331017175532
    RP2                                  16331017175547
    
    SQL>
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area 3221224152 bytes
    Fixed Size                  9304792 bytes
    Variable Size            2801795072 bytes
    Database Buffers          385875968 bytes
    Redo Buffers               24248320 bytes
    Database mounted.
    SQL>
    SQL> flashback database to restore point rp2;
    
    Flashback complete.
    
    SQL> select name, scn from v$restore_point;
    
    NAME                                            SCN
    ------------------------------ --------------------
    RP1                                  16331017175532
    RP2                                  16331017175547
    
    SQL>
    SQL> flashback database to restore point rp1;
    
    Flashback complete.
    
    SQL> select name, scn from v$restore_point;
    
    NAME                                            SCN
    ------------------------------ --------------------
    RP1                                  16331017175532
    RP2                                  16331017175547