My Database Backup Script (OFFLINE / LOGICAL BKUP)

Script1 : Offline backup
This scripts first take the logical export using datapump, then it shutdown the database, Copy the files to tape drive using TAR(it coppies all oracle db related file like spfile, pwdfile, listener,tnsnames files aswell) , then start the database.

$ more offbkup_omr.sh

#!/bin/ksh export ORACLE_HOME="/u01/app/oracle/product/10.2.0" export ORACLE_SID="omr" echo omr database export started at `date` >> /u05/omr/export/offbkup_omr.log (TAKING DATAPUMP EXP) $ORACLE_HOME/bin/expdp system/password dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=ae001t3 echo OMR database export ended at `date` >> /u05/omr/export/offbkup_omr.log echo OMR database shutdown begin at `date` >> /u05/omr/export/offbkup_omr.log(SHUTING DOWN DB B4 OFFLINE BKUP) $ORACLE_HOME/bin/sqlplus /nolog @/u06/omr/omr/scripts/shutdown_omr.sql echo OMR database shutdown at `date` >> /u05/omr/export/offbkup_omr.log echo TAPE archiving started at `date` >> /u05/omr/export/offbkup_omr.log (COPYING FILES TO TAPEDRIVE USING TAR) tar Ecvf /dev/rmt/0 /u05/omr/export /u01/app/oracle/product/10.2.0/dbs/initomr.ora /u01/app/oracle/product/10.2.0/dbs/spfileomr.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/omr /u02/omr/oradata /u03/omr/oradata /u04/omr/oradata /u05/omr/oradata /u06/omr/omr/scripts /u06/omr/arch echo TAPE archiving ended at `date` >> /u05/omr/export/offbkup_omr.log echo OMR database getting started at `date` >> /u05/omr/export/offbkup_omr.log(STARTING DB) $ORACLE_HOME/bin/sqlplus /nolog @/u06/omr/omr/scripts/startup_omr.sql echo OMR database started at `date` >> /u05/omr/export/offbkup_omr.log echo OMR offline backup finished at `date` >> /u05/omr/export/offbkup_omr.log

$ more startup_omr.sql
connect /as sysdba;
startup;
exit;


$ more shutdown_omr.sql
connect / as sysdba;
shutdown immediate;
exit;
===========================================================
Script2:  Datapump export backup.

$ more dailyexpdp_omr.sh

#!/bin/ksh export ORACLE_HOME="/u01/app/oracle/product/10.2.0" export ORACLE_SID="omr" echo export started at `date` >> /u05/omr/export/dailyexpdp_omr.log $ORACLE_HOME/bin/expdp system/omr1956 dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=ae001t3 echo export stopped at `date` >> /u05/omr/export/dailyexpdp_omr.log echo tape archiving started at `date` >> /u05/omr/export/dailyexpdp_omr.log tar Ecvf /dev/rmt/0 /u05/omr/export /u06/omr/arch echo tape archiving stopped at `date` >> /u05/omr/export/dailyexpdp_omr.log

===========================================================

Script3: backing up the archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/omr/arch
echo morning tape archiving ended

===========================================================
Following scripts were used to stop/start the enterprise manager while performing offline backup

$ more emctl_start.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="omr"
echo STARTING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl start dbconsole


$ more emctl_stop.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="omr"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole