Friday, 3 May 2013

Fix Oracle Hanging

What causes Oracle to hang?  How can I detect the cause of an Oracle hang?  How do I fix an Oracle hang problem?
Ans: Oracle hangs only when he is waiting for a resource.  It might be a latch (lock), or server resources (CPU, RAM). 
1.       Oracle path not properly set like $ORACLE_HOME.
2.       Listener is down/hanging. Check tns/listener is working fine or not (lsnrctl status)
3.       External hanging like network issue or firewall issue to make connection to DB. You can test this command. Set sqlnet.authentication_services=(none) in your sqlnet.ora file and retry connecting.
4.       There is not enough RAM to spawn another connection to Oracle
Debugging a hung database
There is a new feature in Oracle 11g SQL*Plus called the “prelim” option
root> sqlplus –prelim
SQL>
or
SQL> set _prelim on

SQL> connect / as sysdba
To run the oradebug commands to diagnose a hung database issue using the new hanganalyze utility:
1. SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to identify process state changes.
2. SQL> oradebug hanganalyze 3
Open a separate SQL session and immediately generate a system state dump.
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Setup DNS for SCAN used in Oracle RAC 11gR2

Create for example a VM machine named mydns and install following:
Need RPMs
[root@mydns ~]# rpm -qa | grep bind
ypbind-1.19-12.el5_6.1
bind-9.3.6-16.P1.el5
bind-utils-9.3.6-16.P1.el5
bind-libs-9.3.6-16.P1.el5
/etc/named.conf
#  minimal DNS conf file for RAC 11g

options {
    //i Forward any name this DNS can't resolve to my router.
    forwarders { 192.168.80.2; };

    // Directory where named will look for zone files.
    directory "/var/named";
};

# Forward Zone
zone "mydom.com" IN {
    type master;
    file "mydom.com.zone";
    allow-update { none; };
};

# Reverse Zone
zone "80.168.192.in-addr.arpa" IN {
    type master;
    file "80.168.192.zone";
    allow-update { none; };
};
Overview
Server
DNS
RAC node 1
RAC node 2
Public Name
mydns.mydom.com
rac1.mydom.com
rac2.mydom.com
Private Name
-
rac1-priv.mydom.com
rac2-priv.mydom.com
VIP Name
-
rac1-vip.mydom.com
rac2-vip.mydom.com
Public IP – eth0
192.168.80.140
192.168.80.151
192.168.80.152
VIP IP – eth0:1
-
192.168.80.171
192.168.80.172
Private IP – eth1
-
192.168.136.151
192.168.136.152


SCAN Adresse
FQN
Shortname
192.168.80.201
rac-scan.mydom.com
rac-scan
192.168.80.202
rac-scan.mydom.com
rac-scan
192.168.80.203
rac-scan.mydom.com
rac-scan

Zone file /etc/named/mydom.com.zone
$ORIGIN mydom.com.
$TTL 1D          ; time-to-live   - (1 day)

@  IN SOA  mydns.mydom. master.mydom.com. (
        201011021   ; serial number  - (yyyymmdd+s)
        1d          ; refresh        - (1 day)
        1h          ; retry          - (1 hour)
        1w          ; expire         - (1 week)
        60          ; minimum        - (1 minute)
)
;
@       NS mydns        ; mydns.mydom.com is the name server

localhost   A        127.0.0.1

; RAC nodes
rac1        A        192.168.80.151
rac2        A        192.168.80.152
rac1-vip    A        192.168.80.171
rac2-vip    A        192.168.80.172
rac1-priv   A        192.168.136.151
rac2-priv   A        192.168.136.152

; DNS
mydns       A        192.168.80.140

; SCAN - Single Client Access Name
rac-scan    A        192.168.80.201
rac-scan    A        192.168.80.202
rac-scan    A        192.168.80.203
Reverse zone file /etc/named/80.168.192.zone
$ORIGIN 80.168.192.in-addr.arpa.
$TTL 1D          ; time-to-live   - (1 day)

@  IN SOA  mydns.mydom. master.mydom.com. (
        201011021   ; serial number  - (yyyymmdd+s)
        1d          ; refresh        - (1 day)
        1h          ; retry          - (1 hour)
        1w          ; expire         - (1 week)
        60          ; minimum        - (1 minute)
)
;
@  NS mydns     ; mydns.mydom.com is the name server

; RAC nodes
151                     PTR     rac1.mydom.com.
152                     PTR     rac2.mydom.com.

; RAC-VIP nodes
171                     PTR     rac1-vip.mydom.com.
172                     PTR     rac1-vip.mydom.com.

; DNS server
40                      PTR     mydns.mydom.com.

; SCAN - Single Client Access Name
201                     PTR     rac-scan.mydom.com.
202                     PTR     rac-scan.mydom.com.
203                     PTR     rac-scan.mydom.com.
/etc/resolv.conf (in DNS machine)
[root@mydns ~]# cat /etc/resolv.conf
nameserver 127.0.0.1
search mydom.com

Check config file
[root@mydns ~]# named-checkconf /etc/named.conf

[root@mydns ~]# named-checkzone mydom.com /var/named/mydom.com.zone
zone mydom.com/IN: loaded serial 201011021
OK

[root@mydns ~]# named-checkzone 80.168.192.in-addr.arpa  /var/named/80.168.192.zone
zone 80.168.192.in-addr.arpa/IN: loaded serial 201011021
OK
[root@mydns ~]#

Run service named
[root@mydns ~]# service named start
Starting named:                                            [  OK  ]
[root@mydns ~]#

/etc/resolv.conf (in a RAC Node)
[root@rac1 ~]# cat /etc/resolv.conf
nameserver 192.168.80.140
search mydom.com
[root@rac1 ~]#
Test
[root@rac1 ~]# nslookup rac1
Server:         192.168.80.140
Address:        192.168.80.140#53

Name:   rac1.mydom.com
Address: 192.168.80.151
[root@rac1 ~]# nslookup rac1-vip
Server:         192.168.80.140
Address:        192.168.80.140#53

Name:   rac1-vip.mydom.com
Address: 192.168.80.171
[root@rac1 ~]# nslookup rac-scan
Server:         192.168.80.140
Address:        192.168.80.140#53

Name:   rac-scan.mydom.com
Address: 192.168.80.201
Name:   rac-scan.mydom.com
Address: 192.168.80.202
Name:   rac-scan.mydom.com
Address: 192.168.80.203
[root@rac1 ~]# nslookup rac-scan
Server:         192.168.80.140
Address:        192.168.80.140#53

Name:   rac-scan.mydom.com
Address: 192.168.80.203
Name:   rac-scan.mydom.com
Address: 192.168.80.201
Name:   rac-scan.mydom.com
Address: 192.168.80.202
Note: nslookup must return 3 IPs in a different order each time
 
[root@rac1 ~]# dig rac-scan.mydom.com

; <<>> DiG 9.3.6-P1-RedHat-9.3.6-16.P1.el5 <<>> rac-scan.mydom.com
;; global options:  printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10628
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 1, ADDITIONAL: 1

;; QUESTION SECTION:
;rac-scan.mydom.com.            IN      A

;; ANSWER SECTION:
rac-scan.mydom.com.     86400   IN      A       192.168.80.202
rac-scan.mydom.com.     86400   IN      A       192.168.80.203
rac-scan.mydom.com.     86400   IN      A       192.168.80.201

;; AUTHORITY SECTION:
mydom.com.              86400   IN      NS      mydns.mydom.com.

;; ADDITIONAL SECTION:
mydns.mydom.com.        86400   IN      A       192.168.80.140

;; Query time: 0 msec
;; SERVER: 192.168.80.140#53(192.168.80.140)
;; WHEN: Tue Apr 24 10:56:49 2012
;; MSG SIZE  rcvd: 120
[root@rac1 ~]# dig -x 192.168.80.203

; <<>> DiG 9.3.6-P1-RedHat-9.3.6-16.P1.el5 <<>> -x 192.168.80.203
;; global options:  printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 56995
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 0

;; QUESTION SECTION:
;203.80.168.192.in-addr.arpa.   IN      PTR

;; ANSWER SECTION:
203.80.168.192.in-addr.arpa. 86400 IN   PTR     rac-scan.mydom.com.

;; AUTHORITY SECTION:
80.168.192.in-addr.arpa. 86400  IN      NS      mydns.80.168.192.in-addr.arpa.

;; Query time: 0 msec
;; SERVER: 192.168.80.140#53(192.168.80.140)
;; WHEN: Tue Apr 24 10:57:14 2012
;; MSG SIZE  rcvd: 97

[root@rac1 ~]#
Link:
http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf