Friday, April 1, 2011

Enable Flashback On Standby

It is possible to enable flashback on a standby independent of the primary. (It's good to have flashback enabled both on primary and standby, if flashback IO is no concern).
Data guard environment created earlier is used here. Steps are similar to How to Enable Flashback for a RAC Database on ASM ( 819905.1)

To enable flashback on standby bring the stop log apply on standby (transport could also be stopped for the duration).
DGMGRL>  edit database rac11g2 set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.
Trying to enable flashback while log apply is on will result in
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
If the standby is open in read only mode then shutdown and bring it up only one instance in mount mode as this is required to enable flashback.
srvctl stop database -d rac11g2s
srvctl start instance -d rac11g2s -i rac11g2s1 -o mount
Once in mount mode enable flashback on the standby
SQL> alter database flashback on;

Database altered.
Enable log apply and transport on
DGMGRL> edit database rac11g2 set state='TRANSPORT-ON';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-ON';
Succeeded.
Open the standby mode in read only if required. Check the flashback on status of the database
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
YES
Having flashback on standby is important especially to recover from open resetlogs situations in primary. If the standby has applied changes past the new resetlog scn and there's no flashback enabled on standby, only way to recover is to recreate the standby. If flashback was enabled on standby then it could be used to flashback to a scn prior to resetlogs and continue to use the standby from then onwards.