Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    11

    Unanswered: OEM and tnsnames.ora

    Hi all,

    This is my first post here...

    I am in the process of completing my Oracle cert, and I'm running Oracle 9i R2 on RH 9.0. The db runs great, but when I try to use OEM I get this error:

    ORA-12514: TNS:Listener could not resolve SERVICE_NAME given in connect descriptor

    I did lookup this error and I know it has something to do with my tnsnames.ora, and here's its contents:


    # TNSNAMES.ORA Network Configuration File: /u01/app/oracle/product/9.2.0.1.0/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    EXTPROC_CONNECTION_DATA.LOCALDOMAIN =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    PSY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(Host = localhost)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = PSY)
    )
    )


    Now my db is named PSY, the sid is PSY.... I tried everything and I can't quite put my finger on what could be wrong. Also, when I start my listener prior to do this, it has no mention of the PSY db, only the EXTPROC and I'm not sure if that's where the problem is, I don't know if it's normal. It says I have one instance of EXTPROC and that's it.


    Can anyone kindly point me into the right direction?

    Also, how do you start the DBCA in linux?

    Thanks, cheers!

    - Myst

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ls -l $ORACLE_HOME/bin/dbca
    # show what?

    What's the contents of listener.ora?

    lsnrctl status
    # show what?

    I'm also suspicious of using "localhost" as the host name in tnsnames.ora.
    Why are you not using a FQDN or IP# instead?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    11

    Arrow

    Thanks for the reply.

    Sure, here goes:

    -rwxr-xr-x 1 oracle oinstall 4039 Mar 19 01:10 /u01/app/oracle/product/
    9.2.0.1.0/bin/dbca

    I tried with my FQDN, but then I got TNS: Listener not available.


    Listener.ora file:


    # LISTENER.ORA Network Configuration File: /u01/app/oracle/product/9.2.0.1.0/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
    (PROGRAM = extproc)
    )
    )


    PSY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(Host = localhost)(Port = 1521))
    )
    (CONNECT_DATA =
    (SID = PSY)
    )
    )


    Finally, status shows:


    LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 12-MAY-2004 22:50:00

    Copyright 1991, 2002, Oracle Corporation. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)) )
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 9.2.0.1.0 - Production
    Start Date 12-MAY-2004 22:49:45
    Uptime 0 days 0 hr. 0 min. 15 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/9.2.0.1.0/network/admin/listener.ora
    Listener Log File /u01/app/oracle/product/9.2.0.1.0/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)) )
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lnx1.ivt h-crusade.com)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully


    Does this seem normal to you?

  4. #4
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    That would be right to replace the localhost by COMPUTER NAME or IP Address.And also, do one thing. Add your DB service name i.e. PSY in Listerner.ora file under address list and restart it. OEM does not recognize the db service on its own as listerner does.

    So, hope it will able to help u.

    Regards,
    Kamesh Rastogi
    - KR

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Mystifier,
    Please note that in your response to me previous questions the
    HOST= keyword/value pair contains THREE different values.

    (HOST = localhost.localdomain)
    (Host = localhost)
    (HOST=lnx1.ivth-crusade.com)

    Now I may not always be correct, but at least I try to be consistent.
    I'm not surprised that SQL*net is confused, because I am confused why
    I am seeing three different values for HOST=
    In my corner of the universe, I see/use the same value for HOST=
    WRT configuration files on a single system.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2004
    Posts
    11
    Yes, you are right! I did not do this deliberately, it's simply because I was trained using Windows (yes, I'm one of those!) so all of this is quite new to me to be honest with you. I'm not too familiar with FQDN and all this stuff, but I did experiment.

    At some point, I did enter localhost because I thought the simpler the better.

    BTW: hostname = lnx1.ivth-crusade.com

    I am at work right now, so let me fix this and get back to you later today and hopefully that will do it :P

    The only thing that boggles me is that I don't have a problem connecting to the db, so I never even bothererd with this. Thanks for the heads up =)

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The only thing that boggles me is that I don't have a problem connecting to >the db, so I never even bothererd with this. Thanks for the heads up =)
    Depending upon ????
    a PC can have more than one "ORACLE_HOME" tree
    and EACH of them will have its own TNSNAMES.ORA file;
    which are NOT shared or known to each other.
    I'd search the whole drive for EVERY occurance of TNSNAMES.ORA file.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2004
    Posts
    11
    Ok, thanks...

    I did a search and only 2 tnsnames.ora were found, this one and the one in the samples folder.

    I also replaced all HOST to lnx1.ivth-crusade.com, and now I get the "no listener" error. Kinda stuck in a deadlock here

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) What's in SQLNET.ORA file?
    2) I don't recommend as a long term "solution",
    but what happens if you use IP# instead of hostname?
    (I wonder if you can resolve name to IP#.)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    May 2004
    Posts
    11
    # SQLNET.ORA Network Configuration File: /u01/app/oracle/product/9.2.0.1.0/network/admin/sqlnet.ora# Generated by Oracle configuration tools.

    NAMES.DEFAULT_DOMAIN = localdomain

    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

    -----------

    I did try with lnx1.ivth-crusade.com above as well. I'm also behind a router, my ip is 192.168.0.101. Are you saying I should try with that IP address in tnsnames.ora, listener.ora and in sqlnet.ora?

    Thanks for helping and replying so quickly, this is driving me nuts!!!

  11. #11
    Join Date
    May 2004
    Posts
    11
    Basically, what is the *simplest* way to configure this? I really don't need to have anything specific because this is a test db ....

    I never ran into such problems either...

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >NAMES.DEFAULT_DOMAIN = localdomain
    comment out this line by....
    #NAMES.DEFAULT_DOMAIN = localdomain
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    May 2004
    Posts
    11
    No, that did not help anacedent.

    I did some more digging and here's what I found:

    I realized that obviously, when I start the listener, since I do not have a PSY instance this seemed abnormal to me. It should have read "Service PSY has 1 instance(s)".

    So I played with the listener.ora file and I used an older file that was used for Oracle 8 and it worked. Here are the contents of my new listener.ora file:


    # LISTENER.ORA Network Configuration File: /u01/app/oracle/product/9.2.0.1.0/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx1.ivth-crusade.com)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = PSY)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
    (SID_NAME = PSY)
    )
    )

    This did the job for me. Changing the host name was actually irrelevant in this case, it works with 192.168.0.101, lnx.ivth-crusade.com or even with localhost.

    I hope my mistakes can help others.


    Thanks guys.

Posting Permissions

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