In this article we will see how to export full Oracle database and remove old backup log file. We will also see how mknod tool help us in taking a full backup.
Scenario
Every Day we have taken the full export backup of one our database. Normally Export dump file size is around 25 GB.
Steps
Daily we have followed the below steps
- Export the full database backup
- Validate the logfile whether backup is successful or not
- Compress the Dumpfile
- Remove the dumpfile & logfile greater than 5 days
Daily we have spend some time for this activity.Really we are not interested to do this activity regularly. So we have automated this activity using shell script.
Details
- Using mknod to compress the dumpfile during export
- Trigger the mail to user whether backup success or not
- If backup success to purge the dumpfile & logfile greater than 5 days
Note: We maintained last 5 days backup only.
Script 1
SCRIPT NAME: export_full_bkup.sh
DESCRIPTION: SCHEMA LEVEL EXPORT & DELETE THE MORE THAN 2 DAYS BACKUP DUMP. BEFORE DELETED THE OLD BACKUP VALIDATE THE CURRENT BACKUP DUMP.
##############################################################################
export_full_bkup.sh
CREATED BY RAJABASKAR THANGARAJ 18-NOV-2008
##############################################################################
#!/bin/sh
. $HOME/.bash_profile
RUNDATE=`date “+%d%m%y at %H:%M:%S”`
DBHOME=$ORACLE_HOME
BACKUP_PATH=/home/oracle/dbatest/raja/mknode/backup
cd $BACKUP_PATH
mknod export_pipe p
gzip -cNf $ORACLE_SID_$RUNDATE.dmp.gz &
exp parfile=$BACKUP_PATH/exp_param file=export_pipe
log=$BACKUP_PATH/export_$ORACLE_SID_$RUNDATE.log
cat $BACKUP_PATH/export_$ORACLE_SID_$RUNDATE.log | grep “EXP-” > $PATH/exp_$ORACLE_SID_$RUNDATE.err
mail -s “Status of $ORACLE_SID export backup on $RUNDATE ” [email protected] < $BACKUP_PATH/exp_$ORACLE_SID_RUNDATE.err
rm -f export_pipe p
WORDCOUNT=`wc $BACKUP_PATH/exp_$ORACLE_SID_$RUNDATE.err | awk ‘{print $1}’`
if [ $WORDCOUNT -ne 0 ]; then
exit 16;
else
find $BACKUP_PATH/*.gz -mtime +5 -exec rm {} ;
find $BACKUP_PATH/*.log -mtime +5 -exec rm {} ;
fi
Script 2
SCRIPT NAME: exp_param
DESCRIPTION: This exp_param file contains export parameter.
(SCRIPT 1 call this exp_param file during export)
##############################################################################
EXPORT PARAMETER PARFILE
CREATED BY RAJABASKAR THANGARAJ 18-NOV-2008
##############################################################################
userid=testpipe1/testpipe1 full=y buffer=10485667 statistics=none object_consistent=y
Why we are using mknod?
Instead of doing the export and zip separately, creating an interim dump file (or doing unzip and import), unix has the ability to pipe the output from one program (such as exp) as input to another program (such as gzip) as they both run in parallel, without having to run the programs sequentially and without having to create interim files.
More details about MKNOD
https://www.primeinspiration.com/programming/database/oracle/84-using-mknod-during-export.html
I Hope this article helped you to understand the automated full export database.Suggestions are welcome.
