ORA-38753: Cannot flashback data file; no flashback log data.

Mehmet Salih Deveci May 24, 2022 Leave a comment

I got ” ORA-38753: Cannot flashback data file; no flashback log data. ” error in Oracle database.

 

ORA-38753: Cannot flashback data file; no flashback log data

Details of error are as follows.

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'

 

 

 

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'

Error: ORA-38753
Text: Cannot flashback data file %s; no flashback log data.
---------------------------------------------------------------------------
Cause: An attempt to perform a FLASHBACK DATABASE failed because the file does not have enough flashback log data to cover the time to
flash back. Either the file did not have flashback generation enabled for it, or had flashback generation turned off for it some
time during the time span of the flashback.


Action: The file cannot be flashed back. The file must be taken offline or the tablespace dropped before continuing with the FLASHBACK
DATABASE command.

 

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
---------- -------------------------------------------------- ---------- ------------------- ---
..
8          '/<path>/<filename>.dbf'     7    <tablespace_name>                NO  <==
..

  
9 rows selected.

 

Attempt to enable flashback for tablespace, fails with the same error :

 

 

SQL> alter tablespace <tablespace_name> 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: '/<path>/<filename>.dbf'

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.

 

The main cause is that flashback logs are not available to flashback the datafile to previous point in time.

Error: ORA-38753
Text: Cannot flashback data file %s; no flashback log data.
---------------------------------------------------------------------------
Cause: An attempt to perform a FLASHBACK DATABASE failed because the
file does not have enough flashback log data to cover the time to
flash back. Either the file did not have flashback generation
enabled for it, or had flashback generation turned off for it some
time during the time span of the flashback.
Action: The file cannot be flashed back. The file must be taken offline
or the tablespace dropped before continuing with the FLASHBACK
DATABASE command.

There is a limitation of ‘FLASHBACK DATABASE’ if the flashback is disabled for tablespace. It is mentioned in Oracle Documentation :

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

“flashback_mode_clause

Use this clause in conjunction with the ALTER DATABASE FLASHBACK clause to specify whether the tablespace can participate in FLASHBACK DATABASE operations. This clause is useful if you have the database in FLASHBACK mode but you do not want Oracle Database to maintain Flashback log data for this tablespace.

This clause is not valid for temporary or undo tablespaces.

FLASHBACK ON
Specify FLASHBACK ON to put the tablespace in FLASHBACK mode. Oracle Database will save Flashback log data for this tablespace and the tablespace can participate in a FLASHBACK DATABASE operation. If you omit the flashback_mode_clause, then FLASHBACK ON is the default.

 

FLASHBACK OFF
Specify FLASHBACK OFF to take the tablespace out of FLASHBACK mode. Oracle Database will not save any Flashback log data for this tablespace. You must take the datafiles in this tablespace offline or drop them prior to any subsequent FLASHBACK DATABASE operation. Alternatively, you can take the entire tablespace offline. In either case, the database does not drop existing Flashback logs.”

 

 

 

The Workaround to this problem is to do a ‘FLASHBACK DATABASE’ and open the database in READ ONLY mode to export the needed data. Then shutdown database
and recover back to current point in time using archived logs. This is a faster option than TSPITR ( Tablespace Point in Time Recovery )
in case if many tablespaces are needed to be brought back to some previous point in time.

Following is an in-house test-case to achieve the same. In this test-case, the flashback is turned off for tablespace ‘APP’ for which
the underlying datafile is file# 8 named ‘/<path>/app01.dbf’.

 

Example Test-case as a workaround for ORA-38753

SQL> select flashback_on from v$database ;

FLASHBACK_ON
------------------
YES

1 row selected.


SQL> select name, flashback_on from v$tablespace where flashback_on='NO';

NAME              FLA
------------------------------ ---
<tablespace_name>  NO
 

9 rows selected.


SQL> alter database open ;

Database altered.


SQL> create table <username>.<TableName> ( a number ) ;

Table created.


SQL> insert into <username>.<TableName> values ( 1 ) ;

1 row created.

SQL> insert into <username>.<TableName> values ( 2) ;

1 row created.

SQL> commit ;

Commit complete.


SQL> col systimestamp format a50
SQL> /

SYSTIMESTAMP CURRENT_SCN
-------------------------------------------------- ---------------
20-DEC-09 09.17.59.820753 PM +05:30 8517809201835

1 row selected.


SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.


SQL> alter tablespace <tablespace_name> flashback off ;

Tablespace altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.


SQL> alter database open ;

Database altered.


SQL> drop table <username>.<TableName> purge ;

Table dropped.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.


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'


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
---------- -------------------------------------------------- ---------- ------------------- ---
..
8 /<path>/<filename>.dbf        7  <tablespace_name>         NO
 

9 rows selected.


SQL> alter database datafile 8 offline ;
Database altered.


SQL> flashback database to scn 8517809201835 ;

Flashback complete.


SQL> alter database open read only ;

Database altered.

SQL> select * from <username>.<TableName>; --- <
A
---------------
1
2

2 rows selected.



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.


SQL> recover database ;
ORA-00279: change 8517809201836 generated at 12/20/2009 21:18:00 needed for thread 1
ORA-00289: suggestion :
/<path>/archivelog/2009_12_20/o1_mf_1_269_%u_.arc
ORA-00280: change 8517809201836 for thread 1 is in sequence #269

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 8517809201842 generated at 12/20/2009 21:18:09 needed for thread 1
ORA-00289: suggestion :
/<path>/archivelog/2009_12_20/o1_mf_1_270_%u_.arc
ORA-00280: change 8517809201842 for thread 1 is in sequence #270
ORA-00278: log file
'/<path>/archivelog/2009_12_20/o1_mf_1_269_5lwkssds_.arc' no longer needed for this recovery

ORA-00279: change 8517809201847 generated at 12/20/2009 21:18:10 needed for thread 1
ORA-00289: suggestion :
/<path>/archivelog/2009_12_20/o1_mf_1_271_%u_.arc
ORA-00280: change 8517809201847 for thread 1 is in sequence #271
ORA-00278: log file
'/<path>/archivelog/2009_12_20/o1_mf_1_270_5lwkstj2_.arc' no longer needed for this recovery

Log applied.
Media recovery complete. <


SQL> alter database open ;

Database altered.

SQL> alter database datafile 8 online ;

Database altered.


SQL> select * from <username>.<TableName>;
*
ERROR at line 1:
ORA-00942: table or view does not exist

 

 

 
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
About Mehmet Salih Deveci



I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.





Leave a Reply 
Your email address will not be published. Required fields are marked *
 
 
 


Powered by IT Tutorial | Designed by SysDBASoft IT

IT Tutorial © Copyright 2024, All Rights Reserved