backup.sh

一本介绍oracle的书籍

#!/bin/bash

#========================================================#
#    如果脚本用于crontab，下面Oracle环境变量必须设置     #
#========================================================#
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/product/8.1.7
ORACLE_SID=oradb6
ORACLE_TERM=ansi
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
NLS_LANG=American_America.ZHS16GBK
NLS_DATE_FORMAT=YYYYMMDDHH24MISS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
TMPDIR=/var/tmp
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORA_NLS33 NLS_LANG 
export NLS_DATE_FORMAT LD_LIBRARY_PATH TMPDIR PATH
#========================================================#

BACKUPDIR=/oradata/backup; export BACKUPDIR
USER_PASSWD=dbuser/oracle

RESULTFILE=/export/home/oracle/log/result.log

cd $BACKUPDIR

exp $USER_PASSWD log=$(date '+%Y%m%d').log file=$(date '+%Y%m%d').dmp owner=dbuser buffer=4096000 rows=n
if [ $? != 0 ]
then
    echo "`date` backup exp fail" >> $RESULTFILE
    exit
fi

rm -f tables.txt
sqlplus -s $USER_PASSWD <<EOF >/dev/null
set heading off;
set term off;
set echo off;
set pagesize 0;
set linesize 1000;
set trimspool on;
set trimout on;
set feedback off;
set colsep |;
spool tables.txt;
select table_name from user_tables;
spool off;
exit;
EOF

for table in $(cat tables.txt)
do
    exp $USER_PASSWD log=${table}_$(date '+%Y%m%d').log file=${table}_$(date '+%Y%m%d').dmp tables=$table direct=y
    if [ $? != 0 ]
    then
        echo "`date` backup exp $table fail" >> $RESULTFILE
        exit
    fi
done

compress -f *.dmp

echo "`date` backup succeed" >> $RESULTFILE