shell连接多个实例查询脚本

admin

在实际得维护工作中，可能需要同时在多个实例中对比同一个查询结果，比如，要得到在运生产库100多套实例得字符集设置，这样得工作用shell可以大大减少人力得支出。本文做简单示例：
1.准备环境，一套19c得cdb,里面有两个pdb,拉起所有pdb和监听。

1. [oracle@vm1 ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 13 08:04:07 2020
   
4. Version 19.7.0.0.0
   
5. 
   
6. Copyright (c) 1982, 2020, Oracle.  All rights reserved.
   
7. 
   
8. Connected to an idle instance.
   
9. 
   
10. SQL> startup
    
11. ORACLE instance started.
    
12. 
    
13. Total System Global Area 2516582152 bytes
    
14. Fixed Size                  9137928 bytes
    
15. Variable Size             822083584 bytes
    
16. Database Buffers         1677721600 bytes
    
17. Redo Buffers                7639040 bytes
    
18. Database mounted.
    
19. Database opened.
    
20. SQL> alter pluggable database all open;
    
21. 
    
22. Pluggable database altered.
    
23. 
    
24. SQL> !lsnrctl start
    
25. 
    
26. LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-JUN-2020 08:04:42
    
27. 
    
28. Copyright (c) 1991, 2019, Oracle.  All rights reserved.
    
29. 
    
30. Starting /app/oracle/db/19.3.0/home1/bin/tnslsnr: please wait...
    
31. 
    
32. TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    
33. System parameter file is /app/oracle/db/19.3.0/home1/network/admin/listener.ora
    
34. Log messages written to /app/oracle/db/diag/tnslsnr/vm1/listener/alert/log.xml
    
35. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))
    
36. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    
37. 
    
38. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm1)(PORT=1521)))
    
39. STATUS of the LISTENER
    
40. ------------------------
    
41. Alias                     LISTENER
    
42. Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    
43. Start Date                13-JUN-2020 08:04:43
    
44. Uptime                    0 days 0 hr. 0 min. 0 sec
    
45. Trace Level               off
    
46. Security                  ON: Local OS Authentication
    
47. SNMP                      OFF
    
48. Listener Parameter File   /app/oracle/db/19.3.0/home1/network/admin/listener.ora
    
49. Listener Log File         /app/oracle/db/diag/tnslsnr/vm1/listener/alert/log.xml
    
50. Listening Endpoints Summary...
    
51.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))
    
52.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    
53. The listener supports no services
    
54. The command completed successfully
    
55. 
    
56. SQL> show pdbs;
    
57. 
    
58.     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    
59. ---------- ------------------------------ ---------- ----------
    
60.          2 PDB$SEED                       READ ONLY  NO
    
61.          3 ZHYV                           READ WRITE NO
    
62.          4 ZHYU                           READ WRITE NO
    
63. 
    
64. SQL> !lsnrctl status
    
65. 
    
66. LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-JUN-2020 08:06:48
    
67. 
    
68. Copyright (c) 1991, 2019, Oracle.  All rights reserved.
    
69. 
    
70. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm1)(PORT=1521)))
    
71. STATUS of the LISTENER
    
72. ------------------------
    
73. Alias                     LISTENER
    
74. Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    
75. Start Date                13-JUN-2020 08:04:43
    
76. Uptime                    0 days 0 hr. 2 min. 4 sec
    
77. Trace Level               off
    
78. Security                  ON: Local OS Authentication
    
79. SNMP                      OFF
    
80. Listener Parameter File   /app/oracle/db/19.3.0/home1/network/admin/listener.ora
    
81. Listener Log File         /app/oracle/db/diag/tnslsnr/vm1/listener/alert/log.xml
    
82. Listening Endpoints Summary...
    
83.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))
    
84.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    
85. Services Summary...
    
86. Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
    
87.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
88. Service "a5930a2320e01e62e0536538a8c08fdd" has 1 instance(s).
    
89.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
90. Service "a5a4fb52bba60ad1e0536538a8c0fd8b" has 1 instance(s).
    
91.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
92. Service "ora19c" has 1 instance(s).
    
93.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
94. Service "ora19cXDB" has 1 instance(s).
    
95.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
96. Service "zhyu" has 1 instance(s).
    
97.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
98. Service "zhyv" has 1 instance(s).
    
99.   Instance "ora19c", status READY, has 1 handler(s) for this service...
    
100. The command completed successfully
     

复制代码

2.准备脚本

1. [oracle@vm1 tmp]$ cd dbs
   
2. [oracle@vm1 dbs]$ ll
   
3. total 4
   
4. -rw-rw-r-- 1 oracle oracle 412 Jun 13 00:45 check.sh    ------------------执行得shell
   
5. drwxrwxr-x 2 oracle oracle  40 Jun 13 00:50 dbs            ------------------需要查询得实例列表
   
6. drwxrwxr-x 2 oracle oracle  40 Jun 13 00:48 res             ------------------查询结果存放目录
   
7. drwxrwxr-x 2 oracle oracle 220 Apr  5 22:12 sqls           ------------------查询语句存放目录
   

复制代码

3.脚本详解
dbs下有个实列列表，用简单连接得方式，是明文得，有兴趣得朋友可以改成加密得：

1. [oracle@vm1 dbs]$ cd dbs
   
2. [oracle@vm1 dbs]$ ls
   
3. db_con_103  dbs_con1
   
4. [oracle@vm1 dbs]$ cat dbs_con1
   
5. dbsnmp/dbsnmp@192.168.56.101:1521/zhyu
   
6. dbsnmp/dbsnmp@192.168.56.101:1521/zhyv
   
7. [oracle@vm1 dbs]$

复制代码

res下是存放sql语句得查询结果得，每个sql一个结果文件，以日期+sql名字为前缀：

1. [oracle@vm1 dbs]$ cd res
   
2. [oracle@vm1 res]$ ls
   
3. 20200613.character.sql.log
   
4. [oracle@vm1 res]$ ll
   
5. total 4
   
6. -rw-rw-r-- 1 oracle oracle 82 Jun 13 00:48 20200613.character.sql.log
   
7. [oracle@vm1 res]$ cat 20200613.character.sql.log
   
8. 
   
9. 192.168.56.101:1521/zhyu       AL32UTF8
   
10. 
    
11. 192.168.56.101:1521/zhyv       AL32UTF8
    

复制代码

sql下存放着常用得或者自定义得sql语句：

1. [oracle@vm1 dbs]$ cd sqls
   
2. [oracle@vm1 sqls]$ ls
   
3. character.sql  ckbingdu.sql  db_file_pct.sql  info_archived_log.sql  info_asm_diskgroup.sql  info_db_user.sql  info_redo_swith.sql  instance.sql  sess_cnt.sql
   
4. [oracle@vm1 sqls]$ cat character.sql
   
5. SET ECHO        OFF
   
6. SET FEEDBACK    off
   
7. SET HEADING     off
   
8. SET LINESIZE    180
   
9. SET PAGESIZE    2000
   
10. SET TIMING      OFF
    
11. SET TRIMSPOOL   ON
    
12. SET VERIFY      OFF
    
13. CLEAR COLUMNS BREAKS COMPUTES
    
14. COLUMN db_con_name                    FORMAT a30      HEADING "Database_connect"
    
15. COLUMN character_set                  FORMAT a80      HEADING "Character_Set"
    
16. 
    
17. select '&dn' db_con_name,value character_set from nls_database_parameters where parameter='NLS_CHARACTERSET';

复制代码

check.sh是一个调用脚本，执行时先读取实例列表，再选择使用哪一个sql语句，然后循环查询，把结果都写到一个日志上面：

1. [oracle@vm1 dbs]$ cat check.sh
   
2. #!/bin/bash
   
3. source ~/.bash_profile
   
4. time=`date '+%Y%m%d'`
   
5. dbc_dir=/tmp/dbs/dbs
   
6. sql_dir=/tmp/dbs/sqls
   
7. res_dir=/tmp/dbs/res
   
8. ls $sql_dir
   
9. echo -n "enter sql you want:"
   
10. read sql
    
11. rm -f $res_dir/$sql.log
    
12. for i in `cat $dbc_dir/dbs_con1`
    
13. do
    
14. dn=`echo $i|awk -F [@] '{print $2}'`
    
15. echo $dn
    
16. sqlplus -S $i<<EOF
    
17. set verify off
    
18. set termout off
    
19. spool $res_dir/$time.$sql.log append
    
20. define dn=$dn
    
21. @$sql_dir/$sql dn
    
22. exit;
    
23. EOF
    
24. done

复制代码

4.运行过如下：

1. [oracle@vm1 dbs]$ sh check.sh
   
2. character.sql  ckbingdu.sql  db_file_pct.sql  info_archived_log.sql  info_asm_diskgroup.sql  info_db_user.sql  info_redo_swith.sql  instance.sql  sess_cnt.sql
   
3. enter sql you want:character.sql
   
4. 192.168.56.101:1521/zhyu
   
5. 192.168.56.101:1521/zhyv
   
6. [oracle@vm1 dbs]$ cd res
   
7. [oracle@vm1 res]$ ls
   
8. 20200613.character.sql.log
   
9. [oracle@vm1 res]$ cat 20200613.character.sql.log
   
10. 
    
11. 192.168.56.101:1521/zhyu       AL32UTF8
    
12. 
    
13. 192.168.56.101:1521/zhyv       AL32UTF8

复制代码