Oracle sort error

[Joe Redman]

When the Oracle DBA trys to do a sort, we get the following error(s):
ORA-01114: IO error writing block to file 4001
ORA-27072: skgfdisp: I/O error
HP-UX Error: 27: File too large.

We have largefiles activated on this filesystem. Sort gets to 30.5GB in size on a temp filesystem large enough to hold it, then bombs out.
Oracle version is 9i. HP-UX 11.0 OS. JFS Version 3.3.

Thanks in advance.

[Andreas D. Skjervold]

Hi

This might be due to the database started with the database owner (oracle) shell variable ulimit set to a value lower than the datafile size.

to check:
ulimit -a
check for file (blocks)
set this to unlimited or a value bigger than your temp datafile size, and restart the database with new setting.

Andreas

[Joe Redman]

Andreas, thanks for the reply.

Ulimit for Oracle already set to unlimited.

I was wondering if this could be a potential disk problem as everything else checks out although, disk mounts and checks out okay.

We are going to try a couple of other things and I will let all know how it comes out via this forum.

Joe

[Paula J Frazer-Campbell]

Hi

I am not 100% sure but doesn't sort when running pipe through a temp file so if after a crash it leaves a 30Gig file would the temp file be about the same size ?

30 +30 = BIG
- crash, and tmp file dissapears
- so only evidence of this is the 30 gig file.
As the 30 + 30 is BIG will your disk handle this?

Is the temp file placed in /tmp ?




Just some ideas

Paula

[Andreas D. Skjervold]

Hi again

Im a litle bit on thin ice here but anyway;
I take it that the sort is done in the TEMP tablespace, and that the tablespace is created with the temporary storage option.

What happens is that Oracle creates the tablespace file, but does infact not claim the space until the FIRST time you make use of this space.

So what you see as plenty of free space in your filesystem might infact not be enough.

So if your temp tablespace is 30 GB you need 30GB free (at least) + if allowing the tablespace to grow (autoextend) you'll need space for this as well.

You might also want to check / apply the latest vxfs patches, so they conform to Oracles latest release note.

Andreas