TNS error - service not found
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: TNS error - service not found

  1. #1
    Join Date
    May 2005
    Posts
    129

    TNS error - service not found

    I have always beat these problems. But today we took on a new remote client and I could not get in via the tnsnames.ora and listener.ora.

    The sitting DBA is a very experienced physical DBA meaning unix and such but not a development DBA.

    Neither of us could make it work after 2 hours.

    There is nothing left to try and tracing with level 16 was useless.

    What else might cause this problem? The above mentioned config files had not been recently modifed - but we also dont know how recently anyone successfully or ever connected to the database.

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Crystal Ball Show me these files...

    listener.ora tnsnames.ora sqlnet.ora any errors

    Sorry cannot see them...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Can we assume that PING\TNSPING are sucessful from the remote client to your db server?
    Assistance is Futile...

  4. #4
    Join Date
    Apr 2001
    Posts
    110
    without ping/tnsping results, even an experienced DBA can't tell what's going on.

    Does your remote client need to pass thru a firewall before reaching the DB?
    What's next after 10g?

  5. #5
    Join Date
    May 2005
    Posts
    129
    Sorry I was there for 13 hours yesterday and forgot to grab the contents of the files. This is local on the server. I can get into the instance with sqlplus "/ a sysdba" but that is not traversing the listener. I will get them today and the tnsping output. Its just a dead simple listener and dead simple tnsnames - seems something else is going on. Found another tnsnames under our monitoring app so that would explain the failure to monitor but it does not explain manual login failures.
    Perhaps they have mixed versions of software I dont know why 2000 lines of sqlnet trace shows nothing out of order.

  6. #6
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    What is the result of TNSping ?
    Last edited by BJE_DBA; 06-10-2005 at 01:28 PM.

  7. #7
    Join Date
    May 2005
    Posts
    129
    Here are the 3 files. This is basically the virgin configs I found which had not been recently modified. That makes me think about a problem elsewhere but I cant think of any place else to look.

    I have since dummed these down to the bare mininum and nothing ever changes, although I had them nearly exact to another client and I got NV - name/value pair errors so perhaps that was due to differing Oracle versions.

    Listener:

    USE_PLUG_AND_PLAY_LISTENER_dqmdb = OFF
    USE_CKPFILE_LISTENER_dqmdb = OFF
    LISTENER_dqmdb=
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = 123.123.123.123)
    (Port = 1530)
    )
    )
    STARTUP_WAIT_TIME_LISTENER_dqmdb = 0
    CONNECT_TIMEOUT_LISTENER_dqmdb = 10
    TRACE_LEVEL_LISTENER = USER
    SID_LIST_LISTENER_dqmdb =
    (SID_DESC =
    (GLOBAL_DBNAME = dqmdb)
    (SID_NAME = dqmdb)
    (ORACLE_HOME = /usr/ora92/OraHome)
    (PRESPAWN_MAX = 10)
    )

    TNS:

    DQMDB.keystone.kfc =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
    (CONNECT_DATA =
    (SERVER=DEDICATED)
    (SERVICE_NAME = dqmdb)
    )
    )
    dqmdb =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
    (CONNECT_DATA =
    (SID = dqmdb)
    )
    )

    SQLNET :

    BEQUEATH_DETACH=yes
    names.directory_path = (TNSNAMES)
    names.default_domain = keystone.kfc
    name.default_zone = keystone.kfc
    AUTOMATIC_IPC = OFF
    DISABLE_OOB = ON
    sqlnet.expire_time = 30
    TRACE_LEVEL_CLIENT=16
    TRACE_FILE_CLIENT = sqlnet.trc
    TRACE_DIRECTORY_CLIENT = /usr/ora92/OraHome/network/trace
    TRACE_UNIQUE_CLIENT = ON
    LOG_FILE_CLIENT = sqlnet.log
    LOG_DIRECTORY_CLIENT = /usr/ora92/OraHome/network/log



    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_dqmdb
    Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
    Start Date 09-JUN-2005 21:59:00
    Uptime 0 days 15 hr. 4 min. 43 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File /usr/ora92/OraHome/network/admin/listener.ora
    Listener Log File /usr/ora92/OraHome/network/log/listener_dqmdb.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=123.123.123.123)(PORT=1530)))
    Services Summary...
    Service "dqmdb" has 1 instance(s).
    Instance "dqmdb", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully



    IP has been dummied up for the posting.


    $ sqlplus DQM@DQMDB.keystone.kfc

    SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jun 10 12:58:41 2005

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter password:
    ERROR:
    ORA-12154: TNS:could not resolve service name

    sqlplus DQM@DQMDB

    Same error ORA-12154
    Last edited by roadwarriorDBA; 06-10-2005 at 02:34 PM.

  8. #8
    Join Date
    May 2005
    Posts
    129
    TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 10-JUN-2005 13:25:26

    Copyright (c) 1997 Oracle Corporation. All rights reserved.

    Used parameter files:
    /usr/ora92/OraHome/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    )SERVICE_NAME = dqmdb) TCP)(HOST = 123.123.123.123)(PORT = 1530))
    OK (0 msec)

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    change

    DQMDB.keystone.kfc =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
    (CONNECT_DATA =
    (SERVER=DEDICATED)
    (SERVICE_NAME = dqmdb)
    )
    )

    to

    DQMDB.keystone.kfc =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
    (CONNECT_DATA =
    (SERVER=DEDICATED)
    (SID = dqmdb)
    )
    )

    and then try...

    by the way the ipaddress in

    dqmdb =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 205.223.10.110)(PORT = 1530))
    (CONNECT_DATA =
    (SID = dqmdb)
    )
    )

    is diff than the one above for DQMDB.keystone.kfc. is that a typo ?
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    May 2005
    Posts
    129
    Typo.


    (SID = dqmdb)

    Then retry

    Enter password:
    ERROR:
    ORA-12154: TNS:could not resolve service name


    I am certain that was one of hundreds of variations we tried yesterday. I am open to any suggestions.

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