Pages

Friday 22 July 2016

ORA-12537: TNS:connection closed error

Couldn't connect the database remotely. We were getting below error

oracle@test01.sangam.com_test011:/opt/oracle/product/rdbms/12.1.0.2/network/admin>sqlplus dbsnmp/*****@test01

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 10 04:26:32 2016

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

ERROR:
ORA-12537: TNS:connection closed


Enter user-name: ^C
oracle@test01.sangam.com_test011:/opt/oracle/product/rdbms/12.1.0.2/network/admin>sqlplus dbsnmp/*****@test01

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 10 04:26:39 2016

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

ERROR:
ORA-12537: TNS:connection closed



In Listener log, it was throwing below error.


10-FEB-2016 04:59:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test01)(CID=(PROGRAM=sqlplus)(HOST=test01.sangam.com)(USER=oracle))(INSTANCE_NAME=test011)) *

(ADDRESS=(PROTOCOL=tcp)(HOST=10.120.128.33)(PORT=20822)) * establish * test01 * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
10-FEB-2016 04:59:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test01)(CID=(PROGRAM=sqlplus)(HOST=test01.sangam.com)(USER=oracle))(INSTANCE_NAME=test011)) *

(ADDRESS=(PROTOCOL=tcp)(HOST=3.239.128.33)(PORT=20824)) * establish * test01 * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
10-FEB-2016 04:59:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test01)(CID=(PROGRAM=sqlplus)(HOST=test01.sangam.com)(USER=oracle))(INSTANCE_NAME=test011)) *

(ADDRESS=(PROTOCOL=tcp)(HOST=3.239.128.33)(PORT=20826)) * establish * test01 * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe



Reason:

This was caused by permission issue.


oracle@test01.sangam.com_test011:/opt/oracle/product/rdbms/12.1.0.2/network/admin>ls -lrt $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 323762228 Nov 16 09:51 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle

oracle@test01.sangam.com_test011:/opt/oracle/product/rdbms/12.1.0.2/network/admin>exit

When the permissions as stated in the document should be

-rwsr-s--x 1 oracle asmadmin Nov 16 09:51 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle

so a quick su to root and a chmod

oracle@test01.sangam.com_test011:/opt/oracle/product/rdbms/12.1.0.2/network/admin>exit
logout
root:test01.sangam.com ~ # chmod 6751 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
root:test01.sangam.com ~ # ls -lrt /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
-rwsr-s--x 1 oracle asmadmin 323762228 Nov 16 09:51 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
root:test01.sangam.com ~ # exit

Repeat same thing on other nodes of cluster.

root:test02.sangam.com ~ # ls -lrt /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
-rwxr-s--x 1 oracle asmadmin 323762228 Nov 16 09:52 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
root:test02.sangam.com ~ # chmod 6751 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
root:test02.sangam.com ~ # ls -lrt /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
-rwsr-s--x 1 oracle asmadmin 323762228 Nov 16 09:52 /opt/oracle/product/rdbms/12.1.0.2/bin/oracle
root:test02.sangam.com ~ #


Try connecting remotely

grid@test02.sangam.com_+ASM1:/export/home/grid>sqlplus dbsnmp/*****@test01-scan:1521/test01

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 10 05:35:35 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> exit


grid@test02.sangam.com_+ASM1:/export/home/grid> sqlplus dbsnmp/*****@'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test01.sangam.com)(PORT = 1521))
(CONNECT_DATA = (SID = test01)))'                                                 <

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 10 05:36:12 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>

23 comments:

  1. Very good idea you've shared here, from here I can be a very valuable new experience

    disaster recovery backup

    ReplyDelete
  2. great.... from last 3 days I am finding solution for this problem finally it's solved. Thanks. I appreciate.

    I wanna became as oracle DBA. If you are willing to teach me, I am ready to pay for your service.
    Thank u

    ReplyDelete
  3. Nice Blog We are providing technical support in Quickbooks Support Phone Number +1-800-986-4607. if you are Expand your business to a new hike, with progressive approach. Seeking for the best accounting software? Then, get QuickBooks installed in your system.

    ReplyDelete
  4. We are the one stop solution to all your glitches in the Quickbooks Payroll Support Phone Number 1-800986-4607. We are Providing immediate & effective response to the user. Our team constitutes of various qualities.Our team is very friendly & polite. So, whatever the issue you are preoccupied .

    ReplyDelete
  5. Contact Quickbooks 24 Hour Support Phone Number 800-901-6679 to acquire instant solution of your issues & queries. Get round the clock assistance from our highly skilled technicians. Whenever appear with technical glitches just make a call on the support anytime.

    ReplyDelete
  6. Is error trouble, worries you much? Then, simply call on the Quickbooks Payroll Desktop Support Number 800-986-4591. Here, you can easily resolve your issues & queries. Our Quickbooks payroll support provide you immediate assistance for your issues.

    ReplyDelete
  7. Are you looking for some expert help to fix your Quickbooks issue? Get instant solutions by contacting us at our toll free number Quickbooks Support Phone Number 800-986-4591. We are ensuring to deliver the best solutions that suits your business needs. Do not waste your precious time by wandering here and there.

    ReplyDelete
  8. Nice Blog ! Let our support team solve your issues. No matter how complex the problem is, we are always here to support you. Right from the installation process to resolving any complex error. Dial our QuickBooks Customer Service Number 1-800-986-4607.
    View on Map: https://tinyurl.com/vq37tnv

    ReplyDelete
  9. Nice & informative Blog ! We are here to solve your QuickBooks issues efficiently. QuickBooks versions for which we provide support are:

    • QuickBooks Premier
    • QuickBooks Pro
    • QuickBooks Enterprise
    • QuickBooks Mac
    • QuickBooks Payroll

    Just connect with our Quickbooks Payroll Support Phone Number 855-907-0406 accounting professionals and fix your issues in seconds!

    ReplyDelete
  10. Are you getting frustrated by any QuickBooks issue? If so, dial our QuickBooks Customer Service Phone Number 855-907-0406 and resolve issues quickly.We have a panel of experts with various qualities.
    View on Map https://tinyurl.com/s8d2kd2

    ReplyDelete
  11. Excellent and very informative blog click here
    Quickbooks Proadvisor Support Phone Number and for more detail dial on 844-908-0801

    ReplyDelete
  12. Nice Blog ! We are here at QuickBooks Support Phone Number 855-9O7-O4O6 to give our customers instant support services. We are responsible for providing support services for all versions of QuickBooks.

    ReplyDelete
  13. Do you want help to get your QuickBooks issues resolved in seconds? If yes, Dial our QuickBooks Support Number 855-907-0406 now! We will let you do your accounting duties without any interruptions.

    ReplyDelete
  14. Dealing with QuickBooks Problems is no more a hassle now! Dial QuickBooks Support Phone Number 855-907-0406 when you experience the issue.
    View on Map: QuickBooks Customer Service

    ReplyDelete
  15. If you need help in solving issues you are facing in any of the above versions, you can connect with us at our QuickBooks Support Phone Number New York 855-907-0406.
    View On Map: QuickBooks Customer Service Phone Number.

    ReplyDelete
  16. If you need help in solving issues you are facing in any of the above versions, you can connect with us at our QuickBooks Support Phone Number New York 855-907-0406.
    View On Map: QuickBooks Support Phone Number.

    ReplyDelete
  17. Well explained and knowledgeable blog click here for
    Quickbooks Customer Service Phone Number for more detail dial on our Quickbooks Customer Service Number 855-907-0406.

    ReplyDelete
  18. Nice & Informative Blog ! We are the one-stop solution to all your issues & query, if facing issues in QuickBooks. Get valuable aid with QuickBooks Customer Helpline Number 855-9O7-O4O6.

    ReplyDelete
  19. Looking for expert advice? Contact QuickBooks Customer Service Number 1-833-780-0086 to get assistance for error related issues. For More: https://g.page/quickbookssupporttexas

    ReplyDelete
  20. our QuickBooks Technical Support Number 1-833-325-0220 and get your queries settled concurrently from our QB technicians that are always there to support you by 24*7. For More: https://tinyurl.com/ybzzmvhr

    ReplyDelete