1 2 3 Previous Next 37 Replies Latest reply: Sep 27, 2012 2:20 AM by Mahmoud_Rabie Go to original post RSS
      • 15. Re: sqlplus hr/hr@orcl error
        EdStevens
        BenK wrote:
        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:
        Ok, so we know there is a listener at node6.node6-pc.com that knows about service 'orcl'

        What we DONT know for sure is that your client's request is asking for service 'orcl', nor do we know for sure that the request is being routed to node6.node6-pc.com.
        • 16. Re: sqlplus hr/hr@orcl error
          Osama_Mustafa
          but ed if you notice tnsping its working ???
          • 17. Re: sqlplus hr/hr@orcl error
            EdStevens
            Osama_mustafa wrote:
            but ed if you notice tnsping its working ???
            "[oracle@node6 dbs]$ tnsping orcl" is working.

            That is because he has an entry for 'orcl' in his tnsnames.

            Please review how the tns client resolves what comes after the '@' in the connect string.
            • 18. Re: sqlplus hr/hr@orcl error
              Osama_Mustafa
              Thank you
              • 19. Re: sqlplus hr/hr@orcl error
                BenK
                hai,

                still returns the same error !

                [oracle@node6 ~]$ sqlplus hr/hr@orcl.node6-pc.com

                SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 10:11:27 2012

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

                ERROR:
                ORA-12154: TNS:could not resolve the connect identifier specified


                Enter user-name:
                ERROR:
                ORA-01017: invalid username/password; logon denied
                • 20. Re: sqlplus hr/hr@orcl error
                  Aman....
                  Well, this is what you posted about the files,
                  >

                  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)
                  )
                  )>

                  So are you sure that you have the database on this IP,192.168.0.55? Is it possible for you to create a new TNS entry in the tnsnames.ora which would point to the same node which the listener is listening to?

                  Also, the service name that you have mentioned in the TNSNAMES.ora is orcl while the listener is listening for a service orcl.node6-pc.com as shown by your output,
                  >
                  [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>

                  So are you sure that you are entering the correct service name in the TNSNAMES.ora file?

                  Aman....
                  • 21. Re: sqlplus hr/hr@orcl error
                    Aman....
                    Osama_mustafa wrote:
                    but ed if you notice tnsping its working ???
                    Just to echo the same what Ed has said, TNSPING is nothing more than a ping to the listener to find the alias in the file. If that's there , tnsping would say ok but that doesn't guarantee that you can connect.

                    Aman....
                    • 22. Re: sqlplus hr/hr@orcl error
                      Osama_Mustafa
                      Post outoput for this :

                      sqlplus username/password@//hostname/ipaddres:PORT/SID

                      ex : sqlplus hr/hr@//node6.node6-pc.com:1521/orcl


                      if this not working remove ORCL entriy from your tnsnames and try again , or change ORCL connection name to ORCL2 connection and try
                      sqlplus hr/hr@orcl
                      • 23. Re: sqlplus hr/hr@orcl error
                        Aman....
                        Osama_mustafa wrote:
                        Post outoput for this :
                        if this not working remove ORCL entriy from your tnsnames and try again , or change ORCL connection name to ORCL2 connection and try
                        sqlplus hr/hr@orcl
                        What you are referring as ORCL2? Why to change ORCL to ORCL2 when its already clear that the error is meant for the service that is not known to the Listener?

                        Aman....
                        • 24. Re: sqlplus hr/hr@orcl error
                          Shivananda Rao
                          if this not working remove ORCL entriy from your tnsnames and try again , or change ORCL connection name to ORCL2 connection and try
                          sqlplus hr/hr@orcl
                          Could you please let us know how would this work ?

                          Changing the connection name from ORCL to ORCL2 really works ? And again you try to connect using "sqlplus hr/hr@orcl" after changing ORCL to ORCL2 !!!!
                          • 25. Re: sqlplus hr/hr@orcl error
                            Osama_Mustafa
                            Since he got the two entity in tnsnames.ora with same name ORCL
                            • 26. Re: sqlplus hr/hr@orcl error
                              Aman....
                              Osama_mustafa wrote:
                              Since he got the two entity in tnsnames.ora with same name ORCL
                              Osama,

                              This is what OP posted,
                              >
                              # 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)
                              )
                              )>

                              Can you show that which two ORCL entries in the tnsnames.ora you are referring to? One post is for listener.ora and another is for tnsnames.ora-separated by the comment
                              # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
                              # Generated by Oracle configuration tools.
                              And even for an instant, there are two entries, how changing one to ORCL2 would solve any purpose when you are suggesting still to connect to ORCL to OP ?

                              Aman....
                              Aman....
                              • 27. Re: sqlplus hr/hr@orcl error
                                Osama_Mustafa
                                Sorry Aman for this mistake i didn't notice that :)
                                • 28. Re: sqlplus hr/hr@orcl error
                                  BenK
                                  hai,

                                  i did what you said but still the same o/p below
                                  what is wrong with me?... i am totally desp

                                  [oracle@node6 ~]$ sqlplus sys/sys@orcl.node6-pc.com

                                  SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 10:10:54 2012

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

                                  ERROR:
                                  ORA-12154: TNS:could not resolve the connect identifier specified


                                  Enter user-name:
                                  ERROR:
                                  ORA-01017: invalid username/password; logon denied
                                  ----------------------------------------------------------------------------------------------------------------------------------------------------------
                                  i edit tnsnames.ora o/p is
                                  [oracle@node6 admin]$ cat tnsnames.ora
                                  # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
                                  # Generated by Oracle configuration tools.

                                  ORCL2 =
                                  (DESCRIPTION =
                                  (ADDRESS_LIST =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))
                                  )
                                  (CONNECT_DATA =
                                  (SERVICE_NAME = orcl2)
                                  )
                                  )

                                  [oracle@node6 admin]$
                                  -----------------------------------------------------------------------------------------------------------------------------------------
                                  [oracle@node6 admin]$ sqlplus hr/hr@orcl

                                  SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 11:59:57 2012

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

                                  ERROR:
                                  ORA-12154: TNS:could not resolve the connect identifier specified


                                  Enter user-name:
                                  ERROR:
                                  ORA-01017: invalid username/password; logon denied
                                  • 29. Re: sqlplus hr/hr@orcl error
                                    Aman....
                                    BenK wrote:
                                    hai,

                                    i did what you said but still the same o/p below
                                    what is wrong with me?... i am totally desp

                                    [oracle@node6 ~]$ sqlplus sys/sys@orcl.node6-pc.com

                                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 10:10:54 2012

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

                                    ERROR:
                                    ORA-12154: TNS:could not resolve the connect identifier specified


                                    Enter user-name:
                                    ERROR:
                                    ORA-01017: invalid username/password; logon denied
                                    ----------------------------------------------------------------------------------------------------------------------------------------------------------
                                    i edit tnsnames.ora o/p is
                                    [oracle@node6 admin]$ cat tnsnames.ora
                                    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
                                    # Generated by Oracle configuration tools.

                                    ORCL2 =
                                    (DESCRIPTION =
                                    (ADDRESS_LIST =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))
                                    )
                                    (CONNECT_DATA =
                                    (SERVICE_NAME = orcl2)
                                    )
                                    )

                                    [oracle@node6 admin]$
                                    -----------------------------------------------------------------------------------------------------------------------------------------
                                    [oracle@node6 admin]$ sqlplus hr/hr@orcl

                                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 11:59:57 2012

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

                                    ERROR:
                                    ORA-12154: TNS:could not resolve the connect identifier specified


                                    Enter user-name:
                                    ERROR:
                                    ORA-01017: invalid username/password; logon denied
                                    The change of ORCL to ORCL2 is a completely useless thing. You should see by yourself that are you having any service with the name ORCL2 listened by your listener. As per the output given by you, its not so this won't solve anything for you.

                                    Aman....