Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unhappy Unanswered: still unable to connect and mystified

    Greetings,

    I have been hammering on this problem for days now, and have run out of things to try to complete a remote access to a 9i database on a RH9 linux server.

    I can ping the server and I can tnsping it using the connect string kfdb1.kfi.net from another machine (with duplicate tnsnames.ora) and locally, but cannot start a sqlplus session locally or remotely using "sqlplus username/pass@kfdb1" or "sqlplus username/pass@kfdb1.kfi.net). I get the always helpful ora-12154 error.

    I also have set a TNS_ADMIN environment var to point to the below tnsnames.ora file, as I was getting duplicate services, even though IU could not another tnsnames.ora file, and checked that spfile parameters are consistent and $ORACLE_xxx env variables are consistent.

    I am really at my wits end, as I have solved this same problem several times before in different environments and am quickly running out of time to fix it before I need to switch hosting our application (JSP/JDBC)in a new colo on new servers.

    PLEASE someone find something stupid below!

    ================

    lsnrctl start output:
    LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 04-JAN-2004 10:46:28

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

    Starting /u01/oracle/9.2.0.1.0/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 9.2.0.1.0 - Production
    System parameter file is /u01/oracle/9.2.0.1.0/network/admin/listener.ora
    Log messages written to /u01/oracle/9.2.0.1.0/network/log/listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.1 .72)(PORT=1521)))

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.1 .72)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 9.2.0.1.0 - Production
    Start Date 04-JAN-2004 10:46:28
    Uptime 0 days 0 hr. 0 min. 0 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File /u01/oracle/9.2.0.1.0/network/admin/listener.ora
    Listener Log File /u01/oracle/9.2.0.1.0/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.1 .72)(PORT=1521)))
    Services Summary...
    Service "kfdb1.kfi.net" has 1 instance(s).
    Instance "kfdb1", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    ======================

    lsnrctl services output:

    LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 04-JAN-2004 10:47:36

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.1 .72)(PORT=1521)))
    Services Summary...
    Service "kfdb1.kfi.net" has 1 instance(s).
    Instance "kfdb1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    The command completed successfully

    =====================

    tnsnames.ora:

    KFDB1.KFI.NET =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.72)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = kfdb1)
    )
    )
    ======================

    listener.ora:

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.72)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = kfdb1.kfi.net)
    (ORACLE_HOME = /u01/oracle/9.2.0.1.0)
    (SID_NAME = kfdb1)
    )
    )

    ====================

    sqlnet.ora:

    domain=kfi.net

    NAMES.DIRECTORY_PATH= (TNSNAMES)
    ====================

    SQL> show parameter name

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert string
    db_name string kfdb1
    global_names boolean FALSE
    instance_name string kfdb1
    lock_name_space string
    log_file_name_convert string
    oracle_trace_collection_name string
    oracle_trace_facility_name string oracled
    plsql_native_make_file_name string
    service_names string kfdb1.kfi.net

    =========================

    oracle user environement variables in bash_profile:

    ORACLE_BASE=/u01/oracle
    export ORACLE_BASE

    ORACLE_HOME=$ORACLE_BASE/9.2.0.1.0
    export ORACLE_HOME

    ORACLE_DOC=$ORACLE_HOME/docs
    export ORACLE_DOC

    ORACLE_SID=kfdb1
    export ORACLE_SID

    PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/sbin:/usr/X11R6/bin:/usr/openwin/bin
    export PATH

    unset USERNAME

    umask 022

    #Disable Native Posix Thread Library for Oracle
    LD_ASSUME_KERNEL=2.4.1
    export LD_ASSUME_KERNEL

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://metalink.oracle.com Doc ID: 114085.1
    TROUBLESHOOTING GUIDE: TNS-12154 TNS:could not resolve service name

    Since you are on *NIX, you might be able to do something similar to what I do on Solaris.
    1) from one window do
    sqlplus
    2) from another window (as root) find the PID for the "sqlplus" process.
    truss -p <PID>
    3) back in window #1 enter
    username/password@alias
    4) Window #2 will show all the files that get opened while trying to establish the SQL*Net connection.

    The s/w might be using a different tnsnames.ora file than the one you think it is using.

    HTH & YMMV

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    Thanks for the suggestion. I searched for and archived all copies of sqlnet files and used strace -p (which FYI is the linux equivalent of truss and a very nifty thing to know: thanks again) but with no change in behavior.

    We just recently got the VPN up to access Xwindows, so I think I'll use netmgr and start over, for lack of anything more intelligent to do at this point, unless there are more suggestions.

    I don't have a metalink account (we bought eqt and licenses from another Co, which included a 4CPU license, but nobody can find the paperwork, of course), so I've been using online docs. The netmgr troubleshooting guide pretty much has stopped me dead because I can't get a successful test and I've exhausted all remedies. This has turned into one of those gremlin problems....

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One other possibility is to enable "DEBUG" tracing in the listener.
    The resultant trace file might provide clues as to the nature of
    the incoming request & why it does not succeed.

Posting Permissions

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