select shell

admin

1.使用场景
从一个本地装有instant client得客户端，在sql脚本目录里选择一个sql,在ip地址池里选择一个ip，查看sql得执行结果，前提是每套库里都有一个固定得监控账户。（tns连接，远端数据库监听要开启）
2.使用过程
执行t.sh，输入要执行得sql编号，输入要查询得数据库编号，查看结果。

1. [oracle@vm1 dbmonitor]$ sh t.sh
   
2. ======================================================================
   
3. ==              SINGLE SQL IN SINGLE DATABASE                       ==
   
4. ==                   AUTHOR:ZHANGYU                                 ==
   
5. ==     demo:selected sql==>selected database==>results              ==
   
6. ======================================================================
   
7. 
   
8. SETP1:WE HAVE QUERY SCRIPTS FOLLOWING,PLEASE SELECT ONE SQL YOU WANTED.
   
9. 
   
10. 1) actsess.sql           20) job.sql
    
11. 2) asmdg.sql           21) parameter.sql
    
12. 3) autotask.sql         22) profile.sql
    
13. 4) awrsetting.sql         23) recyclebin.sql
    
14. 5) bchit.sql           24) redofile.sql
    
15. 6) controlfile.sql         25) registry_history.sql
    
16. 7) cursorusage.sql         26) registry.sql
    
17. 8) database.sql         27) rmanjob.sql
    
18. 9) datafile.sql         28) rman.sql
    
19. 10) dblink.sql           29) scndays.sql
    
20. 11) dboption.sql         30) sequence.sql
    
21. 12) dbproperty.sql         31) sessusage.sql
    
22. 13) dbtime.sql           32) tablespace.sql
    
23. 14) directory.sql         33) userdata.sql
    
24. 15) grant.sql           34) user_roles.sql
    
25. 16) info_database_summary.sql  35) usersegsum.sql
    
26. 17) instance.sql         36) users.sql
    
27. 18) invalididx.sql         37) utinsys.sql
    
28. 19) invalidobj.sql
    
29. please select a num from menu：17
    
30. 
    
31. Selected sql_statement: [ instance.sql ]
    
32. 
    
33. STEP2:WE HAVE AVALIABLE DATABASES FOLLOWING,PLEASE SELECT ONE DATABASE YOU WANTED.
    
34. 
    
35. 1) 192.168.56.101:1511/ora11
    
36. 2) 192.168.56.101:1511/ora11a
    
37. please select a num from menu：1
    
38. 
    
39. Selected Database: [ 192.168.56.101:1511/ora11 ]
    
40. 
    
41. STEP3:THE RESULTS AS FOLLOWING.
    
42. 
    
43. 
    
44. DB_NAME       CHK_DATE  INSTANCE_NAME    INSTANCE_NUMBER VERSION         PAR HOST_NAME                  STARTUP_T    UPTIME STATUS
    
45. ------------------------- --------- ---------------- --------------- ----------------- --- ---------------------------------------------------------------- --------- ---------- ------------
    
46. 192.168.56.101:1511/ora11 06-NOV-22 ora11         1 11.2.0.4.0        NO  vm1                    05-NOV-22        .3 OPEN

复制代码

2.交互式输入用户名和密码

1. ############################################################################
   
2. #author:           zhangyu
   
3. #version:          V1.0
   
4. #production        godba studio           
   
5. #last modified     2022-10-28
   
6. ############################################################################
   
7. 
   
8. #!/bin/bash
   
9. echo "======================================================================"
   
10. echo "==              SINGLE SQL IN SINGLE DATABASE                       =="
    
11. echo "==                   AUTHOR:ZHANGYU                                 =="
    
12. echo "==     demo:selected sql==>selected database==>results              =="
    
13. echo "======================================================================"
    
14. sleep 1
    
15. echo
    
16. 
    
17. #set envirments
    
18. dbc_dir=/home/oracle/dbmonitor/dbs
    
19. sql_dir=/home/oracle/dbmonitor/sqls
    
20. res_dir=/home/oracle/dbmonitor/res
    
21. chk_date=`date +%Y%m%d_%H%m`
    
22. username=dbsnmp
    
23. password=dbsnmp
    
24. ORACLE_HOME=/home/oracle/dbmonitor/instantclient_19_17
    
25. PATH=/home/oracle/dbmonitor/instantclient_19_17:$PATH
    
26. LD_LIBRARY_PATH=/home/oracle/dbmonitor/instantclient_19_17
    
27. PS3="please select a num from menu："
    
28. 
    
29. #get username if not configuared
    
30. #read -r -p "please enter username:" username
    
31. #get password if not configuared
    
32. #read -r -s -p "please enter password:" password
    
33. 
    
34. # check user is oracle or not
    
35. [ $USER != 'oracle' ] && echo "please run the script with oracle user! " && exit 1
    
36. 
    
37. # check sqlplus is available
    
38. sqlplus -v>/dev/null
    
39. [ $? -ne 0 ] && echo -e "\033[31mERROR! sqlplus not found.\033[0m" && exit 1
    
40. 
    
41. # check sqlplus version >12 or not
    
42. client_version=`sqlplus -v|awk '{print $3}'|awk -F[.] '{print $1}'`
    
43. [ $client_version -lt 12 ] && echo -e "\033[31mERROR! sqlplus version must equal to or greater than 12.2.\033[0m" && exit 1
    
44.   
    
45. #LOAD SECRIPTS
    
46. echo -e "\033[31mSETP1:WE HAVE QUERY SCRIPTS FOLLOWING,PLEASE SELECT ONE SQL YOU WANTED.\033[0m"
    
47. echo
    
48. select sql_stmt in $( ls $sql_dir )
    
49.   do
    
50.           export SQL_STMT=$sql_stmt
    
51.           echo
    
52.           printf "`echo "Selected sql_statement: ["` `echo -e "\033[33;5m${SQL_STMT}\033[0m"` `echo "]"`\n"
    
53.           echo
    
54.           break
    
55.   done
    
56. 
    
57. #LOAD DATABASE CONNECT STRINGS
    
58. echo -e "\033[31mSTEP2:WE HAVE AVALIABLE DATABASES FOLLOWING,PLEASE SELECT ONE DATABASE YOU WANTED.\033[0m"
    
59. echo
    
60. select db_con in $( cat $dbc_dir/db_cons |awk -F [@] '{print $2}' )
    
61.   do
    
62.            export DN=$db_con
    
63.            echo
    
64.            printf "`echo "Selected Database: ["` `echo -e "\033[33;5m${DN}\033[0m"` `echo "]"`\n"
    
65.            echo
    
66.            break
    
67.   done
    
68. 
    
69. #print results of items as selected above   
    
70. echo -e "\033[31mSTEP3:THE RESULTS AS FOLLOWING.\033[0m"
    
71. echo
    
72. sqlplus -S $username/$password@$DN<<EOF
    
73. SET LINESIZE 300
    
74. SET PAGESIZE 2000
    
75. SET VERIFY OFF
    
76. SET ECHO OFF
    
77. define dn=$DN
    
78. @$sql_dir/$SQL_STMT
    
79. exit;
    
80. EOF
    
81. exit

复制代码