Flashback guaranteed restore point with ADG for DB upgrade fallback

[Rich J]

Hey all,

I've been working on a plan to get our databases upgraded from 11.2.0.3
to 12.1.0.2. Most of the planning is relatively straightforward, except
for one DB. It has active data guard to a physical standby. I've
managed to work out how to properly upgrade it, despite the
"instructions" at
https://docs.oracle.com/database/121/SBYDB/upgrades.htm#SBYDB4931

The part I can't figure out is how to rollback the upgrade in a timely
manner, should it be needed. The easiest is to restore from backup and
recreate the standby, but that will take several hours.
https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/
says to use Flashback guaranteed restore points, but there's no mention
of how this should work with a standby. No problem, I can figure that
out. The restore point on the primary doesn't exist on the standby, but
I can get the SCN, subtract 2 (as per MOS articles), then FLASHBACK
STANDBY DATABASE TO SCN nnnn. That produces "ORA-38726: Flashback
database logging is not on.", since, well, flashback isn't on. It's not
needed for the primary -- is that a restriction on the standby?

Anyone know 1) where this might be documented, and 2) how one might go
about planning a time-sensitive upgrade fallback for an ADG setup?

I was thinking that instead of relying on the SCN for the Flashback on
the standby that maybe I'd need to setup a separate guaranteed restore
point (with MRP stopped) before the primary one is created. I've been
testing various scenarios, but as it takes >30 minutes to set up each
scenario, I thought I should ask here to possibly save me a couple of
days of fun.

Thanks!
Rich

[n***@gmail.com]

I'll try and look it up when I get back but I think we went with a GRP on
both sides, then flashback independently.

Post by Rich J
Hey all,
I've been working on a plan to get our databases upgraded from 11.2.0.3 to
12.1.0.2. Most of the planning is relatively straightforward, except for
one DB. It has active data guard to a physical standby. I've managed to
work out how to properly upgrade it, despite the "instructions" at
https://docs.oracle.com/database/121/SBYDB/upgrades.htm#SBYDB4931
The part I can't figure out is how to rollback the upgrade in a timely
manner, should it be needed. The easiest is to restore from backup and
recreate the standby, but that will take several hours.
https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/
says to use Flashback guaranteed restore points, but there's no mention of
how this should work with a standby. No problem, I can figure that out.
The restore point on the primary doesn't exist on the standby, but I can
get the SCN, subtract 2 (as per MOS articles), then FLASHBACK STANDBY
DATABASE TO SCN nnnn. That produces "ORA-38726: Flashback database logging
is not on.", since, well, flashback isn't on. It's not needed for the
primary -- is that a restriction on the standby?
Anyone know 1) where this might be documented, and 2) how one might go
about planning a time-sensitive upgrade fallback for an ADG setup?
I was thinking that instead of relying on the SCN for the Flashback on the
standby that maybe I'd need to setup a separate guaranteed restore point
(with MRP stopped) before the primary one is created. I've been testing
various scenarios, but as it takes >30 minutes to set up each scenario, I
thought I should ask here to possibly save me a couple of days of fun.
Thanks!
Rich

[Jose Rodriguez]

I've recently used the exact same approach for an ADG upgrade. This was
11.2.0.3 to 11.2.0.4, but the idea is the same.
As Niall says, just enable flashback database on both sides, create a GRP
on the standby first and then on the primary, so you have an older SCN on
the standby in case of a rollback.
Bear in mind the required FRA space for the flashback logs, not much for
the upgrade itself, but this will be recording the applied redo on the
standby as well if I recall correctly.
I tested it and it worked like a charm.
I am not at my desk right now, so I can't share any docs, but I'll take a
look tomorrow.

Post by n***@gmail.com
I'll try and look it up when I get back but I think we went with a GRP on
both sides, then flashback independently.

Post by Rich J
Hey all,
I've been working on a plan to get our databases upgraded from 11.2.0.3
to 12.1.0.2. Most of the planning is relatively straightforward, except
for one DB. It has active data guard to a physical standby. I've managed
to work out how to properly upgrade it, despite the "instructions" at
https://docs.oracle.com/database/121/SBYDB/upgrades.htm#SBYDB4931
The part I can't figure out is how to rollback the upgrade in a timely
manner, should it be needed. The easiest is to restore from backup and
recreate the standby, but that will take several hours.
https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/
says to use Flashback guaranteed restore points, but there's no mention of
how this should work with a standby. No problem, I can figure that out.
The restore point on the primary doesn't exist on the standby, but I can
get the SCN, subtract 2 (as per MOS articles), then FLASHBACK STANDBY
DATABASE TO SCN nnnn. That produces "ORA-38726: Flashback database logging
is not on.", since, well, flashback isn't on. It's not needed for the
primary -- is that a restriction on the standby?
Anyone know 1) where this might be documented, and 2) how one might go
about planning a time-sensitive upgrade fallback for an ADG setup?
I was thinking that instead of relying on the SCN for the Flashback on
the standby that maybe I'd need to setup a separate guaranteed restore
point (with MRP stopped) before the primary one is created. I've been
testing various scenarios, but as it takes >30 minutes to set up each
scenario, I thought I should ask here to possibly save me a couple of days
of fun.
Thanks!
Rich

[Rich J]

I've recently used the exact same approach for an ADG upgrade. This was 11.2.0.3 to 11.2.0.4, but the idea is the same.
As Niall says, just enable flashback database on both sides, create a GRP on the standby first and then on the primary, so you have an older SCN on the standby in case of a rollback.
Bear in mind the required FRA space for the flashback logs, not much for the upgrade itself, but this will be recording the applied redo on the standby as well if I recall correctly.
I tested it and it worked like a charm.
I am not at my desk right now, so I can't share any docs, but I'll take a look tomorrow.

I'm testing this now, and have gotten further than I did before, but am
getting ORA-19909 starting MRP on the standby. Looking at V$INSTANCE,
the instance_role is "PRIMARY_INSTANCE". It appears someone forgot to
use the "STANDBY" keyword when doing the flashback. Doh. At least that
sounds like it could be the cause.

I don't want to spend the amount of time it would take to properly test
the effects of enabling flashback on the databases, which appears to be
necessary to flashback to an SCN on the standby. Creating a guaranteed
restore point on the standby seems to be the way to go here, as Oracle
allowed it, even if I...erm, "someone"...forgot the proper keyword.

More testing tomorrow -- thanks all for the replies!

Rich

[Seth Miller]

I think you alluded to this Rich but enabling Flashback Database is not
required for GRPs but I haven't tested it on a standby database.


Seth

Post by Jose Rodriguez
I've recently used the exact same approach for an ADG upgrade. This was
11.2.0.3 to 11.2.0.4, but the idea is the same.
As Niall says, just enable flashback database on both sides, create a GRP
on the standby first and then on the primary, so you have an older SCN on
the standby in case of a rollback.
Bear in mind the required FRA space for the flashback logs, not much for
the upgrade itself, but this will be recording the applied redo on the
standby as well if I recall correctly.
I tested it and it worked like a charm.
I am not at my desk right now, so I can't share any docs, but I'll take a look tomorrow.
I'm testing this now, and have gotten further than I did before, but am
getting ORA-19909 starting MRP on the standby. Looking at V$INSTANCE, the
instance_role is "PRIMARY_INSTANCE". It appears someone forgot to use the
"STANDBY" keyword when doing the flashback. Doh. At least that sounds
like it could be the cause.
I don't want to spend the amount of time it would take to properly test
the effects of enabling flashback on the databases, which appears to be
necessary to flashback to an SCN on the standby. Creating a guaranteed
restore point on the standby seems to be the way to go here, as Oracle
allowed it, even if I...erm, "someone"...forgot the proper keyword.
More testing tomorrow -- thanks all for the replies!
Rich

[Rich J]

I don't want to spend the amount of time it would take to properly test the effects of enabling flashback on the databases, which appears to be necessary to flashback to an SCN on the standby. Creating a guaranteed restore point on the standby seems to be the way to go here, as Oracle allowed it, even if I...erm, "someone"...forgot the proper keyword.
More testing tomorrow -- thanks all for the replies! I'm testing this now, and have gotten further than I did before, but am getting ORA-19909 starting MRP on the standby. Looking at V$INSTANCE, the instance_role is "PRIMARY_INSTANCE". It appears someone forgot to use the "STANDBY" keyword when doing the flashback. Doh. At least that sounds like it could be the cause.

Yep, apparently that "STANDBY" keyword in the FLASHBACK command is
important to use when flashing back (flashbacking?) a standby database,
despite my misdiagnosis (I should have referenced V$DATABASE and not
V$INSTANCE to determine primary/standby role). It feels like I should
investigate how to recover from incorrectly issuing the FLASHBACK in a
standby...

And yes, a GRP is required for flashback when flashback database is not
enabled:
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV530


Just some delicate concepts that I needed to get straight in my head in
order to provide that upgrade fallback plan that I will now not need to
use...

Thanks all for the help!

Rich

[Karthikeyan Panchanathan]

We use same way as explained by Joe here.

Make GRP on standby once broke replication with Primary. Then create GRP on Primary.

Keep in mind you need lot of space for FRA and archive-logs in Standby.

We always make sure Primary keep sending log to Standby but never apply.

When Primary passed all validation we enable replication else flashback.

HTH
Karth

Sent from my IPhone

I've recently used the exact same approach for an ADG upgrade. This was 11.2.0.3 to 11.2.0.4, but the idea is the same.
As Niall says, just enable flashback database on both sides, create a GRP on the standby first and then on the primary, so you have an older SCN on the standby in case of a rollback.
Bear in mind the required FRA space for the flashback logs, not much for the upgrade itself, but this will be recording the applied redo on the standby as well if I recall correctly.
I tested it and it worked like a charm.
I am not at my desk right now, so I can't share any docs, but I'll take a look tomorrow.

I'll try and look it up when I get back but I think we went with a GRP on both sides, then flashback independently.

Post by Rich J
Hey all,
I've been working on a plan to get our databases upgraded from 11.2.0.3 to 12.1.0.2. Most of the planning is relatively straightforward, except for one DB. It has active data guard to a physical standby. I've managed to work out how to properly upgrade it, despite the "instructions" at https://docs.oracle.com/database/121/SBYDB/upgrades.htm#SBYDB4931
The part I can't figure out is how to rollback the upgrade in a timely manner, should it be needed. The easiest is to restore from backup and recreate the standby, but that will take several hours. https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/ says to use Flashback guaranteed restore points, but there's no mention of how this should work with a standby. No problem, I can figure that out. The restore point on the primary doesn't exist on the standby, but I can get the SCN, subtract 2 (as per MOS articles), then FLASHBACK STANDBY DATABASE TO SCN nnnn. That produces "ORA-38726: Flashback database logging is not on.", since, well, flashback isn't on. It's not needed for the primary -- is that a restriction on the standby?
Anyone know 1) where this might be documented, and 2) how one might go about planning a time-sensitive upgrade fallback for an ADG setup?
I was thinking that instead of relying on the SCN for the Flashback on the standby that maybe I'd need to setup a separate guaranteed restore point (with MRP stopped) before the primary one is created. I've been testing various scenarios, but as it takes >30 minutes to set up each scenario, I thought I should ask here to possibly save me a couple of days of fun.
Thanks!
Rich

--