crontab上的sqlplus连接错误,但直接起作用 [英] sqlplus connection error on crontab but works directly
问题描述
我正在使用以下脚本来创建带有oracle查询输出的文本文件.当我手动尝试时,脚本可以正常工作.但是,当我将其放置到crontab时,它永远无法获得连接.通过自动化访问sqlplus有任何限制吗?
I'm using following script to create text file with oracle query output. The script work fine when I try it manually. But when I place it to crontab it is permanently fail to get the connection. Is there any restriction to access sqlplus through automation?
rm /export/home/oracle/out.csv
INC=0
while : ; do
INC=$(($INC+1))
if [[ "$INC" -eq 10 ]]; then
echo "Exit after multiple failed attempt to connect to the DB."
break
fi
sqlplus -s username/password@hostname.com:1552/servicename << EOF
set pagesize 10000
set feedback off
set heading off
set echo off
spool /export/home/oracle/out.csv
SET LINESIZE 10000
SET PAGESIZE 50
SELECT TRIM(COUNT(*)) FROM users;
SPOOL OFF
EXIT;
EOF
[[ -f "/export/home/oracle/out.csv" ]] && break
echo "Failed to connect to DB and retrying."
sleep 5
done
推荐答案
Cron实用程序使用"/bin/sh"作为默认外壳程序,因此您需要相应地设置Oracle环境.
Cron utility uses "/bin/sh" as default shell, so you need to setup Oracle environment accordingly.
您可以:
1.--有一个包含必要变量的配置文件.(*)
1.- Have a configuration file containing the necessary variables. (*)
2.-在脚本中硬编码必需的Oracle环境变量.
2.- Hard code the necessary Oracle environment variables in your script.
在您的情况下:
对于解决方案"1",您可以创建具有以下内容的 $ HOME/profile.12cR2 文件:
For solution "1", you can create $HOME/profile.12cR2 file with the following content:
export ORACLE_BASE=/oraclebin/app/oracle/product/databaseR2/base
export ORACLE_HOME=/oraclebin/app/oracle/product/databaseR2/12cR2
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export ORACLE_SID=db02
export EDITOR=vi
并按如下所示更新脚本:
And update your script as follows:
. $HOME/profile.12cR2
rm /export/home/oracle/out.csv
INC=0
while : ; do
INC=$(($INC+1))
if [[ "$INC" -eq 10 ]]; then
echo "Exit after multiple failed attempt to connect to the DB."
break
fi
...
(*)根据您的Oracle数据库基础结构更新此配置文件.
(*) Update this configuration file according your Oracle database infrastructure.
这篇关于crontab上的sqlplus连接错误,但直接起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!