Linux 使用 Shell 脚本检查数据库连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3779569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check database connectivity using Shell script
提问by mohona
I am trying to write a shell script to check database connectivity. Within my script I am using the command
我正在尝试编写一个 shell 脚本来检查数据库连接。在我的脚本中，我正在使用命令
sqlplus uid/pwd@database-schemaname
to connect to my Oracle database.
连接到我的 Oracle 数据库。
Now I want to save the output generated by this command (before it drops to SQL prompt) in a temp file and then grep / find the string "Connected to" from that file to see if the connectivity is fine or not.
现在我想将此命令生成的输出（在它降到 SQL 提示符之前）保存在一个临时文件中，然后 grep / 从该文件中查找字符串“Connected to”以查看连接是否正常。
Can anyone please help me to catch the output and get out of that prompt and test whether connectivity is fine?
任何人都可以帮我捕捉输出并退出该提示并测试连接是否正常？
采纳答案by Jochem
Use a script like this:
使用这样的脚本：
#!/bin/sh
echo "exit" | sqlplus -L uid/pwd@dbname | grep Connected > /dev/null
if [ $? -eq 0 ]
then
echo "OK"
else
echo "NOT OK"
fi
echo "exit" assures that your program exits immediately (this gets piped to sqlplus). -L assures that sqlplus won't ask for password if credentials are not ok (which would make it get stuck as well).
echo "exit" 确保您的程序立即退出（这会通过管道传输到 sqlplus）。-L 确保如果凭据不正确，sqlplus 不会要求输入密码（这也会使其卡住）。
(> /dev/null just hides output from grep, which we don't need because the results are accessed via $? in this case)
（> /dev/null 只是隐藏了 grep 的输出，我们不需要它，因为在这种情况下，结果是通过 $? 访问的）
回答by codaddict
You can avoid the SQL prompt by doing:
您可以通过执行以下操作来避免 SQL 提示：
sqlplus uid/pwd@database-schemaname < /dev/null
SqlPlus exits immediately.
SqlPlus 立即退出。
Now just grep the output of the above as:
现在只需将上面的输出 grep 为：
if sqlplus uid/pwd@database-schemaname < /dev/null | grep 'Connected to'; then
# have connectivity to Oracle
else
# No connectivity
fi
回答by baklarz2048
#!/bin/bash
output=`sqlplus -s "user/[email protected] " <<EOF
set heading off feedback off verify off
select distinct machine from v\$session;
exit
EOF
`
echo $output
if [[ $output =~ ERROR ]]; then
echo "ERROR"
else
echo "OK"
fi
回答by frayser
#! /bin/sh
if echo "exit;" | sqlplus UID/PWD@database-schemaname 2>&1 | grep -q "Connected to"
then echo connected OK
else echo connection FAIL
fi
Not knowing whether the "Connected to" message is put to standard output or standard error, this checks both. "qrep -q" instead of "grep... >/dev/null" assumes Linux.
不知道“已连接到”消息是放在标准输出还是标准错误中，这会检查两者。“qrep -q”而不是“grep...>/dev/null”假设Linux。
回答by abe
Here's a good option which does not expose the password on the command line
这是一个不错的选择，它不会在命令行上公开密码
#!/bin/bash
CONNECT_STRING=<USERNAME>/<PASS>@<SID>
sqlplus -s -L /NOLOG <<EOF
whenever sqlerror exit 1
whenever oserror exit 1
CONNECT $CONNECT_STRING
exit
EOF
SQLPLUS_RC=$?
echo "RC=$SQLPLUS_RC"
[ $SQLPLUS_RC -eq 0 ] && echo "Connected successfully"
[ $SQLPLUS_RC -ne 0 ] && echo "Failed to connect"
exit SQLPLUS_RC
回答by Mtruchado
none of the proposed solutions works for me, as my script is executed in machines running several countries, with different locales, I can't simply check for one String simply because this string in the other machine is translated to a different language. As a solution I'm using SQLcl
建议的解决方案都不适用于我，因为我的脚本是在运行多个国家/地区、具有不同语言环境的机器上执行的，我不能简单地检查一个字符串，因为另一台机器中的这个字符串被翻译成不同的语言。作为解决方案，我使用 SQLcl
https://www.oracle.com/database/technologies/appdev/sqlcl.html
https://www.oracle.com/database/technologies/appdev/sqlcl.html
which is compatible with all sql*plus scripts and allow you to test the database connectivity like this:
它与所有 sql*plus 脚本兼容，并允许您像这样测试数据库连接：
echo "disconnect" | sql -L $DB_CONNECTION_STRING > /dev/null || fail "cannot check connectivity with the database, check your settings"
回答by Mikael Lepist?
This was my one-liner for docker container to wait until DB is ready:
这是我的 docker 容器单线等待数据库准备就绪：
until sqlplus -s sys/Oracle18@oracledbxe/XE as sysdba <<< "SELECT 13376411 FROM DUAL; exit;" | grep "13376411"; do echo "Could not connect to oracle... sleep for a while"; sleep 3; done
And the same in multiple lines:
多行相同：
until sqlplus -s sys/Oracle18@oracledbxe/XE as sysdba <<< "SELECT 13376411 FROM DUAL; exit;" | grep "13376411";
do
echo "Could not connect to oracle... sleep for a while";
sleep 3;
done
So it basically does select with magic number and checks that correct number was actually returned.
所以它基本上确实使用幻数进行选择并检查实际返回的正确数字。
回答by Sai Krishna
#!/bin/sh
echo "exit" | sqlplus -S -L uid/pwd@dbname
if [ $? -eq 0 ]
then
echo "OK"
else
echo "NOT OK"
fi
For connection validation -S would be sufficient.
对于连接验证 -S 就足够了。
The "silent" mode doesn't prevent terminal output. All it does is:
“静默”模式不会阻止终端输出。它所做的只是：
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.
If you want to suppress all terminal output, then you'll need to do something like:
如果要抑制所有终端输出，则需要执行以下操作：
sqlplus ... > /dev/null 2>&1
