Re: RESIZE datafile bug???

In article <E14rru.73_at_cix.compulink.co.uk>,

        aburlison_at_cix.compulink.co.uk ("Alan Burlison") writes:
>> 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.
>
>Why would this bug, which is related to resizing as far as I can see,
>prevent you from renaming a datafile? I don't see the connection between
>your description of the bug and this statement. Am I missing something
>obvious?
>

This bug, which IS RELATED to resizing, has vast ramifications. It completely prevents you from recreating the control file. As this is the only method for doing things like renaming the SYSTEM tablespace, there is a dangerous exposure here, as the act of creating a new controlfile does just enough during the startup (IF any datafile has been shrunk with the RESIZE), that you will only be able to recover your database thru a cold backup.

Note that if you know that you have this problem, (i.e. you follow some process like I outlined, and determine that there are datafiles that have been shrunk below the creation size), then you simply resize them back up to the creation size (at a minimum), and presto ... you're back in a safe situation. At this point, you could take an export of the objects in the oversized tablespace, drop the tablespace, recreate it with an appropriate size, grant quota appropriately, and then import the objects in. Same end-result, without danger, but a lot of time. This time, for a large tablespace, can be critical for a 7x24 operation ... I have single tables approaching 2GB in size ... when you add on index creation time, it could effectively result in a 30 hour outage. Even going to a different schema is painful, as there is still a minimum 15 minute outage just to do the grants, and recompile necessary views on the same tablespaces. A scheduled outage can cost us $4,000 an hour in lost revenues.

Bill  

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