Home » RDBMS Server » Networking and Gateways » TNSping works for oracle user alone. Why? (Oracle 11g on Ubuntu 8.0.4 Server (x86-64))
icon4.gif  TNSping works for oracle user alone. Why? [message #409232] Sat, 20 June 2009 17:36 Go to next message
gansvv
Messages: 11
Registered: June 2009
Location: Gainesville, FL
Junior Member
Hi,
When I login as oracle user on the server, TNSping is able to connect to the listener. But when I login as any other user and try to use TNSPing, it fails with the "TNS-03505: Failed to resolve name" error.

$ tnsping EXTPROC_CONNECTION_DATA

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 20-JUN-2009 18:32:11

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

Used parameter files:
/opt/app/oracle/product/db11g/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name


(I am able to connect from a client machine to the server using Oracle SQL Developer and the database SID.)
I am trying to execute OCCI programs by using another username (apart from "oracle") on the server. I keep getting ORA-12154 error.

$ ./occi_example
terminate called after throwing an instance of 'oracle::occi::SQLException'
  what():  ORA-12154: TNS:could not resolve the connect identifier specified

Aborted


Please help!
thanks in adv.
Re: TNSping works for oracle user alone. Why? [message #409233 is a reply to message #409232] Sat, 20 June 2009 17:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
03505, 00000, "Failed to resolve name"
// *Cause:  The service name you provided could not be found in TNSNAMES.ORA,
//          an Oracle Names server, or a native naming service.
// *Action: Verify that you entered the service name correctly.  You may need
//          to ensure that the name was entered correctly into the network
//          configuration.

12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a 
// naming method repository, or the repository could not be
// located or reached.
// *Action:
//   - If you are using local naming (TNSNAMES.ORA file):
//      - Make sure that "TNSNAMES" is listed as one of the values of the
//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA)
//      - Verify that a TNSNAMES.ORA file exists and is in the proper
//        directory and is accessible.
//      - Check that the net service name used as the connect identifier
//        exists in the TNSNAMES.ORA file.
//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
//        file.  Look for unmatched parentheses or stray characters. Errors
//        in a TNSNAMES.ORA file may make it unusable.
//   - If you are using directory naming:
//      - Verify that "LDAP" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Verify that the LDAP directory server is up and that it is
//        accessible.
//      - Verify that the net service name or database name used as the
//        connect identifier is configured in the directory.
//      - Verify that the default context being used is correct by
//        specifying a fully qualified net service name or a full LDAP DN
//        as the connect identifier
//   - If you are using easy connect naming:
//      - Verify that "EZCONNECT" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Make sure the host, port and service name specified
//        are correct.
//      - Try enclosing the connect identifier in quote marks.
// 
//   See the Oracle Net Services Administrators Guide or the Oracle
//   operating system specific guide for more information on naming.


while logged on to DB server as user oracle issue following commands

lsnrctl status
lsnrctl service
env | sort

CUT & PASTE commands & results back here
Re: TNSping works for oracle user alone. Why? [message #409234 is a reply to message #409233] Sat, 20 June 2009 17:57 Go to previous messageGo to next message
gansvv
Messages: 11
Registered: June 2009
Location: Gainesville, FL
Junior Member
Thanks BlackSwan. Here are the results.

<host> is the hostname.

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 20-JUN-2009 18:49:50

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                20-JUN-2009 18:49:44
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db11g/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/berlin/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "extproc" has 1 instance(s).
  Instance "extproc", status UNKNOWN, has 1 handler(s) for this service...
Service "sid11g" has 1 instance(s).
  Instance "sid11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


$ lsnrctl service

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 20-JUN-2009 18:50:58

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521)))
Services Summary...
Service "extproc" has 1 instance(s).
  Instance "extproc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "sid11g" has 1 instance(s).
  Instance "sid11g", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "sid11g.<host>" has 1 instance(s).
  Instance "sid11g", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "sid11gXDB.<host>" has 1 instance(s).
  Instance "sid11g", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: <domain_name>, pid: 21808>
         (ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=27118))
Service "sid11g_XPT.<host>" has 1 instance(s).
  Instance "sid11g", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully


Here is env|sort for "oracle" user:

EXTPROC_DLLS=ANY
HOME=/home/oracle
LANG=en_US.UTF-8
LD_LIBRARY_PATH=/opt/app/oracle/product/db11g/lib:/lib:/usr/lib:/usr/local/lib
LOGNAME=oracle
MAIL=/var/mail/oracle
OLDPWD=/home/oracle
ONCONFIG=onconfig.std
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/db11g
ORACLE_SID=sid11g
PATH=/usr/sbin:/usr/sbin:/usr/local/bin:/usr/bin:/bin:/opt/app/oracle/product/db11g/bin
PWD=/opt/app/oracle/product/db11g/network/admin
SHELL=/bin/bash
SHLVL=1
TERM=xterm
USER=oracle
_=/usr/bin/env


Thanks!
Re: TNSping works for oracle user alone. Why? [message #409235 is a reply to message #409232] Sat, 20 June 2009 18:15 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>When I login as oracle user on the server, TNSping is able to connect to the listener.
>But when I login as any other user and try to use TNSPing, it fails with the "TNS-03505: Failed to resolve name" error.

>I am trying to execute OCCI programs by using another username (apart from "oracle") on the server.
>I keep getting ORA-12154 error.

I suspect that both of these have the same root cause.
Starting with V10 Oracle changed default protection mask on some/many/most/all files under $ORACLE_HOME.
Oracle tightened up security.

In some cases only user "oracle" can access files.
In other cases members of the "dba" group can access files.

When access to the DB is done via SQL*Net, it is user "oracle" (which owns & runs the listener) that does all the work.
Therefore everything works as desired.

For example a different user can't even get sqlplus to work on the DB server when not a member of "dba" group.

It gets very verbose, but you can possibly verify by using

strace ./occi_example | tee capture.log

capture.log will show which files got accessed & whether the open succeeded or not
Previous Topic: dbdesigner
Next Topic: Listener Doubts
Goto Forum:
  


Current Time: Thu Mar 28 03:31:33 CDT 2024