Workaround for Flashback Database fails with ORA-38753 ORA-01110 (Doc ID 982104.1)

Symptoms

Flashback is turned off for one or more tablespaces to avoid unnecessary flashback logs generation.

Now there is a need to do a 'FLASHBACK DATABASE' to some previous point in time to recover huge wrongly updated data
due to an incorrect run of batch job.

However, FLASHBACK DATABASE can not work if flashback is off for any of the tablespaces. It produces following errors :

SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 8; no flashback log data.
ORA-01110: data file 8: '/<path>/<filename>.dbf'

While querying this datafile, we come to know that the flashback is turned off ( column flashback_on = NO ) for corresponding tablespace:

  

Again, if we take this datafile offline, we can not  get it back when OPEN RESETLOGS is done after FLASHBACK DATABASE :

SQL> alter database datafile 8 offline ;

Database altered.

SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 8 will be lost if RESETLOGS is done
ORA-01110: data file 6: '/<path>/<filename>.dbf'


Performing a TSPITR ( Tablespace Point in Time Recovery ) takes longer if multiple tablespaces are involved for recovery.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.