Flashback Database Support for Shrink Datafile in Oracle 21c

Prior to Oracle 21c, it was not possible to flashback the database to a point before the shrink datafile operation.

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Sep 15 17:25:54 2021

Version 19.9.0.0.0

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON

———– ——————

    2557655 YES

SQL> select bytes from v$datafile where file#=1;

     BYTES

———-

 999153664

SQL> alter database datafile 1 resize 996147200;

Database altered.

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 2.0200E+10 bytes

Fixed Size                 19766544 bytes

Variable Size            2617245696 bytes

Database Buffers         1.7515E+10 bytes

Redo Buffers               47341568 bytes

Database mounted.

SQL>  flashback database to scn 2557655;

ORA-38766: cannot flashback data file 1; file resized smaller

ORA-01110: data file 1:

‘/oracle19cR9/base/oradata/DB19R9/datafile/o1_mf_system_jn3s5rmk_.dbf’

In Oracle version 21c, an improvement has been made in this area, and now it is possible to flashback the database even after performing the shrink operation.

SQL*Plus: Release 21.0.0.0.0 – Production on Wed Sep 15 04:47:02 2021

Version 21.3.0.0.0

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON

———– ——————

   27241819 YES

SQL> select bytes from v$datafile where file#=1;

     BYTES

———-

1547042816

SQL> alter database datafile 1 resize 1447034880;

Database altered.

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 2634021776 bytes

Fixed Size                  9690000 bytes

Variable Size            1442840576 bytes

Database Buffers          889192448 bytes

Redo Buffers              292298752 bytes

Database mounted.

SQL> flashback database to scn 27241819;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.