阿里云oracle备份策略

备份分类

数据备份是为了尽可能快地全盘恢复运行计算机系统所需的数据和系统信息，它不仅在网络系统硬件故障或人为失误时起到保护作用，同时能在集群环境下失效切换之后备机能够正常接管关键业务的基础。当然，数据备份也是系统灾难恢复的前提之一。

从备份模式的角度来说，分为 物理备份 和 逻辑备份 ，也就是我们通常所说的基于数据块级别的备份和基于文件级的备份。

物理备份

物理备份是磁盘块级的备份，它需要数据库处于归档模式下，用rman工具来转储ORACLE物理文件(如数据文件、控制文件、归档日志文件、参数文件，密码文件)，一旦数据库发生故障，可以利用这些文件进行还原。

逻辑备份

逻辑备份是以文件为基本单位的备份，业务数据库采用此种方式，此方法不需要数据库运行在归挡模式下，ORACLE逻辑备份分为三种模式：表备份、用户备份和完全备份，使用的工具有exp，expdp等

关于如何深入的理解物理备份和逻辑备份的概念，详见如下URL：

https://blog.csdn.net/yunqishequ1/article/details/77096560

物理备份策略

1 配置全备脚本

[root@iz2ze8d5ppmu95sr2clgqyz oracle]# vi rman0.sh 

#!/bin/bash
bdate=`date +%Y_%m_%d`
st=`date +%s`
[ -d /u01/app/oracle/rman_backup/${bdate}-LEVEL0 ] || mkdir -p /u01/app/oracle/rman_backup/${bdate}-LEVEL0
[ -d /u01/app/oracle/log ] || mkdir -p /u01/app/oracle/log
chown -R oracle.oinstall /u01/app/oracle/rman_backup
chown -R oracle.oinstall /u01/app/oracle/log
su - oracle -c 'rman log='/u01/app/oracle/log/${bdate}-LEVEL0.log' append'<<EOF &> /dev/null
connect target /;
run{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure retention policy to redundancy 2;
allocate channel c1 type disk;
allocate channel c2 type disk;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 0 database format '/u01/app/oracle/rman_backup/${bdate}-LEVEL0/L0_DATA_%T_%U.bak';
backup archivelog all format '/u01/app/oracle/rman_backup/${bdate}-LEVEL0/L0_ARC_%T_%U.bak' delete all input;
backup current controlfile format'/u01/app/oracle/rman_backup/${bdate}-LEVEL0/L0_CON_%T_%U.bak';
backup spfile format '/u01/app/oracle/rman_backup/${bdate}-LEVEL0/L0_SPFILE_%T_%U.bak';
release channel c1;
release channel c2;
}
EOF
echo >> /u01/app/oracle/log/${bdate}-LEVEL0.log

en=`date +%s`
min=`awk -ven=$en -vst=$st 'BEGIN{print (en-st)/60}'`
echo "it use $min minutes" >> /u01/app/oracle/log/${bdate}-LEVEL0.log
echo  `date` >> /u01/app/oracle/log/${bdate}-LEVEL0.log

echo "*********************************Finish*********************************************" >> /u01/app/oracle/log/${bdate}-LEVEL0.log
echo >> /u01/app/oracle/log/${bdate}-LEVEL0.log

2 配置增备脚本

[root@iz2ze8d5ppmu95sr2clgqyz oracle]# cat rman1.sh 
#!/bin/bash
bdate=`date +%Y_%m_%d`
st=`date +%s` #start
[ -d /u01/app/oracle/rman_backup/${bdate}-LEVLE1 ] || mkdir -p /u01/app/oracle/rman_backup/${bdate}-LEVEL1
[ -d /u01/app/oracle/log ] || mkdir -p /u01/app/oracle/log
chown -R oracle.oinstall /u01/app/oracle/rman_backup
chown -R oracle.oinstall /u01/app/oracle/log
su - oracle -c 'rman log='/u01/app/oracle/log/${bdate}-LEVEL1.log' append'<<EOF &> /dev/null
connect target /;
run{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure retention policy to redundancy 2;
allocate channel c1 type disk;
allocate channel c2 type disk;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 1 database format '/u01/app/oracle/rman_backup/${bdate}-LEVEL1/L1_DATA_%T_%U.bak';
backup archivelog all format '/u01/app/oracle/rman_backup/${bdate}-LEVEL1/L1_ARC_%T_%U.bak' delete all input;
backup current controlfile format'/u01/app/oracle/rman_backup/${bdate}-LEVEL1/L1_CON_%T_%U.bak';
backup spfile format '/u01/app/oracle/rman_backup/${bdate}-LEVEL1/L1_SPFILE_%T_%U.bak';
release channel c1;
release channel c2;
}
EOF
echo >> /u01/app/oracle/log/${bdate}-LEVEL1.log

en=`date +%s` #end
min=`awk -ven=$en -vst=$st 'BEGIN{print (en-st)/60}'` #calc
echo "it use $min minutes" >> /u01/app/oracle/log/${bdate}-LEVEL1.log
echo  `date` >> /u01/app/oracle/log/${bdate}-LEVEL1.log

echo "*********************************Finish*********************************************" >> /u01/app/oracle/log/${bdate}-LEVEL1.log
echo >> /u01/app/oracle/log/${bdate}-LEVEL1.log

3 配置计划任务

root用户下

[root@iz2ze8d5ppmu95sr2clgqyz expdp_backup]# crontab -l
#物理备份
#周日0点全备份
00 00 * * 0   /u01/app/oracle/rman0.sh
#周一到周六0点增量备份
00 00 * * 1-6 /u01/app/oracle/rman1.sh

4 RMAN的保留7天冗余

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

逻辑备份策略

1 确认PUMP目录和授权

SQL> select * from dba_directories;

OWNER                  DIRECTORY_NAME                     DIRECTORY_PATH
------------------------------ -------------------------------------------------- -----
SYS                DUMP                       /u01/app/oracle/expdp_backup


SQL> grant create any directory to sdtd27;
SQL> grant read,write on directory DUMP to sdtd27;

2 编辑自动备份脚本

oracle用户下

脚本名称backup.sh

[oracle@iz2ze8d5ppmu95sr2clgqyz expdp_backup]$ cat /u01/app/oracle/expdp_backup/backup.sh 

#!/bin/bash
ORACLE_SID=orcl
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1

old_file="sdtd27_"$(date -d "2 days ago" +%Y%m%d_.dmp)
rm -rf /u01/app/oracle/expdp_backup/$old_file
new_file="sdtd27_"$(date +%Y%m%d_.dmp)
/oracle/app/oracle/product/11.2.0/dbhome_1/bin/expdp sdtd27/Rzt0510sdtd directory=dump dumpfile=$new_file compression=ALL parallel=4 cluster=n

3 配置计划任务

在oracle用户下

[oracle@iz2ze8d5ppmu95sr2clgqyz ~]$ crontab -l
#逻辑备份
#每天2点逻辑全备
00 02 * * * /u01/app/oracle/expdp_backup/backup.sh > /u01/app/oracle/expdp_backup/backup$(date +%Y%m%d).log 2>&1