crontab上的sqlplus连接错误,但直接起作用 [英] sqlplus connection error on crontab but works directly

查看:65
本文介绍了crontab上的sqlplus连接错误,但直接起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用以下脚本来创建带有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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆