Cannot tnsping or connect locally except from root and oracle Linux accounts, but remote connections work

Posted on 2008-11-10
Last Modified: 2013-12-06
I just installed Oracle 11g on a new Fedora 9 Linux box.  Everything installed fine.  However, I am seeing some odd behavior that I can't figure out: I can tnsping and sqlplus (connect) perfectly from *remote* machines and from the root and oracle user accounts on the local box.  However, whenever I try to tnsping or sqlplus to the database from any "normal" Linux user account it throws a "TNS-03505: Failed to resolve name" error.  At first I thought it was invalid data in tnsnames.ora, but I have triple-checked it and plus I can connect just fine from the 'root' and 'oracle' accounts.

To summarize, here is what I am seeing ('shanadl' is the oracle service name):
1. Log in as 'dougb' on the local Linux box.
2. tnsping shanadl  << ERROR: "TNS-03505: Failed to resolve name"
3.  Log in as 'test' (which is another normal account) on the local Linux box.
4. tnsping shanadl << ERROR: "TNS-03505: Failed to resolve name"
5.  Log in as 'oracle' on the local Linux box.
6. tnsping shanadl << WORKS!
7.  Log in as 'oracle' on the local Linux box.
8. tnsping shanadl << WORKS!
9. Log in to any remote box (e.g., Windows or a different Linux box).
10. tnsping shanadl << WORKS!  (granted, this uses a different tnsnames.ora file local to the client box)

In the past I have installed Oracle 8, 9i, and 10g instances and I have never seen this behavior.  The fact that I can connect to the Oracle instance as root and the 'oracle' accounts but not from normal accounts makes me think this is some new security feature in Oracle 11g.  However, Googling hasn't turned up anything.  Why can't I connect from non-privileged Linux accounts?  I'm really stumped here!  Any advice is appreciated.
Question by:dbeachy1

    Author Comment

    Correction, step 7 should read:
    7. Log in as 'root' on the local Linux box.
    LVL 23

    Accepted Solution

    Weird.  Problems like this are commonly solved when we methodically examine our assumptions, right?  Although you triple-checked a tnsnames file, it's possible that your box has more than one tnsnames.ora.  Check with executing "find / -name "tnsnames.ora" -type f -print" and see whether or not you have duplicates.
    Secondly, are the .profiles for test and dougb identical to the others?  I'd connect as test, and eyeball my "env|sort" results to confirm the Oracle homes, and TNS_ADMIN variable are correct.
    Thirdly, your symptom suggests that the test accounts simply don't have execute permission on $ORACLE_HOME/bin/sqlplus.  Check for group priviledges, and let me know what comes back.

    Author Closing Comment

    Sure enough, it turned out to be a permissions problem on tnsnames.ora! Oracle 11g created the tnsnames.ora file with 0640 permissions (rw owner, r group, NO ACCESS world), and so only root and users in the 'dba' group could connect locally.  I assume this is part of the security ehnancements talked about in Oracle 11g.  In any case, the fix was this:
    1. Log in as oracle.
    2. chmod a+r $ORACLE_HOME/network/admin/tnsnames.ora

    And presto, I can connect from my local account now!  That little change from previous Oracle versions sure caused me a lot of grief.  :)  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    The purpose of this article is to fix the unknown display problem in Linux Mint operating system. After installing the OS if you see Display monitor is not recognized then we can install "MESA" utilities to fix this problem or we can install additio…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now