oracle备份策略

一、RMAN热备份
1、查看是否开启归档

archive log list;

归档未开启

归档开启方法
– 关闭数据库，重启至mount模式

shutdown immediate
startup mount

– 开启归档模式

alter database archivelog;

– 打开数据库

alter database open;

– 检查归档模式是否打开

archive log list

如上图，数据库打开归档模式之后，接下来就可以开始部署 RMAN 脚本进行在线备份了。

2、定期删除归档
由于打开归档模式后会源源不断的产生归档日志，所以建议部署一个定期删除归档的任务：

oracle 用户下执行

mkdir -p /home/oracle/scriptscat << EOF > "/home/oracle/scripts/del_arch.sh"
#!/bin/bash
source ~/.bash_profile
deltime=\$(date +"20%y%m%d%H%M%S")
rman target / nocatalog msglog /home/oracle/scripts/del_arch_\${deltime}.log <<RMAN_EOF
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-7';
delete noprompt force archivelog until time 'SYSDATE-10';
RMAN_EOF
EOFchmod +x /home/oracle/scripts/del_arch.sh

root 用户下执行

cat <<EOF >> /var/spool/cron/oracle
00 02 * * * /home/oracle/scripts/del_arch.sh
EOF

3、建立本地备份
新建本地备份脚本，该脚本为增量备份脚本，分为 0 级和 1 级，周日 0 级全备，周一至周六 1 级备份。
首先需要在本地磁盘创建一个备份目录，需要有足够的磁盘空间：

oracle 用户下执行

mkdir -p /home/oracle/backup
chmod -R 775 /home/oracle/backup

周日为0级备份脚本：

cat << EOF > "/home/oracle/scripts/dbbackup_lv0.sh"

#!/bin/bash
source ~/.bash_profile
backtime=\$(date +"20%y%m%d%H%M%S")
rman target / log=/home/oracle/backup/level0_backup_\${backtime}.log<<RMAN_EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql"alter system archive log current";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup incremental level 0 database include current controlfile format '/home/oracle/backup/backlv0_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT format '/home/oracle/backup/arch_%d_%T_%t_%s_%p';
release channel c1;
release channel c2;
}
RMAN_EOF
EOF

周一至周六为 1 级备份脚本：

cat << EOF > "/home/oracle/scripts/dbbackup_lv1.sh"

#!/bin/bash
source ~/.bash_profile
backtime=\$(date +"20%y%m%d%H%M%S")
rman target / log=/home/oracle/backup/level1_backup_\${backtime}.log<<RMAN_EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql"alter system archive log current";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup incremental level 1 database include current controlfile format '/home/oracle/backup/backlv1_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT format '/home/oracle/backup/arch_%d_%T_%t_%s_%p';
release channel c1;
release channel c2;
}
RMAN_EOF
EOF

cd /home/oracle/scripts
chmod +x dbbackup_lv0.sh
chmod +x dbbackup_lv1.sh

添加到定时任务

在 root 用户下执行

su - root
echo "00 00 * * 0 /home/oracle/scripts/dbbackup_lv0.sh" >> /var/spool/cron/oracle
echo "00 00 * * 1,2,3,4,5,6 /home/oracle/scripts/dbbackup_lv1.sh" >> /var/spool/cron/oracle

连接到 RMAN 查看备份详细情况

rman target /
list backup;

验证备份集

restore validate database;
restore validate spfile;
restore validate controlfile;

4、恢复
①全量恢复：

数据启动到mount状态

sqlplus / as sysdba
startup mount
exit

启动 RMAN

rman target /

恢复数据库

RESTORE DATABASE;

应用归档日志（如果你的数据库是使用归档日志模式运行的，你需要应用归档日志以确保恢复到最新状态：）

RECOVER DATABASE;

退出RMAN

exit

打开数据库

sqlplus / as sysdba
ALTER DATABASE OPEN;

②恢复指定的数据文件（以system01.dbf为例）

确认损坏的数据文件（非必须）

sqlplus / as sysdba
SELECT * FROM DBA_DATA_FILES WHERE FILE_NAME = '/u01/app/oracle/oradata/wjz/system01.dbf';
exit

数据启动到mount状态

sqlplus / as sysdba
startup mount
exit

备份当前状态（非必须）

rman target /
BACKUP DATABASE;

恢复损坏的数据文件

 RESTORE DATAFILE '/u01/app/oracle/oradata/wjz/system01.dbf';

应用归档日志

 RECOVER DATAFILE '/u01/app/oracle/oradata/wjz/system01.dbf';

退出RMAN

exit

打开数据库

sqlplus / as sysdba
ALTER DATABASE OPEN;