Thanks for the kind words.
As I am sure you may have guessed the guilty party is not unknown to this
group <vbg> and now has a beer in hand whilst he waits for a full restore of
datafiles to take place overnight. I too dislike autoextend so I did decide
to 'correct' the situation - it was eating the disk after all. . So
currently we have point in time recovery as plan a) and restore from
previous nights export as plan b). I don't expect to have to use plan b
but....
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message
news:1oRh8.20440$106.1660697_at_bgtnsc05-news.ops.worldnet.att.net...
> OH, Niall, how COULD you?
>
> (I hate when people say things like that. Like that really helps. "Oh my,
> why didn't you install sprinklers? as your house, barn, office building ,
> etc, burns to ash)
>
> I have been suspicious of auto-extend (that's set on a file basis, I
> believe, correct?), quasi-suspicious of "unlimited extents"
>
> I use raw i/o so much that autoextend for datafiles is irrelevant to me; I
> make sure blocksize is big, and unless I died at the keyboard, managing
and
> collapsing extents out of control isn't too bad. If they enhance Oracle to
> do autoextends by allocating from a given raw partition list, I'd be more
> interested.
>
> OEM has been reliable for me in shrinking unused space, and for invoking
> coalescing (though that is easy enough to do in sqlplus or svrmgrl); that
> bit about having to have a nonzero PCTwhatever for Oracle to automatically
> coalesce freespace still galls me and I fail to see the reason. But I am
> just a stupid DBA grunt.)
>
> But, bottom line, mate, "that feature should not have behaved in the
manner
> you have described" and sounds like a big bloody bug to me. OEM
{qualifying.
> OEM on NT running against Oracle 8.x on Solaris, HP-UX, NCR UNIX} would
barf
> and say ixnay to shrinking past the point of the "No more data here"
marker.
>
> I hope you TARred and feathered the guilty parties, and best of luck with
> this, ick.
>
> RSH.
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3c879eee$0$232$ed9e5944_at_reading.news.pipex.net...
> > Ooops
> >
> > Database is busy filling up disk because tablespace is set to
auto-extend.
> > DBA therefore decides as the largest tablespace is 11gig and only
actually
> > contains 400mb of data (because third party app drops and recreates
> > temporary tables in permanent tablespace!). As he happens to have oem
open
> > resizes the datafile using the handy dandy GUI.
> >
> > Alert log follows
> >
> > Thu Mar 07 15:39:37 2002
> > /* OracleOEM */ ALTER DATABASE DATAFILE 'F:\ORACLE\AGR\AGR_AGRTEMP.DBF'
> > RESIZE 4096M
> > Thu Mar 07 15:39:38 2002
> > Completed: /* OracleOEM */ ALTER DATABASE DATAFILE 'F:\ORACLE
> > Thu Mar 07 15:39:39 2002
> > Errors in file g:\oracle\admin\agr\udump\ORA00630.TRC:
> > ORA-01115: IO error reading block from file 7 (block # 49930)
> > ORA-01110: data file 7: 'F:\ORACLE\AGR\AGR_AGRTEMP.DBF'
> > ORA-27091: skgfqio: unable to queue I/O
> > OSD-04026: Invalid parameter passed. (OS 49929)
> > etc etc.
> >
> >
> >
> > So now we are recovering. My question is - my understanding was that
> 'ALTER
> > DATABASE DATAFILE BLAH RESIZE <SOME SIZE> would fail if there was data
in
> > the file beyond the requested resize. I am unclear as to why oracle has
> lost
> > its grip on the datafile size. any clues.
> >
> > Flames along the lines of
> >
> > 1. don't use autoextend
> > 2. don't use oem and
> > 3. don't use NT use a man's operating system are gratefully received
> >
> > Windows NT4 sp6a
> > 8.1.6.1 SE database.
> > OEM 2.2
> >
> >
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> >
> >
>
>
Received on Thu Mar 07 2002 - 17:43:57 CST
