Skip to content

RESIZE or AUTOEXTEND can Over-size Datafiles and Corrupt the Dictionary

April 11, 2012

ALERT: RESIZE or AUTOEXTEND can “Over-size” Datafiles and Corrupt the Dictionary [ID 112011.1]

 

  Modified 07-OCT-2010     Type ALERT     Status ARCHIVED  

***Checked for relevance on 25-Jul-2010*** – Archiving Fixed-Releases: 8174 9010

*** This alert was updated on 09-Mar-2001 to add details of Bug:1646512

Oracle can allow OVERSIZED Datafiles in the Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  This alert covers: Bug:568232 Bug:925105 Bug:813983 and Bug:1646512

  All SQL in this note should be run when connected as the SYS user.
  eg: connect internal, connect / as sysdba or connect sys/sys_password

Versions Affected
~~~~~~~~~~~~~~~~~
  The problems described here affect many Oracle releases thus:
 7.1   to 7.3.4.5 inclusive
 8.0   to 8.0.6.3 inclusive
 8.1.5 to 8.1.7.3 inclusive

  The individual problems are addressed in various releases.
  Individual fix versions are described below in the “Patches” section.

 
Platforms Affected
~~~~~~~~~~~~~~~~~~
  GENERIC – these problems can affect all platforms
            except OS/390 which does NOT support datafile resizing.

 
Description
~~~~~~~~~~~
  There are three bugs covered by this alert but all four can result in
  the same form of dictionary corruption.

  The underlying problem is that an Oracle datafile can have, at most,
  4194303 Oracle datablocks.  But, certain operations allow this value to
  be exceeded resulting in corruption to the data dictionary and
  subsequent ORA-600 errors.
 
Likelihood of Occurrence
~~~~~~~~~~~~~~~~~~~~~~~~
  An ‘oversized’ file is one with more than 4194303 Oracle data blocks.
  As the DB_BLOCK_SIZE is set at database creation time the actual
  maximum file size for a given database (barring any port specific
  limits, notably 2Gb) can be found using the statement:

    SELECT to_char(4194303*value,’999,999,999,999′)||’ bytes’ MAX_FILE_SIZE
      FROM v$parameter WHERE name=’db_block_size’;

  The following operations can cause a file to contain too many Oracle
  blocks:

    Bug:813983
    ~~~~~~~~~~
   Resizing a datafile allows you to resize to a size larger
 than Oracle should allow.
 Eg: ALTER DATABASE DATAFILE ‘xxxx’ RESIZE xxxM;
 
    Bug:568232
    ~~~~~~~~~~
 It is possible to set AUTOEXTEND on a datafile with a MAXSIZE
 which exceeds the maximum allowable size.
 Eg: ALTER DATABASE DATAFILE ‘xxxx’ AUTOEXTEND ON MAXSIZE xxxM;

    Bug:925105
    ~~~~~~~~~~
 It is possible to issue an ‘ADD DATAFILE’ command without
        specifying a size (eg: on a RAW device or if a file already
        exists with the name being added).
        On some platforms this sets the file size to a default value of
 4 billion blocks which is above the maximum 4194303 blocks.
 Eg: ALTER TABLESPACE test ADD DATAFILE ‘/dev/rdsk/dummy’;
            (the lack of a SIZE on this command can cause the problem)
 
    Bug:1646512
    ~~~~~~~~~~~
 It is possible to issue an ‘ADD DATAFILE’ command with a SIZE
        larger than should be allowed. ie: Above 4194303 DB blocks.
 Eg: ALTER TABLESPACE ts ADD DATAFILE ‘/dev/rdsk/ts’ size 1073741824 M;
        This only occurs for very specific sizes – most invalid sizes will
        raise an error.

Workaround
~~~~~~~~~~
  The workaround for all of the above problems is not to use the commands
  described in this alert with sizes above the maximum for your database
  DB_BLOCK_SIZE.

  As sizes are often specified in “K” or “M” never try to use file sizes
  greater than the values given by the following select:

    SELECT to_char(4194303*value,’999,999,999,999′) MAX_BYTES,
           to_char(trunc(4194303*value/1024),’999,999,999′)||’ Kb’ MAX_KB,
           to_char(trunc(4194303*value/1024/1024),’999,999′)||’ Mb’ MAX_MB
      FROM v$parameter WHERE name=’db_block_size’;
  For convenience the table below shows the maximum sizes for common
  DB_BLOCK_SIZES:

 DB_BLOCK_SIZE   Max Mb value to use in any command
 ~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  2048   8191 M
  4096  16383 M
  8192  32767 M
        16384  65535 M

  Note: For a 2K (2048 byte) DB_BLOCK_SIZE an 8Gb datafile is TOO LARGE.
 An 8Gb file would be 8192Mb which is more than 4194303 DB blocks.
Possible Symptoms
~~~~~~~~~~~~~~~~~
  The symptoms of an oversized datafile include any of the following
  internal errors:

 ORA-600 [25012]
 ORA-600 [3292]
 ORA-600 [4375]
 ORA-600 [2847]
        ORA-600 [KCFNEW_1]

  Errors typically occur during:

 ALTER DATABASE DATAFILE ‘…’ RESIZE …M;
    or
 DROP TABLESPACE …;
    or
 When a user session tries to use space beyond the 4194303
 block mark in the file.

  Note that the above ORA-600 errors do not mean you have hit one of these
  bugs as there are other possible causes for these errors.
Checking for Problem Files
~~~~~~~~~~~~~~~~~~~~~~~~~~
  The following statements will show if you have a problem due to one
  of the above bugs:

     1. Check for Oversized files:

   SELECT f.ts#, f.file#, f.status$, f.blocks, v.name
     FROM file$ f, v$datafile v
           WHERE f.blocks > 4194303
      AND f.file#=v.file#
   ;

 If this shows any rows then go to the section below entitled
 “What to do if I have an oversized file”. If no rows are returned
 go the step 2.
     2. Check for files that could extend too far:

   SELECT x.file#, x.maxextend , v.name
     FROM filext$ x, v$datafile v
    WHERE x.maxextend > 4194303
      and v.file#=x.file#
   ;

 If this reports ORA-942 then you have no files which can over-extend.
 (If filext$ does not exist you have no files with AUTOEXTEND set)

 If this reports “no rows selected” then you have no files which can
 over-extend.

 If there are rows returned then reset the maximum file size for the
 file.
 Eg: ALTER DATABASE DATAFILE ‘filename’ AUTOEXTEND ON MAXSIZE xxxM;
     where ‘xxxM’ is less than the maximum allowed (see the
     select in “Workaround” above).

     3. Check for zero length files:

   SELECT f.ts#, f.file#, f.status$, f.blocks, v.name
     FROM file$ f, v$datafile v
           WHERE f.blocks = 0
      AND f.file#=v.file#
   ;

 If this shows any rows then go to the section below entitled
 “What to do if I have an oversized file”.
What to do if I have an ‘oversized’ file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Once a file has been ‘over-sized’ in the database then there are three
  possible options as described below. In ALL cases it is advisable to take
  a full backup of your current situation before proceeding.

 a. If there have been no ORA-600 errors and no space has been
    allocated in the illegal part of the file then you should
    be able to drop the tablespace including its existing contents.
    ie: If the select below returns NO ROWS then you can attempt
        to drop the tablespace including contents:

       SELECT * FROM uet$ WHERE block#+length-1 > 4194303;

    NOTE-A: You should extract any required data from the tablespace
     before you drop it.
 b. The second option is to recover the entire database to a point in
    time BEFORE the problem was introduced. For this you need to
    examine the alert log and find the earliest time when any file
    extended / resized / got added to the database which was oversized.
    Perform point in time recovery to a time before any file became
    oversized.

 c. Consult Oracle Support Services with all the information
    collected so far and ask them to refer to this alert.

Patches
~~~~~~~
  Please contact Oracle Support to find out if a patch is available for
  your platform / version or consider upgrading to 8.1.7 if available.
  The individual bugs are fixed in releases as below:

    Bug:813983 8.1.6.0 onwards
    Bug:568232 8.0.5.0 onwards (including 8.0.6.X  and 8.1.X)
    Bug:925105  8.1.7.0 onwards
    Bug:1646512 8.1.7.4.

  None of the bugs are fixed in any 7.3 patch set.

Fixed-Releases:     8174 9010
Please upgrade to a supported version of the DB.
References
~~~~~~~~~~
  Manual RESIZE allows more than 4 million DB Blocks  Bug:813983
  Autoextend MAXSIZE can be set above 4 million DB blocks  Bug:568232
  ADD DATAFILE with no SIZE can add a bad dictionary entry Bug:925105
  ADD DATAFILE allows SIZE larger than should be possible         Bug:1646512
  2Gb or not 2Gb (2Gb related issues)   Note:62427.1

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: