Tag Archives: Script

Script To Export 11i Database

Posted by on December 15, 2009 No comments 
# expprod.sh
#
# Exports the Oracle E-Business Suite HRMS Production database
# which will then be copied over to the backup server
#
# source the environment
. /u0/oracle/proddb/9.2.0/PROD_ebusiness.env
# stop, then restart the production instance
# to overcome 'snapshot too old' error
# (commented out, as users on the system at 6:00 P.M. will be kicked off)
# cd /u0/oracle/proddb/9.2.0/appsutil/scripts/PROD_ebusiness/
# ./addbctl.sh stop immediate
# ./addbctl.sh start
# get name of log file and dump file
export LOGFILE=expprod_`date +%F_%k-%M-%S`.log
export DUMPFILE=expprod.dmp
export BACKUPDIR=/mnt/prodbkup
export EXPORTDIR=/u0/prodbkup
export ZIPFILE=expprod.tar.gz
export ZIPFILE2=expprod_`date +%F_%k-%M-%S`.tar.gz
# remove existing dump file
rm $EXPORTDIR/$DUMPFILE
rm $EXPORTDIR/$ZIPFILE
# export the ONT schema (for testing purposes)
# exp system/manager file=$EXPORTDIR/$DUMPFILE log=$EXPORTDIR/$LOGFILE owner=ONT
# export the database
exp system/manager file=$EXPORTDIR/$DUMPFILE log=$EXPORTDIR/$LOGFILE full=y consistent=y
# tar and gzip export file
tar -czvf $EXPORTDIR/$ZIPFILE $EXPORTDIR/$DUMPFILE
# remove the previous export dump from the test server, then copy the new one
# rm $BACKUPDIR/$DUMPFILE
# rm $BACKUPDIR/$ZIPFILE (Commented out by VS 10-Sep-09)
# copy the zip file and log file to the backup directory (remote NFS directory on ebusinesstest)
cp $EXPORTDIR/$ZIPFILE $BACKUPDIR/
cp $EXPORTDIR/$LOGFILE $BACKUPDIR/
# rename the zip file in the remote directory to reflect today's date
mv $BACKUPDIR/$ZIPFILE $BACKUPDIR/$ZIPFILE2
# rename the zip file in the local directory to reflect today's date (as an archive copy)
mv $EXPORTDIR/$ZIPFILE $EXPORTDIR/$ZIPFILE2

Backup Script for Oracle 10g

Posted by on December 8, 2009 No comments

Sample script to backup an Oracle 10g database on Linux.

#!/bin/sh
 # orahotbkup.sh
 #
 # Copies Oracle database data files to backup directory on disk
 # and individually gzips them
 #
# set up environment variables
 # need to export the Oracle environment variables
 export ORACLE_SID=orcl
 export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
 export PATH=$ORACLE_HOME/bin:$PATH
FMT_DATE=`/bin/date +%Y%m%d`
 BACKUP_BASE=/u01/app/oracle/oradata/backup/orcl
SCRIPT_LOC=/u01/app/oracle/admin/$ORACLE_SID/scripts/backup
 LOG_LOC=/u01/app/oracle/admin/$ORACLE_SID/scripts/log
 BACKUP_LOC=$BACKUP_BASE/$FMT_DATE
SCRIPT_FILE=$SCRIPT_LOC/dbf$FMT_DATE.sh
 LOG_FILE=$LOG_LOC/$FMT_DATE.log
# make destination directories
 mkdir -p $LOG_LOC
 mkdir -p $SCRIPT_LOC >> $LOG_FILE
 mkdir -p $BACKUP_LOC >> $LOG_FILE
# generate bash script file to copy database data files to destination
 sqlplus / as sysdba <
select '#!/bin/sh ' from dual;
 select 'sqlplus / as sysdba <<EOF'||chr(10)||
 'alter tablespace '||tablespace_name||
 ' begin backup; '||chr(10)||
 'exit'||chr(10)||
 'EOF'||chr(10)||chr(10)||
 'cp '||file_name||' $BACKUP_LOC/'||substr(file_name,instr(file_name,'/',-1)+1)
 ||chr(10)||chr(10)||
 'sqlplus / as sysdba <<EOF'||chr(10)||
 'alter tablespace '||tablespace_name||
 ' end backup; '||chr(10)||
 'exit'||chr(10)||
 'EOF'
 from dba_data_files
 order by file_id
spool $SCRIPT_FILE
/
spool off
 exit
 EOF
# change permission modes on script file to make it executable
 chmod 755 $SCRIPT_FILE
# execute the script file to copy the database data files
 $SCRIPT_FILE
# set name of second script file
 SCRIPT_FILE=$SCRIPT_LOC/arc$FMT_DATE.sh
# generate bash script file to copy archive logs to destination
 sqlplus / as sysdba <
alter system switch logfile;
 alter system switch logfile;
 alter system switch logfile;
alter database backup controlfile to '$BACKUP_LOC/control01.dbf';
select '#!/bin/sh ' from dual;
 select
 'cp '||name||' $BACKUP_LOC/'||substr(name,instr(name,'/',-1)+1)
 ||chr(10)||chr(10)
 from v$archived_log
 where completion_time > sysdate - 1.1
 order by completion_time
spool $SCRIPT_FILE
/
spool off
 exit
 EOF
# change permission modes on script file to make it executable
 chmod 755 $SCRIPT_FILE
# execute the script file to copy the archive log files
 $SCRIPT_FILE
# gzip each database file in the $BACKUP_LOC directory
 gzip $BACKUP_LOC/*
echo All done!

Quick And Dirty Backup and Recovery Test Procedure

Posted by on April 21, 2005 No comments

Step 1.      Shutdown the TEST instance:

./oratest/scripts/stopTEST.sh

Step 2.      Backup the directory /oratest/oracle and all it subdirectories.

Step 3.      Rename the directory /oratest/oracle to /oratest/oracle_backup

Step 4.      Restore from backup /oratest/oracle

Step 5.      Verify Recovery by Comparing file sizes between oracle and oracle_backup

Step 6.      Start the TEST instance:

./oratest/scripts/startTEST.sh

Step 7.      Log on to TEST instance and TEST functionality.

Step 8.      Remove directory /oratest/oracle_backup