This is my first script calling Oracle SQLPlus.
- The /nolog option takes care of hiding the password from ps and similar tools.
- The command line will be 'sqlplus -S user/password@host:port/SID' without /nolog.
- The line 'host sleep 60' can be used in the script to delay execution.
- The 'set' parameters can be grouped together.
- sqlplus should use encrypted connection
- Make sure the script itself is not readable by unauthorized people.
- The script is written in AIX ksh.
Some details were changed, the blocks are part of a working script. You are right if you think there are better ways for handling certain conditions, the script is being improved.
There are some Oracle variables required for sqlplus.
NLS_LANG=<optional>ORA_NLS33=<optional>ORACLE_BASE=<set either this or ORACLE_HOME>ORACLE_HOME=<>PATH=$PATH:<path to sqlplus> TNS_ADMIN= export NLS_LANG ORA_NLS33 ORACLE_BASE ORACLE_HOME PATH TNS_ADMIN As an example, we query an update timestamp from the DB, here, the time when a currency rate was updated.
Unfortunately, I'm still getting a blank line after each select result, so I must handle that later.
query_someapp1_update() { sqlplus -S /nolog <<EOF connect someapp1/password@host:port/SID; set echo off; set heading off; set newpage none; set pagesize 0; -- select distinct value1 from table1 where field1 = 'F1' and curr = 'EUR='; select distinct value2 from table1 where field1 = 'F2' and curr = 'EUR='; -- select to_char (sysdate,'DD MON YYYY') from dual; select to_char (sysdate - 1/24,'HH24:MI') from dual; EOF } Query someapp2's status
query_someapp2_status() { sqlplus -S /nolog <<EOF connect someapp2/password@host:port/SID; set echo off; set heading off; set newpage none; set pagesize 0; select status from sometable; EOF } This function will restart our standalone Java app, "someapp1" if the timestamp shows a defined delay.
restart_someapp1 () { if [ $(expr "$systemtime_in_mins" - "$APPLtime_in_mins") -ge "$allowed_diff" ]; then echo "$(date +%F-%T) [INFO ] Restarting" kill -9 $(ps -u someapp1 -o pid,comm | awk '/java/ {print $1}') su - someapp1 -c ~/someapp1/start_someapp1.sh else echo "$(date +%F-%T) [INFO ] someapp1 check OK" fi } Check if someapp2 should be open
check_status() { if [ $(date +%H%M) -ge 1700 ] || [ $(date +%H%M) -le 0800 ]; then # someapp2 is closed, not checking continue elif [ $(query_someapp2_open | xargs echo) -ne 1 ]; then echo "$(date +%F-%T) [ERROR] someapp2 should be open, but it is closed." else echo "$(date +%F-%T) [INFO ] someapp2 check OK" fi } The main part of the script. xargs comes in the picture, as I need the results on a single line. NB: whitespace is not handled here!
## Query the someapp1 update and the system time from the DB, join the result on a single line, store the result in an array set -A times $(query_someapp1_update | xargs echo | sed 's/\:/ /g') ## Do nothing right after 00:00 to the amount of allowed time difference to avoid messing around with numbers if [ "${times[2]}""${times[3]}" -lt "$allowed_diff" ]; then # This should evaluate to something like '0008 is less than 10' exit 0 fi ## Convert hours to minutes someapp1_time_in_mins=$(expr "${times[0]}" \* 60 + "${times[1]}") systemtime_in_mins=$(expr "${times[2]}" \* 60 + "${times[3]}") Then comes the actual execution of the functions.