Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Location
    Manila, Philippines
    Posts
    16

    Cannot connect to DB using sqlplus but can tnsping the database

    hi guys,

    Im just a starting DBA and I really need help with my problem. I have a windows server with an Oracle 10gR2 version DB. I have 10 DB in it with different and distinct SID. Before connecting to them I already set my oracle sid and oracle home. I even used the tnsping utility and it returned successful results however when I use SQLPLUS to connect to the database its returns an ORA-12560: Tns: protocol adapter error. However I've checked the tnsnames.ora and listener.ora and both file are correct. I tried connecting to other databases on the same server and they are just working fine. There are about 3-4 DB's I can't connect to. I dont know what to check anymore. Please help me! Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    The bottom line is that when you are logged directly onto the DB server system at the OS level, then there is no need for SQL*Net to connect to any database.
    Since you are reporting ORA-12560, you are (ab)using SQL*Net.
    Code:
    12560, 00000, "TNS:protocol adapter error"
    // *Cause: A generic protocol adapter error occurred.
    // *Action: Check addresses used for proper protocol specification. Before
    // reporting this error, look at the error stack and check for lower level
    // transport errors. For further details, turn on tracing and reexecute the
    // operation. Turn off tracing when the operation is complete.
    I suggest that you'd have have more success, from the command line & avoiding the Windoze GUI SQLPLUS interface.

    Can you connect to Oracle from Command Window?
    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.
    Don't say, show. Don't promise, prove.

  3. #3
    Join Date
    May 2009
    Location
    Manila, Philippines
    Posts
    16
    I already tried connecting using the command line however it still returns the same error the got from sqlplus. Its kinda weird. Does the listener have to do something with this? I already tried restarting and it still gives the same error. I know you dont need 1 listener per DB but can I say that this is the fault of the listener?
    Last edited by dadorador; 05-08-09 at 01:37.

  4. #4
    Join Date
    May 2009
    Location
    Manila, Philippines
    Posts
    16
    For example I have a database on the server with an SID of ETMLA220

    on the command line:
    -----------------------------------------------------------------------
    c:\> tnsping ETMLA220

    Copyright (c) 1997, 2005, Oracle. All rights reserved.

    Used parameter files:
    C:\oracle\product\10.2.0\network\admin\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ETMLA220)))
    OK (20 msec)
    --------------------------------------------------------------------------


    and I set the oracle_sid

    c:\> set oracle_sid=ETMLA220

    -------------------------------------------------------------------------
    but when i ran the following command it returns the following results

    c:\> sqlplus / as sysdba

    SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 8 14:01:08 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    ERROR:
    ORA-12560: TNSrotocol adapter error


    Enter user-name:


    I have verified the tnsnames.ora and it seem correct here is the entry

    ETMLA220 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ETMLA220)
    )
    )

    I dont know what to do anymore. Help anyone?

  5. #5
    Join Date
    May 2009
    Location
    Manila, Philippines
    Posts
    16
    I aslo tried connecting like the one below and got the resulting error:

    c:\> sqlplus system/manager@ETMLA220

    SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 8 14:18:39 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

  6. #6
    Join Date
    Mar 2009
    Posts
    14
    first of all check whether you have added your windows user in ORA_DBA group on your machine
    second.. stop / start listener..your second error is very generic and could be resolved just by start/stop the listener

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    >However I've checked the tnsnames.ora and listener.ora and both file are correct.
    Post contents of both using <code tags> as documented in #1 STICKY Post at top of this forum.

    >I tried connecting to other databases on the same server and they are just working fine.
    Please provide 2 lists.
    1) Names,alias,SID,ServiceName of database to which you can connect
    2) Names,alias,SID,ServiceName of database to which you can NOT connect


    Post results from following command:
    SET
    Last edited by anacedent; 05-08-09 at 12:12.
    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.
    Don't say, show. Don't promise, prove.

  8. #8
    Join Date
    May 2009
    Location
    Manila, Philippines
    Posts
    16
    to anadecent: I'll try to attend to your request to post what you need to see since its weekend I can't access the servers yet. Will get back at you asap. Thank you very much for helping me.

Posting Permissions

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