在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 备份恢复方案(三)