Prev: SGA
Next: BULK UPDATE with FORALL
From: Laurenz Albe on 3 Dec 2007 03:36
ErikYkema <erik.ykema(a)gmail.com> wrote:
> 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?

I can confirm everything except for the operator's observations.

- When you use BEQ protocol, the server process is forked from the
client process (as you can verify with ps). This is the same for all
client utilities like sqlldr, imp, or impdp.
- A forked process will inherit the ulimit settings from the parent
process, even if it is SETUID like oracle (I checked this with a small
C program).

So when using sqlldr via BEQ protocol, the ulimits of the calling user
should apply.

I would double check your operator's assumption; try and set ulimit -f
to the desired value manually, check it with ulimit -a, then start
the sqlldr process from the same shell and see what happens.

Yours,
Laurenz Albe

PS: This is the C program I used to check my claim:

#include <stdio.h>
#include <errno.h>
#include <sys/time.h>
#include <sys/resource.h>

int main(int argc, char **argv) {
struct rlimit64 rl;

if (-1 == getrlimit64(RLIMIT_FSIZE, &rl)) {
perror("getrlimit");
return 1;
}

printf("Current file size limit: %lld bytes\n", rl.rlim_cur);
return 0;
}

From: ErikYkema on 6 Dec 2007 04:32
On Dec 3, 9:36 am, Laurenz Albe <inv...(a)spam.to.invalid> wrote:
> ErikYkema <erik.yk...(a)gmail.com> wrote:
> > 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 usingbeq
>
> > 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 usingbeq, 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?
>
> I can confirm everything except for the operator's observations.
>
> - When you useBEQprotocol, the server process is forked from the
> client process (as you can verify with ps). This is the same for all
> client utilities like sqlldr, imp, or impdp.
> - A forked process will inherit the ulimit settings from the parent
> process, even if it is SETUID like oracle (I checked this with a small
> C program).
>
> So when using sqlldr viaBEQprotocol, the ulimits of the calling user
> should apply.
>
> I would double check your operator's assumption; try and set ulimit -f
> to the desired value manually, check it with ulimit -a, then start
> the sqlldr process from the same shell and see what happens.
>
> Yours,
> Laurenz Albe
>
> PS: This is the C program I used to check my claim:
>
> #include <stdio.h>
> #include <errno.h>
> #include <sys/time.h>
> #include <sys/resource.h>
>
> int main(int argc, char **argv) {
> struct rlimit64 rl;
>
> if (-1 == getrlimit64(RLIMIT_FSIZE, &rl)) {
> perror("getrlimit");
> return 1;
> }
>
> printf("Current file size limit: %lld bytes\n", rl.rlim_cur);
> return 0;
>
> }

Thanks Laurenz,
The only thing I am still looking for is a reference in any
documentation from Oracle Corp that also mentions the behavior you
described (beq forks from client). Not that I disagree with you, but I
think it should be documented.
Regards,
Erik
From: Laurenz Albe on 7 Dec 2007 04:13
ErikYkema <erik.ykema(a)gmail.com> wrote:
> The only thing I am still looking for is a reference in any
> documentation from Oracle Corp that also mentions the behavior you
> described (beq forks from client). Not that I disagree with you, but I
> think it should be documented.

You are probably right.
I think that BEQ is not well documented at all.

If you need a proof that the server process is forked from the client
in a BEQ connection, that can be easily done. Just take it out to
highway 61.

No, seriously:
- start sqlplus or sqlldr and connect via BEQ.
- With "ps", convince yourself that the PPID (parent process ID)
of the server process is the PID of the client process.

Yours,
Laurenz Albe
First  |  Prev  | 
Pages: 1 2
Prev: SGA
Next: BULK UPDATE with FORALL