This discussion is archived
5 Replies Latest reply: Jan 22, 2013 10:52 AM by jgarry RSS

once again ora 12514

949210 Newbie
Currently Being Moderated
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 22 22:28:13 2013

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
I am referring [url https://forums.oracle.com/forums/thread.jspa?threadID=2318993&start=0&tstart=0]this link
and the links within the above link,( Ed Stevens link )
tnsping orcl works
sqlplus "/as sysdba" works
ora 12514 when i try to test the string with net manager
sql developer on the other hand shows ora 12505...

my tnsnames file
ORCL_2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15021))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15021))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
my sqlnet file
SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
my listener file
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = G:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 15021))
  )
also c:\app\dfernandes\diag\tnslsnr\sedona\listener\alert\log.xml is not there in my system
i have
fastCopyLog.xml
installIP1Log.xml
they are all modified in the year 2012
the issue has cropped up after i bought an internet connection.. all was well before this
i have tried a few solutions in vain
this is for my personal learning purposes
any help wil be a lot helpful
thanks

Edited by: 946207 on Jan 22, 2013 10:51 PM
  • 1. Re: once again 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/
  • 2. Re: once again ora 12514
    949210 Newbie
    Currently Being Moderated
    thank you for your prompt reply
    here is what you asked for
    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JAN-2013 22:13:50
    
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    
    Welcome to LSNRCTL, type "help" for information.
    
    LSNRCTL> start
    Starting tnslsnr: please wait...
    
    Service OracleOraDb10g_home1TNSListener already running.
    TNS-12560: TNS:protocol adapter error
     TNS-00530: Protocol adapter error
    
    LSNRCTL> stop
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=15021)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       32-bit Windows Error: 61: Unknown error
    LSNRCTL> start
    Starting tnslsnr: please wait...
    
    Service OracleOraDb10g_home1TNSListener already running.
    TNS-12560: TNS:protocol adapter error
     TNS-00530: Protocol adapter error
    
    LSNRCTL> exit
    and
    Welcome to LSNRCTL, type "help" for information.
    
    LSNRCTL> status
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=15021)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       32-bit Windows Error: 61: Unknown error
    LSNRCTL>
    and
    LSNRCTL> service
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=15021)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       32-bit Windows Error: 61: Unknown error
    LSNRCTL>
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> select open_mode from v$database;
    
    OPEN_MODE
    ----------
    READ WRITE
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    C:\Documents and Settings\Administrator.W-8947E10749A84>tnsping orcl
    
    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JAN-2013 23:08:23
    
    Copyright (c) 1997, 2005, Oracle.  All rights reserved.
    
    Used parameter files:
    G:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15021))) (CONNECT_DATA
     = orcl)))
    OK (30 msec)
    a cut out of listener.log file


    17-JAN-2013 04:31:46 * service_update * orcl * 0
    17-JAN-2013 04:55:07 * service_update * orcl * 0
    17-JAN-2013 04:58:05 * service_died * orcl * 12547
    TNS-12547: TNS:lost contact

    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-JAN-2013 14:12:22

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

    System parameter file is G:\oracle\product\10.2.0\db_1\network\admin\listener.ora
    Log messages written to G:\oracle\product\10.2.0\db_1\network\log\listener.log
    Trace information written to G:\oracle\product\10.2.0\db_1\network\trace\listener.trc
    Trace level is currently 0

    Started with pid=1920
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=w-8947e10749a84)(PORT=15021)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    17-JAN-2013 14:13:53 * service_register * orcl * 0
    17-JAN-2013 14:18:01 * service_update * orcl * 0
    17-JAN-2013 14:18:09 * service_died * orcl * 12547
    TNS-12547: TNS:lost contact

    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-JAN-2013 21:49:10

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

    System parameter file is G:\oracle\product\10.2.0\db_1\network\admin\listener.ora
    Log messages written to G:\oracle\product\10.2.0\db_1\network\log\listener.log
    Trace information written to G:\oracle\product\10.2.0\db_1\network\trace\listener.trc
    Trace level is currently 0

    Started with pid=272
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15021)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    21-JAN-2013 21:49:22 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1737)) * establish * orcl * 12505
    TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
    21-JAN-2013 21:57:22 * (CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER=DEDICATED)(CID=(PROGRAM=G:\oracle\product\10.2.0\db_1\jdk\jre\bin\java.exe)(HOST=W-8947E10749A84)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1876)) * establish * orcl * 12514
    TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-JAN-2013 22:13:24

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

    System parameter file is G:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
    Log messages written to G:\oracle\product\10.2.0\db_1\network\log\listener.log
    Trace information written to G:\oracle\product\10.2.0\db_1\network\trace\listener.trc
    Trace level is currently 0

    Started with pid=3684
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15021)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    21-JAN-2013 22:13:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    21-JAN-2013 22:06:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    21-JAN-2013 22:28:13 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=4671)) * establish * orcl * 12505
    TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JAN-2013 21:44:07

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

    System parameter file is G:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
    Log messages written to G:\oracle\product\10.2.0\db_1\network\log\listener.log
    Trace information written to G:\oracle\product\10.2.0\db_1\network\trace\listener.trc
    Trace level is currently 0

    Started with pid=2260
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15021)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    22-JAN-2013 21:44:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15021)))
    Listener completed notification to CRS on stop
    22-JAN-2013 21:44:22 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0

    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JAN-2013 21:44:33

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

    System parameter file is G:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
    Log messages written to G:\oracle\product\10.2.0\db_1\network\log\listener.log
    Trace information written to G:\oracle\product\10.2.0\db_1\network\trace\listener.trc
    Trace level is currently 0

    Started with pid=4068
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15021)))
    Listener completed notification to CRS on start

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    22-JAN-2013 21:44:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    22-JAN-2013 21:44:50 * (CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER=DEDICATED)(CID=(PROGRAM=G:\oracle\product\10.2.0\db_1\jdk\jre\bin\java.exe)(HOST=W-8947E10749A84)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1294)) * establish * orcl * 12514
    TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
    22-JAN-2013 21:50:27 * (CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER=DEDICATED)(CID=(PROGRAM=G:\oracle\product\10.2.0\db_1\jdk\jre\bin\java.exe)(HOST=W-8947E10749A84)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15

    Edited last by: 946207 on Jan 22, 2013 11:49 PM to delete the unneccesary portion
  • 3. Re: once again ora 12514
    sb92075 Guru
    Currently Being Moderated
    when was last time listener worked without error?

    what changed since then?
  • 4. Re: once again ora 12514
    949210 Newbie
    Currently Being Moderated
    what i changed
    LISTENER =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = w-8947e10749a84.)(PORT = 15021))
    )

    got this name as computer name from My computer properties
    and in the net manager changed the host name to
    w-8947e10749a84
    it is working now
    Error 6 initializing SQL*Plus
    Message file sp1<lang>.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
    was what i got in the interim ...when i set ORACLE_HOME environment variable, i have unset that again
    thank you

    Edited by: 946207 on Jan 22, 2013 11:47 PM
  • 5. Re: once again ora 12514
    jgarry Guru
    Currently Being Moderated
    Stray character after the 4?

Legend

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