Export Full Oracle Database & Remove The Old Backup Log File

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

1. Export the full database backup
2. Validate the logfile whether backup is successful or not
3. Compress the Dumpfile
4. 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

1. Using mknod to compress the dumpfile during export
2. Trigger the mail to user whether backup success or not
3. 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 ” rajabaskar.thangaraj@abcde.com < $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

http://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.