Re: increasing COMPATIBLE parameter

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 25 Mar 2015 14:11:48 -0500
Message-ID: <CAJvnOJadrH_mjWV=CBHfQdO_MW8YRTDBtJCUt4FC4nSdZ4qumQ_at_mail.gmail.com>

Since your control file will change substantially, I suggest that you do a full backup immediately upon changing the parameter by the way.

On Wed, Mar 25, 2015 at 1:55 PM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com> wrote:

> Ah. I see the objection, reading farther down the thread.
>
> Okay. I was only *guessing* that COMPATIBLE would probably affect
> optimizer features -- in addition to many others. While it turns out that
> the example/suggestion I have given -- about optimizer issues resulting
> from changing COMPATIBLE -- is probably fatuous, this does not really
> affect the discussion.
>
> Changing COMPATIBLE from 10.2.0.3 to 11.0.2.4 *might* have
> unpredictable affects on the application. It is a one-way change that
> cannot be reverted without rebuilding the database. It is therefore
> prudent to test carefully before making the change.
>
> My apologies to all for a poor choice of illustrations. :-)
>
>
> On Wed, Mar 25, 2015 at 2:49 PM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
> wrote:
>
>> Yes. I know what COMPATIBLE is for. :-)
>>
>> But the *intended* use of COMPATIBLE is not what is being discussed here.
>>
>> On Wed, Mar 25, 2015 at 12:16 PM, Carlos Sierra <
>> carlos.sierra.usa_at_gmail.com> wrote:
>>
>>> Mark,
>>>
>>> COMPATIBLE is used when you do a database upgrade, lets say from
>>> 10.2.0.4 to 11.2.0.4, and you need (for a few days) to have the capability
>>> to go back to 10.2.0.4. Once you are comfortable on the new release, you
>>> simply reset this parameter and let it default for current database release
>>> you are on.
>>>
>>> OPTIMIZER_FEATURES_ENABLE controls many features of the CBO, thus you
>>> can set it to a prior value when and if needed. I only see some value to do
>>> this during a few days after an upgrade. For the most part, you also want
>>> to reset this parameter (remove from SPFILE) and use default value so you
>>> get to benefit of the new features for your release.
>>>
>>> So if you are on 11g, and have been on it for a while, I do not see any
>>> reason to keep COMPATIBLE set to a prior version. It is not like you will
>>> downgrade to this prior version anymore. So I would simply reset it (remove
>>> from SPFILE) and let it default. Yes, you cannot go back to the outdated
>>> value you had. But again, do you still want to go back to your old release?
>>>
>>> Please, corrections are always welcomed.
>>>
>>> Carlos Sierra
>>> Life is Good!
>>>
>>>
>>>
>>> On Mar 25, 2015, at 11:47, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
>>> wrote:
>>>
>>> I bet the database uses the optimizer though. :-)
>>>
>>> There are hundreds, maybe even thousands, of differences in the query
>>> optimizer between 10gR2 and 11gR2, and I expect the vast majority of those
>>> are suppressed when you run with COMPATIBLE=10.2.0.4
>>>
>>> You really should do a regression test before making this change,
>>> especially considering that it is a one-way thing. You can increase the
>>> value of COMPATIBLE, but you can *never* decrease it again. Not unless
>>> you are ready to do an export/import.
>>>
>>> Most likely, you will be perfectly okay. *LOTS* of people complete
>>> this upgrade without a hitch. But in the unlikely(?) case that you are not
>>> one of them, you'll probably want to show you did "due diligence" first.
>>>
>>> On Tue, Mar 24, 2015 at 5:15 PM, Sheehan, Jeremy <
>>> JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:
>>>
>>>> It's been modified in a test instance for a few weeks but I didn't
>>>> change it. Someone else did and didn't document anything and they don't
>>>> remember if anything else was done. This is a very vanilla db. No ASM,
>>>> RAC.
>>>>
>>>> Thanks!
>>>>
>>>> Jeremy
>>>>
>>>> Sent from a phone
>>>>
>>>> *From:* Don Seiler <don_at_seiler.us>
>>>> *Sent:* Mar 24, 2015 4:55 PM
>>>> *To:* Sheehan, Jeremy
>>>> *Cc:* Oracle-L (oracle-l_at_freelists.org)
>>>> *Subject:* Re: increasing COMPATIBLE parameter
>>>>
>>>> This is an EXTERNAL email. Exercise caution. DO NOT open
>>>> attachments or click links from unknown senders or unexpected email.
>>>> ------------------------------
>>>>
>>>> I would be sure to test application behavior on a test/staging
>>>> database prior to doing this. But then again, that's solid advice for
>>>> making any change in a database.
>>>>
>>>> Sometimes behavior of various components can change with the
>>>> compatible parameter. I've seen it in ASM diskgroup compatible changes, for
>>>> what its worth.
>>>>
>>>> Don.
>>>>
>>>> On Tue, Mar 24, 2015 at 3:50 PM, Sheehan, Jeremy <
>>>> JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>>
>>>>>
>>>>> I’m on 11.2.0.3 and am looking to update the COMPATIBLE parameter in a
>>>>> database from 10.2.0.4 to 11.2.0.3. I’ve searched pretty extensively (gone
>>>>> to page 3 of Google results and checked on MOS – 733987.1). Aside from
>>>>> taking a backup before making this change, are there any additional things
>>>>> I should look for or do after the change?
>>>>>
>>>>>
>>>>>
>>>>> Please let me know! Thanks in advance.
>>>>>
>>>>>
>>>>>
>>>>> Jeremy
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Don Seiler
>>>> http://www.seiler.us
>>>>
>>>
>>>
>>>
>>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 25 2015 - 20:11:48 CET