Re: increasing COMPATIBLE parameter

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 <mailto: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 <mailto:don_at_seiler.us>>
> Sent: Mar 24, 2015 4:55 PM
> To: Sheehan, Jeremy
> Cc: Oracle-L (oracle-l_at_freelists.org <mailto: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 <mailto: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 <http://www.seiler.us/>

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