Oracle DBA – A lifelong learning experience

Flashback database – dont forget the standby

Posted by John Hallas on September 21, 2016

Today an  application team had a problem with a production system and they wanted the database restored to a point a couple of days ago. It is a configuration management system and they figured they could lose any changes . Their call.

I did all the good things

  • Checked we had a change in place
  • Blacked out primary and standby in OEM
  • Checked flashback was on  –
select flashback_on from v$database;
  • Checked how far we could flashback to
set lines 120
col oldest_flashback_scn for 99999999999999999999
col oldest_flashback_time for a30
to_char(oldest_flashback_time, 'DD-MM-YYYY HH24:MI:SS')
from v$flashback_database_log;
  • Checked that standby had flashback on.

I stopped managed recovery on standby, shutdown that database, stopped primary and mounted and flashed back to my desired time. Everything was fine.

I went to standby, mounted it and ran

flashback standby database to timestamp
to_timestamp ('19/09/2016 12:00:00','dd/mm/yyyy hh24:mi:ss');
ERROR at line 1:
 ORA-38729: Not enough flashback database log data to do FLASHBACK.


That stumped me. Obviously I knew what the message meant and I knew I had not run the script to see how far we could flashback to on standby but why did we have a problem?

Flashback retention was set to 1440  – 24 hours on both primary and standby but there had been no space pressure on primary so it had retained sufficient  flashback logs to go back 6 days if required

It did not take a lot of digging around to find the explanation. Normally we have one database per server (physical or virtual) in production. We then map that  to a dedicated standby server. In this case we had 3 databases running. Due to performance considerations we run 2 primaries and a standby on one side and 2 standbys and a primary on the other. This database was not the key/busiest database and so the standby was sharing with another standby and the primary for the biggest database. That had generated a lot of logfiles which had caused space to be consumed and the flashback logs had been tidied to meet the 1440 minute parameter setting.

A rebuild of standby is under way currently.

I always try to learn lessons from incidents like this. In this case there were two unusual requirements – flashback production and go back 48 hours.

My lessons learned

Use db_recovery_file_dest_size for each database where we have more than one and set them appropriately to the size of each database.

Could we have some sort of automated routine that creates a guaranteed restore point every hour and then drops it 48 hours later. That needs thinking about as we could risk running out of space.



2 Responses to “Flashback database – dont forget the standby”

  1. Raj Jamadagni said

    John, on your last point, it can be done. We do that automatically in db release context. For very release, liquibase is configured to call a procedure as step 1. It Runs a external procedure call to create a guaranteed restore point and once that is created, it creates a schedule job that automatically drop that named restore point after 24 hours. Similar mechanism can be used to achieve your goal. we have sized recovery directory sufficiently large so we don’t need to tidy up flashback logs until RP needs to be dropped.
    Additionally,on RP topic, for manual testing, we first create a RP on standby after canceling managed recovery. Then create one on primary (the flashback scn on primary needs to be ahead than flashback scn of standby RP). I forgot for which bug, but it seems to work well in 11gr2 and 12c.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: