I would want to open a standby CLONE and put it through a full set of my
regression tests for that database AND plan checks with no effort to control
plans for all important plans.
This has been available for “roll your own” standby recover since 188.8.131.52. I
documented it circa 1995 and presented. Poor listeners in the crowd were
jeering that could not be done because without then having to reinstantiate the
standby. I say this only so that you notice that while this process does
require duplicate space, it absolutely does NOT destroy the standby.
I don’t have the paper handy. Let’s see if I remember the steps.
1) Catch the standby up to a nice point that you like and shut it down.
(Back up your online redo and control files in case you screw up. Stash them on
line in a safe place that cannot be confused with the real ones and don’t put
them on backups to accidentally reload, creating hilarity.)
2) Clone all the files. If you will be using the same host for the test you
will need to do a rename of the database, because you can’t have two databases
of the same name open on the same host and you dramatically raise the
possibility of damage if you fail to clone some bit and Oracle finds the
non-clone. This was developed to use the excess horsepower of the standby host
during normal operations.
3) Rename the database, startup, rename. Shut it down, start it back up to
open as primary.
4) Restart the standby, which uses a tiny fraction of the horsepower of you.
5) Test away.
6) Throw it away.
Originally this was to use the horsepower of the standby recovery machine on a
FROZEN clone of the database for queries where the CFO liked everything to
crossfoot and you didn’t have to specify any sort of asof date because you only
had that one date. Some folks kept 4 months, 5 quarters, and 1 annual clone and
wrote differencing reports between the times. Folks with really big databases
tended to not do that, but often backed up the same offline in case someone
needed to answer an interesting question.
It also allowed report acceleration base on instantiated aggregates, that were
always valid because the details were no longer being updated.
Anyway, disk is cheap. When this was created there were not materialized views,
Oracle didn’t have dataguard (let alone open for read dataguard), flashback
database, or the ability to set the asof time of queries. But you could use
this technique, for example to snap the end of a month, year, or quarter and
beat on it using zero production horsepower. Because the application of logs to
the standby can be set to match a useful event in the business cycle, this
could be really useful. Most of my customers chose the completion of “Generate
Receivables” and made a point to pause concurrent manager activity in a time
frame to that no large rollbacks would take place when you opened the CLONE.
But they did NOT need to shut down the primary database, just manage activity
reasonable understanding a lot of time opening the CLONE could be saved.
Anyway, both Tim and Mladen are correct from particular viewpoints. IF you plan
really heavy testing of the post upgrade functionality, plans, etc., I suggest
that this method, while not strictly required, may be very useful to you
indeed. If nothing else it factors out any overhead to maintaining a flashback
capability on the upgraded version and you can nearly immediately resume your
normal status for your regular dataguard.
Johnny Chan, Brian Christiansen, and I perfected this together (completely
unsupported other than strictly following the rules for recovery by Oracle)
implementing what was then a giant EBS database for Cisco in 1995. I had put
together earlier “roll your own” standbys at Burlington Coat and Millipore but
I had not automated it nearly as well there and the standby site was not clear
across the country for either of them, but rather was only machine standby, not
whole site standby.
Anyway, you can make it work, and your clone doesn’t even differ from a
primary, so your testing of the new release should be “pure.”
Good luck. There is probably a better way to automate this with Oracle tools
(RMAN duplicate or something), but then you have to rely on there being zero
bugs in their tools regarding upgrades and operating off standbys without
messing up the standby.
Again, Good luck.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: Tuesday, August 20, 2019 1:28 PM
To: tim.evdbt@xxxxxxxxx; howard.latham@xxxxxxxxx; ORACLE-L
Subject: Re: Standby First upgrades
But why would I open a standby? He was asking about upgrading it, not opening
it. When an 11G standby is upgraded to 18c, you end up with Oracle 118c
mounting an 11G database. You can not open it. I believe that the goal of the
exercise is to have standby immediately available after the upgrade of the
On 8/20/19 12:50 PM, Tim Gorman wrote:
Not true. To upgrade, database has to be opened, and to open a standby, you
have to RESETLOGS.
Snapshot standby uses Flashback database to restore prior to the RESETLOGS so
that managed recovery can resume uninterrupted.
On 8/20/19 9:43 AM, gogala.mladen wrote:
Yes it is possible because upgrade doesn't do resetlogs.
Sent from my Verizon, Samsung Galaxy smartphone
-------- Original message --------
From: Howard Latham <mailto:howard.latham@xxxxxxxxx> <howard.latham@xxxxxxxxx>
Date: 8/20/19 9:49 AM (GMT-05:00)
To: ORACLE-L <mailto:oracle-l@xxxxxxxxxxxxx> <oracle-l@xxxxxxxxxxxxx>
Subject: Standby First upgrades
Oracle 184.108.40.206 and 18.3
I have been told I can, but looking at ORACLE Doc it says NO!
Is it possible to do a standby first upgrade from 220.127.116.11 to 18 in a dataguard
My gut feeling BTW is no.
Howard A. Latham