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>

ORA-01792: maximum number of columns in a table or view is 1000

select statement throws ORA-01792 error in Oracle 12.1.0.2.0 version during application reload after migrating database from 11g to 12c.

Below is the error in the application logs.

2016-07-09 12:30:00,614 INFO  atomikos createCompositeTransaction ( 3600000 ): created new ROOT transaction with id 3.21.105.48.tm0009301224
2016-07-09 12:30:00,615 INFO  atomikos commit() done (by application) of transaction 3.21.105.48.tm0009301224
2016-07-09 12:30:01,564 WARN  com.svg.core.database.Connection ORA-01792: maximum number of columns in a table or view is 1000

2016-07-09 12:30:01,564 ERROR com.svg.core.database.Connection ORA-01792: maximum number of columns in a table or view is 1000 [Error=1792]
java.sql.SQLSyntaxErrorException: ORA-01792: maximum number of columns in a table or view is 1000

Reason:

The problem is due to Unpublished bugs:

Bug 19653859 - CI BACKPORT OF BUG 19509982 FOR INCLUSION IN DATABASE BP 12.1.0.2.2
Bug 19509982 - DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT


Solution:

The workaround is to set "_fix_control"='17376322:OFF'

SQL> alter session set "_fix_control"='17376322:OFF' sid='*';

or at system level :

SQL> alter system set "_fix_control"='17376322:OFF' sid='*';

or
apply the Patch 19509982 if available for your platform and db version.

ORA-15120: ASM file name 'ORA-27090: Unable to reserve kernel resources for asynchro' does not begin with the ASM prefix character

Database fails to create with below error


Completing Database Creation
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 76%
DBCA_PROGRESS : 85%
PRCR-1079 : Failed to start resource ora.test03.db
CRS-5017: The resource action "ora.test03.db start" encountered the following error:
ORA-01565: error in identifying file '+TEST03_DATA/test03/PARAMETERFILE/spfile.268.917148803'
ORA-17503: ksfdopn:10 Failed to open file +TEST03_DATA/test03/PARAMETERFILE/spfile.268.917148803
ORA-15120: ASM file name 'ORA-27090: Unable to reserve kernel resources for asynchro' does not begin with the ASM prefix character
. For details refer to "(:CLSN00107:)" in "/opt/oracle/product/base/diag/crs/test03.domain.com/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.test03.db' on 'test03.domain.com' failed
CRS-2632: There are no more servers to try to place resource 'ora.test03.db' on that would satisfy its placement policy
DBCA_PROGRESS : 94%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
 /opt/oracle/product/obase/cfgtoollogs/dbca/test03.
Database Information:
Global Database Name:test03
System Identifier(SID) Prefix:test03


Reason:

fs.aio-max-nr value was set low in /etc/sysctl.conf.


fs.aio-max-nr = 1048576

As per Oracle standard, set fs.aio-max-nr to  3145728

fs.aio-max-nr= 3145728

Solution:

1. update fs.aio-max-nr in /etc/sysctl.conf with root user
vi /etc/sysctl.conf
#fs.aio-max-nr = 1048576
fs.aio-max-nr = 3145728

2. Run /sbin/sysctl -p to make it permanent

root:test03.domain.com ~ # sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 1
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmax = 65536
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv4.conf.all.accept_redirects = 0
.......
....
....
.........