# external scripts needed
# ${HOME}/scripts/bin/set_oracle_env.sh       set ORACLE_ENVIRONMENT variables
#!/bin/bash
# set -x
# This is a hot backup script that will get run from cron backing each database.
# Each days files will be prefixed with numbered day of the week. This is done
# so that the script will not need to rename any files. This script will do a hot backup
# of each data file, the spfile, and the control file will be backed up directly
# as well as to trace so that we have a SQL file that could get run to recreate
# the control file. The script will delete anything in the backup directory
# older than 2 days.
if [ $# -lt 1 ]; then
echo "Backup failed due to no ORACLE_SID on command line." >&2
exit 1
else
SID=$1
fi

export TEMP_DIR="/tmp"

# Obtain the Oracle environment from set_oracle_env.sh
# I need to hardcode ${HOME}/scripts because it sets
# the BIN_DIR environment variable
. ${HOME}/scripts/bin/set_oracle_env.sh ${1}

export DATEFORMAT="%Y%m%d"
export TODAY=`date +${DATEFORMAT}`
export CURRENT_BACKUP_DIR="${BACKUP_DIR_BASE}/${ORACLE_SID}/bckup_${TODAY}"
export ADMIN="<your_email_address>"
# Check to see if todays directory exists,
# if it does trash it and recreate it.
if [ -e $CURRENT_BACKUP_DIR ] ;
then
rm -rf $CURRENT_BACKUP_DIR
fi
mkdir  $CURRENT_BACKUP_DIR
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF \
1>> ${CURRENT_BACKUP_DIR}/ora_backup_${ORACLE_SID}.log 2>> ${CURRENT_BACKUP_DIR}/error_${ORACLE_SID}.log
SET TERM OFF
SET ECHO OFF
SET FEEDBACK OFF
SET DEFINE OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SPOOL ${CURRENT_BACKUP_DIR}/oracle_hot_backup_${ORACLE_SID}.sql
DECLARE
v_temp_backup_file VARCHAR2(128);
v_backup_file      VARCHAR2(128);
CURSOR c_tablespace IS
SELECT tablespace_name
FROM dba_tablespaces
WHERE contents <> 'TEMPORARY'
ORDER BY 1;
CURSOR c_datafiles (in_ts_name IN VARCHAR2) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = in_ts_name
ORDER BY 1;
-- -----------------------------------------------------------------------------
FUNCTION get_trace_file_name
RETURN VARCHAR2
AS
v_trace_file_name   VARCHAR2(128);
v_udump_dir         VARCHAR2(128);
BEGIN
SELECT value
INTO v_udump_dir
FROM v\$parameter
WHERE LOWER(name) = 'user_dump_dest';
SELECT p.spid
INTO v_trace_file_name
FROM v\$process p, v\$session s
WHERE s.paddr = p.addr
AND s.audsid=userenv('sessionid')
AND sid = ( SELECT sid
FROM v\$mystat
WHERE ROWNUM = 1 );
RETURN v_udump_dir || '/${ORACLE_SID}_ora_' || v_trace_file_name ||'.trc';
END get_trace_file_name;
-- -----------------------------------------------------------------------------
FUNCTION archivingDisabled
RETURN boolean
AS
v_archiveValue VARCHAR2(10);
v_returnValue  BOOLEAN;
BEGIN
SELECT value
INTO v_archiveValue
FROM v\$parameter
WHERE name = 'log_archive_dest_state_1';
IF v_archiveValue = 'enable'
THEN
v_returnValue := FALSE;
ELSE
v_returnValue := TRUE;
END IF;
RETURN v_returnValue;
END archivingDisabled;
-- -----------------------------------------------------------------------------
FUNCTION get_new_temp_file_name
( p_file_name   IN dba_data_files.file_name%TYPE )
RETURN dba_data_files.file_name%TYPE
AS
BEGIN
RETURN '${TEMP_DIR}/' ||
SUBSTR(p_file_name, INSTR(p_file_name, '/', -1)+1);
END get_new_temp_file_name;
-- -----------------------------------------------------------------------------
FUNCTION get_new_file_name
( p_file_name   IN dba_data_files.file_name%TYPE )
RETURN dba_data_files.file_name%TYPE
AS
BEGIN
RETURN '${CURRENT_BACKUP_DIR}/' ||
SUBSTR(p_file_name, INSTR(p_file_name, '/', -1)+1);
END get_new_file_name;
-- -----------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
IF archivingDisabled
THEN
RAISE_APPLICATION_ERROR(-20001,
'Archiving needs to be enabled in the database to do a hot backup!!!');
END IF;
DBMS_OUTPUT.PUT_LINE (
'!cp ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ' ||
'${CURRENT_BACKUP_DIR}/init${ORACLE_SID}.ora');
DBMS_OUTPUT.PUT_LINE (
'!cp ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora ' ||
'${CURRENT_BACKUP_DIR}/spfile${ORACLE_SID}.ora');
DBMS_OUTPUT.PUT_LINE (
'!cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ' ||
'${CURRENT_BACKUP_DIR}/orapw${ORACLE_SID}.ora');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SET log_archive_dest_5=''location=${CURRENT_BACKUP_DIR}'';');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SET log_archive_dest_state_5=ENABLE;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SWITCH LOGFILE;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM ARCHIVE LOG CURRENT;');
FOR ts IN c_tablespace
LOOP
DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE ' || ts.tablespace_name || ' BEGIN BACKUP;');
FOR df IN c_datafiles (in_ts_name => ts.tablespace_name)
LOOP
v_temp_backup_file := get_new_temp_file_name(df.file_name);
v_backup_file := get_new_file_name(df.file_name);
DBMS_OUTPUT.PUT_LINE ('!cp ' || df.file_name ||' '|| v_temp_backup_file);
DBMS_OUTPUT.PUT_LINE ('!gzip  ' || v_temp_backup_file );
DBMS_OUTPUT.PUT_LINE ('!mv ' || v_temp_backup_file || '.gz ' || v_backup_file || '.gz');
END LOOP;
DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE ' || ts.tablespace_name || ' END BACKUP;');
END LOOP;
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM ARCHIVE LOG CURRENT;');
DBMS_OUTPUT.PUT_LINE (
'ALTER DATABASE BACKUP CONTROLFILE TO ''${CURRENT_BACKUP_DIR}/control.ctl'';');
DBMS_OUTPUT.PUT_LINE ('ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
DBMS_OUTPUT.PUT_LINE (
'!mv '|| get_trace_file_name || ' ${CURRENT_BACKUP_DIR}/control.sql;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SWITCH LOGFILE;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM ARCHIVE LOG CURRENT;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SET log_archive_dest_state_5=''DEFER'';');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SWITCH LOGFILE;');
DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM ARCHIVE LOG CURRENT;');
DBMS_OUTPUT.PUT_LINE ('!sleep 90');
END;
/
SPOOL OFF;
SPOOL ${CURRENT_BACKUP_DIR}/oracle_hot_backup_${ORACLE_SID}.log
SET VERIFY   ON
SET TERM     ON
SET ECHO     ON
SET FEEDBACK ON
SET SERVEROUTPUT ON
@${CURRENT_BACKUP_DIR}/oracle_hot_backup_${ORACLE_SID}.sql
SPOOL OFF;
EOF
# check for existence of backup two days ago,
# if found delete it.
find $BACKUP_DIR/bckup* -mtime +3 -type d | xargs rm -rf 2>> ${CURRENT_BACKUP_DIR}/error_${ORACLE_SID}.log
# Remove the archive logs older than two days.
#if [ -e $ARCHIVE ] ;
#then
#   find $ARCHIVE -mtime +5 | xargs rm -rf 2>> ${BACKUP_LOG}/error_${ORACLE_SID}.log
#fi
# compressing archive files will save several GB of storage on each server
gzip ${CURRENT_BACKUP_DIR}/*.arc -- */
# Generate the success / failure notice and email it to the admin.
if [ -s ${CURRENT_BACKUP_DIR}/error_${ORACLE_SID}.log ] ; then
echo "To: $ADMIN"                                > /tmp/${ORACLE_SID}_backup.mail
echo "From: ${FROM_MAIL}"                       >> /tmp/${ORACLE_SID}_backup.mail
echo "Subject: Errors in $ORACLE_SID backup"    >> /tmp/${ORACLE_SID}_backup.mail
echo                                            >> /tmp/${ORACLE_SID}_backup.mail
cat ${CURRENT_BACKUP_DIR}/error_${ORACLE_SID}.log       >> /tmp/${ORACLE_SID}_backup.mail
echo                                            >> /tmp/${ORACLE_SID}_backup.mail
echo "il mio errore, il vostro errore"          >> /tmp/${ORACLE_SID}_backup.mail
echo                                            >> /tmp/${ORACLE_SID}_backup.mail
echo "$ORACLE_SID"                              >> /tmp/${ORACLE_SID}_backup.mail
cat /tmp/${ORACLE_SID}_backup.mail | /bin/mail -s "Errors in $ORACLE_SID backup" $ADMIN
rm /tmp/${ORACLE_SID}_backup.mail
fi
exit 0