Troubleshooting gc block lost and Poor Network Performance in a RAC Environment (Doc ID 563566.1)

Troubleshooting gc block lost and Poor Network Performance in a RAC Environment (Doc ID 563566.1)

Summary

In Oracle RAC environments, RDBMS gathers global cache work load statistics which are reported in STATSPACK, AWRs and GRID CONTROL. Global cache lost blocks statistics ("gc cr block lost" and/or "gc current block lost") for each node in the cluster as well as aggregate statistics for the cluster represent a problem or inefficiencies in packet processing for the interconnect traffic. These statistics should be monitored and evaluated regularly to guarantee efficient interconnect Global Cache and Enqueue Service (GCS/GES) and cluster processing. Any block loss indicates a problem in network packet processing and should be investigated.

The vast majority of escalations attributed to RDBMS global cache lost blocks can be directly related to faulty or mis-configured interconnects. This document serves as guide for evaluating and investigating common (and sometimes obvious) causes.

Even though much of the discussion focuses on Performance issues, it is possible to get a node/instance eviction due to these problems. Oracle Clusterware & Oracle RAC instances rely on heartbeats for node memberships. If network Heartbeats are consistently dropped, Instance/Node eviction may occur. The Symptoms below are therefore relevant for Node/Instance evictions.

Symptoms:

Primary:

• "gc cr block lost" / "gc current block lost" in top 5 or significant wait event

Secondary:

• SQL traces report multiple gc cr requests / gc current request /
•  gc cr multiblock requests with long and uniform elapsed times
• Poor application performance / throughput
• Packet send/receive errors as displayed in ifconfig or vendor supplied utility
• Netstat reports errors/retransmits/reassembly failures
• Node failures and node integration failures
• Abnormal cpu consumption attributed to network processing

Listener Fails to start TNS-12557,12560,00527 , ORA-12547: TNS:lost contact -- Windows Environment

We have faced very wired issue with Oracle database 11g Listener in Windows environment. For some reason 

it's not starting up and giving  following errors. We have tried all the solutions to resolve this issue, like starting/stopping listener through different Oracle Home, configuring timeout in sqlnet.ora , amending hosts files,creating another listener through netca , even try installing new oracle client with different ORACLE_HOME, but none of this solutions helped. 

But one thing i forget to mentioned here , we have also got another Oracle product on same machine.

WEBLOGIC  10.3.3
FMW 11.1.1.3 

It mean Our Windows Registry have some many key/value for the Oracle product. And we didn't have any clue what was causing problem.

But at last we tried one very simple thing and it works. That I have mentioned in Solution part. 

And guess what , it works !! 

And therefor I guess, one good Oracle DBA hates Windows for their databases.

Errors

TNSListener fails to startup.

Errors include

ORA-12547: TNS:lost contact

TNS-12557: TNS:protocol adapter not loadable

TNS-12560: TNS:protocol adapter error

TNS-527: Protocol Adapter not loadable

Also seen with

Failed to start service, error 997.
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

Solution

Remove the ORACLE_HOME system environment variable.
From Start button, navigate to Control Panel.  Then select the System icon and click the Advanced tab.
Click the Environment Variables button
Find the ORACLE_HOME in the system environment variables and delete it.
Re-boot the machine.

How To Resolve TNS-12535 or TNS-00505 Operation Timed Out Errors

While examining logs I have found lots of “TNS Operation Timeout Errors” in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-MAY-2009 18:54:41
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=3448))


The same error message was repeating during whole day almost for every application server.

I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections. 

Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.

Solution:

Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10


In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.

Troubleshooting ORA-03135: connection lost contact issue

Environment: Oracle database 10.2.0.3 RAC 64bit, websphere application server on RHEL4.5 use JDBC driver,Juniper firewalls (failover)
Problem: application server got error "java.sql.SQLException: ORA-03135: connection lost contact" and connection timeout from database
Objective: to fix the connection lost issue

Steps:
1. search the error message to find out the root cause in Oracle support and google:

Troubleshooting ORA-3135 Connection Lost Contact [ID 787354.1]
Resolving Problems with Connection Idle Timeout With Firewall [ID 257650.1]
Achive Log shipments failing in environment with Juniper firewall [ID 1075432.1]
Logs are not shipped to the physical standby database [ID 1130523.1]
ORA-03135: connetion lost contact while shipping from Primary server to standby server [ID 739522.1]

According to Oracle doc ID 787354.1, this ORA-3135 might be caused by one of the following:

(1) Transparent Application Failover (TAF)
(2) Idle Connection Timeout
(3) Firewall Manipulating  Sqlnet Data.

Otherwise, it might need further analysis.
Also, according to ORA-3135 connection lost contact tips: http://www.dba-oracle.com/t_ora_03135_connection_lost_contact.htm, it might be due to customized profile.

sqlplus / as sysdba
sql> set line 32000
sql> select profile, resource_name, limit
from dba_profiles where resource_name IN ('IDLE_TIME', 'CONNECT_TIME');

note: some commands:
sql> create profile myprof limit connect_time 30;


From application side, to avoid this idle connection timeout, refer to -   Putting a firewall between your AppServer and DBMS -  http://www.websphere-world.com/modules.php?name=News&file=article&sid=522to

2. check firewall idle connection timeout and ALG settings
Checked Juniper firewall SQL ALG setting and idle connection timeout

# check alg
# check service ssh
# check service SQL*Net V2         

And some articles:
a. What is Idle timeouts when any service set - J-Net Community - http://forums.juniper.net/t5/ScreenOS-Firewalls-NOT-SRX/What-is-Idle-timeouts-when-Any-service-set/td-p/163

b. Viewing list of ALGs and disabling an ALG differs on screenos versions - http://kb.juniper.net/InfoCenter/index?page=content&id=KB13509
c. Issues with SQL database applications when traffic traverses SRX - http://kb.juniper.net/InfoCenter/index?page=content&id=KB21550
d. Increasing the session idle timeout of a particular service - http://kb.juniper.net/InfoCenter/index?page=content&id=KB4652&actp=LIST
e. How do I verify the NCP settings? (for SQL ALG) - http://kb.juniper.net/InfoCenter/index?page=content&id=KB9283
f. [SRX]what conditions are required for SQL ALG? - http://kb.juniper.net/InfoCenter/index?page=content&id=KB22418&cat=JUNOS&actp=LIST
g. Users losing connections when the IVE Active/Passive Cluster Fails over - http://kb.juniper.net/InfoCenter/index?page=content&id=KB8539&cat=SSL_VPN&actp=LIST

3. We have disabled SQL ALG and application server seemed busy at the time of connection lost.
Our monitoring shows Firewall actually failed over to another one at the time of connection lost and switched back, so the application server got "ORA-03135: connection lost contact" error twice and need to restart application.

“Device eth0 does not seem to be present” – cloned VM’s

This is because when RHEL clones a VM it gives it a new MAC address.

You can see that instead of just eth0 there is eth1 now too by:

cat /etc/udev/rules.d/70-persistent-net.rules

Two options to fix it:
1. Edit the above file and re-rewrite correct MAC address.

or

2a. up the eth1 ( ifconfig eth1 up)
2b. Go to /etc/sysconfig/network-scripts and rename the file ifcfg-eth0 to ifcfg-eth1, then edit MAC address same as 70-persistent-net.rules  for eth1 in ifcfg-eth1.

Done !!

Solaris Network configuration

###Network Configuration Overview###

2-Modes
 1. Local Files Mode - config is defined statically via key files
 2. Network Client Mode - DHCP is used to auto-config interface(s)

Current Dell PE server has 3 NICs:
 1. e1000g0 - plumbed (configured for network client mode)
 2. iprb0 - unplumbed
 3. iprb1 - unplumbed

1-Virtual Mandatory interface lo0 - loopback

Determine physical interfaces using 'dladm show-dev | show-link'
Determine plumbed and loopback interfaces using 'ifconfig -a'

NIC naming within Solaris OS: i.e. e1000g0 - e1000g(driver name) 0(instance)

Layers 2 & 3 info. - ifconfig -a, or ifconfig e1000g0
Layer 1 info. - dladm show-dev | show-link

###Key network configuration files###
svcs -a | grep physical
svcs -a | grep loopback

1. IP Address - /etc/hostname.e1000g0, /etc/hostname.iprb0 | iprb1
2. Domain name - /etc/defaultdomain - linuxcbt.internal
3. Netmask - /etc/inet/netmasks - 192.168.1.0 255.255.255.0
4. Hosts database - /etc/hosts, /etc/inet/hosts - loopback & ALL interfaces
5. Client DNS resolver file - /etc/resolv.conf
6. Default Gateway - /etc/defaultrouter - 192.168.1.1, 172.16.20.1, 10.0.0.1
7. Node name - /etc/nodename
Name service configuration file - /etc/nsswitch.conf

netstat -D - returns DHCP configuration for ALL interfaces
ifconfig -a - returns configuration for ALL interfaces


Reboot system after transitioning from network client(DHCP) mode to local files(Static) mode


mv  dhcp.e1000g0 to some other name or remove the file so that the DHCP agent is NOT invoked
echo "linuxcbtsun1" > /etc/nodename


###Plumb/enable the iprb0 100Mb/s interface###
Plumbing interfaces is analagous to enabling interfaces
Note: 172.16.20.11 is a Linux host waiting to communicate with iprb0 interface
Steps:
 1. ifconfig iprb0 plumb up - this will enable iprb0 interface
 2. ifconfig iprb0 172.16.20.10 netmask 255.255.255.0 - this will enable layer-3 IPv4 address

Steps to UNplumb an interface:
 1. ifconfig iprb0 unplumb down

###Ensure that newly-plumbed interface settings persists across reboots###
Steps include updating/creating the following files:
 1. echo "172.16.20.10" > /etc/hostname.iprb0
 2. create entry in /etc/hosts - 172.16.20.10 linuxcbtsun1
 3. echo "172.16.20.0 255.255.255.0" >> /etc/inet/netmasks

Note: To down interface, execute:
ifconfig interface_name down
ifconfig iprb0 down && ifconfig iprb0


###Sub-interfaces/Logical Interfaces###
e1000g0(physical interface) - 192.168.1.50(Primary Apache website)
                              192.168.1.51(Secondary Apache website)
     192.168.1.52(Used for SSH)

iprb0 - 172.16.20.10
iprb1

Use 'ifconfig interface_name addif ip_address '
ifconfig e1000g0 addif 192.168.1.51 (RFC-1918 - defaults /24)

Note: This will automatically create an 'e1000g0:1' logical interface
Note: Solaris places new logical interface in DOWN mode by default
Note: use 'ifconfig e1000g0:1 up' to bring the interface up

Note: logical/sub-interfaces are contingent upon physical interfaces
Note: if physical interface is down, so will the logical interface(s)
Note: connections are sourced using IP address of physical interface

###Save logical/sub-interface configuration for persistence across reboots###

1. gedit /etc/hostname.e1000g0:1 - 192.168.1.51
2. gedit /etc/hostname.e1000g0:2 - 192.168.1.52
3. Optionally update /etc/hosts - /etc/inet/hosts
4. Optionally update /etc/inet/netmasks - when subnetting

Note: To remove logical interface execute the following:
ifconfig physical_interface_name removeif ip_address
ifconfig iprb0 removeif 172.16.20.20


###/etc/nsswitch.conf - name service configuration information ###
functions as a policy/rules file for various resolution:
 1. DNS
 2. passwd(/etc/passwd,/etc/shadow),group(/etc/group)
 3. protocols(/etc/inet/protocols)
 4. ethers or mac-to-IP mappings
 5. hosts - where to look for hostname resolution: files(/etc/hosts) dns(/etc/resolv.conf)

============================== NETSTAT ========================

###NETSTAT###

Lists connections for ALL protocols & address families to and from machine
Address Families (AF) include:
 INET - ipv4
 INET6 - ipv6
 UNIX - Unix Domain Sockets(Solaris/FreeBSD/Linux/etc.)

Protocols Supported in INET/INET6 include:
 TCP, IP, ICMP(PING(echo/echo-reply)), IGMP, RAWIP, UDP(DHCP,TFTP,etc.)

Lists routing table
Lists DHCP status for various interfaces
Lists net-to-media table - network to MAC(network card) table

###NETSTAT Usage###
netstat - returns sockets by protocol using /etc/services for lookup
/etc/nssswitch.conf is consulted by netstat to resolve names for IPs

netstat -a - returns ALL protocols for ALL address families (TCP/UDP/UNIX)

netstat -an - -n option disables name resolution of hosts & ports

netstat -i - returns the state of interfaces. pay attention to errors/collisions/queue columns when troubleshooting performance

netstat -m - returns streams(TCP) statistics

netstat -p - returns net-to-media info (MAC/layer-2 info.) i.e. arp

netstat -P protocol (ip|ipv6|icmp|icmpv6|tcp|udp|rawip|raw|igmp) - returns active sockets for selected protocol

netstat -r - returns routing table

netstat -D - returns DHCP configuration (lease duration/renewal/etc.)

netstat -an -f address_family
netstat -an -f inet|inet6|unix
netstat -an -f inet - returns ipv4 only information

netstat -n -f inet
netstat -anf inet -P tcp
netstat -anf inet -P udp

Solaris 10 – Change IP Address without reboot

Adding or editing the IP address on a Solaris 10 server is different from the previous versions of the OS (Solaris 9, Solaris 8 etc).

In the previous versions of the Solaris Operating System, you need to edit the /etc/hosts file and add/edit the entry for the IP address and the hostname.

Example:

192.168.1.1 sun1

However, in Solaris 10, you should edit the /etc/hosts file (a symlink to /etc/inet/hosts file) and the /etc/inet/ipnodes file and add an entry for IP address and hostname.

Once done, restart the Network service using

# svcadm restart network/physical

or reboot the server for the changes to take effect.

Although, the /etc/inet/ipnodes files is primarily for IPv6 only, without adding an entry to the file, the IP address (IPv4) doesn’t become active. This seems to be a known problem but the good news is this is now fixed in the Solaris 10 U4 (08/07 build).

Also, ensure that the /etc/netmasks file with the network ID and the netmask.

'ORA-12547: TNS: lost contact' messages after chanign kernel.shmax parameter

OTN Discussion Forums : Repeated 'ORA-12547: TNS: lost contact' ...: "'ORA-12547: TNS: lost contact' messages after"

UTL_HTTP && Network ACL 11g

SELECT host, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'resolve'),

1, 'GRANTED', 0, 'DENIED', NULL) privilege

FROM dba_network_acls;

SELECT host, lower_port, upper_port, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'connect'),

1, 'GRANTED', 0, 'DENIED', null) privilege

FROM dba_network_acls;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

SELECT * FROM DBA_NETWORK_ACLS;

begin

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

end;

begin

dbms_network_acl_admin.create_acl (

acl => 'volume_utl_http.xml',

description => 'HTTP Access',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

dbms_network_acl_admin.add_privilege (

acl => 'volume_utl_http.xml',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'resolve',

start_date => null,

end_date => null

);

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

commit;

end;

How to get port number list of EM and isqlplus

You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini.

On my system the output is,
bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (THERAP) = 1158
Enterprise Manager Agent Port (THERAP) = 3938
Enterprise Manager Console HTTP Port (arjudba) = 5500
Enterprise Manager Agent Port (arjudba) = 1830
Enterprise Manager Console HTTP Port (arju) = 5501
Enterprise Manager Agent Port (arju) = 1831

But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.