DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, March 10, 2014

Script to check if sql connectivity hangs in RAC nodes due to different issues


### Schedule the script in crontab every 2 minutes based on need, to check if the sql connectivity is ### successful without hanging in each RAC node.


mail_id=`cat /home/oracle/scripts/mail_members.txt`
file=/home/oracle/scripts/logs/1.log
if [ -f $file ];
then
    echo " sqlplus not connected  " >> /home/oracle/scripts/logs/sql.log
    cat /home/oracle/scripts/logs/sql.log | mailx -s "sqlplus not connected" $mail_id
else
    touch /home/oracle/scripts/logs/1.log
    . /home/oracle/oracle.env
    sqlplus -s "/ as sysdba" << EOF
        spool out.log
        select name from v\$database;
        spool off;
    EOF
    rm /home/oracle/scripts/logs/1.log
fi

### If on any instance the sql connectivity hangs and you are intimated. After fixing the issue remove ### the file /home/oracle/scripts/logs/1.log.

How to change Virtual IP and Scan IP in an existing RAC environment

Output of current Host file:
===================

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
#OLD - Public
#195.100.100.103 dbsvr1.test.com dbsvr1
#195.100.100.104 dbsvr2.test.com dbsvr2

#New - Public
172.16.0.23 dbsvr1.test.com dbsvr1
172.16.0.24 dbsvr2.test.com dbsvr2

#Private
10.10.10.1      dbsvr1-priv.test.com    dbsvr1-priv
10.10.10.2      dbsvr2-priv.test.com    dbsvr2-priv

#OLD - Virtual
#195.100.100.211 dbsvr1-vip.test.com     dbsvr1-vip
#195.100.100.212 dbsvr2-vip.test.com     dbsvr2-vip

#New - Virtual
172.16.0.15 dbsvr1-vip.test.com     dbsvr1-vip
172.16.0.16 dbsvr2-vip.test.com     dbsvr2-vip

#OLD - SCAN IP
#195.100.100.207     cluster.test.com       s-cluster
#195.100.100.208        cluster.test.com       s-cluster
#195.100.100.209        cluster.test.com       s-cluster

#New - SCAN IP
172.16.0.11     cluster.test.com       s-cluster
#172.16.0.12        cluster.test.com       s-cluster
#172.16.0.13        cluster.test.com       s-cluster

Before VIP change :
********************

[oracle@dbsvr1 ~]$ srvctl config nodeapps
Network exists: 1/195.100.0.0/255.255.0.0/bond0, type static
VIP exists: /dbsvr1-vip/195.100.100.213/195.100.0.0/255.255.0.0/bond0, hosting node dbsvr1
VIP exists: /195.100.100.214/195.100.100.214/195.100.0.0/255.255.0.0/bond0, hosting node dbsvr2

Before VIP change, Please shutdown the instance on both node.


Step to change the VIP change :
************************************

[root@dbsvr1 ~]# srvctl modify nodeapps -n dbsvr1 -A 172.16.0.15/255.255.0.0/bond0

Note : Do the same above activity in node 2 to change the second node VIP.

Default command : srvctl modify nodeapps -n `hostname` -A VIP/DNS/interfacename


After VIP change :
********************

[root@dbsvr1 ~]# srvctl config nodeapps -a
Network exists: 1/172.16.0.0/255.255.0.0/bond0, type static
VIP exists: /dbsvr1-vip/172.16.0.15/172.16.0.0/255.255.0.0/bond0, hosting node dbsvr1
VIP exists: /195.100.100.214/195.100.100.214/172.16.0.0/255.255.0.0/bond0, hosting node dbsvr2


Before SCAN IP change :
***************************

[root@dbsvr1 ~]# srvctl config scan
SCAN name: s-cluster, Network: 1/172.16.0.0/255.255.0.0/bond0
SCAN VIP name: scan1, IP: /s-cluster/195.100.100.207

Stop the Scan Listener :
*******************************

[root@dbsvr1 ~]# srvctl stop scan_listener
[root@dbsvr1 ~]# srvctl stop scan
[root@dbsvr1 ~]# srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running

[root@dbsvr1 ~]# srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running

Step to change the Scan listner IP :
************************************

[root@dbsvr1 ~]# srvctl modify scan -n s-cluster

Note : s-cluster means SCAN IP alias name in /etc/hosts. It will automatically assign the SCAN IP using this alias name.

Default command : srvctl modify scan -n `SCAN IP alias name in /etc/hosts`


After SCAN IP change :
*******************************

[root@dbsvr1 ~]# srvctl config scan
SCAN name: s-cluster, Network: 1/172.16.0.0/255.255.0.0/bond0
SCAN VIP name: scan1, IP: /s-cluster/172.16.0.21