ORA-12542: TNS:address already in use

dataservices administration
1

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

  1. Environment:
    14.0.3.353

barthodo :de: (BOB member since 2012-04-18)

2

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

osrootofos :us: (BOB member since 2012-02-10)

3

Thank you for this information.

I saw that solution when searching with Google. 8)
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

barthodo :de: (BOB member since 2012-04-18)

4

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.
1024–66535 (inclusive): These ports accept HTTP or HTTPS connections.

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

Thanks

osrootofos :us: (BOB member since 2012-02-10)

5

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

barthodo :de: (BOB member since 2012-04-18)

6

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

manoj_d (BOB member since 2009-01-02)

7

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)
salesforce_status.JPG

barthodo :de: (BOB member since 2012-04-18)

8

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 ?

manoj_d (BOB member since 2009-01-02)

9

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)

barthodo :de: (BOB member since 2012-04-18)

10

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, _error.txt)

manoj_d (BOB member since 2009-01-02)

11

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:

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

eganjp :us: (BOB member since 2007-09-12)

12

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

manoj_d (BOB member since 2009-01-02)

13

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.
~MyBitmap.JPG

eganjp :us: (BOB member since 2007-09-12)

14

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.

eganjp :us: (BOB member since 2007-09-12)

15

HI Manoj,

i’ve opened OSS call 1163496 / 2012 for that 8)

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

Thanks in advance for your help!

barthodo :de: (BOB member since 2012-04-18)