1 2 Previous Next 20 Replies Latest reply: Jan 26, 2008 4:36 PM by Hans Forbrich RSS

    ORA-12514: TNS:listener does not currently know of service requested in con

    614921
      hi,
      i have just installed oracle 10g in my linux 4 32 bit personal system. i can't conect to sqlplus;isqlplus amd EM.
      this is what's happening.
      [oracle@localhost ~]$ sqlplus /nolog
      SQL> connect sys/varun@orcl as sysdba
      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor
      sending u my tnsname.ora file
      # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.

      ORCL =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain.com)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )
        • 1. Re: ORA-12514: TNS:listener does not currently know of service requested in con
          400137
          How about your listener?
          Does ORCL service name is configured in your listener.ora file?

          Can you pass your listener.ora file or the output from:
          lsnrctl services listener name if no default one
          lsnrctl status listener name if no default one
          • 2. Re: ORA-12514: TNS:listener does not currently know of service requested in
            546612
            The basic networking process is:

            1) listener gets started on the service to listen on a specific port for a specific host name or IP address. This is configured in the $ORACLE_HOME/network/admin/listener.ora

            Note that hostname/IP and port are in that file. You change the hostname (as happens with DHCP) and the file is no longer valid.

            2) Database gets started and registers itself to the listener. Until the database registers itself, the listener does not currently know of the requested service

            If the database gets started before the listener, it may be necessary to force registration by using the DBA's 'ALTER SYSTEM REGISTER' command.

            The database uses the initialization parameter LOCAL_LISTENER to either point directly to the listener or to a TNSNAMES.ORA entry that points to the listener. (The default value for LOCAL_LISTENER is null which means 'local host on port 1521.)

            If the host name changes or the port changes, the database may not be able to 'find' the listener at which time, the listener does not currently know of the requested service

            3) The user then attempts to connect using the user's client. The user generally specifies an alias (to look up an entry into the client's TNSNAMES.ORA) or a connection description (which looks like an entry in TNSNAMES.ORA)

            The description lists a host, a port and a service. If the system can get to the host (via IP, via /etc/hosts name, or via DNS resilution) and find the listener listening on the port, it asks the listener for a connection to that service.

            If that service is registrered, all is well. Otherwise if the service is not registered, or an unregistered service is requested, the listener does not currently know of the requested service
            • 3. Re: ORA-12514: TNS:listener does not currently know of service requested in
              614921
              hi,
              i am not connecting to any remote database or so. its just my personal machine so there is no client or so.>> therefore i think listener.ora should not be used by oracle as it should use tnsname.ora. correct me if i am wrong.
              i am sending u listener.ora file
              # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
              # Generated by Oracle configuration tools.

              SID_LIST_LISTENER =
              (SID_LIST =
              (SID_DESC =
              (SID_NAME = PLSExtProc)
              (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
              (PROGRAM = extproc)
              )
              )

              LISTENER =
              (DESCRIPTION_LIST =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain.com)(PORT = 1521))
              )
              )

              >>
              also i like to tell u that somehow i logged into sqlplus:
              u can see how i did that:

              [oracle@localhost ~]$ sqlplus /nolog

              SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 12:56:10 2008

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

              SQL> connect sys as sysdba
              Enter password:
              Connected.
              SQL> startup mount
              ORA-01081: cannot start already-running ORACLE - shut it down first
              SQL> select name from v$database;

              NAME
              ---------
              ORCL
              although i had to create a new pfile from existing spfile.
              now when i connect to EM
              http://localhost.localdomain.com:1158/em

              i get " the connection was refusedwhen attempting to contact localhost.localdomain.com"
              • 4. Re: ORA-12514: TNS:listener does not currently know of service requested in
                546612
                hi,
                i am not connecting to any remote database or so. its
                just my personal machine so there is no client or
                so.>> therefore i think listener.ora should not be
                used by oracle as it should use tnsname.ora. correct
                me if i am wrong.
                You are wrong. There IS a client. sqlplus is a client. It uses client connectivity software - the networking stuff.

                If you specify a 'user/password@alias' the '@' tells it to go hunting for a resolution technique like TNSNAMES.ORA
                If you specify a 'user/password' (or just /) that tells it to go hunting for a bequeath adapter on the local machine.


                You are also wrong about the LISTENER.ORA and TNSNAMES.ORA ... the LISTENER.ORA tells the listener how to listen. The TNSNAMES.ORA tells the client how to get to the listener.


                Think about the above, and then go back to my post to think through the problem.
                • 5. Re: ORA-12514: TNS:listener does not currently know of service requested in
                  614921
                  also this is new to me
                  why i am getting ORA-01031: insufficient privileges other way ;
                  is there a difference b/w
                  1) Enter user-name: sys/varun@orcl as sysdba
                  and 2)Enter user-name: sys as sysdba
                  Enter password:



                  [oracle@localhost ~]$ sqlplus

                  SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 13:14:49 2008

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

                  Enter user-name: sys
                  Enter password:
                  ERROR:
                  ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

                  >>>>
                  Enter user-name: sys/varun@orcl as sysdba
                  ERROR:
                  ORA-01031: insufficient privileges


                  Enter user-name: sys as sysdba
                  Enter password:

                  Connected to:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                  With the Partitioning, OLAP and Data Mining options
                  • 6. Re: ORA-12514: TNS:listener does not currently know of service requested in
                    400137
                    Varun,

                    Read what Hans wrote in his message.

                    It seems like your database is not registered with the listener.

                    Run the command below to see if your db has been registered with the listener.
                    lsnrctl services
                    I think the problem is in your TNSNAMES.ORA file.
                    You used the database SID name as a SERVICE NAME in the TNS alias.

                    Check the database parameter SERVICE_NAMES to see what is the service name of your database.
                    SQL>show parameter service_names
                    then correct your tnsnames.ora file

                    Message was edited by:
                    tekicora
                    • 7. Re: ORA-12514: TNS:listener does not currently know of service requested in
                      614921
                      yes i also think that >>my tnsname.ora file is not configured properly. but as far as adding info to tnsname.ora file is >>out of scope fr me as i am new to it. can u help me out with it. my tnsname.ora file is also posted in the same thread. >>can u help me edit the tnsname.ora file
                      • 8. Re: ORA-12514: TNS:listener does not currently know of service requested in
                        400137
                        Please provide the value of your database SERVICE_NAMES parameter.
                        $sqlplus "/as sysdba"
                        Connected.
                        SQL>show parameter service_names
                        • 9. Re: ORA-12514: TNS:listener does not currently know of service requested in
                          614921
                          sorry buddy
                          no again i can't connect to database;
                          i m getting
                          [oracle@localhost ~]$ sqlplus /nolog
                          SQL> connect sys as sysdba
                          Enter password:
                          ERROR:
                          ORA-12162: TNS:net service name is incorrectly specified
                          SQL> connect sys/varun@orcl as sysdba
                          ERROR:
                          ORA-01031: insufficient privileges
                          • 10. Re: ORA-12514: TNS:listener does not currently know of service requested in
                            92686
                            sorry buddy
                            no again i can't connect to database;

                            SQL> connect sys/varun@orcl as sysdba
                            ERROR:
                            ORA-01031: insufficient privileges
                            Try 'connect system/<password>@orcl instead. You'll still be able to view the requested information as system.
                            • 11. Re: ORA-12514: TNS:listener does not currently know of service requested in
                              546612
                              What you should at this time is to post:

                              1) the tnsnames.ora,
                              2) the listener.ora
                              3) the output from the command line 'lsnrctl status'
                              4) the output from the following usinig SQLPlus as a DBA

                              show parameter name
                              show parameter listener

                              And make sure you enclose any outputs in 'preserve' blocks by using a [ pre ] and a [\ pre ] the around the output - note that for it to work you do not put spaces around 'pre'

                              Message was edited by:
                              Hans Forbrich (alt ID)


                              To connect as a DBA, use

                              connect system/oracle
                              connect / as sysdba

                              Do NOT use 'connect sys' unless you want to use a password. Do NOT use 'connect x/y@alias' unless you want to force using tnsnames.ora
                              • 12. Re: ORA-12514: TNS:listener does not currently know of service requested in
                                400137
                                THIS IS INCORECT
                                [oracle@localhost ~]$ sqlplus /nolog
                                SQL> connect sys as sysdba
                                Enter password:
                                ERROR:
                                ORA-12162: TNS:net service name is incorrectly
                                specified
                                TRY WITH THIS ONE
                                SQL> conn /as sysdba
                                • 13. Re: ORA-12514: TNS:listener does not currently know of service requested in
                                  367117
                                  What are the output for these ?

                                  $ cat /etc/hosts
                                  $ ps -ef | grep lsnr
                                  $ lsnrctl status
                                  $ tnsping orcl

                                  You may have to modify your LISTENER.ORA.... save the original one so that you can get back to it... if required....

                                  SID_LIST_LISTENER =
                                  (SID_LIST =
                                  (SID_DESC =
                                  (SID_NAME = PLSExtProc)
                                  (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
                                  (PROGRAM = extproc)
                                  )
                                  (SID_DESC =
                                  (SID_NAME = ORCL)
                                  (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
                                  )

                                  )

                                  LISTENER =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain.com)(PORT = 1521))
                                  )
                                  )

                                  If you modify your listener... you will have to reload / stop and start...

                                  PS : Always backup the files before modification so that you can reference / fall back, if required.
                                  • 14. Re: ORA-12514: TNS:listener does not currently know of service requested in
                                    614921
                                    tnsname.ora and listener.ora files are already been sent
                                    >>
                                    SQL> show parameter service_names

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    service_names

                                    >>>>>>

                                    [oracle@localhost ~]$ cat /etc/hosts
                                    # Do not remove the following line, or various programs
                                    # that require network functionality will fail.
                                    127.0.0.1 localhost.localdomain.com localhost localhost.localdomain localhost

                                    >>>>>>>>>
                                    [oracle@localhost ~]$ ps -ef | grep lsnr
                                    oracle 3433 1 0 12:08 ? 00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
                                    oracle 6951 6612 0 14:16 pts/1 00:00:00 grep lsnr

                                    >>>>>>>

                                    [oracle@localhost ~]$ lsnrctl status

                                    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2008 14:17:07

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

                                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                                    STATUS of the LISTENER
                                    ------------------------
                                    Alias LISTENER
                                    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                                    Start Date 21-JAN-2008 12:08:00
                                    Uptime 0 days 2 hr. 9 min. 7 sec
                                    Trace Level off
                                    Security ON: Local OS Authentication
                                    SNMP OFF
                                    Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
                                    Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
                                    Listening Endpoints Summary...
                                    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
                                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain.com)(PORT=1521)))
                                    Services Summary...
                                    Service "ORCL" has 2 instance(s).
                                    Instance "ORCL", status READY, has 1 handler(s) for this service...
                                    Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
                                    Service "PLSExtProc" has 1 instance(s).
                                    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                                    Service "orclXDB" has 1 instance(s).
                                    Instance "ORCL", status READY, has 1 handler(s) for this service...
                                    Service "orcl_XPT" has 2 instance(s).
                                    Instance "ORCL", status READY, has 1 handler(s) for this service...
                                    Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
                                    The command completed successfully
                                    >>>>>>>>>>>>>
                                    [oracle@localhost ~]$ tnsping orcl

                                    TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-JAN-2008 14:17:25

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

                                    Used parameter files:
                                    /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


                                    Used TNSNAMES adapter to resolve the alias
                                    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
                                    OK (10 msec)
                                    1 2 Previous Next