This discussion is archived
14 Replies Latest reply: Apr 5, 2012 6:07 AM by 661369 RSS

TNSNAMES / SQLNET General Question

661369 Newbie
Currently Being Moderated
Using 11.2.03 Oracle on 64bit Linux

Due to some problems I've been trying to troubleshoot, I've been trying to dive into the uses of TNSNAMES and SQLNET.

Are they required on the DB server itself for a general install? All my connections use the EZ Connection string style, so I thought TNSNAMES would not be needed. Though after removing it, I had to restart the DB and it would give LOCAL_LISTENER errors until I set TNSNAMES back again. Is there way to avoid using the TNSNAMES completely on the server?

I've tried looking around for information on if it is even needed on a server and if so the specifics of why. I usually stuff reguarding Oracle 8 and 9 and the few things I find using 11 are very very basic and don't really explain the why. They just throw a basic example and say replace this data.

Sorry if it seems pretty basic, I just keep trying to wrap my head around the basics of it. I want to simplify my configs and files and if it isn't needed I would like to know why and remove / edit it down.
  • 1. Re: TNSNAMES / SQLNET General Question
    sb92075 Guru
    Currently Being Moderated
    ChaosAD wrote:
    Using 11.2.03 Oracle on 64bit Linux

    Due to some problems I've been trying to troubleshoot, I've been trying to dive into the uses of TNSNAMES and SQLNET.

    Are they required on the DB server itself for a general install? All my connections use the EZ Connection string style, so I thought TNSNAMES would not be needed. Though after removing it, I had to restart the DB and it would give LOCAL_LISTENER errors until I set TNSNAMES back again. Is there way to avoid using the TNSNAMES completely on the server?

    I've tried looking around for information on if it is even needed on a server and if so the specifics of why. I usually stuff reguarding Oracle 8 and 9 and the few things I find using 11 are very very basic and don't really explain the why. They just throw a basic example and say replace this data.

    Sorry if it seems pretty basic, I just keep trying to wrap my head around the basics of it. I want to simplify my configs and files and if it isn't needed I would like to know why and remove / edit it down.
    SQL*Net is NOT required to install or run any Oracle RDBMS.
    tnsnames.ora is a client side file.
    LOCAL_LISTENER is NOT a required parameter & could be eliminated from spfile/pfile.
  • 2. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Thanks sb92075.

    We use a spfile currently, so how would I remove it? Once removed will it just look for the default listener file and that is? Guessing the TNSNAMES file would not be needed then either with that value gone. I don't remember setting it, so it must be a default install value?

    Trying to understand the why as well so I can learn instead of just cutting and pasting.

    Edited by: ChaosAD on Apr 4, 2012 8:06 PM
  • 3. Re: TNSNAMES / SQLNET General Question
    sb92075 Guru
    Currently Being Moderated
    sqlplus
    / as sysdba
    SHUTDOWN IMMEDIATE
    create pfile from spfile;
    -- now edit the pfile & remove the line for LOCAL_LISTENER
    create spfile from pfile;
    STARTUP
    EXIT
  • 4. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Thanks, I was thinking down that path. With that done can we we safely remove the TNSNAMES then?

    We have a company policy and have to use sqlnet for the following:
    SQLNET.OUTBOUND_CONNECT_TIMEOUT=10
    SQLNET.INBOUND_CONNECT_TIMEOUT=3
    SQLNET.EXPIRE_TIME=10

    We also have to set these in the listener
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2

    Is there a way to make sure these are read and set? In the status I don't see it returning any of these, though they are set in the files.
  • 5. Re: TNSNAMES / SQLNET General Question
    sb92075 Guru
    Currently Being Moderated
    ChaosAD wrote:
    Thanks, I was thinking down that path. With that done can we we safely remove the TNSNAMES then?

    We have a company policy and have to use sqlnet for the following:
    SQLNET.OUTBOUND_CONNECT_TIMEOUT=10
    SQLNET.INBOUND_CONNECT_TIMEOUT=3
    SQLNET.EXPIRE_TIME=10

    We also have to set these in the listener
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2

    Is there a way to make sure these are read and set? In the status I don't see it returning any of these, though they are set in the files.
    I have never, ever used any of them & never had any problems using SQL*Net.
  • 6. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Does there even need to be a listener, tnsnames or sqlnet file for a default oracle install to startup and have clients connect? I have seen articles that said they aren't needed as long as it is a default install and you only plan on having the 1 listener on the default 1521 port.

    If that is the case can I just have a listener file with the parameters I am required to have leave out the standard? Such as the following:

    Before:
    LISTENER =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL=TCP) (HOST=uksn115) (PORT=1521))
    )
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2

    After:
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2
  • 7. Re: TNSNAMES / SQLNET General Question
    sb92075 Guru
    Currently Being Moderated
    ChaosAD wrote:
    Does there even need to be a listener, tnsnames or sqlnet file for a default oracle install to startup and have clients connect? I have seen articles that said they aren't needed as long as it is a default install and you only plan on having the 1 listener on the default 1521 port.

    If that is the case can I just have a listener file with the parameters I am required to have leave out the standard? Such as the following:

    Before:
    LISTENER =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL=TCP) (HOST=uksn115) (PORT=1521))
    )
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2

    After:
    ADMIN_RESTRICTIONS_listener=on
    INBOUND_CONNECT_TIMEOUT_listener=2
    Oracle listener is REQUIRED when REMOTE client needs to connect to the DB Server
    When you have a local client, like sqlplus, running directly on the DB Server itself,
    then no listener is required.
  • 8. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Oh, I thought that Oracle might create a default listener. Thanks for helping clear that part up for me. We def have remote connections so that is a go.

    From what I have read so far it looks like I just need to get rid of LOCAL_LISTENER and then I'll be left with the Listener and SQLNET (due to company required parameters) files and can get rid of theTNSNAMES files

    What is the point of the LOCAL_LISTENER parameter in the first place? If that is set to the same name as the listener defined in your Listener file, why did TNSNAMES have to be set with it in order for the DB to startup?
  • 9. Re: TNSNAMES / SQLNET General Question
    sb92075 Guru
    Currently Being Moderated
    ChaosAD wrote:
    Oh, I thought that Oracle might create a default listener. Thanks for helping clear that part up for me. We def have remote connections so that is a go.

    From what I have read so far it looks like I just need to get rid of LOCAL_LISTENER and then I'll be left with the Listener and SQLNET (due to company required parameters) files and can get rid of theTNSNAMES files

    What is the point of the LOCAL_LISTENER parameter in the first place? If that is set to the same name as the listener defined in your Listener file, why did TNSNAMES have to be set with it in order for the DB to startup?
    For V10+, no listener.ora file is required.

    lsnrctl start

    above will start listener even if no listener.ora file exists
    You can decide if this meets your criteria for a "default" listener or not.
    sqlnet.ora file is not required for SQL*Net to work & can be either a server or client file.
  • 10. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    In order to enforce those parameters for clients connecting, I have to have the sqlnet file on my server though in the network/admin dir correct? Same I imagine for any specific Listener parameters I want to have enforced.

    Thanks sb92075, I appreciate you taking the time to help me understand what probably seems like a basic thing.
  • 11. Re: TNSNAMES / SQLNET General Question
    AliD Expert
    Currently Being Moderated
    In order to enforce those parameters for clients connecting, I have to have the sqlnet file on my server though in the network/admin dir correct?
    You can have SQLNet on either Server side or Client side. Of course, having it on server side means all clients will be using the parameters.

    Listener.ora is only server side.

    P.S. love your name. Sepultura is my favorite band!
  • 12. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Thanks on both accounts!
  • 13. Re: TNSNAMES / SQLNET General Question
    EdStevens Guru
    Currently Being Moderated
    ChaosAD wrote:
    Using 11.2.03 Oracle on 64bit Linux

    Due to some problems I've been trying to troubleshoot, I've been trying to dive into the uses of TNSNAMES and SQLNET.

    Are they required on the DB server itself for a general install? All my connections use the EZ Connection string style, so I thought TNSNAMES would not be needed. Though after removing it, I had to restart the DB and it would give LOCAL_LISTENER errors until I set TNSNAMES back again. Is there way to avoid using the TNSNAMES completely on the server?

    I've tried looking around for information on if it is even needed on a server and if so the specifics of why. I usually stuff reguarding Oracle 8 and 9 and the few things I find using 11 are very very basic and don't really explain the why. They just throw a basic example and say replace this data.

    Sorry if it seems pretty basic, I just keep trying to wrap my head around the basics of it. I want to simplify my configs and files and if it isn't needed I would like to know why and remove / edit it down.
    I have a whole series of articles and demos on sqlnet that you should find useful in answering most of the questions you've asked in this thread. See edstevensdba.wordpress.com
  • 14. Re: TNSNAMES / SQLNET General Question
    661369 Newbie
    Currently Being Moderated
    Thanks, I will take a look at those.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points