1 2 Previous Next 16 Replies Latest reply on Dec 21, 2012 3:51 AM by Salman Qureshi

    Get ORA-12560 error while accessing 11g database

    765115
      Database version: 11.2.0.1.0

      This is a development database running on CentOS. We had setup this database recently and everything has been fine. Today I noticed that when I connect to the database from remote, I get the following error -

      ORA-12560: TNS:protocol adapter error

      I'm able to get in to the database after logging in directly into the database server via putty. I have stopped & restarted the database, but with no help. I have checked the environment variables (ORACLE_HOME, ORACLE_SID) and they seem to be fine.

      Can you please help on what might be the problem here? Is this something to do with some change in the OS firewall setting?

      Edited by: museshad on Dec 20, 2012 12:44 PM
        • 1. Re: Get ORA-12560 error while accessing 11g database
          Salman Qureshi
          Have you created a TNS entry on your remote machine? Please paste here your TNSNAMES.ORA file from remote machine.

          Also paste output of following statement from your database server.
          lsnrctl status
          Salman
          • 2. Re: Get ORA-12560 error while accessing 11g database
            JMOHIT
            Put your firewall as off or give exception of port no. in your firewall.

            after check your connection string in tnsnames.ora file.
            • 3. Re: Get ORA-12560 error while accessing 11g database
              765115
              lsnrctl status
              LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-DEC-2012 13:27:55
              
              Copyright (c) 1991, 2009, Oracle.  All rights reserved.
              
              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
              Start Date                27-AUG-2012 16:50:42
              Uptime                    114 days 20 hr. 37 min. 13 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
              Listener Log File         /u01/app/oracle/diag/tnslsnr/xxxx/listener/alert/log.xml
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
              Services Summary...
              Service "orcl.ENTERPRISENET.ORG" has 1 instance(s).
                Instance "orcl", status READY, has 1 handler(s) for this service...
              Service "orclXDB.ENTERPRISENET.ORG" has 1 instance(s).
                Instance "orcl", status READY, has 1 handler(s) for this service...
              The command completed successfully
              listener.ora
              LISTENER =
                (DESCRIPTION_LIST =
                  (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
                  )
                )
              
              ADR_BASE_LISTENER = /u01/app/oracle
              tnsnames.ora
              ORCL =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = orcl.ENTERPRISENET.ORG)
                  )
                )
              • 4. Re: Get ORA-12560 error while accessing 11g database
                Salman Qureshi
                localhost.localdomain
                Change this value for host name in both your listener.ora file and also in your tnsnames.ora file. put here you database server hostname.
                After editing your listener.ora file, reload listener
                lsnrctl reload 
                Salman
                • 5. Re: Get ORA-12560 error while accessing 11g database
                  765115
                  Modified tnsnames.ora & listener.ora with the server hostname and reloaded listener. I'm still getting the same error.
                  • 6. Re: Get ORA-12560 error while accessing 11g database
                    Salman Qureshi
                    Hi,
                    issue OS ping command from remote machine to database server and paste here result
                    issue TNSPING command "TNSPING orcl" from remote machint and paster here the result.
                    Is there any anti virus or firewall installed/enabled on your this remote machine?

                    Salman
                    • 7. Re: Get ORA-12560 error while accessing 11g database
                      765115
                      I have used the database server's IP address instead of the hostname in the listener.ora & tnsnames.ora. I hope this is not an issue.

                      On the firewall, I need to check this with my infrastructure team, but first want to make sure that I'm not missing any configuration from the Oracle side.

                      tnsping orcl
                      TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-DEC-2012 14:12:45
                      
                      Copyright (c) 1997, 2005, Oracle.  All rights reserved.
                      
                      Used parameter files:
                      C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
                      
                      TNS-03505: Failed to resolve name
                      ping xxxx (IP of database server)
                      C:\Documents and Settings\....>ping xxxx
                      
                      Pinging xxxx with 32 bytes of data:
                      
                      Reply from xxxx: bytes=32 time=3ms TTL=64
                      Reply from xxxx: bytes=32 time=71ms TTL=64
                      Reply from xxxx: bytes=32 time=1ms TTL=64
                      Reply from xxxx: bytes=32 time=1ms TTL=64
                      
                      Ping statistics for xxx:
                          Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
                      Approximate round trip times in milli-seconds:
                          Minimum = 1ms, Maximum = 71ms, Average = 19ms
                      • 8. Re: Get ORA-12560 error while accessing 11g database
                        JMOHIT
                        TNS-03505: Failed to resolve name

                        Create a service name (orcl) at client side using netca or netmgr.
                        • 9. Re: Get ORA-12560 error while accessing 11g database
                          Salman Qureshi
                          I have used the database server's IP address instead of the hostname in the listener.ora & tnsnames.ora. I hope this is not an issue.
                          You are correct, it is not a problem.

                          Is there a single oracle installation on your remote machine? Are you sure that TNSPING/SQLPLUS is executed from the same oracle home where you have created your TNSNAMES.ORA file?

                          Now you should try to check your antivirus and firewall settings.

                          Salman
                          • 10. Re: Get ORA-12560 error while accessing 11g database
                            Osama_Mustafa
                            Refer to MOS
                            Environment Variable 'Network' Causes TNS-12154 and TNS-3505 [ID 276476.1]
                            • 11. Re: Get ORA-12560 error while accessing 11g database
                              765115
                              Salman,

                              Thanks for all your suggestions. I would like to clarify a few things.

                              The database server running on Linux has a single Oracle 11g installation and hence a single ORACLE_HOME. This is not a RAC. When you say "remote machine", I guess you are referring to the remote client (a Windows desktop in my case) from where I'm trying to access the database (using SQL Tools editor). The PING & TNSPING results shown above were executed from the Windows desktop. Changes to tnsnames.ora & listener.ora (to include the IP address instead of localhost) and listener reload were all done in the linux database server.

                              I'm able to connect to the database via SQLPLUS after logging in directly into the linux server.

                              I have also included an entry for orcl in the tnsnames.ora file in my Windows desktop. I guess this is not needed when I'm accessing the database from an editor like SQL Tools.

                              I hope this explains my environment and what I'm trying to do better. Please let me know if you might have any additional suggestions.
                              • 12. Re: Get ORA-12560 error while accessing 11g database
                                Salman Qureshi
                                Hi,
                                In your first post you said "Today I noticed that when I connect to the database from remote, I get the following error - ...". So remote machine certainly means your client from where you connect to your database server.
                                Rest of the things are also clear to me, just wanted to know whether you have multiple oracle installations on your client side or not? Is SQLPLUS/TNSPING from client machien execte from the same oracle home where you edited your tnsnames.ora file?

                                Salman
                                • 13. Re: Get ORA-12560 error while accessing 11g database
                                  765115
                                  The client (my Windows desktop) does not have any Oracle server installations, it just has the Oracle client installed.
                                  • 14. Re: Get ORA-12560 error while accessing 11g database
                                    Salman Qureshi
                                    Please re-read my question. I never "blamed" you to have oracle server installation on client side.
                                    just wanted to know whether you have multiple oracle installations on your client side or not?
                                    If answer is NO, please go ahead to disable your antivirus/firewall and try to make connection.
                                    Salman
                                    1 2 Previous Next