Hi everyone, can you help me ?, I have the following shell:
DIR="/backups/backdata"
DIR_2="/BACKOPENF/database"
LOG="/backups/logs/oracle_backup.log"
EXP_LOG="/backups/logs/Exp`date +%Y%m%d%H%M%S`.log"
EXP_FILE="Exp`date +%Y%m%d%H%M%S`.dmp"
HOLD_DAY=1
# String de conexion
oracle_username="system"
oracle_password="password"
# Se define una funcion ECHO para que siempre ponga fecha
ECHO () {
printf "%s " `date '+%Y-%m-%d %H:%M:%S'`
echo $1
}
ECHO "Inicio"
# Se borran los mas viejos que $HOLD_DAY dias.
ECHO "Borrado de archivos viejos"
OLD_FILE=`find $DIR -mtime +$HOLD_DAY -name Exp20\*.Z`
if [ -n "$OLD_FILE" ]
then
ECHO "El sigiuete archivo es mas viejo que $HOLD_DAY dia y sera borrado.
"
ECHO $OLD_FILE
find $DIR -mtime +$HOLD_DAY -name Exp20\*.Z -exec rm {} \;
fi
mknod "$DIR/$EXP_FILE" p
compress < "$DIR/$EXP_FILE" > "$DIR/$EXP_FILE".Z &
sleep 5
ECHO "Inicio de backup"
/oracle/app/oracle8/product/8.1.7/bin/exp $oracle_username/$oracle_passw
ord@openflex owner=FLEX, EPAYMENT, IMS, EXPERT, ADMIN, FILE="$DIR/$EXP_FILE" LO
G=$EXP_LOG;
ECHO "Fin de backup"
sleep 5
ECHO "Borrado de archivo temporal y copiado del nuevo"
rm "$DIR/$EXP_FILE"
cp "$DIR/$EXP_FILE".Z "$DIR_2"
echo "-----------------EOF-----------------"
# End.
And its execution (crontab) results in the following log file:
2017-02-23 01:00:00 Inicio
2017-02-23 01:00:00 Borrado de archivos viejos
2017-02-23 01:00:05 Inicio de backup
2017-02-23 01:00:05 Fin de backup
2017-02-23 01:00:10 Borrado de archivo temporal y copiado del nuevo
It does all the command except the export line ... Running in console it works fine
You may also need to define the LD_LIBRARY variable. You are using ancient oracle, so I can't remember.
When crontab runs it DOES NOT execute any of your login scripts, like the ones that set up which database instances of oracle you connect to, library location(s) and so on.
You probably want the crontab to run all of the account (the one that crontab is run under) setup scripts like
.profile
Oracle also uses the
.login
script to setup special things - it must reside in the current working directory of the script.
You should probably already know that a database export is not really a backup. It's a logical backup at best,
but could give you data that is not in a consistent state. You really want a physical backup, that will backup
your database, control files and archive log files. I happen to have one from back when.
I used this script to backup Oracle 8.1.7 databases, back when I supported Oracle 8.1.7. You might find this
useful as long as you keep 8.1.7 around. It might also work on any 7.3.4 databases that you might have.
I make no warranties about usefulness or whether or not the code is safe to run.
# 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
