1 2 3 Previous Next 41 Replies Latest reply: Sep 17, 2013 7:42 AM by Nicolas.Gasparotto RSS

    Suddenly getting ORA-12514 error Oracle 11gr2

    c66e295b-e424-45df-a343-9e2219231da4

      I installed Oracle 11g r2 fine, had 2 databases (se3 and mydb, both have db_domain .orcl i.e. mydb.orcl and se3.orcl) running on it fine till yesterday. but suddenly im getting ORA-12514 error (TNS:listener doesn't know of service requested in connect descriptor)..

      My tnsnames.ora file:

      # tnsnames.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

      # Generated by Oracle configuration tools.

       

      LISTENER_MYDB =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

       

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      MYDB =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = mydb.orcl)

          )

        )

       

      SE3 =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = se3.orcl)

          )

        )

       

      LISTENER_SE3 =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

       

      and my listener.ora file is as follows:

      # listener.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\listener.ora

      # Generated by Oracle configuration tools.

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)

            (PROGRAM = extproc)

            (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          )

        )

       

      ADR_BASE_LISTENER = G:\oracledb

       

      * i have both of databases up and running(confirmed through administration assistant), the listener is up.. im able to execute "tnsping mydb/se3".. i'm not able to connect to either one of them through sqlplus/sql developer.. The result of "lsnrctl service" is as follows:

       

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

      TNS-12541: TNS:no listener

      TNS-12560: TNS:protocol adapter error

        TNS-00511: No listener

         32-bit Windows Error: 2: No such file or directory

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

      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

       

      PLEASE HELP ME FRIENDS!! I HAVE A PROJECT DUE IN EARLY OCTOBER

        • 1. Re: Suddenly getting ORA-12514 error Oracle 11gr2
          Asif Muhammad

          Hi,

           

          Instead of this:

          SID_LIST_LISTENER =

            (SID_LIST =

              (SID_DESC =

                (SID_NAME = CLRExtProc)

                (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)

                (PROGRAM = extproc)

                (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")

              )

            )

           

          LISTENER =

            (DESCRIPTION_LIST =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

              )

            )

          have the following in your listener file

          SID_LIST_LISTENER =

            (SID_LIST =

              (SID_DESC =

                (SID_NAME = CLRExtProc)

                (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)

                (PROGRAM = extproc)

                (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")

              )

             (SID_DESC =

                (SID_NAME = mydb)

                (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)

              )

              (SID_DESC =

                (SID_NAME = se3)

                (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)   

              )

            )

           

           

          LISTENER =

            (DESCRIPTION_LIST =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

              )

            )

           

          Also, dont have 'localhost' in your listener.ora file, provide the actual IP or hostanme. NEVER YOU should have

          localhost.

           

          Restart listener and try.

           

          Thanks &

          Best Regard,

          • 2. Re: Suddenly getting ORA-12514 error Oracle 11gr2
            Fran

            are you sure that your listener is up and running? use "lsnrctl status" again and post here the result

            please try  "lsnrctl start" and post if it works

            • 3. Re: Suddenly getting ORA-12514 error Oracle 11gr2
              Nicolas.Gasparotto

              >but suddenly im getting ORA-12514 error

              Suddenly something has probably happen onto your computer. But what ?

              >I HAVE A PROJECT DUE IN EARLY OCTOBER

              You have plenty of time then, 14 days to go at the least.

               

              Nicolas.

              • 4. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                Fran

                AsifMuhammad escribió:

                 

                Also, dont have 'localhost' in your listener.ora file, provide the actual IP or hostanme. NEVER YOU should have

                localhost.

                 

                Restart listener and try.

                 

                Thanks &

                Best Regard,

                why not if it is a test database in your own PC?

                • 5. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                  EdStevens

                  c66e295b-e424-45df-a343-9e2219231da4 wrote:

                   

                  I installed Oracle 11g r2 fine, had 2 databases (se3 and mydb, both have db_domain .orcl i.e. mydb.orcl and se3.orcl) running on it fine till yesterday. but suddenly im getting ORA-12514 error (TNS:listener doesn't know of service requested in connect descriptor)..

                  My tnsnames.ora file:

                  # tnsnames.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

                  # Generated by Oracle configuration tools.

                   

                  LISTENER_MYDB =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                   

                   

                  ORACLR_CONNECTION_DATA =

                    (DESCRIPTION =

                      (ADDRESS_LIST =

                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                      )

                      (CONNECT_DATA =

                        (SID = CLRExtProc)

                        (PRESENTATION = RO)

                      )

                    )

                   

                  MYDB =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                      (CONNECT_DATA =

                        (SERVER = DEDICATED)

                        (SERVICE_NAME = mydb.orcl)

                      )

                    )

                   

                  SE3 =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                      (CONNECT_DATA =

                        (SERVER = DEDICATED)

                        (SERVICE_NAME = se3.orcl)

                      )

                    )

                   

                  LISTENER_SE3 =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                   

                  and my listener.ora file is as follows:

                  # listener.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\listener.ora

                  # Generated by Oracle configuration tools.

                   

                  SID_LIST_LISTENER =

                    (SID_LIST =

                      (SID_DESC =

                        (SID_NAME = CLRExtProc)

                        (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)

                        (PROGRAM = extproc)

                        (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")

                      )

                    )

                   

                  LISTENER =

                    (DESCRIPTION_LIST =

                      (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                      )

                    )

                   

                  ADR_BASE_LISTENER = G:\oracledb

                   

                  * i have both of databases up and running(confirmed through administration assistant), the listener is up.. im able to execute "tnsping mydb/se3".. i'm not able to connect to either one of them through sqlplus/sql developer.. The result of "lsnrctl service" is as follows:

                   

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

                  TNS-12541: TNS:no listener

                  TNS-12560: TNS:protocol adapter error

                    TNS-00511: No listener

                     32-bit Windows Error: 2: No such file or directory

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                  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

                   

                  PLEASE HELP ME FRIENDS!! I HAVE A PROJECT DUE IN EARLY OCTOBER

                   

                   

                  see: http://edstevensdba.wordpress.com/2011/03/19/ora-12514/  (ora-12514  Listener does not know of requested service )

                  • 6. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                    gmartinca

                    Try to start the listener:

                    > lsnrctl start

                     

                    You can see the services with:

                    > lsnrctl services

                     

                     

                    If you want to discard any problem with tnsnames configuration you can do a tnsping:

                    > tnsping ORACLE_SID

                     

                    Also you can connect, via local, setting the ORACLE_SID and trying to connect without "@conn_name". The problem is that you need to change the ORACLE_SID if you want to connect to another database, and this don't solve your problem

                    • 7. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                      c66e295b-e424-45df-a343-9e2219231da4

                      Thanks for reply! i changed the file as you asked.. and still cant connect.. here's the result of "lsnrctl status"

                       

                      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...

                      Service "mydb" has 1 instance(s).

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

                      Service "se3" has 1 instance(s).

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

                      The command completed successfully...

                      • 8. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                        Nicolas.Gasparotto

                        > i changed the file as you asked.. and still cant connect

                        Well, if the issue came all of sudden, then I'm wondering why you change that file.

                         

                        Nicolas.

                        • 9. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                          sb92075

                          c66e295b-e424-45df-a343-9e2219231da4 wrote:

                           

                          Thanks for reply! i changed the file as you asked.. and still cant connect.. here's the result of "lsnrctl status"

                           

                          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...

                          Service "mydb" has 1 instance(s).

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

                          Service "se3" has 1 instance(s).

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

                          The command completed successfully...

                           

                          the SERVICE_NAME  in tnsnames.ora  must match what is reported by lsnrctl status above (no ".orcl")

                          • 10. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                            c66e295b-e424-45df-a343-9e2219231da4

                            I changed my "localhost" to home-0a9f9f6006 (i.e full computer name, WinXP) and also i set ORACLE_SID=se3 and also tried mydb.. but still get an error.. Here's the result of tnsping se3:

                             

                            Used parameter files:

                            G:\oracledb\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

                             

                             

                            Used TNSNAMES adapter to resolve the alias

                            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = home-0a9

                            f9f6006)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = se3.

                            orcl)))

                            TNS-12541: TNS:no listener

                             

                            NOTE: I did change the listener file as asked by bro "ASIF MUHAMMAD" and also localhost as i mentioned.. still no luck.. the databases are up and listener is running too..

                            • 11. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                              c66e295b-e424-45df-a343-9e2219231da4

                              I just changed the SERVICE_NAME from se3.orcl to se3 (also for mydb) but still it gives the same error..

                              Also if i write

                              "sqlplus / as sysdba" it gives the following errors..

                              ERROR:

                              ORA-28056: Writing audit records to Windows Event Log failed

                              ORA-01031: insufficient privileges

                               

                              I tried increasing the log size by clearing all system events, but no luck

                              • 12. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                                gmartinca

                                Did you restart the listener after the changes?

                                • 13. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                                  Nicolas.Gasparotto

                                  From an all of sudden error, you are going to make a lot of random change without really knowing what you are doing. Take a breath, a cup of water or whatever you like to keep it down. Think what was the change since last time it worked. Revert it back if needed.

                                  Hmmm, now you may be in bigger trouble than you were before all those (unnecessary) changes, right ?

                                   

                                  Nicolas.

                                  • 14. Re: Suddenly getting ORA-12514 error Oracle 11gr2
                                    c66e295b-e424-45df-a343-9e2219231da4

                                    Yep i first stopped it and then restarted it.. No Luck!

                                    BTW, why are the service instances status "UNKNOWN" ? are they not recognized by listener ?? i am sorry bro but am quite confused

                                    1 2 3 Previous Next