【代码片】Shell定时导出备份Oracle数据库表

数据库表的备份有很多种，包括物理备份和逻辑备份，当然简单的方法是，直接在数据库中定时创建备份表进行备份。

这里分享个在主机上自动备份的SHELL脚本：

#!/usr/bin/bash
source /int_file/.bash_profile
function crontab_backup()
{
  crontab -l > /int_file/lubinsu/backup/crontab_bak/crontab_`date +%Y%m%d`.bak
  if [ $? = 0 ] 
  then
    find /int_file/lubinsu/backup/crontab_bak -name "crontab_*.bak" -type f -mtime +5 -exec rm {} \;
    return 0
  else
    return 1
  fi
}
function table_backup()
{
  expdp css/XXX#@css DIRECTORY=CSS_DUMP dumpfile=$1_`date +%Y%m%d`.dmp TABLES=$1
  if [ $? = 0 ] 
  then
    find /int_file/lubinsu/backup -name "$1_*.dmp" -type f -mtime +5 -exec rm {} \;
    return 0
  else 
    return 1
  fi
}
crontab_backup
value=`sqlplus -S css/XXX#@CSS << EOF
set heading off feedback off pagesize 0 verify off echo off
select table_name from tab_for_backup;
exit
EOF`
for loop in ${value}
do
  table_backup ${loop}
  if [ $? = 0 ]
  then
    :
  else
    echo "fail"
    exit 1
  fi
done

然后在crontab上加个定时即可：

#Backup crontab and tables daily
20 17 * * * /int_file/lubinsu/scripts/backup.sh >> /int_file/lubinsu/logs/`date +%Y%m%d`/backup.log