Re: increasing COMPATIBLE parameter
- From: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- To: mark.brinsmead@xxxxxxxxx
- Date: Wed, 25 Mar 2015 12:16:34 -0400
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 <mailto: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 <mailto:don@xxxxxxxxx>>
> Sent: Mar 24, 2015 4:55 PM
> To: Sheehan, Jeremy
> Cc: Oracle-L (oracle-l@xxxxxxxxxxxxx <mailto: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 <mailto: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 <http://www.seiler.us/>
Other related posts:
