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

Last updated on APRIL 20, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 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: '/home/oracle/nish01.dbf'

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

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

FILE#      FILE_NAME                                          TS#        TS_NAME             FLA
---------- -------------------------------------------------- ---------- ------------------- ---
1          /u01/app/oracle/oradata/d10gr2/system01.dbf        0          SYSTEM              YES
2          /u01/app/oracle/oradata/d10gr2/undotbs01.dbf       1          UNDOTBS1            YES
3          /u01/app/oracle/oradata/d10gr2/sysaux01.dbf        2          SYSAUX              YES
4          /u01/app/oracle/oradata/d10gr2/users01.dbf         4          USERS               YES
5          /u01/app/oracle/oradata/d10gr2/10g_isc_corr.dbf    5          ISC_CORR            YES
6          /home/oracle/tbs02.dbf                             6          TBS                 YES
7          /home/oracle/tbs01.dbf                             6          TBS                 YES
8          /home/oracle/nish01.dbf                            7          NISH                NO
9          /u01/app/oracle/oradata/d10gr2/streams_tbs.dbf     9          STREAMS_TBS         YES

9 rows selected.


If we try to enable flashback for tablespace, still appears the same error :

SQL> alter tablespace nish flashback on ;
Tablespace altered.

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: '/home/oracle/nish01.dbf'


Again, if we take this datafile offline, we cann't 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: '/home/oracle/nish01.dbf'


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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms