How to flashback databases in dataguard with broker

Last week I had to do some tests with dataguard. To make restores easier restore points were required.

Given is following configuration:
show configuration;
Configuration - ila
Protection Mode: MaxAvailability
Databases:
ila - Primary database
ilal - Logical standby database
ILAP - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

How to set restore points here ?

Two things to consider:
Redo apply to a physical standby is incompatible with setting a restore point so it must me switched off for a while.
SCN of the restore points of the standby databases must be lower than on primary database, otherwise redo shipment does not continue after flashback.

Setting of restore points

First redo shipment has to be stopped:
edit database "ilal" set state=APPLY-OFF;
edit database "ILAP" set state=APPLY-OFF;

Second, restore points on both standby databases are set:
create restore point RZ_C GUARANTEE FLASHBACK DATABASE;

Third, restore point on primary database is set:
create restore point RZ_C GUARANTEE FLASHBACK DATABASE;

Last, redo shipment is switched on again:

edit database "ILAP" set state=APPLY-ON;
edit database "ilal" set state=APPLY-ON;


Now dataguard is running as before.

Let’s flashback the databases:

First switch off redo shipment:

edit database "ILAP" set state=APPLY-OFF;
edit database "ilal" set state=APPLY-OFF;


Second start all three databases in mount mode:

shutdown immediate;
startup mount;


Third flashback primary database:

flashback database to restore point RZ_C;
alter database open read only;


Check whether flashbacked database looks fine.

If yes, open it read write:

shutdown immediate;
startup mount;
alter database open resetlogs;


Do the same on logical standby database:

flashback database to restore point RZ_C;
alter database open read only;


Check whether flashbacked database looks fine.

If yes, open it:

shutdown immediate;
startup mount;
alter database open resetlogs;


Last do flashback on physical standby:

flashback database to restore point RZ_C;

Now start redo shipment again:

edit database "ILAP" set state=APPLY-ON;
edit database "ilal" set state=APPLY-ON;
After a few minutes, dataguard will run as before:
show configuration;
Configuration - ila
Protection Mode: MaxAvailability
Databases:
ila - Primary database
ilal - Logical standby database
ILAP - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS