BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Take control of your CMS with Infolytic (Opens a new window)  

General Notice: No events within the next 45 days.

ORA-12542: TNS:address already in use


 
Search this topic... | Search DI: Installation, Administration, and Connectivities... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Author Message
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Mon Nov 26, 2012 7:43 am 
Post subject: ORA-12542: TNS:address already in use

Hi all,

i had the situation that all Jobs aborted with Error:

ORA-12542: TNS:address already in use

We never had this error before, but we found out, that no more port is available in the "NETSTAT"

I saw that Salesforce Adapter (so the HTTP-Proxy) connection is blocking thousands of ports.
After i restarted Salesforce Adapter i was able to process Jobs again.

It seems that Salesforce Adapter is opening Port-Connections and doesn't close them after data was transferred.

We also have some Real-Time-Jobs which are sending many real-time requests to Salesforce. Could it be that Salesforce opens a Port for every request but doesn't close it afterwards?

How we can avoid that salesforce adapter is filling up the netstat?
Is there a parameter to "control" the Port-Closing?
Or is this a bug?

We have this problem in both environments:

1. Environment:

14.0.1.142

2. Environment:
14.0.3.353
Back to top
osrootofos
Principal Member
Principal Member



Joined: 10 Feb 2012

Posts: 148
Location: United States


flag
PostPosted: Mon Nov 26, 2012 8:40 pm 
Post subject: Re: ORA-12542: TNS:address already in use

Hi

you need to start your listener with unique add.


Fixing the ORA-12542 on UNIX/Linux

In UNIX, the ORA-12542 error is an easy fix, just re-set the listener to run on a different port or add to the queue size for the listener in your listener.ora file.

listener=(address=
(protocol=tcp)
(host=myhost)
(port=2241)
(queuesize=60)
)

Fixing the ORA-12542 on Windows

The error "ORA-12542: TNS:address already in use" is rare on UNIX but very common on Windows crapware and /net platforms, and this may be a bug. See MOSC Note:252614.1, and search for bugs relating to ORA-12542. It may have to do with a memory leak.

On Windows, user SJH suggests this solution for the ORA-12542 error:

[The ORA-12542 TNS address in use error] is caused due to the fact that the free ports in the windows client has been exhausted and it is trying to make use of a port which is in TIME_WAIT state which gives the error TNS-12542: Address already in use.

There are 2 solutions available:

1.) Increase the free port range:
======================
Start Registry Editor (Regedt32.exe).

Locate the following key in the registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

On the Edit menu, click Add Value, and then add the following registry value:

Value Name: MaxUserPort Data Type: REG_DWORD Value: 65534

Valid Range: 5000-65534 (decimal) Default: 0x1388 (5000 decimal)

Description: This parameter controls the maximum port number used when an application requests any available user port from the system. Normally, ephemeral (that is, short-lived) ports are allocated between the values of 1024 and 5000 inclusive.

2.) Decrease the value for TIME_WAIT state:
================================
TcpTimedWaitDelay (new in Windows NT versions 3.51 SP5 and later)
Key: Tcpip\Parameters
Value Type: REG_DWORD - Time in seconds
Valid Range: 30-300 (decimal)
Default: 0xF0 (240 decimal)




Thanks
Back to top
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Tue Nov 27, 2012 4:02 am 
Post subject: Re: ORA-12542: TNS:address already in use

Thank you for this information.

I saw that solution when searching with Google. icon_cool.gif
But increasing the Port-Range will only temporary solve this problem or?
And Timeout will not work, because connections are in state "established"

I'm wondering about these connections.
At the moment NO Salesforce extraction is running, but i can find thousands of such entries in NETSTAT:

TCP mchp409a:1738 proxyemea1.***.net:8080 ESTABLISHED 13416
[javaw.exe]


13416 = PID of Salesforce Adapter
proxyemea1.***.net:8080 = Used proxy for Salesforce communication


Why are thousands of connections established and reserving ports for the Salesforce adapter when we don't extract anything?

Best Regards
Back to top
osrootofos
Principal Member
Principal Member



Joined: 10 Feb 2012

Posts: 148
Location: United States


flag
PostPosted: Wed Nov 28, 2012 5:00 am 
Post subject: Re: ORA-12542: TNS:address already in use

Hi

There should be a port dedicated to listener and no one else should use that port.

when a request comes the listener listens on that port.

so please assign a dedicated port to the listener.

For security reasons, Salesforce restricts the outbound ports you may specify to one of the following:
80: This port only accepts HTTP connections.
443: This port only accepts HTTPS connections.
102466535 (inclusive): These ports accept HTTP or HTTPS connections.

for the salseforce ports open i will see to it and let you know.

Thanks
Back to top
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Fri Dec 14, 2012 2:34 am 
Post subject: Re: ORA-12542: TNS:address already in use

Hi,

we have a dedicated port for the listener.

We're using Salesforce-Adapter with proxy-server.
I must use Port 8080 on proxy to be able to connect to Salesforce through internet.


I absolutely have no clue, how to solve this connection problem.
We have increased the max ports and still have this issue sometimes.

I don't know why the proxy-server (which is used in salesforce adapter) is holding thousands of connections in netstat when it's doing nothing at that moment.
Sometimes Salesforce Extraction is aborting, afterwards it takes some time and we get this TNS problems.
Could it be, that if a job abort, the connections aren't closed?

From the PID information out of netstat i can see that salesforce adapter and web services are "holding" the most connections.
Nearly all of them as status "ESTABLISHED".
Since our project will grow fast i must find a solution, that database / server will run stable in the future.

Does anyone have an additional idea?

Best Regards
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2744



PostPosted: Sat Dec 15, 2012 8:55 pm 
Post subject: Re: ORA-12542: TNS:address already in use

since you mentioned that when you stop the salesforce adapter the ports are released, in that case it's possible that if you are using SFDC source or target in real time job, for every request a session might be getting created and not closed once the request is processed

can you check the number of session that are open for the SFDC Adapter instance, you can see this from Adapter Status page in MC when you click on the adpater instance

also, enable the trace for the SFDC adapter and look for steps that are happening in processing a real time message request like start operation, stop operation etc and compare this with the same processing if done in test mode for that real time job
Back to top
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Mon Dec 17, 2012 8:46 am 
Post subject: Re: ORA-12542: TNS:address already in use

Hi Manoj,

i've done your proposed steps.

To have it initial i stopped and started the salesforce adapter.

1. After start of Salesforce Adapter
-> No sessions are active

2. After start o Real-Time Job which consumes a Salesforce Table
-> I can see Session ID 3, 4, and 5 (don't know why ID 1 and 2 are not displayed)

3. After web Service call
-> I can see Session ID 3,4, and 5 (no visibile changes in session counts)


It seems that not all active sessions are displayed?! And some information is cut off? (See attached picture)

My guess why we are running into problems with TNS after some time.....
If we start the Real-Time Service all calls are fine (no further sessions are opened)
If nobody is using this Real-Time Service for a long time, the first incoming request is always failing.
It seems that Salesforce-Connection timed out!? (i finally have to validate that tomorrow)
Second request (with same input parameters) is ok then, but it increased the Session ID (now i can see 3, 4, 5, 6, 7 and 8 ). <- For that i can insert a screenshots later (must wait for time-out)
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2744



PostPosted: Mon Dec 17, 2012 11:49 am 
Post subject: Re: ORA-12542: TNS:address already in use

the session 3,4,5,6,7,8 that you see are for one request for you have multiple requests running in parallel using the same Adapter ? if not then this could be a bug, either the same session should be reused or close, I will have to debug this

yes, if you are using SFDC Adapter in real-time job, and the SFDC session is idle it will timeout, but I think there is a retry in adapter so the request may take little longer to process but may not fail, I will have to check this

ideally I would not recommend using SFDC in real time job, what are you reading from SFDC ? or you are loading to SFDC ?
Back to top
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Tue Dec 18, 2012 9:57 am 
Post subject: Re: ORA-12542: TNS:address already in use

Hi Manoj,

today i can see 38! open sessions.
At the moment we have ONE Real-time job (with 3 Instance, but always only 1 is started) with reading from one SFDC-Table (Today we had 661 requests)

As you've mentioned the connection seems to get a timeout at any time and the sessions aren't closed then. Afterwards if someone calls the WebService again it openes additional sessions and the first call fails.

We have the requirement to read in real-time from SFDC tables (Account and Opportunity) to get the most actual information from there.
It's very time critial (that's why we cannot create a job, which periodically extract these tables to our repository)

I see two proposals:

- In Real-Time Jobs Salesforce connections shouldn't get a timeout
- Real-Time Jobs only initialize until the point where Salesforce-Table must be read (so session will be only opened in case there is a request, and after request was processed close the session again)
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2744



PostPosted: Wed Dec 19, 2012 12:20 am 
Post subject: Re: ORA-12542: TNS:address already in use

ok, I need some logs and may need to test few things in your env, can you file a case for this issue also ?

attach the following logs
Real Time Job logs (the logs will be in %LINK_DIR%\log\<JobServerName>\<RepoName>\<Real_Time_Job_Name>_*.txt
SFDC Adapter trace and error log (the logs will be in %LINK_DIR%\adapters\log\<SFDCInstanceName>_trace.txt, <SFDCInstanceName>_error.txt)
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3655
Location: Denver Area


flag
PostPosted: Wed Dec 19, 2012 11:38 am 
Post subject: Re: ORA-12542: TNS:address already in use

barthodo, I think I may be seeing the same thing with DS 3.1. What is the IP address you are associating with SFDC in the NETSTAT report? Can you provide an example of what you see in NETSTAT?

Here is a small example of what I see:
Quote:
TCP jobserver_os_name:4668 xxx.xxx.xxx.xxx:1521 ESTABLISHED
TCP jobserver_os_name:4669 xxx.xxx.xxx.xxx:1521 ESTABLISHED
TCP jobserver_os_name:4670 xxx.xxx.xxx.xxx:1521 ESTABLISHED
TCP jobserver_os_name:4675 xxx.xxx.xxx.xxx:1521 TIME_WAIT
TCP jobserver_os_name:4676 xxx.xxx.xxx.xxx:1521 TIME_WAIT
TCP jobserver_os_name:4677 xxx.xxx.xxx.xxx:1521 TIME_WAIT


There are about 74 lines in the report associated with that IP address.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)


Last edited by eganjp on Wed Dec 19, 2012 5:16 pm, edited 1 time in total
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2744



PostPosted: Wed Dec 19, 2012 1:32 pm 
Post subject: Re: ORA-12542: TNS:address already in use

do you also see number of SFDc Session open ?
can you also check the memory usage of the SFDC Java process, you can get the process id from the management console and check for that in the task manager on Adapter JobServer
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3655
Location: Denver Area


flag
PostPosted: Wed Dec 19, 2012 1:43 pm 
Post subject: Re: ORA-12542: TNS:address already in use

Yes, I think I see open SFDC sessions. I looked in Adapter Instances->JobServer_1->Salesforce_adapter. There are a number of entries. There are no active jobs at this time so I would not expect to see active sessions.

The screenshot below is only of about half the active sessions.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3655
Location: Denver Area


flag
PostPosted: Wed Dec 19, 2012 5:14 pm 
Post subject: Re: ORA-12542: TNS:address already in use

You may want to ignore the NETSTAT results I posted above. I went over them with the network administrator and the IP address in the list is an internal IP address, not an external IP address. So there are no SFDC connections showing up in NETSTAT.
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
barthodo
Principal Member
Principal Member



Joined: 18 Apr 2012

Posts: 187
Location: Munich


flag
PostPosted: Thu Dec 20, 2012 3:33 am 
Post subject: Re: ORA-12542: TNS:address already in use

HI Manoj,

i've opened OSS call 1163496 / 2012 for that icon_cool.gif

I enabled the trace over night.
Hopefully this will deliver needed information.

Thanks in advance for your help!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0302 seconds using 17 queries. (SQL 0.0177 Parse 0.0004 Other 0.0121)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo