Snapshot standby convert issue

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 11 Oct 2012 14:12:11 +1000
Message-ID: <5076471B.1010304_at_tpg.com.au>



G'day.
Environment:
     Solaris 11, local zone
     Oracle 11.2.0.3.3 RDBMS and ASM

I did open an SR on this, but it's quite urgent... I've got a standby database in an Active Dataguard setup that was opened as Snapshot Standby twice, with a convert to physical in between. The sequence of events is:

  • convert physical standby to snapshot standby
  • open read/write and perform some serious testing
  • convert snapshot standby to physical standby ( to restore the initial test baseline - no redo apply at this stage )
  • convert physical standby to snapshot standby again
  • do more serious testing
  • convert snapshot to physical again.. ERROR

The last step, converting the snapshot back to physical again, fails with ORA-38729 "Not enough flashback database log data to do FLASHBACK". On investigation it turns out that the second convert created a guaranteed flashback restore point, but the first available flashback SCN as reported in v$flashback_database_log is 2 higher than the SCN of the guaranteed restore point, and the first time is 10 seconds later.

The first flashback log known to the database (as reported in v$flashback_database_logfile) is more than 20 minutes earlier than the second convert to snapshot, and has a *first* SCN much higher than the restore point (presumably from the previous incarnation). The second flashback log is more than one hour younger than the earliest time reported in v$flashback_database_log, which leads me to think that the previous and current incarnations share the first available flashback log file. If that is true, than why does the snapshot think it doesn't have enough flashback to convert back?

Does anyone have similar experience and perhaps a method to get around this and convert the snapshot back to physical?

Rebuilding the snapshot will take at least 5 days and we're hopefully going live in 8...

Cheers,
Tony

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 06:12:11 CEST

Original text of this message