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

      在DB运维的过程中，难免碰到需要跨多个服务器以及多个Oracle实例查询数据库的情形。比如我们需要查看当前所有生产环境数据库上open_cursor参数的值以便考虑是否需要增加。而需要查看的数据库服务器上有多个实例，同时又存在多个数据库服务器，这样子下来，几十个DB，上百个的情形一个个查，那得把人给累死。那就像点办法撒，写个shell脚本，轮巡所有服务器及服务器上的所有实例。见本文的描述。

     关于shell脚本中如何调用SQL，可以参考下列链接:
          Linux/Unix shell 脚本中调用SQL,RMAN脚本
          Linux/Unix shell sql 之间传递变量
    
1、使用ssh-keygen生成密钥实现快速登陆

  要跨服务器自动执行脚本，得需要实现免密码自动登陆，然后才能在多个服务器之间跳转，因此我们首先需要生成登陆密钥。
  生存登陆密钥需要三个步骤，在本地机器创建密钥，复制公钥要远程主机，将公钥追加到远程主机的authorized_keys
  下面是一个操作示例: 

    oracle@linux1:~> mkdir ~/.ssh             #首先在本地创建.ssh目录并赋予权限
    oracle@linux1:~> chmod 700 ~/.ssh
    oracle@linux1:~> ssh-keygen -t rsa        #使用ssh-keygen生成密钥对，也可以使用dsa方式
    Generating public/private rsa key pair.
    Enter file in which to save the key (/users/oracle/.ssh/id_rsa): 
    Enter passphrase (empty for no passphrase): 
    Enter same passphrase again: 
    Your identification has been saved in /users/oracle/.ssh/id_rsa.
    Your public key has been saved in /users/oracle/.ssh/id_rsa.pub.
    The key fingerprint is:
    01:c8:48:01:f2:3d:a7:b4:cd:4a:9c:10:2d:ba:ef:4e oracle@linux1
    
    oracle@linux2:~> mkdir ~/.ssh            #远程服务器创建.ssh目录并赋予权限
    oracle@linux2:~> chmod 700 ~/.ssh
    
    oracle@linux1:~> scp ~/.ssh/id_rsa.pub 172.168.1.196:~/.ssh     #复制公钥到远程服务器，即机器linux2
    The authenticity of host '172.168.1.196 (172.168.1.196)' can't be established.
    RSA key fingerprint is 08:3d:69:80:85:1d:ce:57:32:e0:72:e0:38:66:0c:36.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '172.168.1.196' (RSA) to the list of known hosts.
    Password: 
    id_rsa.pub            100%  393     0.4KB/s   00:00  
    
    oracle@linux2:~> cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys  #将公钥追加到远程服务器的authorized_keys
    
    oracle@linux1:~> ssh 172.168.1.196 date                          #验证是否需要输入密码                                   
    Thu Aug 22 10:50:47 HKT 2013

2、使用tnsnames方式轮巡多服务器及实例

  #下面是通过tnsnames方式进行轮巡的shell脚本，也就是仅仅需要从远程服务器获取/etc/oratab下的所有实例即可
  #需要注意的：
  # a. 确保本地环境存在到所有远程服务器的tnsnames连接字符串，如果没有也没有关系，会收到错误
  # b. 需要所有被轮巡数据库的用户及密码并具有相应的权限
  # c. 如果所需要执行的sql属于比较复杂的情形，我们可以将sql语句写入到一个单独的文件，然后调用
  
  oracle@linux1:~> more query_multi_inst_tns.sh 
  # ------------------------------------------------------------------------------------+
  # Script Name: query_multi_inst_tns.sh                                                |
  # Desc:  This script login to different remote host where define in known_host file,  |
  #        after that look through oratab and execute SQL for each instance             | 
  #        in local machine by tnsnames.                                                |
  # Req:   Configure a secure shell by ssh-keygen to all remote host                    | 
  #        Add Oracle Net string to local tnsnames.ora for each remote instance         |
  # Author : Robinson                                                                   |
  # Blog   : http://blog.csdn.net/robinson_0612                                         |
  # ------------------------------------------------------------------------------------+
  #!/bin/bash
  
  # --------------------------------------------
  # 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.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 "------------------------------------"
          $ORACLE_HOME/bin/sqlplus -S usr/passwd@$db <<EOF
          col name format a30
          col value format a20
          select name,value from v\$parameter where name='open_cursors';
          exit
  EOF
      done
  done
  }|tee -a ${LOGFILE} 2>&1
  exit

3、直接在远程服务器环境轮巡所有实例

  #下面是直接在远程主机环境执行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  <<EOF
          col name format a30
          col value format a20
          select name,value from v\\"\$"parameter where name='open_cursors';
          exit
  EOF"
      done
  done
  }|tee -a ${LOGFILE} 2>&1
  exit

4、测试

#使用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

更多参考:
  Linux/Unix shell 脚本中调用SQL,RMAN脚本
  Linux/Unix shell sql 之间传递变量
  Linux/Unix shell 参数传递到SQL脚本
  Linux/Unix shell 调用 PL/SQL
  Linux/Unix shell 监控Oracle实例(monitor instance)
  Linux/Unix shell 监控Oracle监听器(monitor listener)
  Linux/Unix shell 监控Oracle告警日志(monitor alter log file)
  Linux/Unix shell 自动导出Oracle数据库
  Linux/Unix shell 自动 FTP 备份档案
  Linux/Unix shell 自动导入Oracle数据库
  Linux/Unix shell 自动发送AWR report
  Linux/Unix shell 自动发送AWR report(二)
  Linux/Unix shell 脚本清除归档日志文件
  Linux/Unix shell 脚本监控磁盘可用空间
  Oracle 测试常用表BIG_TABLE
  Oracle 性能相关常用脚本(SQL)
  Oracle OWI 等待事件视图(v$session_wait/v$session_event/v$system_event)
  Oracle 监控索引的使用率
  Linux 下RMAN备份shell脚本
  Oracle RMAN 清除归档日志
  sqlplus spool 到动态日志文件名
  基于catalog 创建RMAN存储脚本
  批量迁移Oracle数据文件，日志文件及控制文件
  中小型数据库 RMAN CATALOG 备份恢复方案(一)
  中小型数据库 RMAN CATALOG 备份恢复方案(二)
  中小型数据库 RMAN CATALOG 备份恢复方案(三)