Re: increasing COMPATIBLE parameter
- From: MARK BRINSMEAD <mark.brinsmead@xxxxxxxxx>
- To: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- Date: Wed, 25 Mar 2015 14:49:58 -0400
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@xxxxxxxxx
> 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@xxxxxxxxx>
> 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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxx>
>> *Sent:* Mar 24, 2015 4:55 PM
>> *To:* Sheehan, Jeremy
>> *Cc:* Oracle-L (oracle-l@xxxxxxxxxxxxx)
>> *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@xxxxxxxxxxxxxxxxx> 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
>>
>
>
>
Other related posts:
