Connection Error ORA 12154
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Connection Error ORA 12154

  1. #1
    Join Date
    May 2007
    Posts
    13

    Connection Error ORA 12154

    Ive recently installed Oracle 10g on my Windows XP and was able to connect. When i did a shutdown immediate and tried to connect again as a sysdba i get an error:

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

    Did a few basic checks like Oracle services, listener and all seems up and running. As im studyin Oracle 10g currently, please explain me in simple language meand guide to what i should do to resolve this problem. Any help is much appreciated!

    Below is my TNS, listener details for your reference

    TNSNAMES

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )

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

    LISTENER

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    )
    ================================

    Using 'conn sys@orcl as sysdba
    password: sysdba'

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you shut it down you listener doesnt know about the database anymore

    use sqlplus "/ as sysdba" to start it up

  3. #3
    Join Date
    May 2007
    Posts
    13
    You are right in saying that: But what is the solution to this issue and I get an error when i connect to the web iSQL plus - i do get the same error-

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

    Results below from command prmpt:
    =======================================
    SQL> conn / as sysdba
    Connected.
    SQL> exit
    Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

    C:\Documents and Settings\Nitesh>sqlplus /nolog

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 13 15:44:01 2007

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

    SQL> conn sys@orcl as sysdba
    Enter password:
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    ==========================================

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    On the box where you installed the database...

    set oracle_sid=your_oracle_sid
    sqlplus "/ as sysdba"
    startup
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2007
    Posts
    13
    Dyou mean where i installed my Oracle software, if yes then c: drive. not sure what if thats what u mean?

    Would you know what causing an error when I get an ORA-12514: when i connect as conn sys@orcl as sysdba and via the web?

    This worked for me

    output in comman prompt.
    =========================================
    C:\>net start oracleserviceorcl
    The requested service has already been started.

    More help is available by typing NET HELPMSG 2182.

    C:\>set ORACLE_SID=orcl

    C:\>sqlplus sys/sysdba as sysdba

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 13 17:26:41 2007

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


    Connected to:
    Oracle Database 10g Release 10.2.0.1.0 - Production

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    so whats the problem? if you shutdown the database you cannot connect using

    sys@orcl as sysdba you have to use / as sysdba

  7. #7
    Join Date
    May 2007
    Posts
    13
    Yes I can use using 'conn / as sysdba' but i cannot connect to iSQL Plus via the internet. I get the same error there. Whats the fix for that?

    See screen dump. cheers!
    Attached Images Attached Images

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    i give up, YOU CANT - that is expected behaviour - is you shut down the database you canot get to it from isqlplus

    is normal as there is no listener for it anymore!

  9. #9
    Join Date
    May 2007
    Posts
    13
    If you try to explain me may be i will understand then. I know i cannot connect to isql plus when its shutdown. The error i get is when im connected not when im not??

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    OK let me try
    There is a process, called listener out of the database. It is responcible for getting u connected to the database. When the listener does not knows of about the database because it is down or still have not registered to it, the listener cannot connect u
    On the other hand, when logged at the machine where the DB is installed, and u use SQL Plus to
    conn / as sysdba
    the listener is not used for connecting u. SQL PLus by itself gets to the oracle home directory and does what's necessary
    However, if U use
    conn sys/password@string as sysdba
    the @string part points u to the listener and since listener do not knows of your database, the conn fails
    iSQL plus allways goes trough the listener so no chance to pass trough SQL plus
    I hope I have not missed many things and described it in a way u can understand

    Regards

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