Corruption / Internal Errors Possible after Upgrading a Database to
Oracle 9.2.0.1It 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:
- An object is created in a pre-9.0 database
- The database is upgraded to Oracle 9.0.1 (any Patch Set level) and the
object is truncated.
- 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:
- 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.
- 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:
- 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;
- Identify any objects which may have been truncated. 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
;
- For each of these objects rebuild its freelists using the
DBMS_REPAIR.REBUILD_FREELISTS procedure, starting with the object which
have FREELIST GROUPS.
Eg: execute dbms_repair.rebuild_freelists('SCOTT','EMP',null,dbms_repair.table_object); See
Detecting
and Repairing Data Block Corruption in the Database Administrators
Guide for details of using this procedure. Note that you should rebuild
freelists for all potentially affected objects REGARDLESS of whether they
have FREELIST GROUPS or not. 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:
- Login to MetaLink - http://metalink.oracle.com
- Choose Patches from the Menu.
- Input 2384289 into the Patch Number field.
- Click the Submit button.
At the present time (31st May
2002) no patches have yet been uploaded.
References
|