On Dec 3, 9:36 am, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> ErikYkema <erik.yk..._at_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_at_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
Received on Thu Dec 06 2007 - 03:32:37 CST
