FAQ
Hi Gurus,

I've a production database environment 10.2.0.3 on SUSE linux. We have two
databases running on the same oracle binaries. On the second database there
is a datamart ETL job (Informatica) run globally by all the regions.

Since this past weekend, this job has been failing continuously, with the
following error:

Message: Error connecting to database...

WRT_8001 [Session s_m_GAPositionStage Username datamart DB Error -1805

Database driver error...

Function Name : Logon

ORA-12542: TNS:address already in use

Database driver error...

Function Name : Connect
Database Error: Failed to connect to database using user [datamart] and
connection string

This error is not thrown in any of the other jobs on this database or the
other database.

In an effort to resolve this, we put in the parameter "queue_size" to 20 in
listener.ora. But it failed again with the same error. Now I'm going to
increase its value to 128 which is the default value of OS parameter
SOMAXCONN at SUSE linux. There is another note which asks to add a listener
port in this situation. Do you think queuesize can do it or should I add a
new port? The current listener port is 1526 and is using a non-default
listener.

Since this is the production box I've to resolve it ASAP as business is
getting effected.

Any expert opinion folks??

Thanks for the help

Search Discussions

  • Anupam Pandey at Apr 18, 2010 at 4:33 am
    But this should not be the problem because I can have multiple databases
    running on same host
    and using the same port (say 1521 ) for listener .
    So the same port can be re-used by multiple database listeners.

    Thanks And Regards,

    Anupam Pandey
    On Fri, Apr 16, 2010 at 9:49 PM, Jared Still wrote:
    On Thu, Apr 15, 2010 at 7:55 PM, Saad Khan wrote:


    Any expert opinion folks??
    I am not going to claim expert status on this - I've never seen the error.

    The context of the error though tells me that some process is attempting
    start the listener on port 1526, and there is already a process using port
    1526 - probably the listener that is already running.

    Verify what processes are attached to the port with fuser.
    This must be run as root:
    fuser -v -n tcp 1526

    example:

    fuser -v -n tcp 1521
    here: 1521

    USER PID ACCESS COMMAND
    1521/tcp oracle 16134 f.... tnslsnr


    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Anupam Pandey at Apr 18, 2010 at 4:35 am
    But this should not be the problem because I can have multiple databases
    running on same host
    and using the same port (say 1521 ) for listener .
    So the same port can be re-used by multiple database listeners.

    Thanks And Regards,

    Anupam Pandey
    On Fri, Apr 16, 2010 at 9:49 PM, Jared Still wrote:
    On Thu, Apr 15, 2010 at 7:55 PM, Saad Khan wrote:


    Any expert opinion folks??
    I am not going to claim expert status on this - I've never seen the error.

    The context of the error though tells me that some process is attempting
    start the listener on port 1526, and there is already a process using port
    1526 - probably the listener that is already running.

    Verify what processes are attached to the port with fuser.
    This must be run as root:
    fuser -v -n tcp 1526

    example:

    fuser -v -n tcp 1521
    here: 1521

    USER PID ACCESS COMMAND
    1521/tcp oracle 16134 f.... tnslsnr


    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Apr 18, 2010 at 1:46 pm
    The same port cannot be used by multiple listeners. One listener can service
    multiple databases or multiple services. You cannot however have multiple
    processes listening on the same port. I don't see the os but on Linux at
    about that release a second listener could be spawned under high load
    leading to the symptoms described - this would show up in the fuser command
    that jared listed.

    Niall Litchfield

    On Apr 18, 2010 12:35 AM, "Anupam Pandey" wrote:

    But this should not be the problem because I can have multiple databases
    running on same host
    and using the same port (say 1521 ) for listener .
    So the same port can be re-used by multiple database listeners.

    Thanks And Regards,

    Anupam Pandey

    On Fri, Apr 16, 2010 at 9:49 PM, Jared Still wrote: > >
    On Thu, Apr 15, 2010 a...
  • Saad Khan at Apr 20, 2010 at 2:16 pm
    I opened a SR with oracle and they explained that it has nothing to do with
    oracle database rather its something with the OS at client and ETL itself.
    They mentioned its a known issue (though I didnt find this solution in
    metalink) and following is the action to be taken at the app server where
    ETL runs.

    Increase the free port range as follows:

    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:

    Edit -> New-> DWORD 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.

    The change is made and app server is rebooted after that. Datamart ETL is
    yet to run, so fingers crossed :)

    On Sun, Apr 18, 2010 at 9:46 AM, Niall Litchfield <
    niall.litchfield_at_gmail.com> wrote:
    The same port cannot be used by multiple listeners. One listener can
    service multiple databases or multiple services. You cannot however have
    multiple processes listening on the same port. I don't see the os but on
    Linux at about that release a second listener could be spawned under high
    load leading to the symptoms described - this would show up in the fuser
    command that jared listed.

    Niall Litchfield

    On Apr 18, 2010 12:35 AM, "Anupam Pandey" wrote:

    But this should not be the problem because I can have multiple databases
    running on same host
    and using the same port (say 1521 ) for listener .
    So the same port can be re-used by multiple database listeners.

    Thanks And Regards,
    Anupam Pandey

    On Fri, Apr 16, 2010 at 9:49 PM, Jared Still wrote: >
    On Thu, Apr 15, 2010 a...
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 16, '10 at 2:55a
activeApr 20, '10 at 2:16p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase