Re: RESIZE datafile bug???

In article <328ECDFA.3B4E_at_qrcsun.qrc.org>,

        "Lun Wing San (Oracle)" <wslun_at_qrcsun.qrc.org> writes:
>Bill Beaton wrote:
>>
>> 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.
 

>> 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.
>
> First of all, I also think that it is a tragedy. Anyway, it
> is not always possible to decrease the size of a datafile to a
> specific value as the manual said. So, it is better to check the size
> of the datafile after resizing.

No, no Lun, you don't seem to understand the problem ... the datafile DOES resize correctly, and ORACLE SEEMS to do everything correctly, except that there is a field in each data file header block that ORACLE leaves at either the original create size, or the largest ever size that is given to the data file. The only time that this field ever seems to be used is if, and only if, you have to do a 'create controlfile' statement. My testing didn't seem to identify the issue on media recoveries from hot backups, or any other situation where the controlfile is not re-genned. At this point (and by my testing, ONLY in this circumstance), the database becomes completely and forever unusable ... be very sure that you have full COLD backups (which you should do anyways), if you're going to create controlfile.

The workaround, which I tested yesterday, is to do the following on the working, running instance before you get into the situation ...

1. Issue "alter session set events 'immedate trace name file_hdrs level 1';"
2. For each datafile listed (i.e. each group of lines headed by a line with "DATA FILE #___:"
3. get the full path name from the next line
4. get the buggy size that ORACLE expects from the next line by multiplying the numbers for 'filesize=' and bsize='
5. using something like "ls -l", find out what the actual size is
6. For each file where the UNIX file size is LESS than the above calculation you MUST do the following fix:
7. Make sure that the file system has room to expand the file, or do the "alter database datafile 'n' offline, UNIX file move to the new system, then alter database rename 'pathname' to 'new', alter database datafile 'n' online combination so that the file has enough room to grow.
8. Issue 'alter database datafile 'pathname' resize yyyy; where yyyy is the size that ORACLE expects. It is ok (by my tests) to go larger than that.

ORACLE does have a patch for HP/UX, and is currently (and quickly, I hope) generating the same one for Solaris 2.5.1 ... basically, just altering the datafile header on a shrink, as well as a growth (a real bug).

If the system is too tight to expand the datafile(s), and if there is ANY possibility that you might have to recreate the controlfile (for example, to move the SYSTEM tablespace), then I'd jump on ORACLE support NOW to get an emergency patch for your hardware. This assumes that you have at some point shrunk a datafile ... it seems to be a complete non-issue for growth.

Bill

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