How to solve the problem of running a script in Linux with an Oracle connection?

Question

Oracle

#!/bin/sh

# v15

usage ()
{
     echo ""
   echo " Full database backup script."
   echo ""
     echo " usage: $0 ORACLE_SID RETENTION_DAYs PARALLELISM PATH_TO_BACKUP PATH_TO_LOGS EMAIL_TO"
     echo " example: $0 ____ 14 4 /backup/______ /backup/logs [email protected]"
     echo " Use 'none' for EMAIL_TO if you have not email"
     echo " example: $0 ____  14 4 /backup/_____ /backup/logs none"
     echo ""
}

if [ X`whoami`X != 'XoracleX' ]; then
  echo "You must be logged in as oracle to run $0 ."
  echo "Log in as oracle and restart $0 execution."
  exit 1
fi

if [ $# != 6 ]
then
    usage
    exit
fi



export ORACLE_SID=$1 
RETENTION=$2
PARALLELISM=$3
PATH_TO_BACKUP=$4
PATH_TO_LOGS=$5
EMAIL_TO=$6


if [ `grep ^$ORACLE_SID: /etc/oratab | wc -l` -eq 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 echo ${subject}
 echo 'Please add database description to the /etc/oratab file'
 [ ${EMAIL_TO} != "none" ] && echo 'Please add database description to the /etc/oratab file' | mail -s "${subject}" "${EMAIL_TO}"
 exit 1
fi

if [ `grep ^$ORACLE_SID: /etc/oratab | wc -l` -gt 1 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 echo ${subject}
 echo 'Please remove duplicate lines from the /etc/oratab'
 [ ${EMAIL_TO} != "none" ] && echo 'Please remove duplicate lines from the /etc/oratab' | mail -s "${subject}" "${EMAIL_TO}"
 exit 1
fi

export PATH=/usr/local/bin:$PATH
export ORAENV_ASK=NO
. oraenv

rman=$ORACLE_HOME/bin/rman
export NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
export NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"

START_TIME=`date +%s`

HOST=`hostname`

LOG_BACKUP=${PATH_TO_LOGS}/FullBackupRMAN_${HOST}_${ORACLE_SID}_`date +%Y-%m-%d_%H-%M-%S`.log

ERROR_TEXT=`mkdir -p "${PATH_TO_BACKUP}" "${PATH_TO_LOGS}" 2>&1`

rc=$?

if [ ${rc} -ne 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 echo ${subject}
 echo ${ERROR_TEXT}
 [ ${EMAIL_TO} != "none" ] && echo ${ERROR_TEXT} | mail -s "${subject}" "${EMAIL_TO}"
 exit ${rc}
fi

ERROR_TEXT=`touch "${PATH_TO_BACKUP}/test_file" "${PATH_TO_LOGS}/test_file" 2>&1`

rc=$?

if [ ${rc} -ne 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 echo ${subject}
 echo ${ERROR_TEXT}
 [ ${EMAIL_TO} != "none" ] && echo ${ERROR_TEXT} | mail -s "${subject}" "${EMAIL_TO}"
 exit ${rc}
fi

result=`echo "select 'DBS='||open_mode open_mode from v\$database;" | sqlplus -S "/ as sysdba" | grep -c "DBS=READ WRITE"`

if [ ${result} -ne 1 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 ERROR_TEXT="Databae not open"
 echo ${subject}
 echo ${ERROR_TEXT}
 [ ${EMAIL_TO} != "none" ] && echo ${ERROR_TEXT} | mail -s "${subject}" "${EMAIL_TO}"
 exit 1
fi

cat <<EOF | sqlplus -S "/ as sysdba" | tee $PATH_TO_BACKUP/.test_$ORACLE_SID.sh | grep ^ORA-
set heading off feedback off trimspool on linesize 500 pages 0 define off
select distinct substr(name,1,instr(name,'/',-1,1)-1) name from (
select 'DBF,'||name name from v\$datafile
union
select 'DBF,'||name name from v\$tempfile
union
select 'CTL,'||name name from v\$controlfile
union
select 'DBF,'||member name from v\$logfile)
union
select distinct 'DUMP,'||value from v\$parameter
where name in ('log_archive_dest','user_dump_dest',
 'audit_file_dest','background_dump_dest','core_dump_dest')
 and value is not null; 
EOF

[ `grep ^ORA- $PATH_TO_BACKUP/.test_$ORACLE_SID.sh | wc -l` -gt 0 ] && echo "Some error when invoke sqlplus" && exit 1

ERROR_TEXT=`cp $PATH_TO_BACKUP/.test_$ORACLE_SID.sh $PATH_TO_BACKUP/test_$ORACLE_SID.sh 2>&1`

rc=$?

if [ ${rc} -ne 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} backup ERROR"
 echo ${subject}
 echo "${ERROR_TEXT}"
 [ ${EMAIL_TO} != "none" ] && echo "${ERROR_TEXT}" | mail -s "${subject}" "${EMAIL_TO}"
 exit ${rc}
fi

cat <<EOF | $rman target / 2>&1 | tee >> ${LOG_BACKUP}
run {
 configure retention policy to recovery window of ${RETENTION} days;
 configure controlfile autobackup off;
 configure controlfile autobackup format for device type disk to '${PATH_TO_BACKUP}/bkp_CTL_%F';
 configure default device type to disk;
 configure device type disk parallelism ${PARALLELISM};
 configure channel device type disk format '${PATH_TO_BACKUP}/bkp_%U' maxpiecesize 2000M maxopenfiles 10;
 crosscheck archivelog all;
 crosscheck backup;
 delete noprompt expired backup;
 delete noprompt expired copy;
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;
 backup as compressed backupset database filesperset 1 tag 'backup';
 backup current controlfile format '${PATH_TO_BACKUP}/bkp_CTL_%U' tag 'backup';
 backup current controlfile for standby format '${PATH_TO_BACKUP}/bkp_SCTL_%U' tag 'backup';
 backup current controlfile format '${PATH_TO_BACKUP}/CTL' tag 'backup' reuse;
 backup spfile format '${PATH_TO_BACKUP}/SPFILE' tag 'backup' reuse;
}
list backup;
EOF

rc=$?

TOTAL_TIME=$((`date +%s`/60-${START_TIME}/60))

if [ ${rc} -ne 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} database backup ERROR elapsed ${TOTAL_TIME} minutes"
 echo ${subject}
 echo -e " \\n LOG is ${LOG_BACKUP}\\n\\nErrors: \\n`grep -i -E "RMAN-|ORA-|error" ${LOG_BACKUP} 2> /dev/null`"
 [ ${EMAIL_TO} != "none" ] && echo -e " \\n LOG is ${LOG_BACKUP}\\n\\nErrors: \\n`grep -i -E "RMAN-|ORA-|error" ${LOG_BACKUP} 2> /dev/null`" | mail -s "${subject}" "${EMAIL_TO}"
 exit ${rc}
fi
 
NUM_ERRORS=`grep -c -i -E "RMAN-|ORA-|error" ${LOG_BACKUP}`
 
if [ ${NUM_ERRORS} -ne 0 ] ; then
 subject="Database ${ORACLE_SID} at ${HOST} database backup ERROR elapsed ${TOTAL_TIME} minutes"
 echo ${subject}
 echo -e " \\n LOG is ${LOG_BACKUP}\\n\\nErrors: \\n`grep -i -E "RMAN-|ORA-|error" ${LOG_BACKUP} 2> /dev/null`"
 [ ${EMAIL_TO} != "none" ] && echo -e " \\n LOG is ${LOG_BACKUP}\\n\\nErrors: \\n`grep -i -E "RMAN-|ORA-|error" ${LOG_BACKUP} 2> /dev/null`" | mail -s "${subject}" "${EMAIL_TO}"
 exit ${rc}
fi

subject="Database ${ORACLE_SID} at ${HOST} database backup COMPLETED elapsed ${TOTAL_TIME} minutes"
echo ${subject}
echo -e " \\n LOG is ${LOG_BACKUP}\\n"
[ ${EMAIL_TO} != "none" ] && echo -e " \\n LOG is ${LOG_BACKUP}\\n\\n`cat ${LOG_BACKUP} 2> /dev/null`" | mail -s "${subject}" "${EMAIL_TO}"

#find ${PATH_TO_LOGS} -maxdepth 1 -name '*.log' -mtime +30 -type f -exec rm -f {} \;

when running this script, an error occurs in the log: The Oracle base remains unchanged with value /opt/oracle
Database r45GZK1 at r45-GKN-P-DB0 backup ERROR
Databae not open
It seems to me that the problem is in connecting to the database. although I could be wrong. can anyone suggest what could be the problem?

Answer 1

Judging by the "Databae not open" error, your instance is not running at all. :)