This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Sep 17, 2013 5:33 AM by sb92075 RSS

Suddenly getting ORA-12514 error Oracle 11gr2

c66e295b-e424-45df-a343-9e2219231da4 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    >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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Did you restart the listener after the changes?

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

    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 Newbie
    Currently Being Moderated

    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

Legend

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