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

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

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

[python]  view plain copy print ?

1. 要跨服务器自动执行脚本，得需要实现免密码自动登陆，然后才能在多个服务器之间跳转，因此我们首先需要生成登陆密钥。  
2. 生存登陆密钥需要三个步骤，在本地机器创建密钥，复制公钥要远程主机，将公钥追加到远程主机的authorized_keys  
3. 下面是一个操作示例:   
4.   
5.   oracle@linux1:~> mkdir ~/.ssh             #首先在本地创建.ssh目录并赋予权限  
6.   oracle@linux1:~> chmod 700 ~/.ssh  
7.   oracle@linux1:~> ssh-keygen -t rsa        #使用ssh-keygen生成密钥对，也可以使用dsa方式  
8.   Generating public/private rsa key pair.  
9.   Enter file in which to save the key (/users/oracle/.ssh/id_rsa):   
10.   Enter passphrase (empty for no passphrase):   
11.   Enter same passphrase again:   
12.   Your identification has been saved in /users/oracle/.ssh/id_rsa.  
13.   Your public key has been saved in /users/oracle/.ssh/id_rsa.pub.  
14.   The key fingerprint is:  
15.   01:c8:48:01:f2:3d:a7:b4:cd:4a:9c:10:2d:ba:ef:4e oracle@linux1  
16.     
17.   oracle@linux2:~> mkdir ~/.ssh            #远程服务器创建.ssh目录并赋予权限  
18.   oracle@linux2:~> chmod 700 ~/.ssh  
19.     
20.   oracle@linux1:~> scp ~/.ssh/id_rsa.pub 172.168.1.196:~/.ssh     #复制公钥到远程服务器，即机器linux2  
21.   The authenticity of host '172.168.1.196 (172.168.1.196)' can't be established.  
22.   RSA key fingerprint is 08:3d:69:80:85:1d:ce:57:32:e0:72:e0:38:66:0c:36.  
23.   Are you sure you want to continue connecting (yes/no)? yes  
24.   Warning: Permanently added '172.168.1.196' (RSA) to the list of known hosts.  
25.   Password:   
26.   id_rsa.pub            100%  393     0.4KB/s   00:00    
27.     
28.   oracle@linux2:~> cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys  #将公钥追加到远程服务器的authorized_keys  
29.     
30.   oracle@linux1:~> ssh 172.168.1.196 date                          #验证是否需要输入密码                                     
31.   Thu Aug 22 10:50:47 HKT 2013  

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

[python]  view plain copy print ?

1. #下面是通过tnsnames方式进行轮巡的shell脚本，也就是仅仅需要从远程服务器获取/etc/oratab下的所有实例即可  
2. #需要注意的：  
3. # a. 确保本地环境存在到所有远程服务器的tnsnames连接字符串，如果没有也没有关系，会收到错误  
4. # b. 需要所有被轮巡数据库的用户及密码并具有相应的权限  
5. # c. 如果所需要执行的sql属于比较复杂的情形，我们可以将sql语句写入到一个单独的文件，然后调用  
6.   
7. oracle@linux1:~> more query_multi_inst_tns.sh   
8. # ------------------------------------------------------------------------------------+  
9. # Script Name: query_multi_inst_tns.sh                                                |  
10. # Desc:  This script login to different remote host where define in known_host file,  |  
11. #        after that look through oratab and execute SQL for each instance             |   
12. #        in local machine by tnsnames.                                                |  
13. # Req:   Configure a secure shell by ssh-keygen to all remote host                    |   
14. #        Add Oracle Net string to local tnsnames.ora for each remote instance         |  
15. # Author : Robinson                                                                   |  
16. # Blog   : http://blog.csdn.net/robinson_0612                                         |  
17. # ------------------------------------------------------------------------------------+  
18. #!/bin/bash  
19.   
20. # --------------------------------------------  
21. # Set environment vairable and define variable  
22. # --------------------------------------------  
23.   
24. if [ -f ~/.bash_profile ]; then  
25.     . ~/.bash_profile  
26. fi  
27.   
28. ORATAB=/etc/oratab  
29. RHOST=~/.ssh/known_hosts  
30. LOGFILE=/users/robin/dba_scripts/custom/log/query_multi_inst.log  
31.   
32. # -------------------------------  
33. # take a loop in each hostname  
34. # -------------------------------  
35.   
36. {  
37. for host in `cat $RHOST | awk '{print $1}'`   
38. do  
39.     echo "************************************"  
40.     echo "Current host is $host."  
41.     echo "************************************"  
42.     echo ""  
43.   
44.     # ---------------------------  
45.     # take a loop in ORATAB file  
46.     # ---------------------------  
47.   
48.     for db in `ssh $host cat $ORATAB |egrep ':N|:Y'|grep -v \* | grep -v \# |cut -d ":" -f1`  
49.     do  
50.         echo "------------------------------------"  
51.         echo "Current database is $db.            "  
52.         echo "------------------------------------"  
53.         $ORACLE_HOME/bin/sqlplus -S usr/passwd@$db <<EOF  
54.         col name format a30  
55.         col value format a20  
56.         select name,value from v\$parameter where name='open_cursors';  
57.         exit  
58. EOF  
59.     done  
60. done  
61. }|tee -a ${LOGFILE} 2>&1  
62. exit  

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

[python]  view plain copy print ?

1. #下面是直接在远程主机环境执行SQL并轮巡的shell脚本，这种方式可以用于没有或不知道数据库用户及密码的情形  
2. #需要注意的：  
3. # a. 该方式使用了基于操作系统用户身份验证，应确保支持该方式  
4. # b. 如果需要执行的SQL语句比较复杂，直接嵌套的shell脚本也复杂。如果写成.sql文件，每一个服务器需要存在一个副本  
5.   
6. oracle@linux1:~> more query_multi_inst_notns.sh   
7. #!/bin/bash  
8. # ------------------------------------------------------------------------------------+  
9. # Script Name: query_multi_inst_notns.sh                                              |  
10. # Desc:  This script login to different remote host where define in known_host file,  |   
11. #        after that look through oratab and execute SQL in each remote instance.      |    
12. # Req:   Configure a secure shell by ssh-keygen to all remote host                    |  
13. # Author : Robinson                                                                   |  
14. # Blog   : http://blog.csdn.net/robinson_0612                                         |  
15. # ------------------------------------------------------------------------------------+  
16. #  
17. # --------------------------------------------  
18. # Set environment vairable and define variable  
19. # --------------------------------------------  
20.   
21. if [ -f ~/.bash_profile ]; then  
22.     . ~/.bash_profile  
23. fi  
24.   
25. ORATAB=/etc/oratab  
26. RHOST=~/.ssh/known_hosts  
27. LOGFILE=/users/robin/dba_scripts/custom/log/query_multi_inst_notns.log  
28.   
29. # -------------------------------  
30. # take a loop in each hostname  
31. # -------------------------------  
32.   
33. {  
34. for host in `cat $RHOST | awk '{print $1}'`   
35. do  
36.     echo "************************************"  
37.     echo "Current host is $host."  
38.     echo "************************************"  
39.     echo ""  
40.   
41.     # ---------------------------  
42.     # take a loop in ORATAB file  
43.     # ---------------------------  
44.   
45.     for db in `ssh $host cat $ORATAB |egrep ':N|:Y'|grep -v \* | grep -v \# |cut -d ":" -f1`  
46.     do  
47.         echo "------------------------------------"  
48.         echo "Current database is $db.            "  
49.         echo "------------------------------------"  
50.         home=`ssh $host cat $ORATAB |egrep ':N|:Y'|grep -v \* | grep -v \# | grep $db |cut -d ":" -f2`  
51.         ssh $host "  
52.         export ORACLE_SID=$db  
53.         export ORACLE_HOME=${home}  
54.         ${ORACLE_HOME}/bin/sqlplus -S / as sysdba  <<EOF  
55.         col name format a30  
56.         col value format a20  
57.         select name,value from v\\"\$"parameter where name='open_cursors';  
58.         exit  
59. EOF"  
60.     done  
61. done  
62. }|tee -a ${LOGFILE} 2>&1  
63. exit  

4、测试

[python]  view plain copy print ?

1. #使用tnsnames方式测试  
2. oracle@linux1:~> ./query_multi_inst_tns.sh   
3. ************************************  
4. Current host is 172.168.2.196.  
5. ************************************  
6.   
7. ------------------------------------  
8. Current database is US001.              
9. ------------------------------------  
10.   
11. NAME                           VALUE  
12. ------------------------------ --------------------  
13. open_cursors                   300  
14.   
15. ------------------------------------  
16. Current database is US002.              
17. ------------------------------------  
18.   
19. NAME                           VALUE  
20. ------------------------------ --------------------  
21. open_cursors                   300  
22.   
23. ------------------------------------  
24. Current database is US003.            #这个是没有tnsnames的情形  
25. ------------------------------------  
26. ERROR:  
27. ORA-12154: TNS:could not resolve the connect identifier specified  
28.   
29.   
30. #直接在远程服务器环境轮巡测试  
31. oracle@linux1:~> ./query_multi_inst_notns.sh   
32. ************************************  
33. Current host is 172.168.1.196.  
34. ************************************  
35.   
36. ------------------------------------  
37. Current database is US001.              
38. ------------------------------------  
39.   
40. NAME                           VALUE  
41. ------------------------------ --------------------  
42. open_cursors                   300  
43.   
44. ------------------------------------  
45. Current database is US002.              
46. ------------------------------------  
47.   
48. NAME                           VALUE  
49. ------------------------------ --------------------  
50. open_cursors                   300  
51.   
52. ------------------------------------  
53. Current database is US005.            #远程服务器上的实例没有启动的情形  
54. ------------------------------------  
55.         select name,value from v$parameter where name='open_cursors'  
56. *  
57. ERROR at line 1:  
58. ORA-01034: ORACLE not available