13 Replies Latest reply: Feb 17, 2014 12:02 PM by sb92075 RSS

Problem on ORA-12514

5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
Currently Being Moderated

Hi All..

i have installed orcle 11g realease 2 64 bit edition on my Window7 machine.

My service name is By default orcl.

I am unable to connect any user using the service name, i am getting the error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

 

SQL>

Enter user-name: system@orcl

Enter password:

ERROR:

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

 

Also due to this i am not able to connect with sqldeveloper.

  • 1. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    please help me.. to resolve this issue...

  • 2. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    ORA-12514 ALWAYS only occurs due to a problem on DB Server system.

    One cause of this problem is when the Oracle database is down & needs to be started.

    A remote client send a request to the Listener asking to be connected to a specific service.

    If/when the listener does not know anything about that service, the listener responds with ORA-12514

     

     

     

    post results from the following two OS commands:

     

    lsnrctl status

    lsnrctl service

     

     

     

    Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

     

     

     

    *This line contains valuable debugging details. So post this line & surrounding lines.*

     

     

     

    for additional debugging suggestions read the URL below:

     

    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

  • 3. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    On your instruction i executed these commands but unable to find listener.ora file

    The oputput of lsnrctl status and lsnrctl service are as follows:

    ********************************************************************************************************

    C:\Users>lsnrctl status

    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-FEB-2014 23:03:02

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    STATUS of the LISTENER

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

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

    Start Date                17-FEB-2014 11:49:57

    Uptime                    0 days 11 hr. 13 min. 9 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Parameter File   E:\Server\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

    Listener Log File         e:\server\oracle\diag\tnslsnr\Lappy\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

    ***************************************************************************************************

    C:\Users>lsnrctl service

    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-FEB-2014 23:05:35

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:0 refused:0

             LOCAL SERVER

    The command completed successfully

    ********************************************************************************************************************

  • 4. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    It does not appear that the database has been started

    open Command Window the do line for line EXACTLY as below

     

    sqlplus

    / as sysdba

    startup

    exit

     

    COPY the results from above then PASTE all back here

     

    results (below) clearly show where listener log file can be found!

    Listener Log File         e:\server\oracle\diag\tnslsnr\Lappy\listener\alert\log.xml

  • 5. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    Thanking u for your valuable responses in sort time :

    Data base is already started

    and the responce of the scrript as follows

     

    Enter user-name: / as sysdba

    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> startup

    ORA-01081: cannot start already-running ORACLE - shut it down first

  • 6. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    open Command Window & do as below

     

    lsnrctl stop

     

    cd  E:\Server\Oracle\product\11.2.0\dbhome_1\network\admin\

    ren listener.ora listener.ora.sav

    lsnrctl start

    #wait 60+ seconds before proceeding

    lsnrctl status

    lsnrctl service

     

    COPY the results from above then  PASTE  all back here


  • 7. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    After following the above steps

    the lsnrctl status and lsnrctl service result are

     

    E:\Server\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl start

     

     

    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-FEB-2014 00:24:48

     

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

     

    Starting tnslsnr: please wait...

     

     

    TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

    Log messages written to e:\server\oracle\diag\tnslsnr\Lappy\listener\alert\log.xml

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lappy)(PORT=1521)))

     

     

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    STATUS of the LISTENER

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

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

    Start Date                18-FEB-2014 00:24:54

    Uptime                    0 days 0 hr. 0 min. 5 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Log File         e:\server\oracle\diag\tnslsnr\Lappy\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lappy)(PORT=1521)))

    The listener supports no services

    The command completed successfully

     

     

    E:\Server\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl status

     

     

    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-FEB-2014 00:30:50

     

     

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

     

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    STATUS of the LISTENER

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

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

    Start Date                18-FEB-2014 00:24:54

    Uptime                    0 days 0 hr. 6 min. 0 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Log File         e:\server\oracle\diag\tnslsnr\Lappy\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lappy)(PORT=1521)))

    Services Summary...

    Service "orcl" has 1 instance(s).

      Instance "orcl", status READY, has 1 handler(s) for this service...

    Service "orclXDB" has 1 instance(s).

      Instance "orcl", status READY, has 1 handler(s) for this service...

    The command completed successfully

     

     

    E:\Server\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl service

     

     

    LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-FEB-2014 00:31:01

     

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

     

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    Services Summary...

    Service "orcl" has 1 instance(s).

      Instance "orcl", status READY, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:12 refused:0 state:ready

             LOCAL SERVER

    Service "orclXDB" has 1 instance(s).

      Instance "orcl", status READY, has 1 handler(s) for this service...

        Handler(s):

          "D000" established:0 refused:0 current:0 max:1022 state:ready

             DISPATCHER <machine: LAPPY, pid: 4676>

             (ADDRESS=(PROTOCOL=tcp)(HOST=lappy)(PORT=49262))

    The command completed successfully

     

     

    E:\Server\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN>

  • 8. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    please  notice that the listener now report DB instance "orcl" is now available to accept connection requests & previously listener did no know about any database.

  • 9. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    Thanks a lot.. for this issued i am hanging up for so many days.. and with your great effort i am able to solve it.. thanks a billion to you..

    One request to share some knowledge...

    1. I am wondering we renamed the listener.ora to listener.ora.sav , will it affect any other process or operation on the oracle database.

    2. I wanted to know what caused this problem.

     

    Waiting eagerly for your response.

  • 10. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    >1. I am wondering we renamed the listener.ora to listener.ora.sav , will it affect any other process or operation on the oracle database.


    the listener is NOT involved with any ongoing packet exchange between client & the database.

    you can stop the listener without impacting any existing connections into the database.

    when no listener exists (it has been stopped), no new remote connections can be made to the database.


    >2. I wanted to know what caused this problem.

     

    Problem Exists Between Keyboard And Chair

    "bad" content in listener.ora prevented the desired operation by the listener.

  • 11. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    Thanks a lot a lot boss..

  • 12. Re: Problem on ORA-12514
    5e42974a-8e87-41a8-8be3-8c45d2c0e866 Newbie
    Currently Being Moderated

    thanks a lot.. for your wonderful cooperation....

    no new remote connections can be made to the database.

    How can i be able to connect from remote location....

  • 13. Re: Problem on ORA-12514
    sb92075 Guru
    Currently Being Moderated

    5e42974a-8e87-41a8-8be3-8c45d2c0e866 wrote:

     

    thanks a lot.. for your wonderful cooperation....

    no new remote connections can be made to the database.

    How can i be able to connect from remote location....

    start the listener

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points