Flashback logging at tablespace level

Our site policy is to enable flashback logging on our production databases unless there is a good reason not to, two examples are the data warehouse where volume is prohibitive and the other is a highly active Peoplesoft database where performance is a primary consideration and we suffer from “flashback buf free by RVWR" wait event as it cannot write the flashback logs quickly enough. 

On the same system we had experimented with disabling flashback against two tablespaces which contained objects of a transient nature and that could be rebuilt if necessary. We finally agreed that the overhead of flashback was too heavy balanced aganst the likelyhood of us ever using it and so we disabled it at the database level.

However, when we are applying application code changes during an agreed outage we bounce the database and restart it with flashback enabled. Therefore when the changes have been applied and basic testing has taken place we can quickly flashback the database to a clean point with a minimum of fuss in the event that the changes have not produced the desired results. If the bundle is OK we disable flashback logging dynamically without a further database bounce. We find this an excellent belt and braces method which has little overhead or manageability cost.

During a recent bundle release we did want to flashback but had some problems

SQL>flashback database to scn 25708250758;
ERROR at line 1:
ORA-38753: Cannot flashback data file 447; no flashback log data.
ORA-01110: data file 447: '+DATAHR/database/datafile/tlwrk32mssmidx.366.698430803'
ORA-38753: Cannot flashback data file 446; no flashback log data.
ORA-01110: data file 446: '+DATAHR/database/datafile/tlwrk32mssm.329.698430723'

Alert log:

ORA-38753 signalled during: flashback database to scn 25708250758...

This was because flashback had been disabled for two tablespaces:

Oracle Error:

ORA-38753: Cannot flashback data file string; 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.

SQL>select * from v$tablespace where FLASHBACK_ON='NO';

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
       507 TLWRK32MSSM                    YES NO  NO
       508 TLWRK32MSSMIDX                 YES NO  NO

Now that did cause us a few problems but the cause was self-evident, two tablespaces were still set to not allow flashback logging despite the whole database being put into flashback mode. A stupid oversight but understandable. However I was questioned by the application teams as to why this had not shown up on the pre-production database which is created by RMAN duplicate as a copy of production. Good question to which I did not know the answer.

After a bit of investigation I still did not have any idea but had a feeling that it was associated with the database refresh. It transires that a RMAN duplicate command sets all datafiles to flashback_on whatever the original state. Nothing wrong with that and I am sure it is documented somewhere (although obvious Google keyword searches did not help). Just one of those gotchas that happen.

Whilst nothing earth-shattering I hope this post has pointed out a useful function of flashback logging, namely to quickly revert a system if acceptance testing after a database schema/code change does not work as planned.