CONFESSION TIME.
Niall Litchfield
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
******************************************
Jonathan Lewis
You missed
Don't use an unsupported (or is it just out of date)
version of Oracle ;)
Is it possible that Oracle has a little bitty section
of code that knows how to handle very large files
just so long as you don't resize them ?
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Niall Litchfield wrote in message
<3c879eee$0$232$ed9e...@reading.news.pipex.net>...
Niall Litchfield
Actually it is this handling absurdly large datafiles that I am worried
about. Anyway we are noww ready to try to recover the tablespace so i'll let
you all know.
--
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
******************************************
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:1015522434.29636....@news.demon.co.uk...
Connor McDonald
There are a few notes on metalink about bugs in Oracle where resize
and/or autoextend are allowed to freely go beyond what Oracle can itelf
cope with...I don't have the note numbers off-hand, but they all pretty
much end with "please do recovery and then apply patch ..."
Charming eh?
hth
connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Niall Litchfield
disk.
"Connor McDonald" <connor_...@yahoo.com> wrote in message
news:3C87BA...@yahoo.com...
Joe Armstrong-Champ
We ran into this problem in 8.0.3 EE on AIX 4.3. It was a known bug and
we upgraded quickly to 8.0.4. Luckily it was a test db.
Joe
RSH
(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-litc...@audit-commission.gov.uk> wrote in message
news:3c879eee$0$232$ed9e...@reading.news.pipex.net...
Niall Litchfield
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_O...@worldnet.att.net> wrote in message
news:1oRh8.20440$106.1...@bgtnsc05-news.ops.worldnet.att.net...
Greg Kainz
planning, like not setting the sum total of max extended files to something
larger than disk capacity, or even better, a somewhat arbitrary holdover
from the old days, 2G maybe...
And, as much I detest Windoze in general, I actually have grown somewhat
fond of OEM, since I've been forced to survive in a Windoze environment off
and on (at least I can have a reasonable facsimilie of CRON by using job
scheduler).
Best of luck with the recovery...
Greg
"Niall Litchfield" <niall.li...@btinternet.com> wrote in message
news:a68tvs$j9n$1...@paris.btinternet.com...
Niall Litchfield
news:1oRh8.20440$106.1...@bgtnsc05-news.ops.worldnet.att.net...
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.
It also says it on NT (I have checked and rechecked). Oracle support suggest
that the file that was shrunk actually had some block corruption (presumably
in an empty part of the datafile if that makes any sense). So I guess Oracle
did check to see if it was safe to shrink the file 9there being no data
there it was) but didn't check the integrity of the file. My gut feeling is
still that the root cause was a 11gb file on NTFS and Oracle bugs with
handling large datafiles. I have asked support to see if they can come up
with explanations for the corruption but I guess this might be wishful
thinking.
Nuno Souto
>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)
Oh shewt! I've seen this b4, but in Unix. It was a bug with boundary
code (4Gb): a word (or byte. or whatever) was lost right at boundary.
>
>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.
the bug. it totally ignores that little bit of data around the 4Gb
boundary. and loses track of addresses, pointers, etc.
Nothing to do with NTFS, AFAIK. Same happens with Unix. 32-bit
thing.
>Flames along the lines of
>
last thing you need now is flames.
>Windows NT4 sp6a
Surprising. I thought this problem was restricted to nix.
>8.1.6.1 SE database.
surprising too. that version should not have this error. it happened
a lot with 8.0.x, but seldom with 8.1.x. Maybe a left-over?
>OEM 2.2
that could be part of the problem, but I don't know enough of its
operation in NT to comment.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Hemant K Chitale
Niall,
See Bug # 1668488 on NT
--- issue is with AutoExtend OR Resize when
the size is at the 4GB boundary. Workaround :
specify 1MB more or less than 4GB.
Never use boundary sizes.
Even with 64-bit Oracle I set the maxsize
of datafiles to 1900M or 2000M but not 2048M.
My flames ?
1. Yes, use AutoExtend but ALWAYS hard-code
the Maxsize (1900M or 2000M)
2. OEM . blah ! svrmgrl or sqlplus. Great scripting
language.
3. NT. Not worth talking about.
Hemant K Chitale
Remove the "_nospam" in my email-id and
domain-name when replying by email.
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3c879eee$0$232$ed9e...@reading.news.pipex.net...
Niall Litchfield
used to exist.
Shame the Oracle TAR was resolved with no reference to this bug.
anyway we live and learn. (and hopefully keep our jobs at the same time. )
--
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
******************************************
"Hemant K Chitale" <hkchital_nospam@singnet_nospam.com.sg> wrote in message
news:a6alpm$4b$1...@violet.singnet.com.sg...
Dusan Bolek
> boundary. and loses track of addresses, pointers, etc.
> Nothing to do with NTFS, AFAIK. Same happens with Unix. 32-bit
> thing.
That's good to know. So, now it's clear: Never shrink really big
datafile, if you do not have a spare time for recovery.
> last thing you need now is flames.
I respectfully disagree, flames can be very helplful for him. If their
datacenter catches the flames, then nobody would take care about some
f*cking 11GB datafile and unavailable database. :-)
> >Windows NT4 sp6a
> Surprising. I thought this problem was restricted to nix.
Microsoft already said that they're catching on Unixes. :-)
> >OEM 2.2
> that could be part of the problem, but I don't know enough of its
> operation in NT to comment.
Don't think so. I think that OEM is just a clicking interface to SQL
statements. Every action from OEM should be just ordinary SQL
statement for database. So I think that he would have the same problem
even with:
alter database datafile <blah> resize .... typed via svrmgrl.
I hope that recovery will be succesful and users patient and tactful.
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.
Niall Litchfield
news:a6alpm$4b$1...@violet.singnet.com.sg...
>
>
> See Bug # 1668488 on NT
> --- issue is with AutoExtend OR Resize when
> the size is at the 4GB boundary. Workaround :
> specify 1MB more or less than 4GB.
>
> Never use boundary sizes.
> Even with 64-bit Oracle I set the maxsize
> of datafiles to 1900M or 2000M but not 2048M.
>
> My flames ?
> 1. Yes, use AutoExtend but ALWAYS hard-code
> the Maxsize (1900M or 2000M)
I have no comment, other than to say I didn't set this up just noticed the
ludicrous tablespace with 12gb allocated and 400mb or so of data.
> 2. OEM . blah ! svrmgrl or sqlplus. Great scripting
> language.
agreed but doesn't prtoect you from this one.
> 3. NT. Not worth talking about.
I know where you are coming from but disagree. (The db only actually
contains 6gb of data NT can handle that.)
NorwoodThree
autoextend; just put caps on the tablepaces like 2 gig or something
that fits right.
If your db trending states that it is growing fast enough to fill the
disk perhaps you should consider getting more disk.
RSH
gives you in PL/SQL and work using them into checking all your datafiles,
tablespaces, indices, and so forth.
And if all come up clean, try this little tidbit (we KNEW we had a rotten
tablespace and/or datafile, but none of that stuff detected it);
just write a script to take each tablespace offline and put it back online
again. We found the skunk, it would not come back online, and we did a
pretty easy recover and all was well. (Apart from the hysterics limited to
the confines of the card-key access controlled IT department.)
RSH.
Nuno Souto
>> >Windows NT4 sp6a
>> Surprising. I thought this problem was restricted to nix.
>
>Microsoft already said that they're catching on Unixes. :-)
>
ROFL!!!!! Must remember this one!
Cheers
Nuno Souto
nso...@optushome.com.au.nospam