DSXchange

Hi ,

I've got average Datastage skills. So below issue which i'm facing may be a silly one. I'm getting the below error when I'm increasing the number of nodes or run multiple jobs. When number of nodes is 8, the job is running fine. But once I increase the number of nodes to 10 or when I run multiple jobs in parallel, I'm getting the below error.

The connector could not establish connection to the specified Oracle server. Method: OCIServerAttach, Error code: 12541, Error message ORA-12541: TNS:no listener. (CC_OraConnection::verifyConnection, file CC_OraConnection.cpp, line 5,195)

I'm using Datastage version 11.5.0.1

Can anyone please help me understand what's happening and how to tackle it. Any help will be much appreciated.

Talk to your DBA to get the listener started or to get the TNS entry corrected.

If it was problem with the listener or TNS entry, then it shouldn't have worked when number of nodes is 8. In my case, the job is running successfully when node count is 8,only when the node count is more OR when I run multiple jobs, then only i'm facing the issue.

Just a guess... Are you perhaps running on a grid where some of the nodes are unable to access a common tnsnames.ora file?

Sorry.We are not in a grid.

You are doing a partitioned read. You technically make one connection per degree of parallelism. You overextended your stay on the Oracle host and he didn't have any more connections to offer you.

Not a very gracious host then, it would seem.

I think there's a (different) specific error message when that happens, but certainly could be resource related on the Oracle side. Best to check with your DBA, their alert log should show if you are banging up against any limits.

We ran into a scenario like this before:

If the Oracle is running in a cluster (RAC) then you could have many different IP addresses of the Oracle servers. If your firewall rules are not fully allowing traffic through all of the IP addresses, then you will randomly get Oracle connection error messages.

Paul,

Yes your are correct I am doing a partitioned read with 8 nodes. My datastage job is having 20 Oracle connectors pointing to same database. So how can I figure out the problem that oracle is not allowing more connections (steps would be helpful)?

20 Oracle connectors in just 1 job? That's an important piece of information. Add on to that 10 nodes or 'multiple jobs in parallel' and I'd still wager the target instance isn't configured to handle that much activity in general, or that much from the same user.

As noted, you start figuring this by involving your DBA. Talk to them, explain what you are doing and seeing. They can monitor and possibly adjust the connection related limits they have in place. That or tell you to not do so much at the same time.

In your dev environment, hit the Balance optimization button to see how it would re-write your job for you. Sounds like it could merge some of those connectors together.

As chulett indicated... involve your Oracle DBA, but break the news gently to him that you are basically assaulting his poor database with an army of connections.

I had a talk with my DBA and he has shown that there is no limit restrictions from his end.

Also, meanwhile I created one more Datastage project (project_2) in Dev env and copied the job from project_1 to project_2.
I am able to run the job in both projects at same time without any error (utilizing 8 + 8 nodes).

It indicates that there is a possibility that Datastage is restricting connections somewhere.

Please advice.