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.
Code:
# 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
This User Gave Thanks to gandolf989 For This Post:
Hi all,
I am trying to run a script in crontab but I receive the email below. Note the empty variable SHELL. Apparently cron doesn't invoke a shell. That explains the errors in the script it is trying to execute. Anyone know what maybe wrong?
Subject: Output from cron job... (3 Replies)
Hi,
i try to execute the following in a crontab file and it doesn't work while the same line is OK on the Unix (AIX) shell command line.
42 15 * * 1 > /users/notes01/tata.${date +"%d%m%Y"}
it seems to be a pb related to date flags expression but i can't see why , somebody have an idea ?
... (4 Replies)
Hi Folks,
Could you please suggest me how to run a shell script on a solaris env without using crontab. I am actually trying to write a shell script which will grep "WORD" in the logfile andd sends a email.Thanks in advance.
Thanks
Sandeep. (3 Replies)
I need your help please
Inin a production system i found crontab entries was removed because i typed crontab -l with username corasc and didn't show anything.
i asked the administrador to restored the mentioned crontab, he restored the crontab:
The problem is when restored the crontab file is... (2 Replies)
Hi,
I'm trying the backup a few information commands of my Check Point FW (it's on a SPLAT linux machine). This is the script I wrote:
#!/bin/bash
cd /var/tmp/
file1=netstat_`/bin/date +%d%m%y`.txt
file2=ifconfig_`/bin/date +%d%m%y`.txt
file3=cpstatos_`/bin/date +%d%m%y`.txt... (2 Replies)
Hi all
I installed Debian and i have a few scripts that outputs what is happening.
The wierd part...after fresh install all works ok but after i open or edit Crontab it stops executing the scripts...and scripts runs manually so its not a problem with scripts...what happens is that i usually... (3 Replies)
Hi All,
I have scheduled a script to run every five minutes through crontab. If there is any issue with the script which may need manual intervention, I have used the mailx commands. But at the same time, I would want the cron to be disabled until the issue is resolved. Once things are fine,... (2 Replies)
hi,
i have shell script which checks for service running on platform,
if service is receiving calls, then do nothing
if service is not receiving calls, then stop the service
i want to run this script in 2 timeslots.
i.e. in the day from 9 am to 5 pm it should run every 10 minutes
and ... (2 Replies)
Hi friends,
Actually i wrote many scripts in korn shell for time saving activity in storage domain. But every week i am spending some time for the running of script. So every Monday i wants to run my scripts automatically. So corn will be helpful according to my friend's opinion.
But I don't... (5 Replies)
Dear Folks,
I have written a shell script which internally connects to oracle database through sqplplus command line.
The script runs fine when run manually.
Now I am scheduling it to run (Linux environment) from crontab.
In crontab it is failing with an error:
sqlplus command... (6 Replies)