Oracle 增量备份和逻辑备份脚本

1. 增量备份

  先看下RMAN配置，备份保留7天，控制文件自动备份到指定目录：

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIMARYDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/dbbak/ctrbak/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/app/oracle/product/dbs/snapcf_primdb.f'; # default

  了解下DELETE EXPIRED与DELETE OBSOLETE的区别：

• DELETE EXPIRED命令从RMAN存储库中删除过期的备份和副本，删除的是那些本来RMAN以为存在但是实际上在磁盘或者磁带上已经被删除了的信息，删除的只是RMAN资料库中的记录；
• DELETE OBSOLETE删除保留策略定义的所有过时的备份文件，备份文件从RMAN存储库（备份介质、恢复目录）中删除，并在控制文件中标记为DELETED。

  每周末进行全量备份：

# SCRIPT:BackupFull.sh
# AUTHOR:ChengYu
# CREATEDATE:2017/11/16
# UPDATE:2017/11/20
# DESC:backup database with RMAN
# CONNECT DATABASE
echo "~~~~~~~~~Welcome to BackupFull, Dear ChengYu!~~~~~~~~~"
export ORACLE_BASE=/home/app/oracle
export ORACLE_SID=primdb
export ORACLE_HOME=$ORACLE_BASE/product
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
rman target ChengYu/******** << EOF
run{
allocate channel cl type disk;
backup incremental level 0 tag 'DB0' format '/home/oracle/dbbak/fullbak/DB0%d%T%s.BKP' database plus archivelog;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
release channel cl;
}
exit
EOF
# end

  周1-周6增量备份：

# SCRIPT:Incr1Backup.sh
# AUTHOR:ChengYu
# DATE:2017/11/16
# UPDATE:2017/11/20
# DESC:backup database with RMAN
# CONNECT DATABASE
echo "~~~~~~~~~Welcome to Incr1Backup,Dear ChengYu!~~~~~~~~~"
export ORACLE_BASE=/home/app/oracle
export ORACLE_SID=primdb
export ORACLE_HOME=$ORACLE_BASE/product
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

rman target ChengYu/******** << EOF
run{
allocate channel cl type disk;
backup incremental level 1 tag 'DB1' format '/home/oracle/dbbak/fullbak/DB1%d%T%s.BKP' database plus archivelog;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
release channel cl;
}
exit
EOF
# end

2. 逻辑备份

  通过EXPDP进行逻辑备份，逐个项目数据库实现每日备份，备份保留7天：

# SCRIPT:LogicalBackup.sh
# CREATER:ChengYu
# DATE:2017/11/20
# DESC:Backup with EXPDP
# CONNECT DATABASE
export ORACLE_BASE=/home/app/oracle
export ORACLE_SID=primdb
export ORACLE_HOME=$ORACLE_BASE/product
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
# Get current date
RunDate=`date +%Y%m%d`
# Output information
BakDate=`date '+%Y-%m-%d %H:%M'`
echo "~~~~~~~~~~~~~~~~~~~~~~"$BakDate"~~~~~~~~~~~~~~~~~~~~~~~~"
echo
echo "~~~~~~~~~Welcome to LogicalBackup, Dear ChengYu!~~~~~~~~~"
echo
# The specified backup directory
DefDir=/home/oracle/dbbak/LogicBackup
logdir=/home/oracle/dbbak/log
# Backup detail
expdp ChengYu/********* directory=data_pump_dir_logicalbak dumpfile=spring$RunDate.dmp schemas=spring log=spring$RunDate.log
sleep 5
expdp ChengYu/********* directory=data_pump_dir_logicalbak dumpfile=summer$RunDate.dmp schemas=summerlog=summer$RunDate.log
sleep 5
expdp ChengYu/********* directory=data_pump_dir_logicalbak dumpfile=autumn$RunDate.dmp schemas=autumn log=autumn$RunDate.log
sleep 5
#Combine the logs
cat $DefDir/*$RunDate.log >>$logdir/merge.log
# 发送邮件
# mail -s "DataBase Backup and Archivelog delete Log of DBPLUS1" -r ChengYu@dbplus1.sda.com 88414341@qq.com <$logdir/merge.log
# Compress file
tar --remove-files -zcvf $DefDir/LogicBak$RunDate.tar.gz $DefDir/*$RunDate.*
# Delete expired files
find $DefDir/LogicalBak*.tar.gz -atime +7 -exec rm -f {} \;
# Output information
echo "~~~~~~~~~Bye, Have A Nice Day!~~~~~~~~~"

  开启定时备份：

  [oracle@ChengYu ~]$ crontab -e

00 23 * * 0 /home/oracle/dbbak/script/BackupFull.sh >/home/oracle/dbbak/log/merge.log
00 23 * * 1-6 /home/oracle/dbbak/script/Incr1Backup.sh >/home/oracle/dbbak/log/merge.log
30 23 * * * /home/oracle/dbbak/script/LogicalBackup.sh >>/home/oracle/dbbak/log/LogicalBackup.log

2020年07月15日

  2个月约饭7-8次，憨憨LD这次应该是等到她的真命天子了，今年的“11.11”应该不用我再送关怀了…