TNSNames.Ora : SERVICE_NAME vs SID Help!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: TNSNames.Ora : SERVICE_NAME vs SID Help!

  1. #1
    Join Date
    Nov 2000
    Posts
    344
    Hi,

    On 3 separate 64 HP boxes running hpux 11, I have 3
    instances of Oracle 8.1.6.3.1 64 bit. (one db per server)

    From my desktop PC, I can connect to 2 of them with
    my tnsnames.ora file looking like this :

    SID_NAME =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (COMMUNITY = tcp.world)
    (PROTOCOL = TCP )
    (Host = server1.mycompany.com ) (Port = 12345 )
    )
    )
    (CONNECT_DATA = (SID= SID_NAME))
    )

    and I can change the 2nd to last line to :

    (CONNECT_DATA = (SERVICE_NAME= SID_NAME))

    and it still works.

    But, connecting to one of the databases with my tnsnames file
    having the 'service_name = ' gives me a ORA-12504 error.

    Now, I know that the simple fix is to change my tnsnames.ora file and I have done that. But I am interested in this from
    an educational standpoint...

    I think all of my databases are setup on my servers the same
    way. They are all running the same version of the database,
    and the same version of the listener. What could it be?

    thanks in advance!
    -John




  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The service_name may or may not be the ORACLE_SID. It is setup via the init.ora file with the "service_names" parameter. You can check this parameter by:
    select name, value from v$parameters
    where name like 'service%'
    /
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    Hi Jeff,

    Thanks for the reply. Unfortunately, that does not seem to be the problem. I checked in v$parameter for all 3 databases, they all have

    name value
    -----------------
    service_names SID_NAME.world


    where SID_NAME is the sid for the database

    except for the '.world' part it matchs the INSTANCE_NAME in v$instance and NAME in v$database.

    any other ideas?

    Thanks...
    -John

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The world part normally get to ge setted up at sqlnet.ora file and tnsnames.ora files. You can just have the service name set as Jeff explained but have the world/domain option to tag it. This you would do through tnsnames.ora and sqlnet.ora files normally found under $ORACLE_HOME/network/admin


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Nov 2000
    Posts
    344
    Hi Sam,

    thanks for your help.

    In my sqlnet.ora file I have the following :

    NAMES.DEFAULT_DOMAIN = WORLD
    SQLNET.AUTHENTICATION_SERVICES= (NTS)
    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

    And in my tnsnames.ora I actually list the entries twice,
    once like :

    jdorlon =
    (DESCRIPTION = blah blah blah...

    and anther time like this :

    jdorlon.world =
    (DESCRIPTION = blah blah blah...

    with the .world on the name of it being the only difference.

    I have tried to connect specifying the .world and leaving it
    off, but to no avail.

    Is this what you were talking about or did I misss something?

    Thanks...
    -John

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If you do not want the world part to be used, then go ahead and comment out the names.default_domain and on the tnsnames.ora remove the community and then try


    sqlplus sys@jordon


    it should work.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Nov 2000
    Posts
    344
    Same results. :-(

    ORA-12504.




  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Problem Description:
    ====================

    Using Net8 configuration Assistant tool to add entry to "tnsname.ora" file
    and selecting to create using: Oracle8i release 8.1 database or service
    Or manually creating your "tnsnames.ora" file using the service_name or
    instance_name parameters .

    After configuring the tnsnames file you try to connect and receive the
    following errors:

    ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
    Cause: The SID was missing from the CONNECT_DATA.
    Action: Check that the connect descriptor corresponding to the service
    name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.
    ORA-12154: TNS:could not resolve service name
    Cause: The service name specified is not defined correctly in the
    TNSNAMES.ORA file.
    Action: Make the following checks and correct the error:
    - Verify that a TNSNAMES.ORA file exists and is in the proper
    place and accessible. See the operating system specific manual
    for details on the required name and location.
    - Check to see that the service name exists in one of the
    TNSNAMES.ORA files and add it if necessary.
    - Make sure there are no syntax errors anywhere in the file.
    Particularly look for unmatched parentheses or stray characters.
    Any error in a TNSNAMES.ORA file makes it unusable. See
    Chapter 4 in the SQL*Net V2 Administrator's Guide. If
    possible, regenerate the configuration files using the Oracle
    Network Manager.


    Problem Explanation:
    ====================

    This is happening due to the fact that the Oracle8i Instance or Listener is
    not set up to use the Oracle8i New Features for database configuration.

    The "init.ora" file does not contain the following entries:

    service_names or instance_name

    The "listener.ora" file does not contain:

    global_dbname =


    Search Words:
    =============

    ORA-12504
    tnsnames
    Oracle8i
    ORA-12154

    Solution: Configure "tnsnames.ora" file with appropriate entries

    Solution Description:
    =====================

    Depending on how your instance is set up with Oracle8i you need to set up
    your tnsnames.ora file accordingly:

    If your Oracle8i database is configured to use the NewFeatures for Oracle 8i:
    In your "INIT.ora" file you have set up your instance to use

    service_names and instance_name

    And have set up your "listener.ora" file with global_dbname than you will be
    able to configure your "tnsnames.ora" file to include service_name and/or
    instance_name parameter.

    This will enable you to take advantage of the Oracl8i New Features for
    Network

    Otherwise you would use the tnsnames.ora file for V2 or V8.

    Example1 Tnsnames.ora file for using the new Oracle8i New Features:

    net_service_name =
    (description =
    (address = (protocol_address_information))
    (connect_data=
    (service_name = )
    (instance_name= )))

    Example2 Tnsnames.ora file using old features:

    service_name/alias =
    (DESCRIPTION =
    (ADDRESS =
    (COMMUNITY = SAMPLE_COMMUNITY) <=== not needed with Net8 (V2 only)
    (PROTOCOL = TCP)
    (HOST = )
    (PORT = 1521)
    )
    (CONNECT_DATA = (SID = )
    )
    )

    NOTE: If using the Oracle Tool Net8 Congfiguration Assistant to configure your
    "tnsnames.ora" file

    For example1 select :

    Oracle8i release 8.1. database or service (3rd screen of the config tool)

    For example2 select:

    Oracle8i release 8.0 or previous database or service (3rd screen of
    the config tool)


    Solution Explanation:
    =====================

    In order to use or take advantage of ththe New Network Features using service
    name you must have the following information in the "init.ora" file:

    service_names
    instance_name

    the "listener.ora" file must have global_dbname (which should match the value
    contained in the service_names from the "init.ora" file)

    If you don't have any of this configured than you must configure your tnsnames.
    ora file using the old network Net8 convention mode (see example2 above)


    CHECK THIS OUT

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Nov 2000
    Posts
    344
    Thanks for sticking with me, Sam, I owe ya one.

    I do not have global_names set, and I am using the old
    config in my listener.ora. Still I don't see the difference
    between my different instances, they all appear to be the same way, but at least I see somthing to change now. I'll give it a try when I can get a few minutes of downtime...

    :-)

    -John

  10. #10
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    jdorlon,

    From what I remember from the past with regards to using SID= vs. SERVICE_NAME= is that when you choose to use SERVICE_NAME, you need to have the listener.ora entry to have the GLOBAL_NAME= and have it equaling the same value.

    If this is what you've found, then I can confirm it.

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