TNS connection closed error

dataservices job design
1

Hi,

WHile running a job in Data Services, i am getting below mentioned error:

“TNS connection is closed”

But at the same time, i am able to run another jobs which are pointing to the same database.

Any idea what can be the issue?

Thanks in advance!

suma_ys (BOB member since 2009-06-12)

2

Ask your DBA if there are any session timeouts specified for the database.

Werner Daehn :de: (BOB member since 2004-12-17)

3

I checked it. It seems there is no such issue.

Also When i am running one job to load data into a table in a database, i am unable to run job to load data into another table of the same database.

Onlycommon thing in these two jobs are source master tables.

Any views reg. this?

suma_ys (BOB member since 2009-06-12)

4

I would start tracing the session via Oracle settings. Only input I have, I am using Oracle a lot, in parallel & with long running & complex jobs and never seen issues like this.

Is your network unreliable so that the TCP connection of Oracle is disconnected?

Werner Daehn :de: (BOB member since 2004-12-17)

5

We faced this issue earlier when we are trying to execute many jobs in parallel.
We found that each jobs creating its own connection and it reached the max limit in oracle.
Later our DBA increaed this parameter in oracle and the problem is solved.

prabaharan (BOB member since 2007-05-16)

6

Thank you.

Here i also want to add that source and target objects are partitioned in the jobs that we are attempting to run in parellel.

Also each table has around 60 to 70 partitions. The DOP has also been set for the DFs to 3.
Can this also be reason for this issue?

Also does it has anything to do with Number of Engine processes value?

I tried disabling partitions in table in Job2 and kept the DOP as it is, it is working fine. But if i keep the partitions and remove the DOP, it doesn’t work.

suma_ys (BOB member since 2009-06-12)

7

Okay, then I support the suggestion to increase the number of session and processes in your source/target database.

alter system set processes=500 scope=spfile;
alter system set sessions=800 scope=spfile;

and then restart the database.

Also, you might want to think about reducing the number of partitions, 70 is quite high unless you have 500CPUs. Simply open the table from the object library, go to the partition and remove every second partition or so. Then DS will not execute a
select * from table (partition p1);
but instead using its range/list partition clause
select * from table where partition_key < 21;

And as the table is partitioned by partition key and the range values are 11,21,31,41,51…
above select will still read the partition only but two partitions.

Werner Daehn :de: (BOB member since 2004-12-17)