Home » RDBMS Server » Server Utilities » SQL Loader problem in Oracle 10g
SQL Loader problem in Oracle 10g [message #256783] Mon, 06 August 2007 11:30 Go to next message
bheemsen
Messages: 13
Registered: February 2006
Junior Member
Hi,

We recently upgraded to Oracle 10.2.0.2 from Oracle 9.2.

Our OS is: Unix AIX

After the upgrade we are facing a strange problem with our sql loader. We load several flat files everyday one after the other into our flat tables using sql loader. After the upgrade, the sql loader hangs completely on a file (at random) and sits there for ever. No error in the logs or anywhere else. No code changes were made after the upgrade.

Here is the syntax we are using from the Unix script to invoke the sql loader.

sqlldr PARFILE=<file with userid=user/password@dbname> data=data_file.dat control=control_file.ctl log=log_file.log bad=bad_file.bad discard=discard_file.dsc errors=999 direct=true

Same syntax is used repeatedly from a Unix script for all the files and all the tables. But it works fine one day for all the files/tables and fails on other day for a file (random, could be any).

No errors found anywhere. Unix command 'ps -ef' says sqlldr is executing. When I kill using 'kill -9' command, the sqlldr continues with next file without any problem. I tested loading the same file on our test system (10g, Unix AIX) and it works just fine.

Did anyone come across the same problem with Oracle 10g ? Any inputs are greatly appreciated.

Thanks
-Bheem
Re: SQL Loader problem in Oracle 10g [message #256791 is a reply to message #256783] Mon, 06 August 2007 13:09 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What version of SQL*Loader are you using?
Re: SQL Loader problem in Oracle 10g [message #256795 is a reply to message #256783] Mon, 06 August 2007 13:20 Go to previous messageGo to next message
bheemsen
Messages: 13
Registered: February 2006
Junior Member
SQL*Loader: Release 10.2.0.2.0 - Production

It was already mentioned in my original post.

Thanks
-Bheem
Re: SQL Loader problem in Oracle 10g [message #256796 is a reply to message #256795] Mon, 06 August 2007 13:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There was NO mention of the fact that you were actually using the 10.2.0.2 version of the SQL*Loader binary. There have been plenty of folks on here that upgrade their database and still try to use the 9.2 version of the SQL*Loader binary.

Have you tried trussing the command ??
Re: SQL Loader problem in Oracle 10g [message #256820 is a reply to message #256783] Mon, 06 August 2007 15:33 Go to previous messageGo to next message
bheemsen
Messages: 13
Registered: February 2006
Junior Member
Thanks ebrian for your reply.

This is our production batch script that is scheduled to run everyday from cron. I do not want to change a script that is in production. Do you know how to run a truss command on a running script ? Please reply. Also, this sql loader problem is happening at randmon on somedays. I can run the truss command when next time I see the problem.

Thanks
-Bheem
Re: SQL Loader problem in Oracle 10g [message #256828 is a reply to message #256820] Mon, 06 August 2007 18:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Since the issue is sporadic, trussing the process may not provide much help.

I assume the upgrade performed without errors? Do you see any invalid objects in the database? Are you on the same hardware and filesystem that your 9.2 database was? Don't know if relinking sqlldr would provide any help.

Was the load that you did on your test server for just one file? Are you able to run thru the whole load process on your test server in the same fashion as you do on your prod box?
Re: SQL Loader problem in Oracle 10g [message #257189 is a reply to message #256783] Tue, 07 August 2007 13:11 Go to previous messageGo to next message
bheemsen
Messages: 13
Registered: February 2006
Junior Member
Yes, the upgrade was performed without any errors.
No invalid objects.
After the upgrade we are using the same hardware and file system.

Unfortnately it is difficult to reproduce the same problem, as it is happening on any file at random. All our systems are on 10.2.0.2. On test system the files loaded successfully.

The day I had posted this question, the sql loader hang on a particular file. The same file got loaded successfully without any problem yesterday on the production system. Nothing changed. It is very weird. So the problem is every now and then. Today we had no problems.

We load multilple files one after the other into the same flat tables using one script everyday.

Hope this answers your questions.

Thanks
-Bheem
Re: SQL Loader problem in Oracle 10g [message #257242 is a reply to message #257189] Tue, 07 August 2007 19:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
With that in mind, I would consider trying to relink sqlldr and monitor the hanging. If it still hangs you may want to consider filing an SR with Oracle.
Re: SQL Loader problem in Oracle 10g [message #257304 is a reply to message #256783] Wed, 08 August 2007 01:23 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
To bheemsen,

you state that there is a sqlldr process (kill -9), then there should also be an oracle shadow proces associated with this (I assume you don't use multithreaded server)
There should also be an associated sid to identify the process in Oracle.
Have you tried to look in the v$session_wait for the process ?
Have you tried to see if there is a locking problm ? There is a nice note on Metalink (Note:245981.1) about locking identifications through v$session.
Also it should be possible to start a trace on the sid with :
execute sys.dbms_system.set_ev(sid,serial,10046,level,''); 

Where sid, serial and level are the numbers you supply. (sid,serial) from v$session and level could be 8.

best regards
Carl Bruhn
Re: SQL Loader problem in Oracle 10g [message #406776 is a reply to message #257304] Fri, 05 June 2009 15:51 Go to previous message
jkowtko
Messages: 1
Registered: June 2009
Location: SF Bay Area
Junior Member
We've run into what looks like the same problem:
- direct load
- a few dozen relatively small files, loaded in succession
- client and server on different machines
- 10.2.0.1 client, 10.2.0.4 server
- batch job was running from cron

At first the problem seemed intermittent, then we could reproduce it on our Production instance. Reproduced 2-3 times on one specific table, and again on a different table. UAT did not see the problem.

I suspected that Direct Loader was getting caught up coordinating the use of free/used space maps from the database (since it formats the data pages directly) that reached a certain corner condition -- so we turned off direct load and that seems to avoid the problem. Runs 3x slower, but problem avoided.

So I suspect direct load has some quirks in it. If anyone knows what this could be (i.e. bug, version compatibility issue, database maintenance issue, or general config issue) please let us know.

Thanks. john
Previous Topic: Error in SQL* Loader pgm
Next Topic: load into oracle 7.3
Goto Forum:
  


Current Time: Sun Dec 04 20:46:43 CST 2016

Total time taken to generate the page: 0.09012 seconds