Results 1 to 3 of 3

Thread: tnsnames enigma

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: tnsnames enigma

    Hello,

    I'm trying to understand tnsnames.ora on my unix boxes. I have 9i installed on HP-UX and Tru64. From my windows machine, I can connect to them both using sqlplus.

    To connect to the DBs on either machine from windows, I used the net config assistant. For the Service Name, I give the global dbname. At the end of the creation, it automatically fills in the Net Service Name with just the SID. This way, I can connect with sqlplus user/pass@SID. It also created an sqlnet.ora file.

    I have 2 problems on UNIX:

    1. how do I configure tnsnames.ora to be able connect to a DB on another machine?
    2. how do I configure tnsnames.ora to be able to connect to a local database?

    The problem is that I don't know the dependence between Service Name, the connection identifier, sqlnet.ora, and ORACLE_SID.

    If I'm on the HP-UX machine and I try to connect to the Tru64 machine, I put in my tnsnames.ora file:

    DBTRU64.TRU64.SUBNET.COMPANY.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tru64.subnet.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = dbtru64.tru64.subnet.company.com)
    )
    )

    Then I connect to the database from HP-UX like this:
    sqlplus user/pass@dbtru64.tru64.subnet.company.com

    but I would rather just do:
    sqlplus user/pass@dbtru64

    but I don't know how.

    I have the same problem locally. I added a local db in the tnsnames.ora:

    HPDB.HPUX.SUBNET.COMPANY.COM =
    (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(Key = hpdb))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = hpdb.hpux.subnet.company.com)
    (INSTANCE_NAME = hpdb)
    (GLOBAL_NAME = hpdb.hpux.subnet.company.com)
    )
    )

    but whether I use the SID or whether I use the global dbname after the '@', I always get ORA-12154: TNS:could not resolve service name. I have to set ORACLE_SID in order to connect.

    I've tried playing with the sqlnet.ora file, but I simply don't know what I'm doing. I've tried to gleen some wisdom from the web, but I just can't crack the tnsnames enigma.

    any help appreciated.

    thanks.

  2. #2
    Join Date
    Feb 2004
    Posts
    3
    I think I sort of understand the sqlnet.ora file now. I've set

    NAMES.DEFAULT_DOMAIN = subnet.company.com

    and changed my tnsnames for the local DB:

    HPDB.SUBNET.CPQCORP.NET =
    and
    TRU64.SUBNET.CPQCORP.NET =

    so when I use
    sqlplus user/pass@tru64
    I can connect to the network db. When I do

    sqlplus user/pass@hpdb

    I no longer get the error ORA-12154, but now I get
    ORA-12541: TNS:no listener. I've looked at my listener.ora file, and I have:
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = hpdb)) )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpux.subnet.company.com)(PORT = 1521))
    )
    )

    Is this not enough? Do I need to add it to the SID_LIST_LISTENER?

    Thanks.

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    everything works

    I've tested this with my other 4 databases on my HP-UX machine and it works. I think I just didn't have any luck with this particular instance (hpdb).

    As it always happens, I found the solution to my problems just after having posted a cry for help.

    until next time...

Posting Permissions

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