Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Oracle Alert on Upgrades to 9.2.0.1

Oracle Alert on Upgrades to 9.2.0.1

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 04 Jun 2002 08:13:49 -0800
Message-ID: <F001.004738A1.20020604081349@fatcity.com>

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 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        	
	 ;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:
    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. 

  At the present time (31st May 2002) no patches have yet been uploaded. References
  a.. Detecting and Repairing Data Block Corruption in the Database Administrators Guide for details of DBMS_REPAIR.REBUILD_FREELISTS.   b.. Base bug for this problem <Bug:2384289> Hemant K Chitale
http://hkchital.tripod.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jun 04 2002 - 11:13:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US