Re: RESIZE datafile bug???

In article <56gait$sge_at_elmo.cadvision.com>,

        beatonb_at_cadvision.com (Bill Beaton) writes:
>I've just been hit by a weird problem in one of my test instances ...
>maybe someone else has come across this, and found a fix for it
>
>1. Oracle 7.2.2.4 on Sun Solaris 2.5.1, NOARCHIVELOG mode
>2. SeveraL days ago I issued an
> alter database datafile '/oracle/db02/tools.001' resize 10M
> because it was created at 30M, and we were never going to load
> anything into it. Datafile worked fine ... could create, drop
> objects, and coalesce the free space without problem.
>3. Today, in testing for a machine migration, we plugged all the
> drives into the new cpu. ORACLE started up fine, and all
> database objects could be manipulated.
>4. Later, because we will be restriping the data files, I decided
> to use my usual method ...
> a. alter database backup controlfile to trace
> b. edit the control file backup to reflect the new spindle
> locations
> c. shutdown the database, move the various datafiles,
> and do some necessary filename renaming in the init.ora file
> d. wipe out the existing control files, and in sql*dba:
> - run the edited trace file to recreate the database
> using the new filenames.
>
>During creation of the new control file, ORACLE aborted saying that the
>file I had resized several days ago was only 10M, while it should be 30M,
>and then aborted the startup. It will not create the control file, so I
>can't get as far as startup mount, in order to drop the datafile.
>
>This is actually a potentially serious issue, as it implies (unless I've
>missed something obvious), that one can't recreate a database from a
>backup control file if any data files have been resized.
>

I've been working on this one all day, with ORACLE in particular, and have a small piece of the answer, and a potential workaround ...

1. This was reported by nilo_at_sundb04.cern.ch apparently, for which ORACLE opened bug 309181. When I gave this to my ORACLE rep, he was able to move fairly quickly:
2. The patch already exists for HP/UX
3. A patch for Solaris 2.5.1 is being generated by Oracle support as a priority 1 ... that seems to mean within 2 weeks for availability.
4. I found a work-around myself ... if the data-files can be resized to the same size, or larger, than their size high-water, then the bug doesn't occur. The gotcha here is to determine that larger size, and for that matter, identify data files needing this sort of fix.
5. Oracle support believes that the critical size number is probably encoded in the data file header block. If this is so, and if Oracle Support can both determine and publish the byte locations in the data file of this information, then it would be a simple matter to match the extracted bytes from the data file, along with an "ls -l" output of the same file to both determine if a data-file resize is needed, and what actual size specification is required.
6. According to Oracle, this is a very recently reported bug, and so a warning to all DBAs ... until all Oracle releases have been patched, do NOT short-cut calculating the size for a new table-space by allocating a huge area, populating, and then resizing down.

If you have a release with this bug ... most of 7.2 and 7.3 releases, your database is at risk. It is IMPOSSIBLE to move the system tablespace to a different physical space in most cases, and even with complex symlinks, you may not be able to accomplish the task without destroying the database.

I don't feel bad about this ... no problems when I can test every DB alteration on small test instances, altho I honestly don't know how my subconscious managed to actually test the resize downwards.

Bill

-- 
----------------------------------
E-Mail: Beatonb_at_cadvision.com or Bill_Beaton_at_calgary.qc-data.com
----------------------------------