I am working on a possible scenario, where a big data changes would happen on oracle dataguard primary (a large DB) and if something is wrong with the data quality after I would like to use my standby as the fallback scenario.

My idea was:

  1. stop log apply on standby: EDIT DATABASE 'standby_db' SET STATE='APPLY-OFF';
  2. do the 24 hours SQL updates/inserts on primary
  3. if data quality is not ok , make a switchover or failover from primary to standby (because standby has the data state before the updates/inserts began on primary).

No if I understand on SWITCHOVER the role transition from primary DB to standby DB , the primary would place a marker on the last change in the redo, which is sent to standby DB. Primary converts itself to standby DB. The standby would note the marker in the redo from primary. But I have stopped LOG apply , so I would not like to have the latest changes from the primary I would like to start using my DB from the data state I have "frozen" on stanbdy by stopping log apply. A failover is not an option here also , since my primary is live and kicking and not lost?

UPDATE to the question: I guess I will be using the FAILOVER. When I stop the apply log on standby, then thh broker will not cooperate later when using Failover. So I will then kill all the sessions on primary, and make ALTER DATABASE FAILOVER TO standby_db;

Does this sound reasonable or not? Thanks in advance.

1 Answer 1


You cannot Failover (or Switchover) to the Standby because it is not "in step" with the Primary.
You would lose data and DataGuard absolutely will not let you do this (without a "fight").

Instead, you can convert your Standby into a Primary database and open it, resetlogs, then throw away the old Primary and rebuild it from your new Primary.

The Good News:
You lose everything that's been done since you stopped the Redo Apply.

Your have the database back to where it was. That's fine is it's an "experimental" system that you don't mind throwing around like this, but your Production system? Think again.

The Not so Good News:
You lose everything that's been done since you stopped the Redo Apply.

That includes:

  • everything that you've been doing, which you know about and can do again if you have to, but also
  • everything that everybody else has been doing, about which you know absolutely nothing and they won't even remember, let alone be able to do it again!
  • Hi. Thanks for the fast reply. How do you mean convert standby to Primary and open it ( snapshot standby?). It is a production system. I just need a good fallback. How can I thriw away primary abdeckt why ? Nov 15, 2022 at 20:19
  • Hi @Phill. As for the convert the Standby into Primary (during stopped apply log) you mean "alter database activate standby database" or ALTER DATABASE CONVERT TO PHYSICAL STANDBY (the primary) and then switch ? I am not sure how can you even CONVERT a standby database to primary , I guess only switchover/failover is possible? Any commands, hints? Nov 16, 2022 at 9:38
  • ... Activate Physical Standby. Basically, you're doing the same thing as if your Primary server / database had upped and died on you. There is no longer a primary to switchover /from/ but you need to get what /was/ the standby into a useable state. You CANNOT use Switchover or Failover - the broker WILL NOT allow you to do either because of the "break" in applied Redo.
    – Phill W.
    Nov 16, 2022 at 13:58
  • Thanks. So I need to simulate then primary crash since primary will not be crashed but just with wrong data inside. I cannot shutdown the DB Server where primary resides, so I guess I need to force a shutdown but that standby cannit be influenced and activated with the stoppen redo Apply and older log file state (good data state). Nov 17, 2022 at 19:04
  • Hi @Phill W. I am reading also this How to Perform Failover When GAP on Standby (Doc ID 846087.1) but it seems very old (for older databases). In theory it says that with GAP the ALTER DATABASE ACTIVATE STANDBY DATABASE; should work fine. I am not sure about it in 19c databases? Nov 21, 2022 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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