We have 2 Oracle databases; a primary and a logical standby, configured with Dataguard. Whenever we have an application rollout on the primary, we create a restore point in case anything goes wrong.

Last Tuesday something went wrong and we had to restore the primary database to its restore point.

That caused the logical standby database to stop applying archive logs (which makes sense, because the primary database was basically reverted in time, leaving the logical standby in an undefined state).

How could we have prevented this situation?

I imagine this scenario:

  1. Stop SQL Apply on logical standby
  2. Create restore point on primary
  3. Do stuff on primary, error occurs
  4. Flashback primary database to restore point
  5. Open primary database
  6. Start SQL Apply on logical standby

Would this work? Would it allow the logical standby to continue applying without getting diverged from the primary database?

  • In my scenario there is a small window of time between stopping SQL Apply and creating the named RP. What would be the added value of using the exact SCN for flashback instead of a named RP? I am more concerned if Dataguard is smart enough to not get confused with any archive logs associated with the failed application rollout and flashback. Jun 3 '16 at 14:53
  • You probably should take a restore point on logical standby, after stopping apply but before taking the RP on primary. Use that if needed on LGSTDBY to flashback if you flashback primary. some testing is required, we do this for physical standby during our releases. as long as standby scn is a bit behind primary, it is all good when flashing back.
    – Raj
    Jun 7 '16 at 14:37
  • Thanks all! Will try with a rp in logical standby before we make the rp on primary. Jun 7 '16 at 14:39
  • We do this all the time except with physical standby. stop recovery on standby, take a guaranteed RP. Switch log on primary, take guaranteed RP and do the release. You will want to have RP scn on standby BEHIND that of RP on primary for easier recovery. Once everything is certified, we drop RP on both, restart recovery on standby.
    – Raj
    Mar 10 '17 at 12:39

I would change the whole process of deploying.

  1. Stop SQL Apply.
  2. Create restore point on standby.
  3. Deploy to standby
  4. When testing is complete, flashback standby.
  5. Start SQL Apply.
  6. Safe to deploy on primary.
  • After step 6 you start deployment on primary without any restore point and SQL Apply running. That way I can't rollback my deployment (if necessary), so an error in the deployment on primary renders both databases unusable (or need te restore from backup instead of flashback). Besides, the logical standby has a different functional use/infrastructure, so not possible to test the full application on logical standby. Mar 22 '17 at 13:29
  • Testing on the standby only reduces the changes of something bad happening. Either way, there is no way to prevent something bad from happening. At which point you can either go back to a restore point or use flashback. However, you would still have the issue you encountered with having to manually fix the SQL Apply. Not much you can do about it.
    – Eli
    Mar 22 '17 at 13:40
  • Something bad can always happen, but your answer has no failsafe from the moment you start deployment on primary. The possible risk is that you have to rebuild the logical standby because it is diverted from primary. And my question was how we could've avoided that. Mar 22 '17 at 13:43

Final situation that works is:

  1. Stop SQL Apply on logical standby
  2. Create restore point on standby
  3. Create restore point on primary
  4. Do stuff on primary, error occurs
  5. Flashback primary database to restore point
  6. Flashback logical to restore point
  7. Open primary database
  8. Start SQL Apply on logical standby

Thanks all for your input.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.