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

    Problem on ORA-12514

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

      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

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

          • 2. Re: Problem on ORA-12514
            sb92075

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            >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

                              Thanks a lot a lot boss..

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

                                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

                                  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