TNS connection error

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
hsudars
Premium Member
Premium Member
Posts: 5
Joined: Wed Apr 10, 2013 9:30 pm

TNS connection error

Post by hsudars »

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.
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
hsudars
Premium Member
Premium Member
Posts: 5
Joined: Wed Apr 10, 2013 9:30 pm

Post by hsudars »

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.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Just a guess... Are you perhaps running on a grid where some of the nodes are unable to access a common tnsnames.ora file?
Choose a job you love, and you will never have to work a day in your life. - Confucius
hsudars
Premium Member
Premium Member
Posts: 5
Joined: Wed Apr 10, 2013 9:30 pm

Post by hsudars »

Sorry.We are not in a grid.
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not a very gracious host then, it would seem. :wink:

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
hsudars
Premium Member
Premium Member
Posts: 5
Joined: Wed Apr 10, 2013 9:30 pm

Post by hsudars »

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)?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

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.
hsudars
Premium Member
Premium Member
Posts: 5
Joined: Wed Apr 10, 2013 9:30 pm

Post by hsudars »

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.
Post Reply