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

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.

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

…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.

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

“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.

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.

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?

“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.*).

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 :)

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.

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.

>>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
=========

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.

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

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 [email protected];
rowid as select * from [email protected]
*
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.

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

[…] https://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 […]