Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: SQL Plus on XP (TNS LISTENER)

    Hi

    I have a question. I recently installed Oracle 9i on my machine.
    I cant seem to login into the DB.

    It says TNS Listner not found.

    I am really not sure what I should specify the service name which is of the form SID_HOSTNAME, as the HOSTNAME in my case should be LOCALHOST ...

    I would appreciate quick response to the same.

    My tnsnames.ora is
    -----
    # TNSNAMES.ORA Network Configuration File: C:\oracle\ora90\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    OEMREP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MohiniAruneesh)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = OEMREP)
    )
    )

    SALHOTRA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MohiniAruneesh)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = SALHOTRA)
    )
    )

    ARUNEESH =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MohiniAruneesh)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ARUNEESH)
    )
    )

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MohiniAruneesh)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = MODOSE)
    (PRESENTATION = http://HRService)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    -----
    Regards
    Aruneesh

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    If it is on your own computer, you do not need to specify a host.

    Regardless, you need to start your listener.
    Open a command prompt and type: lsnrctl start
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Lsnrctl

    I dont happen to see the command lsnrctl on the windows platform.

    On the linux platform at work, I know the file, and I can easily connect TOAD and other tools with the DB.

    It is my first stint with the XP and 9i.

    I saw in the services in XP, that the Oracle TNS listener is running.


    I am sure it is with the service i need to use.
    Since at work, we had a SID XYZ and HOST www.site.com, so the service I used to use to connect was

    XYZ_www.site.com along with valid username and password.

    I am not sure what I should do in case of XP.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    open a command prompt.

    type: lsnrctl stat

    if it says it is on, great. If it says it is off, then start it up and go from there.

    Just because the 'service' says it is running means nothing. This happened to me once where the SERVICE said it was running, but when I typed in LSNRCTL STAT, it said listener was not running.

    WHY? Who knows. Windows pisses me off.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Red face

    lsnrctl stat says that few services are running.
    Any more leads into the same.
    Thanx and Regards
    Aruneesh

    ----------------

    LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-SEP-2003 12:25: 11

    Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0) ))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
    Start Date 08-SEP-2003 10:30:31
    Uptime 0 days 1 hr. 54 min. 42 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File C:\oracle\ora90\network\admin\listener.ora
    Listener Log File C:\oracle\ora90\network\log\listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\ pipe\EXTPROC0ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MohiniAr uneesh)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MohiniAr uneesh)(PORT=8080))(Presenta
    tion=HTTP)(Session=RAW))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MohiniAr uneesh)(PORT=2100))(Presenta
    tion=FTP)(Session=RAW))
    Services Summary...
    Service "ARUNEESH" has 2 instance(s).
    Instance "ARUNEESH", status UNKNOWN, has 1 handler(s) for this service...
    Instance "ARUNEESH", status READY, has 1 handler(s) for this service...
    Service "ARUNEESHXDB" has 1 instance(s).
    Instance "ARUNEESH", status READY, has 1 handler(s) for this service...
    Service "OEMREP" has 1 instance(s).
    Instance "OEMREP", status UNKNOWN, has 1 handler(s) for this service...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "SALHOTRA" has 1 instance(s).
    Instance "SALHOTRA", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    if ARuneesh has two instances, then perhaps in your tnsnames you should specify the SID= in there.

    (CONNECT_DATA =
    (SID = your_sid)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    yeah, you need something like this:

    PHP Code:
    KOD1.PRIMUM.COM =
      (
    DESCRIPTION =
        (
    ADDRESS_LIST =
          (
    ADDRESS = (PROTOCOL TCP)(HOST 192.168.0.70)(PORT 1521))
        )
        (
    CONNECT_DATA =
          (
    SERVICE_NAME kod1.projectyangtze.com)
          (
    INSTANCE_NAME kod1)
        )
      ) 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Lsnrctl stat (renew)

    Actually the last output showed two instances of ARUNEESH, as i started lnsrctl twice.
    This is when i started the lsnrctl after rebooting my XP machine.

    --------------

    LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-SEP-2003 13:02:
    51

    Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

    Starting tnslsnr: please wait...

    TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
    System parameter file is C:\oracle\ora90\network\admin\listener.ora
    Log messages written to C:\oracle\ora90\network\log\listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\ pipe\EXTPROC0ipc
    )))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MohiniAr uneesh)(PORT=152
    1)))

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0) ))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
    tion
    Start Date 08-SEP-2003 13:02:54
    Uptime 0 days 0 hr. 0 min. 2 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File C:\oracle\ora90\network\admin\listener.ora
    Listener Log File C:\oracle\ora90\network\log\listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\ pipe\EXTPROC0ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MohiniAr uneesh)(PORT=1521)))
    Services Summary...
    Service "ARUNEESH" has 1 instance(s).
    Instance "ARUNEESH", status UNKNOWN, has 1 handler(s) for this service...
    Service "OEMREP" has 1 instance(s).
    Instance "OEMREP", status UNKNOWN, has 1 handler(s) for this service...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "SALHOTRA" has 1 instance(s).
    Instance "SALHOTRA", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    did you try using the sid with the service name in your tnsnames.ora file?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow no luck

    I added the following entry to tnsnames.ora

    CINK =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = CINK)
    )
    )



    --------------

    I also added the following entry to the listener.ora


    CINK =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = CINK)
    )
    )



    ...


    I am trying to connect to the Oracle DB using the SQL * Plus ... but it is giving me the error

    TNS : could not resolve service name.


    I am not sure if I need to make modifications to tnsnames.ora or listener.ora.

    I would appreciate your response.

    Thanx and Regards
    Aruneesh

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Post your sqlnet.ora your tnsnames (just the one for your local connect) and your listener.ora files

    I assumed you were wanting to connect to aruneesh.
    Anyways, our tnsnames should look something like this.

    CINK.mycomputer.com =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = CINK.mycomputer.com)
    (INSTANCE_NAME = YOUR_SID_HERE)
    )
    )


    for host, use the IP
    for service-name, you want to mimic whatever your listener says your service name is.

    Why do you have so many services in your listener?
    you only need one service your your own instance on that machine.
    Think of it as listening for connections. Since you are not hosting 3 instances then you wouldn't have 3 services. Just one.
    I am assuming that CINK is your local instance/sid and service name?
    If not, then provide the information regarding that.
    Last edited by The_Duck; 09-08-03 at 17:00.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    my guess is that you need to find your default domain and program that in there.

    you can do this by opening up Oracle Net Manager, highlight Profile, then click the Oracle Names tab. That will tell you what your default domain is.

    From there, you plug that in to your tnsnames (or have Net Manager configure your tnsnames for you). So, your tnsnames would have:

    CINK.default_domain =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = CINK)
    (INSTANCE_NAME = YOUR_SID_HERE)
    )
    )
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Got the answer

    I got the answer by reading on tnsnames.ora and listener.ora documentation.

    The URL I used was
    http://www.engin.umich.edu/caen/wls/...5/tnsnames.htm

    Duck I appreciate your help in this regards.

    Finally, I am able to login into the system, using all the windows client.

    Thanx and Regards
    Aruneesh

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296

    Re: Got the answer

    Originally posted by aruneeshsalhotr
    I got the answer by reading on tnsnames.ora and listener.ora documentation.

    The URL I used was
    http://www.engin.umich.edu/caen/wls/...5/tnsnames.htm

    Duck I appreciate your help in this regards.

    Finally, I am able to login into the system, using all the windows client.

    Thanx and Regards
    Aruneesh
    Well, what was the solution.
    I am dying to know!
    I thought for sure it was your domain.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Solution

    Hi

    There are a couple of things that I learnt during this process.
    Firstly, when the DB was created, XP created services in the system.
    Now the services in XP has to match the service name in listener.ora.

    Also the service name in tnsnames.ora also has to match the XP service name.

    I am pasting a part of tnsnames.ora and listener.ora for a XP Oracle Service termed OracleServiceAruneesh

    tnsnames.ora
    ----------------
    DB.salhotra.com =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ARUNEESH)
    )
    )


    listener.ora
    -------------
    (SID_DESC =
    (GLOBAL_DBNAME = ARUNEESH)
    (ORACLE_HOME = C:\oracle\ora90)
    (SID_NAME = ARUNEESH)
    )


    Hope it helps ur hunger for info/solution.

    Do let me know if you want more specific information on the same.

    If you want, you could IM me at yahoo : aruneeshsalhotra


    Thanx and Regards
    Aruneesh

Posting Permissions

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