在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 备份恢复方案(三)
扫一扫
3398
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
