RE: Standby First upgrades

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 8.1.0.37. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Tuesday, August 20, 2019 1:28 PM
To: tim.evdbt_at_gmail.com; howard.latham_at_gmail.com; 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 primary.

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_at_gmail.com> <howard.latham_at_gmail.com>

Date: 8/20/19 9:49 AM (GMT-05:00)

To: ORACLE-L <mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org>

Subject: Standby First upgrades  

Oracle 11.2.0.4 and 18.3

DATAGUARD RHEL 6     I have been told I can, but looking at ORACLE Doc it says NO!

Is it possible to do a standby first upgrade from 11.2.0.4 to 18 in a dataguard setup?

My gut feeling BTW is no.

Best Wishes  

Howard A. Latham  

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 22 2019 - 17:17:12 CEST