Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle Alert on upgrades to 9.2.0.1
Corruption / Internal Errors Possible after Upgrading a Database to Oracle
9.2.0.1
It is vital that you read this alert if upgrading to Oracle 9.2.0.1 from an
earlier release
Versions Affected
This problem affects Oracle databases upgraded from pre-9.0.1 to 9.0.1.X
and then to 9.2.0.1.
Platforms Affected
The problem is GENERIC - all platforms are affected.
Description
Silent database corruption and / or internal ORA-600 errors can occur if
an Oracle database is upgraded as follows:
1.. An object is created in a pre-9.0 database 2.. The database is upgraded to Oracle 9.0.1 (any Patch Set level) and the object is truncated.
3.. The database is subsequently upgraded to Oracle 9.2.0.1 and inserts
performed on the object.
If any previously truncated objects in the database use FREELIST GROUPS
then data corruption can occur in the above scenario . (FREELIST GROUPS are
generally used in Parallel Server (OPS) or Real Application Clusters (RAC)
environments but may exist in single instance databases.)
Where FREELIST GROUPS are not used freelist corruption can occur which
results in internal ORA-600 errors when the truncated object/s are inserted
into.
Likelihood of Occurrence
There is a strong possibility of hitting this problem for databases
upgraded as described above. The problem is most likely to occur on heavily
used objects and only where TRUNCATE has been used in 9.0.1.
Data corruption can only occur when the truncated object has FREELIST
GROUPS but this corruption can affect any other object in the database.
This problem does not affect automatic space managed segments.
Possible Symptoms
The possible symptoms of this issue include:
1.. Database corruption of any object in the database.
Corruption can occur if an object with FREELIST GROUPS has been
truncated in 9.0 as the bug can leave a freelist entry pointing at space
which is no longer part of the truncated object. In this case an insert into
this incorrect "free" block can corrupt some other database object. Hence a
number of different errors can occur when the affected block is accessed.
2.. For objects without FREELIST GROUPS an insert using the bad freelist
entry is like to result in an ORA-600 [ktsgsp5] or ORA-600 [kdddgb2] error
in the inserting session. In this case the actual freelist is corrupt but
the ORA-600 prevents any subsequent data corruption.
Workaround
If you are Upgrading / Planning to upgrade
After upgrading to Oracle9i Release 2 (9.2) the freelists should be
rebuilt for all objects which fit the above scenario, especially if they use
FREELIST GROUPS. This should be done PRIOR to any insert activity on the
database:
1.. Identify any objects using FREELIST GROUPS. These are the most important objects to rebuild the freelists for if they have been truncated:
SELECT * FROM dba_segments WHERE freelist_groups>1;2.. Identify any objects which may have beentruncated. The following query will show objects which may have been truncated in the past (it will also show indexes which have had ALTER INDEX .. REBUILD used on them, and similar operations).
SET PAGES 1000 SET LINES 200 SELECT object_type, owner, substr(object_name,1,30) object_name, subobject_name, created FROM dba_objects o, sys.clu$ c WHERE object_id!=data_object_id AND data_object_id=c.dataobj#(+) AND ( c.dataobj# is null /* Not in a CLUSTER */ OR c.obj# = object_id /* OR is the cluster itself */ ) ORDER BY 1,2,3 ;3.. For each of these objects rebuild its freelists using theDBMS_REPAIR.REBUILD_FREELISTS procedure, starting with the object which have FREELIST GROUPS.
Eg: execute
If DBMS_REPAIR.REBUILD_FREELISTS reports an error for any objects make
a note of the details and contact Oracle Support.
If you have already encountered a corruption
If you have already upgraded and encountered a corruption from this
problem the only solution is to rebuild the affected object/s.
If you encounter the ORA-600 [ktsgsp5] or ORA-600 [kdddgb2]
No data corruption should have occurred - only the objects freelist is
corrupt. It should be possible to rebuild the freelists as described above.
Note that these ORA-600 errors do not mean you have hit this particular
problem - these errors can have other root causes.
Patches
This bug will be fixed in Oracle 9.2.0.2 .
Patches for this bug for 9.2.0.1 can be found on Metalink by following
these steps:
1.. Login to MetaLink - http://metalink.oracle.com 2.. Choose Patches from the Menu. 3.. Input 2384289 into the Patch Number field. 4.. Click the Submit button.
b.. Base bug for this problem <Bug:2384289> c.. d..