Flashback disabled automatically – a minor rant
Posted by John Hallas on January 6, 2010
I posted the mail below on the Oracle-L mailing yesterday and was struck by a response given by many
I believe that there is a fundamental flaw in how flashback is managed in a database.
If I make the decision, based on business requirements and technical reasons, that I want flashback logging to be enabled for a database then I would expect that to remain the situation.
However Oracle can disable flashback and not really inform the user at all. Yes there is a message in the alert log
Errors in file /app/oracle/admin/FLASH10G/bdump/flash10g_rvwr_22255.trc: ORA-38701: Flashback database log 77 seq 201 thread 1: "+FRA/flash10g/flashback/log_77.2579.702048581" ORA-17503: ksfdopn:2 Failed to open file +PETRAFRA/flash10g/flashback/log_77.2579.702048581 ORA-15012: ASM file '+FRA/flash10g/flashback/log_77.2579.702048581' does not exist Thu Nov 5 15:14:07 2009 ************************************* RVWR encountered an error when writing flashback database logs. See error stack in alert log. To avoid crashing the instance, this instance has turned off flashback database. *************************************
However that is very easily to miss and there is no OEM policy to raise an alert.
I have tried to cause flashback to be automatically turned off but I cannot replicate it very easily as the archivelog fills prior to flashback being turned off.
I have logged an SR with Oracle as I want to develop a test so that I can put processes in place to capture the event and raise automatic alerts.
Oracle are now suggesting that this very difficult to do because of the code paths used and are suggesting I raise an enhancement request to get an OEM policy to be created to report when fkashback has become disabled automatically.
I favour a init.ora parameter which asks “Should the database hang if flashback_area full” which the DBA can set to true if he wants flashback logging to be treated like archive logging. I know that the database can be recovered without flashback but cannot with archiving but I still consider the situation should be handled much better than it currently it.
The consensus seemed to be live with it and use a shell script to search the alert logs for relevant errors and deal with them appropriately. Harsh but fair you may think. However I believe that when we are paying significant money to Oracle for their technology including extensive use of OEM then I do not think that we should have to be using in-house scripts to pick up errors that Grid does not deal with in what I believe to be an appropriate manner.
Ten years ago I was all in favour of reading the alert log and taking appropriate action but I think it a backward step to assume that we still do that and not ask the tool of choice to do what is required.
I will be putting an enhancement request in to Oracle to ask that we have a parameter to control whether or not flashback is automatically disabled. Failing that get a policy alert raised when flashback has been disabled, but continue with the database usage.
One other irritation with flashback logs. One has to restart the database to enable them and yet then can be disabled dynamically. I suppose that is an advantage as well but it always strikes me as odd.