Home > askTom > – Thanks for the question regarding “compatible init.ora parameter”, version 7.3.4

– Thanks for the question regarding “compatible init.ora parameter”, version 7.3.4

You Asked

need extensive documentation on compatible parameter
any pointers ?

Thanks

and we said…

This is support note:

Article-ID:         <Note:30699.1>
Alias:              PARAMETER:COMPATIBLE
Circulation:        PUBLISHED (EXTERNAL)
Folder:             server.Rdbms.Reference.Parameters
Topic:              Standard INIT.ORA Parameters
Title:              PARAMETER: INIT.ORA: COMPATIBLE
Document-Type:      REFERENCE
Impact:             LOW
Skill-Level:        ACCOMPLISHED

Parameter: COMPATIBLE
~~~~~~~~~~~~~~~~~~~~~

 Parameter type:             string
 Parameter class:            static
 Default value:              Oracle8i is 8.1.0
                 Oracle 8 is 8.0.0
                 Oracle 7 is 7.0.x
 Range of values:            default release to current release
 Multiple instances:         must have the same value
 Related:             <Parameter:COMPATIBLE_NO_RECOVERY>

Description:
~~~~~~~~~~~~
This parameter allows you to use a new release, while at the same time  guaranteeing
backward compatibility with an earlier release (in case  it becomes necessary to revert
to the earlier release). This parameter specifies the release with which Oracle7 Server
must maintain compatibility. Some features of the current release may be restricted. For
example, if you are running release 7.2.2.0 with compatibility set to 7.1.0.0 in order to
guarantee compatibility, you will not be able to use 7.2 features. 

When using the standby database and feature, this parameter must have the same value on
the primary and standby databases, and the value must be 7.3.0.0.0 or higher.

This parameter allows you to immediately take advantage of the maintenance improvements
of a new release in your production systems without testing the new functionality in your
environment. 

The default value is the earliest release with which compatibility can be guaranteed.
Ie: It is not possible to set COMPATIBLE to 7.3 on an Oracle8 database.

Extra Notes:
    Features enabled at different levels include:
          7.1.0     read-only tablespaces, multiple same-type
                    triggers on a  table
          7.1.5     direct reads
          7.2.0     improved hash function for new hash
                    clusters, user defined
                    hash expression on hash clusters, resizable
                    datafiles
          7.3.0     standby database, unlimited extents,
                    temporary tablespace (sort segments)
          7.3.x     bitmap indexes
      8.0.0        Lots of things
      8.1.0        UROWID and ROWIDs for IOT tables
Reviews
5 stars setting compatble parameter   November 27, 2003 – 9am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
Hi
We are using 9.2.0.1 but compatible is set to 9.0.0, do we have to set it to 9.2.0? Any gains?

Followup   November 27, 2003 – 11am Central time zone:

compatible tells us what features we are allowed to use that affect how stuff is stored on disk.

you do not "have" to set it.

you might find that some new features are not available to you (you'll get an error message to that
effect)

note that if you set it up, you in general cannot set it back (it modifies how we store, persist
data on disk -- this is not reversible).... SO, test it first on a test system.
5 stars setting compatible parameter on Oracle 10g   May 9, 2005 – 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Vladimir Rojas from CA USA
Hi Tom,
Can I have oracle 10g instance with compatible parameter set-up as 9.2.0.0, ?, what is the process
to do that...... I try when I create a database instance but without success

Thanks and Regards
Vlad

Followup   May 9, 2005 – 3pm Central time zone:

yes, but why?  what is the goal here?  

you would have to set it in the parameter file before creating the database.

idle> startup nomount;
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                   778032 bytes
Variable Size              61874384 bytes
Database Buffers           50331648 bytes
Redo Buffers                 262144 bytes
idle> create database;

Database created.

idle> !cat initXXX.ora
db_name=XXX
compatible=9.2.0.0

idle> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      9.2.0.0
5 stars Compatible Param   May 25, 2005 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Himanshu from INDIA
Hi, 

Could you please confirm the compatible parameter needs to be set to ‘9.2.0.x’ release or to only
‘9.2.0’ for all subsequent release in 9.2 series. 

My guess is 9.2.0 should be sufficient. 

Can you provide us with some matrix on this ? 

This is needed ASAP. 

Regards
Himanshu

Followup   May 25, 2005 – 2pm Central time zone:

compatible should be set to the level you need it to be set to.

9.2.0 should be sufficient, there is no "matrix" I'm aware of.
5 stars Compatible   June 14, 2005 – 1am Central time zone
Bookmark | Bottom | Top
Reviewer: atul from India
Hi tom,

What's the differnece if we set compatible to 9.2.0.6 or 9.2.0

My database has been upgraded to 9.2.0.6.

Please suggest

Followup   June 14, 2005 – 9am Central time zone:

if you set it to 9206 you cannot downgrade to 9200, it enables the use of new on disk storage
structures that once we start using them - you cannot downgrade anymore.
5 stars How Can I Resolve Version Problem   July 28, 2005 – 8am Central time zone
Bookmark | Bottom | Top
Reviewer: Muhammad Waseem Haroon from Karachi, Pakistan
How Can I Resolve an Error :
ORA-01571: redo version 9.2.0.0.0 incompatible with ORACLE version 9.0.0.0.0

Please Help

Thanks in Advance.

Followup   July 28, 2005 – 10am Central time zone:

one would need more information as to the steps leading up to getting this error.

[tkyte@desktop tkyte]$ oerr ora 1571
01571, 00000, "redo version %s incompatible with ORACLE version %s"
// *Cause:  This software version can not read the current redo logs, and
//          either crash recovery is required or there are offline database
//          files that need media recovery. If a file name is listed then
//          it needs media recovery.
// *Action: Shutdown and startup using the compatible software. Do any required
//          media recovery, and open the database. Shutdown and then startup
//          using current software. If the file is going to be dropped then
//          take it offline with the DROP option to skip this check.
5 stars incompatiblity   July 29, 2005 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Muhammad Waseem Haroon from Karachi, Pakistan
How Can I Resolve an Error :
ORA-01571: redo version 9.2.0.0.0 incompatible with ORACLE version 9.0.0.0.0

Please Help

Thanks in Advance.

Followup:
one would need more information as to the steps leading up to getting this
error.

[tkyte@desktop tkyte]$ oerr ora 1571
01571, 00000, "redo version %s incompatible with ORACLE version %s"
// *Cause:  This software version can not read the current redo logs, and
//          either crash recovery is required or there are offline database
//          files that need media recovery. If a file name is listed then
//          it needs media recovery.
// *Action: Shutdown and startup using the compatible software. Do any required
//          media recovery, and open the database. Shutdown and then startup
//          using current software. If the file is going to be dropped then
//          take it offline with the DROP option to skip this check.

Actually the Case is that, I have installed Oracle 9i Version 9.0.0.0.0 (For Testing) and after the
installation I Have Restored the full offline Backup of version 9.2.0.0.0 (PROD Database).
I have issued the command "STARTUP".
Database is mounting.... but not opening and its giving me an error

ORA-01571: redo version 9.2.0.0.0 incompatible with ORACLE version 9.0.0.0.0

I Have Just Received this Error, I Hav'nt Received any other Error.
How can I Resolve this Issue of incompatiblity?

Thanks in Advance
Muhammad Waseem Haroon
mwaseem_haroon@hotmail.com

Followup   July 29, 2005 – 2pm Central time zone:

that is not going to work, think about it.

You have old software,
new datafiles

9.0 software cannot read 9.2 *stuff* and the stuff you have is 9.2
5 stars compatible initialization parameter   October 14, 2005 – 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader from San Francisco
Hi Tom,
If the compatibility parameter is set as:
    COMPATIBLE=9.2.0

Does Oracle interpret it as 9.2.0.* or 9.2.0.1.0?
In other words, does COMPATIBLE=9.2.0 mean the lowest or the highest sub-release?

On a related topic, when you install the Oracle software on your systems, do you use the full
release number in your ORACLE_HOME?  For example, which of these would you use for ORACLE_HOME when
installing 9.2.0.4.0
  (a) /opt/oracle/product/9.2.0.4.0
  (b) /opt/oracle/product/9.2.0

If (a) was used, the directory name would not match the version when the software is eventually
upgraded to 9.2.0.7.0.  So is it better to install the software into (a) instead of (b)?

Followup   October 14, 2005 – 6pm Central time zone:

It is "lowest"

As a test case - my compatible was 10.2.0

I set it to 10.2.0.1... and bounced.

Then I set it to 10.2.0 and get:

ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version
10.2.0.0.0
ORA-00202: control file: '/home/ora10gr2/oradata/ora10gr2/control01.ctl'

(now, I'm off to fix that ;) 

I don't like to use the full release number - just the major release - my homes look like
..../9ir1/, ..../9ir2/, .../10gr1/...., and so on.

for the reason you pointed out. the directory would seem to say "i'm 9.2.0.4" but reality might be
that it is 9.2.0.7
5 stars Compatible   March 30, 2006 – 3am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
Tom,

We are going to replace our old 9i database server and move it to a new HW. So I want to use this
to upgrade the database to 10g at the same time. The problem is that the application vendor says
that the application is "not tested/certified with Oracle 10g" and recommends to stay with 9i.

My question is:

Can I install 10g software, set compatible parameter to 9.2.0 before creating the database and
later when the software vendor certified the application simple change the compitible parameter
(saving upgrade 9i to 10g this way)?

Followup   March 31, 2006 – 11am Central time zone:

You would have to work with your 3rd party software vendor.  

compatible simply tells us what we can write to disk.  We won't use new redo log formats, we won't
write new block level information, we won't create datafiles that have anything 9ir2 didn't have.

But you would be entirely using the 10g software, optimizer, set of features.

compatible would not change this all.
3 stars Snapshot in 9.2.0   April 17, 2006 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Kuldeep from India
Dear Tom,

I have upgraded my database from 8.1.7 to 9.2.0.7 on SunOS.

After upgrade, refreshing my snapshot are taking greater time and two snapshot refresh does not
complete at all and hangs the session.

I am using the following command to refresh snapshot:
dbms_snapshot.refresh(list=> 'censys_tns',rollback_seg=>'amarbs01')

I read somewhere that after upgrade to oracle 9i re-creating the snapshot would help. I dropped the
snapshot and trying to re-create it but create command is not completing, it hangs forever.

-------------- snapshot create command --------------
CREATE MATERIALIZED VIEW OPS$VSIS.CENSYS_TNS
PCTFREE    0
PCTUSED    99
INITRANS   2
MAXTRANS   255
STORAGE (
  INITIAL         20480 K
  NEXT            5120 K
  MINEXTENTS      1
  MAXEXTENTS      UNLIMITED
  PCTINCREASE     0
  FREELISTS       1
  FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
TABLESPACE REFERENCE
LOGGING
NOCACHE
NOPARALLEL
USING INDEX PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE (
  BUFFER_POOL DEFAULT)
REFRESH FORCE
  WITH ROWID
 USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
select  /*+ FULL(tns) FULL(ord) */ distinct
    tns.CUSTOMER    cust_ref_no
   ,tns.SITE        site_code
   ,decode(tns.SERVICE_TYPE,'BILLING',null,tns.SWITCH_ID) switch_id
   ,tns.PRIMARY     public_tn
 from TN_SYS@censys tns
    ,CENSYS_MAX_ORD ord
 where tns.ORDER_NUM = ord.ORDER_NUM
 and tns.SITE is not null;
-------------------------------------------------------

When I execute snapshot's query it is returning the result in a couple of minutes, I am also able
to create table using this query (create table as).

The compatible parameter set in my datbase is 9.2.0.

Will re-setting compatible to 8.1.7 would help any way, because snapshots are depreciated in
oracle9i.
If not please help me to overcome from this problem.

Thanks and regards,

Followup   April 17, 2006 – 8pm Central time zone:

snapshots are NOT deprecated (materialized views are snapshots are materialized views).  the "word"
snapshot might be, but materialized views are the snapshots of old (in fact, there are synonyms
making the two the same)

compatible does not affect how things work, it only affects what features might be used (preventing
you from using features that would create on disk images that older releases would never be able to
understand).

lose the hints, why have hints, just lose them.

And trace it, what is happening - if possible compare the trace to the 817 trace and see "what is
different"

Let me ask you - when you tested this before upgrading, did you experience the same issues?
4 stars Snapshot refresh problem in oracle 9.2.0.7   April 24, 2006 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Kuldeep from India
Dear Tom,

Thanks for your reply.

I created censys_tns snapshot view after removing hints. It took around 14 minutes. Thanks. Earlier
I was not able to create 

this because it was taking too long time to create and session seems to be hang.

I had not tested any of snapshot in 8.1.7 database, all were running fine in 8.1.7. I do not have
trace of database 8.1.7 for 

any snapshot refresh. But I have traced the development database's snapshot refresh where it is
taking half time than the 

production. But parameters on development database are different. These are as below:

On Development
--------------
SQL> show parameter block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     200
db_block_checking                    boolean     FALSE
db_block_checksum                    boolean     TRUE
db_block_size                        integer     2048
db_file_multiblock_read_count        integer     8

On Production
--------------
SQL> show parameter block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     76800
db_block_checking                    boolean     FALSE
db_block_checksum                    boolean     TRUE
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     128

Trace on Development
====================
********************************************************************************

INSERT /*+ APPEND */ INTO "OPS$VSIS"."CENSYS_SO" select distinct
         sot.CUSTOMER           cust_ref_no
        ,tn.TN_AREA             site_code
        ,tn.SWITCH_ID           switch_id
        ,sot.TN                 public_tn
  from  SO_TN@censys  sot
       ,TN@censys  tn
  where sot.TN              = tn.TN
  and   sot.STATUS      in (select LOOKUP_CODE
                             from   VSIS_LOOKUP_CODES
                             where  LOOKUP_DOMAIN = 'CENSYS_SO'
                             and    LOOKUP_SUBCODE = 'STATUS')
  and   tn.TN_AREA           is not null
  and   length(tn.TN_AREA) <= 6

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.09          0          0          0           0
Execute      2    347.87     961.25     192664    3228308     133009      452280
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    347.88     961.35     192664    3228308     133009      452280

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 53     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT
 452280   SORT UNIQUE
 470576    MERGE JOIN
1564373     SORT JOIN
1564373      TABLE ACCESS BY INDEX ROWID VSIS_LOOKUP_CODES
3128747       NESTED LOOPS
1564373        REMOTE
1564373        INDEX RANGE SCAN VLC_PK (object id 43189)
 470576     SORT JOIN
 471415      REMOTE

********************************************************************************

Trace on Production
===================
********************************************************************************

INSERT /*+ APPEND */ INTO "OPS$VSIS"."CENSYS_SO1" SELECT DISTINCT
         sot.CUSTOMER           cust_ref_no
        ,tn.TN_AREA             site_code
        ,tn.SWITCH_ID           switch_id
        ,sot.TN                 public_tn
  FROM  SO_TN@censys  sot
       ,TN@censys  tn
  WHERE sot.TN              = tn.TN
  AND   sot.STATUS      IN (SELECT LOOKUP_CODE
                             FROM   VSIS_LOOKUP_CODES
                             WHERE  LOOKUP_DOMAIN = 'CENSYS_SO'
                             AND    LOOKUP_SUBCODE = 'STATUS')
  AND   tn.TN_AREA           IS NOT NULL
  AND   LENGTH(tn.TN_AREA) <= 6

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.30          0          1          0           0
Execute      1    694.49    1578.19       2591     470609         14      452300
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    694.52    1578.50       2591     470610         14      452300

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT
 452300   SORT UNIQUE
 470607    NESTED LOOPS
 471476     NESTED LOOPS
1564285      REMOTE
 471476      REMOTE
 470607     TABLE ACCESS BY INDEX ROWID VSIS_LOOKUP_CODES
 470607      INDEX UNIQUE SCAN VLC_PK (object id 179852)

********************************************************************************

I am also facing performance problem in my SQLs after upgrade to 9.2.0.7.
=========================================================================

Earlier below query was running in just 15-20 seconds but now it is taking 4 minutes. Unfortunately
for this too
I have not captured the explain plan in 8i db.
I have removed hint from query and analyzed all 6 tables and its 13 indexes using analyze
table/index <name> compute statistics.

But this query is not using any index and taking much time. 

Please help.

SQL> select /* + FIRST_ROWS */ jc.job_no,
  2         jc.phase_end_datetime,
  3         jc.job_status,
  4         olj.order_no,
  5         co.customer_no,
  6         rt.report_name,
  7         rt.data_source_type,
  8         substr(c.customer_name,1,15)
  9  from   ops$vsis.vsis_job_control jc,
 10         ops$vsis.vsis_ord_line_jobs olj,
 11         ops$vsis.vsis_cust_orders co,
 12         ops$vsis.vsis_cust_order_details cod,
 13         ops$vsis.vsis_customers c,
 14         ops$vsis.vsis_report_templates rt
 15  where  jc.job_status = 'E'
 16    and  jc.phase_end_datetime  >  (sysdate-30)
 17    and  jc.job_no = olj.job_no
 18    and  olj.order_no = cod.order_no
 19    and  olj.order_line_no = cod.order_line_no
 20    and  olj.order_line_detail_no = cod.order_line_detail_no
 21    and  cod.order_no   = co.order_no
 22    and  co.customer_no = c.customer_no
 23    and  cod.report_no  = rt.report_no
 24    and  jc.job_line_no = (select /* + index (d VJC_VOLJ_FK_I) */ max(d.job_line_no)
 25                               from ops$vsis.vsis_job_control d
 26                                   where jc.job_no = d.job_no);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21915 Card=1515 Byte
          s=234825)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=21915 Card=1515 Bytes=234825)
   3    2       MERGE JOIN (Cost=21397 Card=43914 Bytes=6806670)
   4    3         SORT (JOIN) (Cost=11500 Card=23530 Bytes=3482440)
   5    4           MERGE JOIN (Cost=10994 Card=23530 Bytes=3482440)
   6    5             SORT (JOIN) (Cost=10989 Card=23530 Bytes=2447120)
   7    6               MERGE JOIN (Cost=10622 Card=23530 Bytes=2447120)
   8    7                 SORT (JOIN) (Cost=10616 Card=23530 Bytes=1694160)
   9    8                   MERGE JOIN (Cost=10351 Card=23530 Bytes=1694160)
  10    9                     MERGE JOIN (Cost=10264 Card=23530 Bytes=1364740)
  11   10                       SORT (JOIN) (Cost=10098 Card=23530 Bytes=941200)
  12   11                         MERGE JOIN (Cost=9939 Card=23530 Bytes=941200)
  13   12                           SORT (JOIN) (Cost=535 Card=23530 Bytes=470600)
  14   13                             TABLE ACCESS (FULL) OF 'VSIS_JOB_CONTROL' (Cost=440
Card=23530 Bytes=470600)
  15   12                           SORT (JOIN) (Cost=9404 Card=2270192 Bytes=45403840)
  16   15                             TABLE ACCESS (FULL) OF 'VSIS_ORD_LINE_JOBS' (Cost=393
Card=2270192 Bytes=45403840)
  17   10                       SORT (JOIN) (Cost=166 Card=42563 Bytes=766134)
  18   17                         TABLE ACCESS (FULL) OF 'VSIS_CUST_ORDER_DETAILS' (Cost=6
Card=42563 Bytes=766134)
  19    9                     SORT (JOIN) (Cost=88 Card=24777 Bytes=346878)
  20   19                       TABLE ACCESS (FULL) OF 'VSIS_CUST_ORDERS' (Cost=7 Card=24777
Bytes=346878)
  21    7                 SORT (JOIN) (Cost=7 Card=438 Bytes=14016)
  22   21                   TABLE ACCESS (FULL) OF 'VSIS_CUSTOMERS' (Cost=2 Card=438 Bytes=14016)
  23    5             SORT (JOIN) (Cost=5 Card=52 Bytes=2288)
  24   23               TABLE ACCESS (FULL) OF 'VSIS_REPORT_TEMPLATES' (Cost=2 Card=52 Bytes=2288)
  25    3         SORT (JOIN) (Cost=9897 Card=4235330 Bytes=29647310)
  26   25           TABLE ACCESS (FULL) OF 'VSIS_JOB_CONTROL' (Cost=440 Card=4235330
Bytes=29647310)
4 stars Plans and Compatible?   January 17, 2008 – 4am Central time zone
Bookmark | Bottom | Top
Reviewer: Mette Stephansen from DK DENMARK
Hi Tom,

Q0) Will a change in compatible possibly change the execution plans?

We are on 9.2.0.6 and compatble 9.2.0.0.0.  

Q1) They want to change to 9.2.0 in compatible, but to my knowledge this change will have no effect
at all, is that correct? (It will as you stated in this thread take the lowest possible compatible
which will be 9.2.0.0.0)

Q2) But a change to 9.2.0.6.0 might affect plans and other stuff?

best regards
Mette

Followup   January 17, 2008 – 11am Central time zone:

compatible affects what we store on disk. optimizer features enable would be optimizer related.

i would not expect any plan changes, no.

5 stars Clearification   January 17, 2008 – 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Mette from DK Denmark
Thanks Tom,

Does that mean that the answers are:

Q0) No effects on plans

Q1) No diff. in specifying 9.2.0 and 9.2.0.0.0. So no effects in chainging

Q2) Not plans, but possibly other stuff (ie things residing on disk)

regards
Mette

Followup   January 19, 2008 – 10pm Central time zone:

q0) i would not expect any, no.

q1) those would be the same, 9.2.0

q2) should only affect that which is stored on disk.

3 stars DBA_REGISTRY   April 4, 2008 – 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: mohammed Abuhamdieh
Greetings Mr.Kyte,

we have upgrade the database from 9.2.0.0.0 to 9.2.0.8.0 the compatible parameter is set to
9.2.0.0.0, but when i select from dba_registry it gives me that i have the "xml database" in
invalid state we tried to recompile all objects through the "utlrp.sql" but nothing happened.

my question is, does setting the compatible parameter to 9.2.0.8.0 can resolve this issue. and what
is the risk of setting it to 9.2.0.8.0.

Followup   April 4, 2008 – 10pm Central time zone:

compatible won’t do anything for you in this case. compatible only controls “things we store on disk”

please utilize support for install/configuration issues.

5 stars   July 8, 2008 – 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander

Hi,

Couple questions about this parameter.

1) When patching, should* we set this to our current patch version or is it just for major releases?

2) Will it have the same effect as a major release, and prevent us from rolling back the patch once it is set?

*By should I mean, if I want all the fixes in the patch to take affect, do I need to set this, e.g., I upgraded from 10.2.0.2 to 10.2.0.4, do I have to now

alter system set compatible = '10.2.0.4' scope=spfile;

Thank you.

Followup   July 8, 2008 – 5pm Central time zone:

when you change this, there is no going back – it permits the use of disk storage structures that older releases did not understand. You would be allowing those structures to be created.

You need to set it “up” when you want to use a new feature that uses some new structure (eg: in 11gR1, you can have table compression enabled for conventional path operations – you need compatible set to 11.1 in order to use that new feature)

Otherwise, it is your call – do you want to enable these new features – doing so will make it not possible to downgrade – once you set it up and open up the database – there is no going back.

It is pretty much mostly a major release thing

3 stars When compatible may be incompatible   July 14, 2009 – 6pm Central time zone
Bookmark | Bottom | Top
Reviewer: Mike from The Woodlands, TX USA

Hi Tom, thank you for taking your time to help all of us Oracle users!

Should sys objects stay the same between a 10.2.0.3 home’s database with compatible = 10.2.0.0.0 and an 11.1.0.6 home’s database with compatible = 10.2.0.0.0?

I ask because I’ve recently discovered a vendor’s application is breaking when I test it on an 11.1.0.6 home’s database configured with compatible = 10.2.0.0.0. The application works on a 10.2.0.3 home with the database configured to compatible = 10.2.0.0.0 I do realize that this would be a very difficult task for the database software to achieve, but the documentation leads me to believe that it should work, even without testing first. Any clarification as to when we should actually use this parameter will be appreciated!

Notes from 11g Reference Guide:
COMPATIBLE allows you to use a new release of Oracle, while at the same time
guaranteeing backward compatibility with an earlier release. This is helpful if it
becomes necessary to revert to the earlier release.
This parameter specifies the release with which Oracle must maintain compatibility. It
allows you to take advantage of the maintenance improvements of a new release
immediately in your production systems without testing the new functionality in your
environment
. Some features of the release may be restricted.

The sys objects I found, there may be more, to break the vendor’s application are:
– Oracle 10g instance with compatible = 10.2.0.0.0

CREATE OR REPLACE
TYPE SYS.ODCICOLINFO AS object
(
TableSchema VARCHAR2(30),
TableName VARCHAR2(30),
ColName VARCHAR2(4000),
ColTypeName VARCHAR2(30),
ColTypeSchema VARCHAR2(30),
TablePartition VARCHAR2(30)
);
/

CREATE OR REPLACE
TYPE SYS.ODCIINDEXINFO AS object
(
IndexSchema VARCHAR2(30),
IndexName VARCHAR2(30),
IndexCols ODCIColInfoList,
IndexPartition VARCHAR2(30),
IndexInfoFlags NUMBER,
IndexParaDegree NUMBER
);

– Oracle 11g with compatible set to 10.2.0.0.0
– Note the additional attributes

CREATE OR REPLACE TYPE SYS.ODCIColInfo AS object
(
TableSchema VARCHAR2(30),
TableName VARCHAR2(30),
ColName VARCHAR2(4000),
ColTypeName VARCHAR2(30),
ColTypeSchema VARCHAR2(30),
TablePartition VARCHAR2(30),
ColInfoFlags NUMBER,
OrderByPosition NUMBER,
TablePartitionIden NUMBER,
TablePartitionTotal NUMBER
);
/

CREATE OR REPLACE TYPE SYS.ODCIIndexInfo AS object
(
IndexSchema VARCHAR2(30),
IndexName VARCHAR2(30),
IndexCols ODCIColInfoList,
IndexPartition VARCHAR2(30),
IndexInfoFlags NUMBER,
IndexParaDegree NUMBER,
IndexPartitionIden NUMBER,
IndexPartitionTotal NUMBER
);

Unfortunately the vendor will not support 11g for another year. I really wish to keep with the latest version. Is this a bug?

Followup   July 15, 2009 – 11am Central time zone:

… Should sys objects stay the same between a 10.2.0.3 home’s database with compatible = 10.2.0.0.0 and an 11.1.0.6 home’s database with compatible = 10.2.0.0.0? …

no, compatible only controls what is written to disk.

You have compatible at 10.2

That means 11g will generate redo that a 10.2 database could process.

That means 11g will generate database blocks that 10.2 database could process (eg: new OLTP compressed blocks will NOT appear in that database – that feature will not be able to be used, since 10.2 cannot read that block format)

That means anything written to disk will be in a format the earlier release could read.

but the data dictionary will be definitely different between each release. We give you upgrade scripts to upgrade 10.2 to 11.1. We give you downgrade scripts to downgrade 11.1 to 10.2. But each release needs to have it’s dictionary format in place.

4 stars taking back the compatible prameter   October 3, 2009 – 4am Central time zone
Bookmark | Bottom | Top
Reviewer: muhammad from sa
Hi Tom,
We have a mission critical application database which was upgraded to 10g with the compatible
parameter still set to 9205
why? because there were sql codes embeded in the application front end that uses specific hints as
per 9205 optimizer.

Later vendor has supplied to 10g compliance code and we tested it and now we would like to change
the parameter to 10g
for reasons like a)better performance of db since optimizor will chose better plan, b) usage of adv
feature of fsfo etc

Problem is the development is asking for a fall back procedure. 

We do have a standby database for this. Now if we stop replication to that site after change of
parameter in prod.
And said the management that if they feel to fall back , they can move to drc but with old data.

Question is: I know there is no fall back of the parameter. But in real world, its hard to convince
management
and they would like a foll proof fallback procedure with much less down time and with changes in
tact.
How can we do it?

Followup   October 7, 2009 – 4pm Central time zone:

… why? because there were sql codes embeded in the application front end that
uses specific hints as per 9205 optimizer. …

too bad compatible has NOTHING at all to do with that. compatible will not affect the optimizer in the way you seem to think. it (compatible) controls what we write to disk – what format it is.

once you set compatible to X+1 from X, you cannot set back to X. That is because is controls what we write to disk – not what the optimizer does. And if we write something that only 10g can read – we cannot – repeat CANNOT – go back to 9i. So, as soon as you open a database with compatible at X+1, it’ll be X+1 and no going back to X – ever.

how can you do it? It is called testing, testing your application in test with compatible set higher.

3 stars COMPATIBLE=9.2.0.8 and AWR   December 7, 2009 – 3am Central time zone
Bookmark | Bottom | Top
Reviewer: Ishi Vora
Hi Tom,

This question is for information purpose only. I would like to know if AWR would work fine in my
10.2.0.4 database even if COMPATIBLE parameter value is set to 9.2.0.8?

Can you also point me to some documentation on OTN or Metalink which lists all 10G features reuire
COMPATIBLE parameter value to 10.2.X.X..?

Thanks for your help...

Cheers,
Ishi

Followup   December 7, 2009 – 2pm Central time zone:

compatible controls what is written to disk, how it is written – how the redo stream is formatted, how the database blocks are formatted.

Only features that change what is logged or what the structure of a block looks like would need compatible to be set to a certain level.

AWR is a repository, it doesn’t change the way blocks are formatted on disk.

I’m not aware of a comprehensive list of features that require compatible to be set to a certain level. Basically – you would go to the feature in the documentation (you must be doing that anyway, you are learning about this new feature before you use it) and if it says “compatible must be x.y.z” then you have it.

5 stars Version difference   February 11, 2010 – 11pm Central time zone
Bookmark | Bottom | Top
Reviewer: Niddhi from India
Hi, 

This is for the first time I am working with Solaris and installing oracle over it.

When i give the command
          STARTUP MOUNT

It gives me the following error:

ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version 10.2.0.

Kindly let me know how to solve this as I am stuck with this for over a day now.

Thanks!!

Followup   February 16, 2010 – 11am Central time zone:

you chopped off the message, it would look in full like this:

ORA-00201: control file version 10.2.0.4.0 incompatible with ORACLE version
10.2.0.0.0
ORA-00202: control file: '/home/ora10gr2/oradata/ora10gr2/control01.ctl'

update your compatible init.ora parameter to say 10.2.0.4 instead of whatever it currently says.

sys%ORA10GR2> startup force pfile=initora10gr2.ora
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             138413308 bytes
Database Buffers          390070272 bytes
Redo Buffers                7118848 bytes
ORA-00201: control file version 10.2.0.4.0 incompatible with ORACLE version
10.2.0.0.0
ORA-00202: control file: '/home/ora10gr2/oradata/ora10gr2/control01.ctl'

sys%ORA10GR2> !grep compatible initora10gr2.ora
*.compatible='10.2.0.'

sys%ORA10GR2> edit initora10gr2.ora

sys%ORA10GR2> !grep compatible initora10gr2.ora
*.compatible='10.2.0.4.0'

sys%ORA10GR2> startup force pfile=initora10gr2.ora
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             230687996 bytes
Database Buffers          297795584 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
sys%ORA10GR2>
5 stars COMPATIBLE parameter and optimizer plans   August 5, 2010 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Arun Mathur from Marietta,GA
Hi Tom,

We had an Oracle consultant review our database instances for any general recommendations. One item
pointed out dealt with the compatible parameter. It's currently set to 9.2.0.0.0, and we are
running 10.2.0.4. He mentioned that we could be shooting ourselves in the foot by forcing Oracle to
not use the 10g optimizer. However, based on the documentation and this discussion, I am under the
impression that modifying the compatible parameter does not influence the optimizer's behavior in
any way. Our optimizer_features_enabled_parameter is currently 10.2.0.4. So, I'd like to conclude
that we do not need to modify our compatible parameter in order to ensure our 10g databases use the
10g optimizer, and we only need to modify the compatible parameter when it's required by a feature
we're interested in using.

What do you think?

Thanks,
Arun

Followup   August 5, 2010 – 1pm Central time zone:

compatible isn’t ‘optimizer’, it controls what format disk files are generated in. Right now you are making 10g write redo log files that look like 9i redo log. Data files are written in a format that 9i could read. Any change that took place to file formats is forbidden to be used by you right now.

optimizer features enable is all about the optimizer. It will make the 10g optimizer work “a lot like, but not exactly the same as” the 9i optimizer if you have it set to 9.x

You are correct in your observation.

5 stars Thanks for the quick response!   August 5, 2010 – 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
4 stars 10g compatible variants   November 1, 2010 – 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Alejandro Flores from Guadalajara, México
Hi Tom,

Is there any difference in database performance between to set compatible = 10.2.0 and compatible =
10.2.0.4?

Thank you in advance.

Followup   November 1, 2010 – 3pm Central time zone:

compatible isn’t about performance inasmuch as it is about what FEATURES you may use.

No, there wouldn’t really be a difference between the two above ‘performance’ wise.

5 stars Compatibility parameter during backup and restore   December 23, 2010 – 12am Central time zone
Bookmark | Bottom | Top
Reviewer: Pradeep from India
Hi Tom,

I sincerely appreciate your responses on oracle issues.

I have one question regarding the compatibile parameter.

We have taken a backup of DB at version 9.2.0.8.0. 

Now we are restoring this into version 10.1.0.2.0. 

Error says the export version (9.2.0.8) is incompatible with import version (10.1.0.2.0).

My question is whether we need to change the init.ora compatible parameter of DB (at 10.1.0.2.0) to
9.2.0.8 so that it accepts the backup?

Thanks, 

Pradeep

Followup   December 23, 2010 – 10am Central time zone:

you didn’t take a backup! You created a logical copy, a dump, of a database – one that might be able to be imported – or not. it is certainly NOT a backup however, it is just a bunch of data you might be able to reload at some point in time.

(for example, if you used sys or sysdba to export AND you used consistent=yes, I seriously doubt you can import that data – since consistent=y and sysdba are incompatible.

you do not need to change compatible at all – you just need to make sure the export comes from any version LESS THAN or EQUAL TO the version you are importing into.

And again – please do not call this a backup, you’ll only confuse people. It is a dump, an export, a logical copy. It is NOT a backup. You cannot “restore” this data, you might be able to import it.

5 stars thank you TOM   December 23, 2010 – 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Pradeep from India
Thank you for the clarification TOM. 

Ill remember this. 

The export is done by user 'system' and also imported by user 'system'.

Actually the export and import happens through application GUI so we dont really have an option of
choosing the parameters (like consistent=y)

Thank you once again!!

Pradeep

Followup   December 24, 2010 – 12pm Central time zone:

hopefully they use consistent=y

otherwise, your dump is going to be logically corrupt and if you use database constraints – it won’t import. if you do all constraints in the application – you just have garbage and you won’t know it right away.

4 stars should i upgrade?   February 8, 2011 – 3am Central time zone
Bookmark | Bottom | Top
Reviewer: A
Hello Tom,
On a 10.2.0.4 database where the compatible parameter is set to 10.2.0.3, will it behave as
10.2.0.3 or 10.2.0.4?

As we are getting ORA-00600 errors on 10.2.0.3 but on not 10.2.0.4 (where the compatible parameter
is set to 10.2.0.3). Oracle is suggesting us to upgrade it to 10.2.0.4.

We are not sure whether migrating it to 10.2.0.4 will really solve the issue as we are unable to
reproduce it on 10.2.0.4 (compatible parameter = 10.2.0.3).

We have tried to simulate the same set of data/steps both the dbs

I know the question is quite vague but your thoughts please ...

Followup   February 9, 2011 – 7am Central time zone:

I cannot comment without knowing the background – I don’t have visibility to your SR and cannot really comment given this little bit of detail

5 stars Downgrade a database   June 12, 2011 – 6am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
Hi,
I need to downgrade my oracle database from 10.2.0.3 to 10.2.0.2. 

The closest instructions I can find is the 10g Release 2 (Downgrading a Database Back to the
Previous Oracle Database Release) guide.

As for compatibility, it says "If the compatibility level of your release 10.2 database is 10.2.0
or higher, then you will not be able to downgrade."

SELECT name, value, description FROM v$parameter
WHERE name='compatible';

I get 10.2.0.2 . Does this mean I won't be able to do this ??

Am I looking at the right documentation ? I can't find anything else relating to what I want to do.

I'd appreciate your suggestions.

Followup   June 17, 2011 – 10am Central time zone:

You can downgrade – since you want to go to 10.2.0.2. The doc you read was talking about a major database downgrade – back to 10.1 from 10.2 – which you cannot do.

Please utilize support to get the procedure for the downgrade.

Categories: askTom Tags:
  1. October 10th, 2011 at 20:40 | #1

    Thanks for the share! Very useful info, looking to communicate!

    Webmaster of best home gym

  2. October 11th, 2011 at 21:53 | #2

    Thanks for the informative article, it was a good read and I hope its ok that I share this with some facebook friends. Thanks.

  3. October 16th, 2011 at 02:10 | #3

    This article provide many tips. Very useful to me. Thanks a lot ?

  1. No trackbacks yet.