Oracle Upgrade to 10.2.0.3 – Watch for ORA-600 [22635]

Jan 28, 2007 / By Alex Gorbachev

Tags: ,

When checking for new known issues with 10.2.0.3 patchset on Metalink I discovered that an upgrade problem was added – Metalink Note 401435.1.

The issue affects all 64 platforms especially when database is upgraded from any previous 32 bit release. So far there are no one-off patch and no workaround except… well, use Data Pump or exp/imp. Typical, “workaround” approach from Oracle. In my last post about 10.2.0.3 issues I mentioned Veritas bug (now fixed with one-off), for which Oracle proposed moving files to non-Veritas storage. Do they think that companies using Veritas do it just for fun of spending an awful lot amount of money? Well, some might do but I digress.

Here is the number of useful Metalink notes if you are interested:

You did not forget to test you backups, eh?

Update: Metalink Note 412271.1 was updated with much more details since I posted this entry and patches 5871314 and 5892355 are available. Note that the problem is there any change of bits 32->64 or 64->32 and even if change occurred before 10.2.0.3 patched. See note 412271.1 for more details.

26 Responses to “Oracle Upgrade to 10.2.0.3 – Watch for ORA-600 [22635]”

  • Ghassan Salem says:

    Alex,
    To get this problem, your DB must have been created with 8.0, 32 bits, with user defined types. There are Oracle clients in this case, (none of mine, yet), but odds are very small that there are many of them.

  • Thanks for info. ML doesn’t say it explicitly, though. Perhaps, 8.0 is one of possible ways? Some notes I mentioned indeed reference 8.0 to reproduce the issue.
    Common description is:
    Upgrade of a database which was originally created in
    Oracle with a different wordsize
    to 10.2.0.3 fails
    with ORA-600 [22635].

    We do have some clients with databases evolving from as far as 8.0 or even earlier.

  • Alexander Fatkulin says:

    Well,

    I almost get used to that ora-600 [uh-uh] [ouch-ouch] [ORA-00603: ORACLE server session terminated by fatal error] stuff :)

    One of the recent…

    SQL> create table transactions (
    2 tran_id number primary key,
    3 amount number not null
    4 );

    Table created.

    SQL> create materialized view log on transactions with rowid, sequence (amount) including new values;

    Materialized view log created.

    SQL> create materialized view tran_sum
    2 refresh fast on commit
    3 with rowid as
    4 select sum(amount) tran_sum, count(*) tran_count
    5 from transactions;

    Materialized view created.

    SQL> alter table tran_sum add constraint ck_tran_sum check (nvl(tran_sum,0)=0) initially deferred deferrable;

    Table altered.

    SQL> insert into transactions values (1, -1);

    1 row created.

    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-00603: ORACLE server session terminated by fatal error

    Now that looks like bug 2972961 which is believed to be affecting versions

  • Alexander Fatkulin says:

    …versions < 10.2

    Are we supposed to do regression testing instead of oracle now? :)

    Logged SR.

  • Interesting… is it a combination of aggregated MV + fast refresh on commit + deferred constraint on MV using NVL or reproducible in a simpler way? Btw, what’s Oracle version?

    “Are we supposed to do regression testing instead of oracle now?”
    For a while already… first as beta testers and then as post-beta testers.

  • Alexander Fatkulin says:

    As long as I can tell..

    All you need is (a) on commit refresh MV with (b) deferrable constraint that gets violated during commit.

    fast/complete doesn’t matter

    the reason I’ve included extra stuff – to make an example more meaningful (actually it’s a rough scratch from an existing app)

    by far reproduces on

    10.2.0.3 Linux x86/Win32
    10.2.0.2 Linux x86-64

  • Alexander Fatkulin says:

    “For a while already… first as beta testers and then as post-beta testers.”
    I don’t believe that regression testing is something on what (post-) beta-testers would focus in the first place.

  • Ghassan Salem says:

    Alexes,
    the bug in quesion is fixed in 9i and 10gr1, and I see a fix in 10.2 (done a long time ago, but somesays it does not seem to be included in the code). If you have an SR open, ask the support guy about it.

  • Alexander Fatkulin says:

    Ghassan, currently there is no fix for 10GR2.

    There is generic bug 2972961 fixed in 9.2.0.7 and 10.1.0.5 (and believed to be affecting versions < 10.2 I mentioned that in the first post).

    There is bug 5525732 for 10.2.0.2. Oracle Support asked us to wait till fix for 5525732 is being provided. The workaround is to use immediate constraint which is not an option for us.

    But the whole point is not the bug itself but its regression nature. Would you imagine getting Oracle 12g (or whatever it called) and first testing if the darn thing is still able to handle deferrable constraints? :)

  • Sasha, you mean this scenario still working in 10.1, eh?

  • Alexander Fatkulin says:

    “Sasha, you mean this scenario still working in 10.1, eh?”

    Exactly,
    as long as you are on 10.1.0.5/9.2.0.7 or applied interim patch 2972961 (available for 9.2.0.6/10.1.0.3/10.1.0.4 but _not_ for 10.2.0.*).

  • Alexander Fatkulin says:

    oh, I’ll post another buggy puzzle if Alex don’t mind… :)

    How to get this:

    SQL> select count(*) from transactions;

    no rows selected select count(*) from dual where 1=0;

    COUNT(*)
    ———-
    0

    Now how one will get “no rows selected” with a count(*) ?

    Hint: the key to the answer (with a little tweaking) can be found above :)

  • Anurag Varma says:

    We hit the bug ORA-600 [22635] on one of our databases while patching it from 10.2.0.2 to 10.2.0.3.
    I believe it was originally create as a 8i/8.0 database long time back.

    Didn’t want to try the exp/imp time consuming method … so we are waiting for the fix…

    Anurag

  • Thanks for info Anurag. I would appreciate if you keep us posted of the resolution.

  • Anurag Varma says:

    Alex,

    BTW: I see a patch fix uploaded today to metalink for this issue.
    Patch 5892355 currently available for Solaris 64 bit. I’m assuming patches
    for other envs might be coming soon…

    Have not tested it yet ..

    Anurag

  • Anurag,
    Cool. If the patch is not available than they should be able to port it on request.
    Thanks for info.

  • Bernad Antonuk says:

    >>To get this problem, your DB must have been created with 8.0, 32 bits, with user >>defined types. There are Oracle clients in this case, (none of mine, yet), but odds >>are very

    Sorry but I disagree….We ran into last night with a 9.2.0.4 database….32 bit on Solaris 2.8…which we replicated via DataGuard to Solaris 10 using 9.2.0.4….32 bit…
    broke the DG pair…activated the standby and tried to upgrade to 10G only to die….
    According to the note from Oracle…it’s trying to go from 32 bit 9i to 64 bit 10g that
    will break….Development has released 2 one of patches to address the issue

    ========

    Development have released Patch 5871314 and Patch 5892355 for 10.2.0.3 on your platform.

    These patches should be applied using OPatch 10.2.0.3
    (installed with the 10.2.0.3 patchset, or available from Metalink as Patch 4898608)
    to the 10.2.0.3 installation before upgrading the database
    =========

  • Bernard Antonuk says:

    Just tested Patch 5871314 and Patch 5892355 for 10.2.0.3 on Solaris 10…
    They work…upgrade completed…

  • Bernard, thanks for info. I updated the article with path numbers.
    Actually, any change in bit numbers will cause an issue even if change happened during one of previous upgrades.

  • Tom says:

    Why did my patch apply failed?

    $ export OBJECT_MODE=32_64
    $ $ORACLE_HOME/OPatch/opatch apply
    Invoking OPatch 10.2.0.3.0

    Oracle interim Patch Installer version 10.2.0.3.0
    Copyright (c) 2005, Oracle Corporation. All rights reserved..

    Oracle Home : /export/home/oracle/10.2.0
    Central Inventory : /export/home/oracle/oraInventory
    from : /var/opt/oracle/oraInst.loc
    OPatch version : 10.2.0.3.0
    OUI version : 10.2.0.3.0
    OUI location : /export/home/oracle/10.2.0/oui
    Log file location : /export/home/oracle/10.2.0/cfgtoollogs/opatch/opatch2007-04-
    03_20-41-45PM.log

    ApplySession applying interim patch ‘5871314’ to OH ‘/export/home/oracle/10.2.0′
    Invoking fuser to check for active processes.
    Invoking fuser on “/export/home/oracle/10.2.0/bin/oracle”

    OPatch detected non-cluster Oracle Home from the inventory and will patch the lo
    cal system only.

    Please shutdown Oracle instances running out of this ORACLE_HOME on the local sy
    stem.
    (Oracle Home = ‘/export/home/oracle/10.2.0′)

    Is the local system ready for patching?

    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    Backing up files and inventory (not for auto-rollback) for the Oracle Home
    Backing up files affected by the patch ‘5871314’ for restore. This might take a while…
    Backing up files affected by the patch ‘5871314’ for rollback. This might take a while…

    Patching component oracle.rdbms, 10.2.0.3.0…
    Updating archive file “/export/home/oracle/10.2.0/lib/libgeneric10.a” with “lib/libgeneric10.a/kope2.o”
    Updating archive file “/export/home/oracle/10.2.0/lib/libgeneric10.a” with “lib/libgeneric10.a/kotg.o”
    Updating archive file “/export/home/oracle/10.2.0/lib32/libgeneric10.a” with “lib32/libgeneric10.a/kope2.o”
    Updating archive file “/export/home/oracle/10.2.0/lib32/libgeneric10.a” with “lib32/libgeneric10.a/kotg.o”

    Patching component oracle.rdbms.rsf, 10.2.0.3.0…
    Updating archive file “/export/home/oracle/10.2.0/lib/libgeneric10.a” with “lib/libgeneric10.a/kope2.o”
    Updating archive file “/export/home/oracle/10.2.0/lib/libgeneric10.a” with “lib/libgeneric10.a/kotg.o”
    Updating archive file “/export/home/oracle/10.2.0/lib32/libgeneric10.a” with “lib32/libgeneric10.a/kope2.o”
    Updating archive file “/export/home/oracle/10.2.0/lib32/libgeneric10.a” with “lib32/libgeneric10.a/kotg.o”
    ApplySession adding interim patch ‘5871314’ to inventory

    Verifying the update…
    Inventory check OK: Patch ID 5871314 is registered in Oracle Home inventory with proper meta-data.
    Archive failed: failed to update “/export/home/oracle/10.2.0/lib/libgeneric10.a” with updated “/ora10203/5871314/files/lib/libgeneric10.a/kope2.o”
    Archive failed: failed to update “/export/home/oracle/10.2.0/lib/libgeneric10.a” with updated “/ora10203/5871314/files/lib/libgeneric10.a/kotg.o”
    Archive failed: failed to update “/export/home/oracle/10.2.0/lib/libgeneric10.a” with updated “/ora10203/5871314/files/lib/libgeneric10.a/kope2.o”
    Archive failed: failed to update “/export/home/oracle/10.2.0/lib/libgeneric10.a” with updated “/ora10203/5871314/files/lib/libgeneric10.a/kotg.o”

    ApplySession failed: ApplySession failed in system modification phase… ‘Verification of patch failed: null’
    OPatch will attempt to restore the system…
    Restoring the Oracle Home…
    Checking if OPatch needs to invoke ‘make’ to restore some binaries…
    OPatch was able to restore your system. Look at log file and timestamp of each file to make sure your system is in the state prior to applying the patch.

    OPatch failed with error code 73

  • Chirag Majmundar says:

    Facing the same error though on 9.2.0.7 version with solaris.

    SQL> create materialized view mv_cust_impact refresh fast on demand with
    2 rowid as select * from cust_impact@mastest.snappy.mcilink.com;
    rowid as select * from cust_impact@mastest.snappy.mcilink.com
    *
    ERROR at line 2:
    ORA-00603: ORACLE server session terminated by fatal error

    This is a large table. When I do this on the smaller table, it gets created immediately.

    but with large table, it has started giving errors.

  • Chirag, your issue is probably different and you should check what’s your ora-600 (do you have one?) and look up possible issue on Metalink using ORA-600 lookup tool.

  • unix86.org » Blog Archive » Oracle Resources and Links says:

    […] http://www.pythian.com/blogs/380/oracle-upgrade-to-10203-watch-for-ora-600-22635 […]

  • […] messages — some objects not found, ORA-600, etc. I wish this were as pleasant as tasting ORA-600 chocolates (photo courtesy of Doug Burns): In a nutshell, it seemed like the SYSTEM tablespace was not in […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>