My Oracle Support Banner

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

Last updated on DECEMBER 12, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 03-May-2013***


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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
 Example Test-case as a workaround for ORA-38753
References


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