This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Sep 27, 2012 12:20 AM by Mahmoud_Rabie RSS

sqlplus hr/hr@orcl error

BenK Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    post
    -lsnrctl status
    -listener.ora
    -tnsnames.ora
  • 2. Re: sqlplus hr/hr@orcl error
    Marco V. Expert
    Currently Being Moderated
    $ 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. Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    after doing that re post lsnrctl status please
  • 11. Re: sqlplus hr/hr@orcl error
    BenK Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    try to connect using
    sqlplus hr/hr@orcl.node6-pc.com
  • 13. Re: sqlplus hr/hr@orcl error
    EdStevens Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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