Linux/Unix shell脚本跨服务器跨实例执行SQL(二)

[python] 
#下面是直接在远程主机环境执行SQL并轮巡的shell脚本，这种方式可以用于没有或不知道数据库用户及密码的情形  
#需要注意的：  
# a. 该方式使用了基于操作系统用户身份验证，应确保支持该方式  
# b. 如果需要执行的SQL语句比较复杂，直接嵌套的shell脚本也复杂。如果写成.sql文件，每一个服务器需要存在一个副本  
  
oracle@linux1:~> more query_multi_inst_notns.sh   
#!/bin/bash  
# ------------------------------------------------------------------------------------+  
# Script Name: query_multi_inst_notns.sh                                              |  
# Desc:  This script login to different remote host where define in known_host file,  |   
#        after that look through oratab and execute SQL in each remote instance.      |    
# Req:   Configure a secure shell by ssh-keygen to all remote host                    |  
# Author : Robinson                                                                   |  
# Blog   : http://blog.csdn.net/robinson_0612                                         |  
# ------------------------------------------------------------------------------------+  
#  
# --------------------------------------------  
# Set environment vairable and define variable  
# --------------------------------------------  
  
if [ -f ~/.bash_profile ]; then  
    . ~/.bash_profile  
fi  
  
ORATAB=/etc/oratab  
RHOST=~/.ssh/known_hosts  
LOGFILE=/users/robin/dba_scripts/custom/log/query_multi_inst_notns.log  
  
# -------------------------------  
# take a loop in each hostname  
# -------------------------------  
  
{  
for host in `cat $RHOST | awk '{print $1}'`   
do  
    echo "************************************"  
    echo "Current host is $host."  
    echo "************************************"  
    echo ""  
  
    # ---------------------------  
    # take a loop in ORATAB file  
    # ---------------------------  
  
    for db in `ssh $host cat $ORATAB |egrep ':N|:Y'|grep -v \* | grep -v \# |cut -d ":" -f1`  
    do  
        echo "------------------------------------"  
        echo "Current database is $db.            "  
        echo "------------------------------------"  
        home=`ssh $host cat $ORATAB |egrep ':N|:Y'|grep -v \* | grep -v \# | grep $db |cut -d ":" -f2`  
        ssh $host "  
        export ORACLE_SID=$db  
        export ORACLE_HOME=${home}  
        ${ORACLE_HOME}/bin/sqlplus -S / as sysdba  <&1  
exit  

[python] 
#使用tnsnames方式测试  
oracle@linux1:~> ./query_multi_inst_tns.sh   
************************************  
Current host is 172.168.2.196.  
************************************  
  
------------------------------------  
Current database is US001.              
------------------------------------  
  
NAME                           VALUE  
------------------------------ --------------------  
open_cursors                   300  
  
------------------------------------  
Current database is US002.              
------------------------------------  
  
NAME                           VALUE  
------------------------------ --------------------  
open_cursors                   300  
  
------------------------------------  
Current database is US003.            #这个是没有tnsnames的情形  
------------------------------------  
ERROR:  
ORA-12154: TNS:could not resolve the connect identifier specified  
  
  
#直接在远程服务器环境轮巡测试  
oracle@linux1:~> ./query_multi_inst_notns.sh   
************************************  
Current host is 172.168.1.196.  
************************************  
  
------------------------------------  
Current database is US001.              
------------------------------------  
  
NAME                           VALUE  
------------------------------ --------------------  
open_cursors                   300  
  
------------------------------------  
Current database is US002.              
------------------------------------  
  
NAME                           VALUE  
------------------------------ --------------------  
open_cursors                   300  
  
------------------------------------  
Current database is US005.            #远程服务器上的实例没有启动的情形  
------------------------------------  
        select name,value from v$parameter where name='open_cursors'  
*  
ERROR at line 1:  
ORA-01034: ORACLE not available