1 2 3 Previous Next 37 Replies Latest reply: Sep 27, 2012 2:20 AM by Mahmoud_Rabie RSS

    sqlplus hr/hr@orcl error

    BenK
      hai,
      i can connect using sqlplus hr/hr i get sql prompt but sqlplus hr/hr@orcl, i din't get the connection, error is ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor . i installed oracle 11g using software only option and restore the clone.but i install oracle with oracle orcl database option i get the connection.sqlplus hr/hr@orcl its working why?please help me
        • 1. Re: sqlplus hr/hr@orcl error
          Osama_Mustafa
          post
          -lsnrctl status
          -listener.ora
          -tnsnames.ora
          • 2. Re: sqlplus hr/hr@orcl error
            Marco V.
            $ oerr ora 12514
            12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
            // *Cause:  The listener received a request to establish a connection to a
            // database or other service. The connect descriptor received by the listener
            // specified a service name for a service (usually a database service)
            // that either has not yet dynamically registered with the listener or has
            // not been statically configured for the listener. This may be a temporary
            // condition such as after the listener has started, but before the database
            // instance has registered with the listener.
            // *Action:
            // - Wait a moment and try to connect a second time.
            // - Check which services are currently known by the listener by executing:
            // lsnrctl services <listener name>
            // - Check that the SERVICE_NAME parameter in the connect descriptor of the
            // net service name used specifies a service known by the listener.
            // - If an easy connect naming connect identifier was used, check that
            // the service name specified is a service known by the listener.
            // - Check for an event in the listener.log file.
            • 3. Re: sqlplus hr/hr@orcl error
              Marco V.
              Have a look also at this note:
              TroubleShooting Guide For ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor [ID 444705.1]
              • 4. Re: sqlplus hr/hr@orcl error
                Helios-GunesEROL
                Hi;

                1. What is OS?
                2. What is Db version
                3. What tnsping orcl return?

                Regard
                Helios
                • 5. Re: sqlplus hr/hr@orcl error
                  BenK
                  sir,

                  These are my afore said details

                  SQL> host lsnrctl status

                  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2012 16:46:02

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                  Start Date 25-SEP-2012 16:45:35
                  Uptime 0 days 0 hr. 0 min. 27 sec
                  Trace Level off
                  Security ON: Local OS Authentication
                  SNMP OFF
                  Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                  Listener Log File /u01/app/oracle/diag/tnslsnr/node6/listener/alert/log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node6.node6-pc.com)(PORT=1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                  The listener supports no services
                  The command completed successfully

                  # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                  # Generated by Oracle configuration tools.

                  LISTENER =
                  (DESCRIPTION_LIST =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                  (ADDRESS = (PROTOCOL = TCP)(HOST = node6.node6-pc.com)(PORT = 1521))
                  (ORACLE_SID = orcl)
                  (GLOBAL_NAME = orcl)
                  )
                  )

                  orcl =
                  (DESCRIPTION =
                  # (ADDRESS = (PROTOCOL = TCP)(HOST = dw9)(PORT = 1521))
                  (ADDRESS = (PROTOCOL = TCP)(HOST = node6.node6-pc.com)(PORT = 1521))
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = orcl)
                  )
                  )

                  ADR_BASE_LISTENER = /u01/app/oracle


                  # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
                  # Generated by Oracle configuration tools.

                  ORCL =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SERVICE_NAME = orcl)
                  )
                  )
                  • 6. Re: sqlplus hr/hr@orcl error
                    BenK
                    hai,
                    os - ORACLE ENTERPRISE LINUX 5.5

                    SQL> select * from v$version;

                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    PL/SQL Release 11.2.0.1.0 - Production
                    CORE 11.2.0.1.0 Production
                    TNS for Linux: Version 11.2.0.1.0 - Production
                    NLSRTL Version 11.2.0.1.0 - Production

                    [oracle@node6 dbs]$ tnsping orcl

                    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2012 16:57:00

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

                    Used parameter files:
                    /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


                    Used TNSNAMES adapter to resolve the alias
                    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
                    OK (0 msec)
                    • 7. Re: sqlplus hr/hr@orcl error
                      Osama_Mustafa
                      there's no services register with your listener

                      And you didn't post OS version

                      Windows :
                      Set ORACLE_SID=orcl
                      Sqlplus / as sysdba
                      alter system register ;

                      Linux :
                      export ORACLE_SID=orcl
                      sqlplus / as sysdba
                      alter system register ;
                      • 8. Re: sqlplus hr/hr@orcl error
                        BenK
                        hai,
                        my os version is
                        [oracle@node6 ~]$ cat /etc/redhat-release
                        Red Hat Enterprise Linux Server release 5.5 (Tikanga)

                        [oracle@node6 ~]$ uname -a
                        Linux node6.node6-pc.com 2.6.18-194.el5xen #1 SMP Mon Mar 29 22:22:00 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

                        i did export ORACLE_SID=orcl

                        [oracle@node6 ~]$ sqlplus / as sysdba

                        SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 17:17:40 2012

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


                        Connected to:
                        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options

                        SQL> alter system register;

                        System altered.

                        SQL> exit
                        Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options
                        [oracle@node6 ~]$ sqlplus hr/hr

                        SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 17:18:05 2012

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


                        Connected to:
                        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options

                        SQL> exit
                        Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options
                        [oracle@node6 ~]$ sqlplus hr/hr@orcl

                        SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 17:18:12 2012

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

                        ERROR:
                        ORA-12514: TNS:listener does not currently know of service requested in connect
                        descriptor


                        Enter user-name:
                        • 9. Re: sqlplus hr/hr@orcl error
                          EdStevens
                          BenK wrote:
                          hai,
                          i can connect using sqlplus hr/hr i get sql prompt but sqlplus hr/hr@orcl, i din't get the connection, error is ORA-12514: TNS:listener does not currently know of service requested in connect
                          descriptor . i installed oracle 11g using software only option and restore the clone.but i install oracle with oracle orcl database option i get the connection.sqlplus hr/hr@orcl its working why?please help me
                          see http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
                          • 10. Re: sqlplus hr/hr@orcl error
                            Osama_Mustafa
                            after doing that re post lsnrctl status please
                            • 11. Re: sqlplus hr/hr@orcl error
                              BenK
                              hai,

                              [oracle@node6 ~]$ lsnrctl status

                              LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2012 17:52:15

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

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                              STATUS of the LISTENER
                              ------------------------
                              Alias LISTENER
                              Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                              Start Date 25-SEP-2012 16:45:35
                              Uptime 0 days 1 hr. 6 min. 40 sec
                              Trace Level off
                              Security ON: Local OS Authentication
                              SNMP OFF
                              Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                              Listener Log File /u01/app/oracle/diag/tnslsnr/node6/listener/alert/log.xml
                              Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node6.node6-pc.com)(PORT=1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                              Services Summary...
                              Service "orcl.node6-pc.com" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                              Service "orclXDB.node6-pc.com" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                              The command completed successfully
                              [oracle@node6 ~]$
                              [oracle@node6 ~]$
                              [oracle@node6 ~]$
                              [oracle@node6 ~]$ sqlplus sys/sys@orcl as sysdba

                              SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 17:52:40 2012

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

                              ERROR:
                              ORA-12514: TNS:listener does not currently know of service requested in connect
                              descriptor


                              Enter user-name:
                              • 12. Re: sqlplus hr/hr@orcl error
                                Osama_Mustafa
                                try to connect using
                                sqlplus hr/hr@orcl.node6-pc.com
                                • 13. Re: sqlplus hr/hr@orcl error
                                  EdStevens
                                  BenK wrote:
                                  sir,

                                  These are my afore said details

                                  SQL> host lsnrctl status

                                  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2012 16:46:02

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

                                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                                  STATUS of the LISTENER
                                  ------------------------
                                  Alias LISTENER
                                  Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                                  Start Date 25-SEP-2012 16:45:35
                                  Uptime 0 days 0 hr. 0 min. 27 sec
                                  Trace Level off
                                  Security ON: Local OS Authentication
                                  SNMP OFF
                                  Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                                  Listener Log File /u01/app/oracle/diag/tnslsnr/node6/listener/alert/log.xml
                                  Listening Endpoints Summary...
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node6.node6-pc.com)(PORT=1521))(ORACLE_SID=orcl)(GLOBAL_NAME=orcl))
                                  The listener supports no services
                                  The command completed successfully

                                  # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
                                  # Generated by Oracle configuration tools.

                                  LISTENER =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = node6.node6-pc.com)(PORT = 1521))
                                  (ORACLE_SID = orcl)
                                  (GLOBAL_NAME = orcl)
                                  )
                                  )

                                  orcl =
                                  (DESCRIPTION =
                                  # (ADDRESS = (PROTOCOL = TCP)(HOST = dw9)(PORT = 1521))
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = node6.node6-pc.com)(PORT = 1521))
                                  (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = orcl)
                                  )
                                  )

                                  ADR_BASE_LISTENER = /u01/app/oracle
                                  the above looks like a seriously fubar listner.ora. What's with the 'orcl' section? What's with the ORACLE_SID and GLOBAL_NAME in the LISTENER section? How did you create this mess? I'd delete it and either 1) recreate it using netca or 2) just do without and let the listener start with all default values.

                                  >
                                  # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
                                  # Generated by Oracle configuration tools.

                                  ORCL =
                                  (DESCRIPTION =
                                  (ADDRESS_LIST =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))
                                  )
                                  (CONNECT_DATA =
                                  (SERVICE_NAME = orcl)
                                  )
                                  )
                                  Is 192.168.0.55 the ip address of the machine running your database?

                                  Edited by: EdStevens on Sep 25, 2012 7:30 AM
                                  • 14. Re: sqlplus hr/hr@orcl error
                                    EdStevens
                                    Osama_mustafa wrote:
                                    try to connect using
                                    sqlplus hr/hr@orcl.node6-pc.com
                                    unless he has an entry in tnsnames as orcl.node6-pc.com, all that will do is change his error from a 12514 to a 12154
                                    1 2 3 Previous Next