5 Replies Latest reply: Jan 22, 2013 12:52 PM by jgarry RSS

    once again ora 12514

    949210
      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
          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
            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
              when was last time listener worked without error?

              what changed since then?
              • 4. Re: once again ora 12514
                949210
                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
                  Stray character after the 4?