RDBMS -- ORA-38753 -- The effect of flashback_mode_clause (tablespace) to restore points & snapshot standby operations

Last year , I wrote a blog post about using guaranteed restore points.. In that blog post, I did a demo to show you the concept and tried to explain the Guaranteed Restore Points with or without Flashback Logging (database level), along with the prerequisites and restrictions.

Here is the link of that blog post -> http://ermanarslan.blogspot.com.tr/2017/08/rdbms-flashback-feature-demo-guranteed.html)

Today, I'm here to write about a very specific but also very important thing that you may face while restoring to a guaranteed restore point.

Although, this blog post seems to be related with the guaranteed restore points only, it is actually not .. That is, as the standby snapshot technology relies on the guaranteed restore point, this blog post is also related with the snapshot standby technology. So, you may face this issue while converting your snapshot standby to physical standby, as well.

I hope, you read this blog post, before facing with that thing, because it is a little shocking :)

In spite of the name "Guaranteed Restore Point", you need to be aware of the following fact in order to be able to restore to a guaranteed restore point! ->

You must not have any tablespaces which have Flashback_on set to NO.

If you have flashback_on set to OFF  for a tablespace-> then you may end of the following error stack while converting a standby snapshot to physical standby, or while doing a flashback to a restore point ->

ORA-38753: Cannot flashback data file XX; no flashback log data.
ORA-01110: data file YYY: 'XXX' 

Although, FLASHBACK_ON is by default set to YES, it can be changed to NO.. So if you do this, you won't be able to restore to a guaranteed restore point.

When FLASHBACK_ON is set to YES for a tablespace, Oracle Database will save Flashback log data for that tablespace and thus, the tablespace can participate in a FLASHBACK DATABASE operation.

However, when FLASHBACK_ON is set to OFF for a tablespace, then Oracle Database will not save any Flashback log data for that tablespace. That's why, if FLASHBACK_ON is set to OFF for a tablespace, you must take the datafiles of this tablespace offline (or put the tablespace offline) or drop them prior to any subsequent FLASHBACK DATABASE operation.

Relevant commands for disabling/enabling flashback for a tablespace; 

alter tablespace XXX flashback off; 

alter tablespace XXX flashback on;

So if you are planning to use guaranteed restore points, or snapshot standby technology, it is better to check v$tablespace to ensure that all the critical tablespaces are flashback enabled.

FLASHBACK_ON column in v$tablespace -> Indicates whether the tablespace participates in FLASHBACK DATABASE operations (YES) or not (NO)

Interesting right? Although, the database must not be in flashback mode, all the critical tablespaces must be in flashback mode, in order to be able to restore to a guaranteed restore point (or to be able to convert a snapshot standby to a physical standby)

So if, for any reason, you need to flashback your database to a restore point in a database environment where there are tablespaces for which the flashback modes are set to OFF, you need to follow the action plan documented in the following MOS note:

Flashback Database fails with ORA-38753 ORA-01110 with Tablespaces having Flashback off & RESETLOGS (Doc ID 1588027.1)