From: ErikYkema on
Hello,
We just experienced the following ulimit event in a production
Sql*Loader run, and I am looking for your help in understanding why it
is as we saw.

Oracle EE 817(a)AIX5L.
Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
calls sqlldr is 2 GB. We insert (not direct path) and connect using
beq (no @SID in connect string) and receive a message like:
ORA-01115: IO error reading block from file 1 (block # 5970)
ORA-01110: data file 1: '/db8/rstst/system.dbf'
ORA-27092: skgfofi: size of file exceeds file size limit of the
process

It turns out that this datafile is around 2GB in size.
I first thought that the instance had been started in a wrong way,
using wrong (2GB) ulimit. A bounce of the instance with correct
environment didn't solve it.

We were able to insert the rows using Sql*Plus inserts manually (and
to extend the dbf.)

We found some references on the internet confirming the problem and
the following suggested approaches:
- either make sure the user that calls sqlldr has a ulimit at least
equal to oracle
- and/or make sure not to connect sqlldr using beq

An operator assures us that he has set the ulimit fsize (hard and
soft) for the calling user to 4 GB, and still was not able to complete
the sqlldr run succesfully, on the same error.

Now our assumption for explaining this is:
When calling the sqlldr executable using beq, the oracle server side
process that writes (and reads) the data file does not have exactly
the same ulimit as the other instance oracle processes. It also seems
not to have the calling user's ulimit. (If the operator's observation
was correct.)

Why would/could this process be different? What is exactly the nature
of this process? Or is something else going on?
Would this case also hold for imp and other Oracle Utilities?

Regards,
Erik
From: Frank van Bortel on
ErikYkema wrote:
> Hello,
> We just experienced the following ulimit event in a production
> Sql*Loader run, and I am looking for your help in understanding why it
> is as we saw.
>
> Oracle EE 817(a)AIX5L.
> Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
> calls sqlldr is 2 GB. We insert (not direct path) and connect using
> beq (no @SID in connect string) and receive a message like:
> ORA-01115: IO error reading block from file 1 (block # 5970)
> ORA-01110: data file 1: '/db8/rstst/system.dbf'
> ORA-27092: skgfofi: size of file exceeds file size limit of the
> process
>
> It turns out that this datafile is around 2GB in size.
> I first thought that the instance had been started in a wrong way,
> using wrong (2GB) ulimit. A bounce of the instance with correct
> environment didn't solve it.
>
> We were able to insert the rows using Sql*Plus inserts manually (and
> to extend the dbf.)
>
> We found some references on the internet confirming the problem and
> the following suggested approaches:
> - either make sure the user that calls sqlldr has a ulimit at least
> equal to oracle
> - and/or make sure not to connect sqlldr using beq
>
> An operator assures us that he has set the ulimit fsize (hard and
> soft) for the calling user to 4 GB, and still was not able to complete
> the sqlldr run succesfully, on the same error.
>
> Now our assumption for explaining this is:
> When calling the sqlldr executable using beq, the oracle server side
> process that writes (and reads) the data file does not have exactly
> the same ulimit as the other instance oracle processes. It also seems
> not to have the calling user's ulimit. (If the operator's observation
> was correct.)
>
> Why would/could this process be different? What is exactly the nature
> of this process? Or is something else going on?
> Would this case also hold for imp and other Oracle Utilities?
>
> Regards,
> Erik

There are some flaws in your posting:
- your error has nothing to do with sqlldr, or whatever process,
but with a clumsy AIX sysadmin, that did not enable large files.
Come on, you're on 5L!

- you insert data into tables, located in the SYSTEM tablespace.
Don't - your instance will, sooner or later, come to a grinding halt.

- I may be wrong on this one, but back in the 8i days, imp and exp
executables were 32 bits, thereby restricting to file operations on
files no larger than 2GB. Not sure if the same applies to sqlldr.
But even if it did, that just means your input file can be no larger
than 2GB-1 - again, not connected to the error.

And... you're at least 3 MAJOR releases behind schedule: 9iR1,
9iR2, 10GR1. 10GR2 is current, 11G is out, and you're still on
8i?!?
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
From: ErikYkema on
On Nov 30, 8:41 pm, Frank van Bortel <frank.van.bor...(a)gmail.com>
wrote:
> ErikYkema wrote:
> > Hello,
> > We just experienced the following ulimit event in a production
> > Sql*Loader run, and I am looking for your help in understanding why it
> > is as we saw.
>
> > Oracle EE 817(a)AIX5L.
> > Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
> > calls sqlldr is 2 GB. We insert (not direct path) and connect using
> > beq (no @SID in connect string) and receive a message like:
> > ORA-01115: IO error reading block from file 1 (block # 5970)
> > ORA-01110: data file 1: '/db8/rstst/user.dbf' # quoted post changed by Erik
> > ORA-27092: skgfofi: size of file exceeds file size limit of the
> > process
>
> > It turns out that this datafile is around 2GB in size.
> > I first thought that the instance had been started in a wrong way,
> > using wrong (2GB) ulimit. A bounce of the instance with correct
> > environment didn't solve it.
>
> > We were able to insert the rows using Sql*Plus inserts manually (and
> > to extend the dbf.)
>
> > We found some references on the internet confirming the problem and
> > the following suggested approaches:
> > - either make sure the user that calls sqlldr has a ulimit at least
> > equal to oracle
> > - and/or make sure not to connect sqlldr using beq
>
> > An operator assures us that he has set the ulimit fsize (hard and
> > soft) for the calling user to 4 GB, and still was not able to complete
> > the sqlldr run succesfully, on the same error.
>
> > Now our assumption for explaining this is:
> > When calling the sqlldr executable using beq, the oracle server side
> > process that writes (and reads) the data file does not have exactly
> > the same ulimit as the other instance oracle processes. It also seems
> > not to have the calling user's ulimit. (If the operator's observation
> > was correct.)
>
> > Why would/could this process be different? What is exactly the nature
> > of this process? Or is something else going on?
> > Would this case also hold for imp and other Oracle Utilities?
>
> > Regards,
> > Erik
>
> There are some flaws in your posting:
> - your error has nothing to do with sqlldr, or whatever process,
> but with a clumsy AIX sysadmin, that did not enable large files.
> Come on, you're on 5L!
>
> - you insert data into tables, located in the SYSTEM tablespace.
> Don't - your instance will, sooner or later, come to a grinding halt.
>
> - I may be wrong on this one, but back in the 8i days, imp and exp
> executables were 32 bits, thereby restricting to file operations on
> files no larger than 2GB. Not sure if the same applies to sqlldr.
> But even if it did, that just means your input file can be no larger
> than 2GB-1 - again, not connected to the error.
>
> And... you're at least 3 MAJOR releases behind schedule: 9iR1,
> 9iR2, 10GR1. 10GR2 is current, 11G is out, and you're still on
> 8i?!?
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

Hi Frank,
Thanks for your answer.
I was inappropriate in my sample, it was in reality a user tablespace
and didn't see that my copy-pasted sample was to system. Sorry for
that. (Though I said '_like_ the error below')
We have many datafiles for this instance that are much larger than 2
GB. (So I think we do have 'large files' enabled.)

Yes I am aware of the age of 8i - it is what is is. I expected the
rant.

Your last thought triggers a question: is sqlldr modifying the
datafile, I expected it to be a database instance process, connected
to the session initiated by sqlldr.

More thoughts are appreciated. The issue seems to be: non-oracle user
ulimits applied when using beq, not when using Sql*Net.

Regards, Erik Ykema
From: bdbafh on
On Nov 30, 2:53 pm, ErikYkema <erik.yk...(a)gmail.com> wrote:
> On Nov 30, 8:41 pm, Frank van Bortel <frank.van.bor...(a)gmail.com>
> wrote:
>
>
>
> > ErikYkema wrote:
> > > Hello,
> > > We just experienced the following ulimit event in a production
> > > Sql*Loader run, and I am looking for your help in understanding why it
> > > is as we saw.
>
> > > Oracle EE 817(a)AIX5L.
> > > Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
> > > calls sqlldr is 2 GB. We insert (not direct path) and connect using
> > > beq (no @SID in connect string) and receive a message like:
> > > ORA-01115: IO error reading block from file 1 (block # 5970)
> > > ORA-01110: data file 1: '/db8/rstst/user.dbf' # quoted post changed by Erik
> > > ORA-27092: skgfofi: size of file exceeds file size limit of the
> > > process
>
> > > It turns out that this datafile is around 2GB in size.
> > > I first thought that the instance had been started in a wrong way,
> > > using wrong (2GB) ulimit. A bounce of the instance with correct
> > > environment didn't solve it.
>
> > > We were able to insert the rows using Sql*Plus inserts manually (and
> > > to extend the dbf.)
>
> > > We found some references on the internet confirming the problem and
> > > the following suggested approaches:
> > > - either make sure the user that calls sqlldr has a ulimit at least
> > > equal to oracle
> > > - and/or make sure not to connect sqlldr using beq
>
> > > An operator assures us that he has set the ulimit fsize (hard and
> > > soft) for the calling user to 4 GB, and still was not able to complete
> > > the sqlldr run succesfully, on the same error.
>
> > > Now our assumption for explaining this is:
> > > When calling the sqlldr executable using beq, the oracle server side
> > > process that writes (and reads) the data file does not have exactly
> > > the same ulimit as the other instance oracle processes. It also seems
> > > not to have the calling user's ulimit. (If the operator's observation
> > > was correct.)
>
> > > Why would/could this process be different? What is exactly the nature
> > > of this process? Or is something else going on?
> > > Would this case also hold for imp and other Oracle Utilities?
>
> > > Regards,
> > > Erik
>
> > There are some flaws in your posting:
> > - your error has nothing to do with sqlldr, or whatever process,
> > but with a clumsy AIX sysadmin, that did not enable large files.
> > Come on, you're on 5L!
>
> > - you insert data into tables, located in the SYSTEM tablespace.
> > Don't - your instance will, sooner or later, come to a grinding halt.
>
> > - I may be wrong on this one, but back in the 8i days, imp and exp
> > executables were 32 bits, thereby restricting to file operations on
> > files no larger than 2GB. Not sure if the same applies to sqlldr.
> > But even if it did, that just means your input file can be no larger
> > than 2GB-1 - again, not connected to the error.
>
> > And... you're at least 3 MAJOR releases behind schedule: 9iR1,
> > 9iR2, 10GR1. 10GR2 is current, 11G is out, and you're still on
> > 8i?!?
> > --
> > Regards,
> > Frank van Bortel
>
> > Top-posting is one way to shut me up...
>
> Hi Frank,
> Thanks for your answer.
> I was inappropriate in my sample, it was in reality a user tablespace
> and didn't see that my copy-pasted sample was to system. Sorry for
> that. (Though I said '_like_ the error below')
> We have many datafiles for this instance that are much larger than 2
> GB. (So I think we do have 'large files' enabled.)
>
> Yes I am aware of the age of 8i - it is what is is. I expected the
> rant.
>
> Your last thought triggers a question: is sqlldr modifying the
> datafile, I expected it to be a database instance process, connected
> to the session initiated by sqlldr.
>
> More thoughts are appreciated. The issue seems to be: non-oracle user
> ulimits applied when using beq, not when using Sql*Net.
>
> Regards, Erik Ykema

Try and see if a conventional load fails with the same error.
I'm assuming that you're using direct load now.
Yes, in direct load, the user's session is writing directly to the
datafile (above the high water mark) rather than going through the
buffer cache.

I don't have an AIX box to test this out on, and I don't have any
boxes < 10g R1.

-bdbafh
From: Frank van Bortel on
ErikYkema wrote:
> On Nov 30, 8:41 pm, Frank van Bortel <frank.van.bor...(a)gmail.com>
> wrote:
>> ErikYkema wrote:
>>> Hello,
>>> We just experienced the following ulimit event in a production
>>> Sql*Loader run, and I am looking for your help in understanding why it
>>> is as we saw.
>>> Oracle EE 817(a)AIX5L.
>>> Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
>>> calls sqlldr is 2 GB. We insert (not direct path) and connect using
>>> beq (no @SID in connect string) and receive a message like:
>>> ORA-01115: IO error reading block from file 1 (block # 5970)
>>> ORA-01110: data file 1: '/db8/rstst/user.dbf' # quoted post changed by Erik
>>> ORA-27092: skgfofi: size of file exceeds file size limit of the
>>> process
>>> It turns out that this datafile is around 2GB in size.
>>> I first thought that the instance had been started in a wrong way,
>>> using wrong (2GB) ulimit. A bounce of the instance with correct
>>> environment didn't solve it.
>>> We were able to insert the rows using Sql*Plus inserts manually (and
>>> to extend the dbf.)
>>> We found some references on the internet confirming the problem and
>>> the following suggested approaches:
>>> - either make sure the user that calls sqlldr has a ulimit at least
>>> equal to oracle
>>> - and/or make sure not to connect sqlldr using beq
>>> An operator assures us that he has set the ulimit fsize (hard and
>>> soft) for the calling user to 4 GB, and still was not able to complete
>>> the sqlldr run succesfully, on the same error.
>>> Now our assumption for explaining this is:
>>> When calling the sqlldr executable using beq, the oracle server side
>>> process that writes (and reads) the data file does not have exactly
>>> the same ulimit as the other instance oracle processes. It also seems
>>> not to have the calling user's ulimit. (If the operator's observation
>>> was correct.)
>>> Why would/could this process be different? What is exactly the nature
>>> of this process? Or is something else going on?
>>> Would this case also hold for imp and other Oracle Utilities?
>>> Regards,
>>> Erik
>> There are some flaws in your posting:
>> - your error has nothing to do with sqlldr, or whatever process,
>> but with a clumsy AIX sysadmin, that did not enable large files.
>> Come on, you're on 5L!
>>
>> - you insert data into tables, located in the SYSTEM tablespace.
>> Don't - your instance will, sooner or later, come to a grinding halt.
>>
>> - I may be wrong on this one, but back in the 8i days, imp and exp
>> executables were 32 bits, thereby restricting to file operations on
>> files no larger than 2GB. Not sure if the same applies to sqlldr.
>> But even if it did, that just means your input file can be no larger
>> than 2GB-1 - again, not connected to the error.
>>
>> And... you're at least 3 MAJOR releases behind schedule: 9iR1,
>> 9iR2, 10GR1. 10GR2 is current, 11G is out, and you're still on
>> 8i?!?
>> --
>> Regards,
>> Frank van Bortel
>>
>> Top-posting is one way to shut me up...
>
> Hi Frank,
> Thanks for your answer.
> I was inappropriate in my sample, it was in reality a user tablespace
> and didn't see that my copy-pasted sample was to system. Sorry for
> that. (Though I said '_like_ the error below')

Missed that - basically skimmed the posting; hard day at work
trying to get !&*#@(% iAS 10.1.3 to behave on a 32GB HP-UX cluster.

> We have many datafiles for this instance that are much larger than 2
> GB. (So I think we do have 'large files' enabled.)
>

Erhm - you just may have a point here... ^-8

> Yes I am aware of the age of 8i - it is what is is. I expected the
> rant.

Was I ranting? Merely explaining the state of business in oracle land.
>
> Your last thought triggers a question: is sqlldr modifying the
> datafile, I expected it to be a database instance process, connected
> to the session initiated by sqlldr.
>
> More thoughts are appreciated. The issue seems to be: non-oracle user
> ulimits applied when using beq, not when using Sql*Net.
>
> Regards, Erik Ykema

Well, sorta' like bdbafh... haven't seen AIX in a while
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
 |  Next  |  Last
Pages: 1 2
Prev: SGA
Next: BULK UPDATE with FORALL