Wednesday, December 3, 2014

VMware Snapshot causing database connection to error out "Connection reset by peer"

Every time my customer performs VMware Snapshot on VMs connecting to the Oracle database in a Linux physical box, it triggers Oracle database connection resets. After a few weeks of running RDA, OSWatcher, listener.log and etc, Customer administrator found out that someone set the net_ipv4.tcp_retries2 parameter to a very low value. Default value is 15 which is roughly 13 - 30 minutes of timeout. In this case, someone setting the tcp_retries2 to 3, that translates into 3-5 minutes before connection is reset and if VMware Snapshot taking longer than that period of time due to "stun", it would reset the connection to the database. I believed setting this too low will trigger "connection reset by peers" if the network is slow enough in any other situations.


There is absolutely no reason to set the tcp_retries2 to a really low number.

Tuesday, December 2, 2014

Unable to drop tablespace

Trying to drop tablespaces in my lab part of my cleanup task. Somehow I can't drop this SIE. Apparently, the materialized log still tied up with the user. Since, I no longer need this tablespace, I just drop the user as well and that solved my problem.



SQL> drop tablespace SIE including contents and datafiles;
drop tablespace 
SIE including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace


Note used the same name of the username and the tablespace for better keeping tracking purposes.



SQL> drop user 
SIE cascade;

User dropped.

SQL> drop tablespace 
SIE including contents and datafiles;

Tablespace dropped.

SQL>