14 Replies Latest reply: Jan 12, 2013 12:07 PM by Arif Khadas RSS

    ORA-12514 & Listener Status Instance status Unknown issue

    Arif Khadas
      Dear All,

      Database Version - 10.2.0.5.0
      Operating System - Windows XP

      I am using Oracle 10g version, which has two services (bit10g and ora10g) installed on it.
      Service ora10g was working before upgradation of Database to 10.2.0.5.0 and it was fine.
      On upgradation I was unable to start and connect using ora10g so I created another that is bit10g.

      Service bit10g was working fine for quite a long time. The last I worked and connected was on thu and it was connecting with no issues.

      Now when I am trying to connect to database today I am unable to connect as I am getting
      ORA12514 - TNS:listener does not currently know of service.

      Secondly I tried to search forums which guided me to check the Listener Status.
      Microsoft Windows XP [Version 5.1.2600]
      (C) Copyright 1985-2001 Microsoft Corp.
      
      C:\Documents and Settings\Admin>lsnrctl status
      
      LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 12-JAN-2013 11:42
      :54
      
      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=toshiba-arif)(PORT=1521)
      ))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Produ
      ction
      Start Date                12-JAN-2013 11:18:12
      Uptime                    0 days 0 hr. 24 min. 43 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   C:\ora10g\product\10.2.0\db_1\network\admin\listener.o
      ra
      Listener Log File         C:\ora10g\product\10.2.0\db_1\network\log\listener.log
      
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=toshiba-arif)(PORT=1521)))
      Services Summary...
      Service "Oracle8" has 1 instance(s).
        Instance "bit10g", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      Following is my listener.ora file
      # listener.ora Network Configuration File: C:\ora10g\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
      # Generated by Oracle configuration tools.
      
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (GLOBAL_DBNAME = Oracle8)
            (SID_NAME = bit10g)
          )
        )
      
      LISTENER =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = toshiba-arif)(PORT = 1521))
        )
      Please note that I am a developer with no DBA exposure.
      So please guide me so that I will be able to resolve my issue.

      Thanks & Regards

      Arif Khadas
        • 1. Re: ORA-12514 & Listener Status Instance status Unknown issue
          Paul M.
          Try as follows :

          - stop the listener

          - rename listener.ora to something else

          - start the listener

          - wait up to a minute and retry "lsnrctl status" : result ?

          Also, what's the result of
          SQL> sho parameter service_names
          ?
          • 2. Re: ORA-12514 & Listener Status Instance status Unknown issue
            Arif Khadas
            Dear Paul,

            Thanks for your quick response.
            - stop the listener
            done
            - rename listener.ora to something else
            renamed it as listener_old.ora
            - start the listener
            started
            - wait up to a minute and retry "lsnrctl status" : result ?
            Microsoft Windows XP [Version 5.1.2600]
            (C) Copyright 1985-2001 Microsoft Corp.
            
            C:\Documents and Settings\Admin>lsnrctl status
            
            LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 12-JAN-2013 13:03
            :20
            
            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 32-bit Windows: Version 10.2.0.5.0 - Produ
            ction
            Start Date                12-JAN-2013 13:00:57
            Uptime                    0 days 0 hr. 2 min. 22 sec
            Trace Level               off
            Security                  ON: Local OS Authentication
            SNMP                      OFF
            Listener Log File         C:\ora10g\product\10.2.0\db_1\network\log\listener.log
            
            Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=toshiba-arif)(PORT=1521)))
            The listener supports no services
            The command completed successfully
            Also, what's the result of
            SQL> sho parameter service_names
            ?
            Now I am not able to start SQL so how can I check execute this command?

            Thanks.
            • 3. Re: ORA-12514 & Listener Status Instance status Unknown issue
              Paul M.
              Now I am not able to start SQL
              Do you get error(s) ? which one(s) ?
              • 4. Re: ORA-12514 & Listener Status Instance status Unknown issue
                Arif Khadas
                I tried this?

                C:\ora10g\product\10.2.0\db_1>sqlplus /nolog

                SQL*Plus: Release 9.0.1.3.0 - Production on Sat Jan 12 13:13:25 2013

                (c) Copyright 2001 Oracle Corporation. All rights reserved.

                SQL> sho parameter service_names
                SP2-0640: Not connected
                SQL> sho parameter service_names
                SP2-0640: Not connected
                • 5. Re: ORA-12514 & Listener Status Instance status Unknown issue
                  Paul M.
                  SQL*Plus: Release 9.0.1.3.0 - Production on Sat Jan 12 13:13:25 2013
                  You are connecting from a different Oracle Home : set the correct environment (ORACLE_HOME, PATH, ORACLE_SID), and retry.

                  Or, post tnsnames.ora from 9.0.1 Oracle Home.
                  • 6. Re: ORA-12514 & Listener Status Instance status Unknown issue
                    Arif Khadas
                    Once again Thanks Paul for your quick response.

                    I also have Oracle 9i installed on my machine but the service and Listener for 9i is stopped.

                    Following is the tnsnames.ora for 9i

                    # TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
                    # Generated by Oracle configuration tools.

                    BIT10G =
                    (DESCRIPTION =
                    (ADDRESS =
                    (PROTOCOL = TCP)
                    (HOST = toshiba-arif)
                    (PORT = 1521)
                    )
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = bit10g)
                    )
                    )

                    BIT_ORA9I =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = IAS)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = ORA9I)
                    )
                    )

                    ORA10G =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = toshiba-arif)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = ora10g)
                    )
                    )

                    ORA9I =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = toshiba-arif)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = ora9i)
                    )
                    )

                    INST1_HTTP =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = AH-IBM)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SERVER = SHARED)
                    (SERVICE_NAME = MODOSE)
                    (PRESENTATION = http://HRService)
                    )
                    )

                    CRCPROD =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.185)(PORT = 1521))
                    )
                    (CONNECT_DATA =
                    (SERVICE_NAME = bridges)
                    )
                    )

                    IAS_ORA10G =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = IAS)(PORT = 1522))
                    )
                    (CONNECT_DATA =
                    (SID = ORA10G)
                    )
                    )

                    EXTPROC_CONNECTION_DATA =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                    )
                    (CONNECT_DATA =
                    (SID = PLSExtProc)
                    (PRESENTATION = RO)
                    )
                    )

                    BIT_ORA10G =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = IAS)(PORT = 1522))
                    )
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = ORA10G)
                    )
                    )
                    • 7. Re: ORA-12514 & Listener Status Instance status Unknown issue
                      Arif Khadas
                      If I start the listener and service for 9i I am able to connect to the 9i database.

                      Also one point that I have noticed is that when I start the Service BIT10g manually, the service gets started in a very quick time,
                      where as before it used to take some more time which is much more then whats happening currently.
                      • 8. Re: ORA-12514 & Listener Status Instance status Unknown issue
                        Paul M.
                        We need to know if your 10g database is registered with the listener (or even if it is running...), so please set 10g Oracle Home, and execute
                        C:\>sqlplus / as sysdba
                        what do you get ?
                        • 9. Re: ORA-12514 & Listener Status Instance status Unknown issue
                          Arif Khadas
                          Thanks once again Mr. Paul for your generous replies.
                          C:\>set oracle_home=c:\ora10g\product\10.2.0\db_1
                          
                          C:\>sqlplus / as sysdba
                          Message 395 not found;  product=SQLPlus; facility=SP2
                          Message 396 not found;  product=SQLPlus; facility=SP2
                          Message 397 not found;  product=SQLPlus; facility=SP2
                          Message 398 not found;  product=SQLPlus; facility=SP2
                          Message 399 not found;  product=SQLPlus; facility=SP2
                          Message 400 not found;  product=SQLPlus; facility=SP2
                          Message 743 not found;  product=SQLPlus; facility=SP2
                          Message 402 not found;  product=SQLPlus; facility=SP2
                          Message 401 not found;  product=SQLPlus; facility=SP2
                          Hope this is what you wanted.
                          • 10. Re: ORA-12514 & Listener Status Instance status Unknown issue
                            Arif Khadas
                            I also tried the following:
                            C:\>set oracle_home=c:\ora10g\product\10.2.0\db_1\bin
                            
                            C:\>sqlplus / as sysdba
                            
                            C:\>sqlplus / as sysdba
                            But this did not give any output.
                            • 11. Re: ORA-12514 & Listener Status Instance status Unknown issue
                              Paul M.
                              C:\>set ORACLE_HOME=c:\ora10g\product\10.2.0\db_1

                              C:\>set PATH=%ORACLE_HOME%\bin;%PATH%

                              C:\>set ORACLE_SID=BIT10g (is this your SID ?)

                              C:\>sqlplus / as sysdba

                              Result ?
                              • 12. Re: ORA-12514 & Listener Status Instance status Unknown issue
                                Arif Khadas
                                I Appreciate your help immensely, sorry for the inconvenience caused.

                                [code\
                                C:\>SET ORACLE_HOME=C:\ORA10G\PRODUCT\10.2.0\DB_1

                                C:\>SET PATH=%ORACLE_HOME%\BIN;%PATH%

                                C:\>SET ORACLE_SID=BIT10G

                                C:\>sqlplus / AS SYSDBA

                                SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 12 14:37:39 2013

                                Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

                                ERROR:
                                ORA-28056: Writing audit records to Windows Event Log failed
                                OSD-1622087439: Message 1622087439 not found; product=RDBMS; facility=SOSD
                                O/S-Error: (OS 1502) The event log file is full.
                                ORA-28056: Writing audit records to Windows Event Log failed
                                OSD-1622087439: Message 1622087439 not found; product=RDBMS; facility=SOSD
                                O/S-Error: (OS 1502) The event log file is full.


                                Enter user-name: SA
                                Enter password:
                                ERROR:
                                ORA-01034: ORACLE not available
                                ORA-27101: shared memory realm does not exist


                                Enter user-name: SYSTEM
                                Enter password:
                                ERROR:
                                ORA-01034: ORACLE not available
                                ORA-27101: shared memory realm does not exist


                                SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus



                                Thanks once again for all your support.
                                • 13. Re: ORA-12514 & Listener Status Instance status Unknown issue
                                  Paul M.
                                  O/S-Error: (OS 1502) The event log file is full.
                                  This is your problem...
                                  • 14. Re: ORA-12514 & Listener Status Instance status Unknown issue
                                    Arif Khadas
                                    I cleared all the Event Logs and the problem was solved all thanks to your kind efforts.

                                    May GOD bless you with his special treasures of blessing always !

                                    Best Regards

                                    Arif Khadas