problem with database connection

wasimraja · Post by **wasimraja** » Mon Nov 26, 2007 6:46 pm

Hi,

I have a sequence of jobs which run daily starting from 8 PM till 1 AM.
My job is aborting daily at around 11 PM to 12 PM and it throws one of the following errors.

"ORA-03135: connection lost contact"
"ORA-03114: not connected to ORACLE"

This happens after the job has started loading data and fails in the middle. This problem usually happens with jobs which have low rows/sec. (around 150 as my jobs have 5-6 references and 2-3 transformers) and it also happens with jobs which have 1000 rows/sec.

I have already raised a case with Oracle support on this. I need to know if there is some setting in datastage which may cause this problem or if I am doing something wrong.

The source, target and reference all are the same Oracle 10g database.
The dba also says that there are no timeout parameters set which means that a connection willnot timeout automatically. I also tried opening a connection to the database in the server through sqlplus and left it open. It didnt timeout for 5 days until I closed it myself.

Please let me know the resolution if anyone has faced this problem before.

Any suggestion would be greatly appreciated.

rleishman · Post by **rleishman** » Mon Nov 26, 2007 8:04 pm

How about any CPU quotas? Your 5 day session would not have tripped them.

Also post the entire error-stack captured in the DS log.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 26, 2007 8:39 pm

Forget rows/sec!!! It's meaningless, and it's a "red herring" in the present context.

My guess is that there's a blip in your network causing these disconnections. Get your network administrator to investigate.

wasimraja · Post by **wasimraja** » Tue Nov 27, 2007 12:22 pm

Thanks everyone.

I searched help and all I could find was that it could be a problem with the network connection. But, I couldnt really confirm on that as the connection which I opened lasted for 5 days and didnt get disconnnected at all. Oracle have suggested some settings to be done to avoid this error. I will try implementing them and see if it resolves the problem. I have also raised a case with DS support just in case. Will let you guys know when I find a fix for this.

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Nov 27, 2007 3:10 pm

Does it fail on the same job everyday? Can you try to run the job after breaking it. You said you have 5-6 references. Can you break the job into two jobs with 3 references each?

I am assuming the oracle is forcing you out becuase of the queries you firing on it. (7 at a time). Its forcing you out because of temporary buffer space getting filled. Its my assumption. But i think breaking the job into two should help you.

wasimraja · Post by **wasimraja** » Tue Nov 27, 2007 3:57 pm

It doesnot fail on the same job everyday. So, I cannot break the job into two as I dont know which job to break. Moreover, the job which failed runs fine immediately after a restart.