Hello,
I'm running the following shell script called uphs_bkup_rpt.sh as user 'oracle' in one of our test environments:
#!/bin/bash
#Declare variables
. $HOME/.env_variables
ORACLE_SID=$1
ORAENV_ASK=NO
. oraenv
LOG_DIR=/$BACKUP_MOUNT/logs
MAIL_LIST="UPHSORACLEDBA@test.edu"
# Login to SQL Plus and execute the script
sqlplus -s / @$SCRIPT_DIR/uphs_bkup_rpt.sql
# Send email with script output attached
if [ -s $LOG_DIR/uphs_bkup_rpt.log ]
then
EMAIL="Oracle@Backup" mutt -s "UPHS - Oracle Backup Report" -a $LOG_DIR/$uphs_bkup_rpt.log $MAIL_LIST < /dev/null
fi
When executed from the script directory as ./uphs_bkup_rpt.sh TEST (with TEST as the value for the ORACLE_SID variable), I'm prompted for the ORACLE_HOME. The ORACLE_HOME is set in .$HOME/.env_variables.
I'm trying to run the shell script without any prompts so I can setup a cron job. The code below is in the SQL script being called from the shell script:
SET PAGESIZE 50
COL dbname FOR A12 HEADING 'Database'
COL status FOR A6 HEADING 'Status'
COL backup_date FOR A14 HEADING 'Backup|Date'
COL dow FOR A12 HEADING 'Day of|the Week'
COL scheduled FOR A10 HEADING 'Scheduled'
SPOOL $LOG_DIR/uphs_bkup_rpt.log
SELECT LOWER(dbname) dbname,
TO_CHAR(NULL) status,
TO_DATE(NULL) backup_date,
TO_CHAR(NULL) "DOW",
CASE WHEN TO_CHAR(SYSDATE,'DY')='MON' AND MON IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'TUE' AND TUE IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'WED' AND WED IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'THU' AND THU IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'FRI' AND FRI IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'SAT' AND SAT IN (0,1) THEN 'YES'
WHEN TO_CHAR(SYSDATE,'DY') = 'SUN' AND SUN IN (0,1) THEN 'YES'
ELSE 'NO'
END AS SCHEDULED
FROM backup_schedules
WHERE dbname NOT IN (SELECT LOWER(s.dbname)
FROM backup_status s
WHERE TO_DATE(backup_date, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY'))
UNION ALL
SELECT LOWER(dbname) dbname,
status,
backup_date,
TO_CHAR(backup_date, 'Day') "DOW",
'YES' SCHEDULED
FROM backup_status
WHERE TO_DATE(backup_date, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')
ORDER BY dbname ASC;
SPOOL OFF;
EXIT;
Any help is appreciated. Thanks!
Frank