TNS Listener issue
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: TNS Listener issue

  1. #1
    Join Date
    Feb 2010
    Posts
    36

    TNS Listener issue

    I have two databases on one machine. I can login to only one db at a time. If I try to connect other db it gives me the error:

    TNS listener currently does not know service requested in connect descriptor.

    Even if I set the oracle_sid "ORADB", it get connected to "ORADEV". And if I specify the Connect identifiet it gives me TNS error.

    Kindly assist.

    Regards,
    Mayura

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How many listeners? one per instance?

    Either way, issue is most likely related to an issue on tnsnames.ora file.

    For future reference - for better/faster help copy/paste session log as well as printout of related files.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2010
    Posts
    36
    The Db name mentioned above were just examples. The actual db names are in tns file below. The details as as below:

    LSNRCTL> status
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_GISDEV
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
    Start Date 20-JAN-2011 10:16:27
    Uptime 4 days 5 hr. 5 min. 37 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File C:\ORA\dbadmin\product\11.2.0\GISDEV\NETWORK\ADMIN\listener.ora
    Listener Log File c:\ora\dbadmin\diag\tnslsnr\PDFPGISORDEV\listener_gisdev\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PDFPGISORDEV.PADOTGIS.state.pa.us)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "giszgg" has 1 instance(s).
    Instance "gisdev", status READY, has 2 handler(s) for this service...
    Service "orad2" has 1 instance(s).
    Instance "orad2", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully


    ===============================================================================
    TNSNAMES.ORA

    ORAD2 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 164.156.155.129)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = orad2)
    )
    )


    giszgg =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 164.156.155.134)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = giszgg)
    )
    )

    gisdev =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 164.156.155.134)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = gisdev)
    )
    )


    Thanks...

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Quote Originally Posted by mayura.ch View Post
    Service "orad2" has 1 instance(s).
    Instance "orad2", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully

    check out why the status of the above instance is blocked.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Feb 2010
    Posts
    36
    But orad2 is on different server and gisdev,giszgg are on same server. And I am trying to keep gisdev and giszgg UP at the same time..

    Mean while I will check the reason being orad2 blocked.

    Thanks...

  6. #6
    Join Date
    Feb 2010
    Posts
    36
    Now I do not have the status Blocked.

    Can anyone let me know why I am not able to connect both the servers at a time?

    The issue was I have created giszgg cloned db from orad2. GISZGG and GISDEV are on same server. gisdev was up and running when I did clone and now all the trace files and alert log is getting created by gisdev suffix. Help me out to get solution on this so that GISZGG and GISDEV can run independantly?

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,971
    Do a tnsping to both databases and post the results. Verify that the entries in the tnsnames.ora file are connect and point to different databases. Check your listener.ora file, verify the sid names, ports and server names. If everything looks correct try renaming the sqlnet.ora file and then connecting to both databases.
    this space intentionally left blank

  8. #8
    Join Date
    Feb 2010
    Posts
    36
    I can do tnsping to both the dbs

    See the below error. That's very strange:

    LSNRCTL> status
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_GISDEVVersion TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
    Start Date 25-JAN-2011 08:36:39
    Uptime 0 days 3 hr. 8 min. 6 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File C:\ORA\dbadmin\product\11.2.0\GISDEV\NETWORK\ADMIN\listener.ora
    Listener Log File c:\ora\dbadmin\diag\tnslsnr\PDFPGISORDEV\listener_gisdev\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PDFPGISORDEV.PADOTGIS.state.pa.us)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "giszgg" has 1 instance(s).
    Instance "gisdev", status READY, has 2 handler(s) for this service...
    The command completed successfully
    LSNRCTL>
    LSNRCTL>
    LSNRCTL>
    LSNRCTL>
    LSNRCTL>
    LSNRCTL>
    LSNRCTL> start listener_giszgg
    TNS-01106: Listener using listener name LISTENER_GISDEV has already been started
    LSNRCTL>
    ++++++++++++++++++++++++++++++++++++++++++++++++++

    C:\ORA\dbadmin\product\11.2.0\GISDEV\BIN>tnsping gisdev

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-JAN-2011 11:42:36

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\ORA\dbadmin\product\11.2.0\GISDEV\NETWORK\ADMIN\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 164.156.155.134)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = gisdev)))
    OK (0 msec)

    C:\ORA\dbadmin\product\11.2.0\GISDEV\BIN>tnsping giszgg

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-JAN-2011 11:42:43

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\ORA\dbadmin\product\11.2.0\GISDEV\NETWORK\ADMIN\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 164.156.155.134)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = giszgg)))
    OK (10 msec)

    C:\ORA\dbadmin\product\11.2.0\GISDEV\BIN>


    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    and the listener.ora is:

    SID_LIST_LISTENER_GISDEV =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\ORA\dbadmin\product\11.2.0\GISDEV)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\ORA\dbadmin\product\11.2.0\GISDEV\bin\oraclr11.dll")
    )
    )

    LISTENER_GISZGG =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PDFPGISORDEV.PADOTGIS.state.pa.us)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER_GISDEV = C:\ORA\dbadmin

    ADR_BASE_LISTENER_GISZGG = C:\ORA\dbadmin

    LISTENER_GISDEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PDFPGISORDEV.PADOTGIS.state.pa.us)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TPC)(KEY = EXTPROC1521))
    )
    )

  9. #9
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    according to me you cannot have two listeners listening to same port at a time,so i would suggest to separate out the listener ,create a new listener.ora assign new port(say 1529) set local_listener parameter.You should be all set

  10. #10
    Join Date
    Feb 2011
    Location
    Norwich, UK
    Posts
    5
    In our listener.ora file, in the SID_LIST_LISTENER section we have multiple databases specified in the SID_LIST. For example:

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /apps/oracle/10gR2)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = db1.domain.co.uk)
    (ORACLE_HOME = /apps/oracle/10gR2)
    (SID_NAME = db1)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = db2.domain.co.k)
    (ORACLE_HOME = /apps/oracle/10gR2)
    (SID_NAME = db2)
    )
    )

    This does mean that we only have one listener for all our DBs, if you want one listener per DB then I guess you would need similiar to above but with something like SID_LIST_LISTENER1 and then SID_LIST_LISTENER2. Each with their single SID_LIST and SID_DESC section.

    Is that of any help?

    With the tnsnames.ora file we have our SIDs listed, all with the same port.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width