作为IT运维人员，尤其是数据库岗位，数据的备份重于一切。
现在很多用户会有一个普遍误区，认为现在类似ADG这类灾备已经很完善，且实时性也更佳，往往就忽略了传统的备份效用。
但实际上，我们千万不能因为有了容灾建设就盲目忽略备份的作用，二者其实有着本质区别。很多场景，灾备都是无法替代传统备份的，二者是缺一不可的关系。
之前在
- 单实例Primary快速搭建Standby RAC参考手册(19.16 ADG)
中搭建了一套 Single Instance Primary RAC Standby 的初始环境。
下面我们就给这套数据库环境制定备份策略。
- 1.主库备份策略
- 2.备库备份策略
- 3.测试备份策略
- 4.ADG环境下的特殊配置
1.主库备份策略
需求：数据库每天全备 归档每6小时备份一次；
crontab定时任务设置：
每天1:30执行数据库的全备，每6小时执行数据库归档日志的备份：
代码语言：javascript复制[oracle@bogon orabak]$ crontab -l
30 1 * * * /hdd/scripts/backup.sh /hdd/orabak
0 */6 * * * /hdd/scripts/backuparch.sh /hdd/orabak调用的相关脚本内容参考：
vi /hdd/scripts/backup.sh
代码语言：javascript复制#!/bin/bash
#ENV
export ORACLE_SID=demo
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
#backup RMAN
if [ ! -d "$1" ]; then
echo "You have input no dir for $1"
exit 1
fi
echo "=================================================================================" >>${1}/backup_full.log
echo "Begin backup at : `date`" >> ${1}/backup_full.log
rman target / <<EOF >> ${1}/backup_full.log
run {
CONFIGURE RETENTION POLICY TO REDUNDANCY = 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup as compressed backupset database format '${1}/FULLBAK_%d_%T_%s_%p.DBFILE';
backup current controlfile format '${1}/%d.%s.%p.%T.CTL';
backup spfile format '${1}/%d.%s.%p.%T.SPFILE';
crosscheck backup;
crosscheck copy;
sql "alter system archive log current";
backup as COMPRESSED backupset archivelog all not backed up format '${1}/%d.%s.%p.%T.ARC';
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-10';
delete noprompt expired backup;
delete noprompt obsolete;
}
exit
EOF
echo "End backup at : `date`" >>${1}/backup_full.log
echo "Begin cp to NAS at : `date`" >>${1}/backup_full.log
cp ${1}/*`date %Y%m%d`* /public/Others/orabak
echo "End cp to NAS at : `date`" >>${1}/backup_full.log
echo "=================================================================================" >>${1}/backup_full.log
exit 0vi /hdd/scripts/backuparch.sh
代码语言：javascript复制#!/bin/bash
#ENV
export ORACLE_SID=demo
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
#backup RMAN
if [ ! -d "$1" ]; then
echo "You have input no dir for $1"
exit 1
fi
echo "=================================================================================" >>${1}/backup_arch.log
echo "Begin backup at : `date`" >> ${1}/backup_arch.log
rman target / <<EOF >> ${1}/backup_arch.log
run {
CONFIGURE RETENTION POLICY TO REDUNDANCY = 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup current controlfile format '${1}/%d.%s.%p.%T.CTL';
backup spfile format '${1}/%d.%s.%p.%T.SPFILE';
crosscheck backup;
crosscheck copy;
sql "alter system archive log current";
backup as COMPRESSED backupset archivelog all not backed up format '${1}/%d.%s.%p.%T.ARC';
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-10';
delete noprompt expired backup;
delete noprompt obsolete;
}
exit
EOF
echo "End backup at : `date`" >>${1}/backup_arch.log
echo "Begin cp to NAS at : `date`" >>${1}/backup_arch.log
cp ${1}/*`date %Y%m%d`*.{CTL,SPFILE,ARC} /public/Others/orabak
echo "End cp to NAS at : `date`" >>${1}/backup_arch.log
echo "=================================================================================" >>${1}/backup_arch.log
exit 02.备库备份策略
需求：每10分钟清除4h之前的归档日志；
crontab定时任务设置：
代码语言：javascript复制[oracle@db01rac1 scripts]$ crontab -l
*/10 * * * * /u01/scripts/delarch.sh /u01/scripts调用的相关脚本内容参考：
vi /u01/scripts/delarch.sh
代码语言：javascript复制#!/bin/bash
#ENV
export ORACLE_SID=jydb1;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1;
export PATH=$ORACLE_HOME/bin:$PATH;
#RMAN delete archivelog
if [ ! -d "$1" ]; then
echo "You have input no dir for $1"
exit 1
fi
echo "=================================================================================" >>${1}/delarch.log
echo "Begin backup at : `date`" >> ${1}/delarch.log
rman target / <<EOF >> ${1}/delarch.log
delete noprompt archivelog all completed before 'sysdate - 1/24*4';
EOF
echo "End backup at : `date`" >>${1}/delarch.log
echo "=================================================================================" >>${1}/delarch.log
exit 03.测试备份策略
在实际部署上面备份脚本后，验证阶段发现有报错信息：
代码语言：javascript复制You have mail in /var/spool/mail/oracle
[oracle@bogon ~]$
[oracle@bogon ~]$ tail -200f /var/spool/mail/oracle
From oracle@bogon.localdomain Mon Jan 30 18:00:25 2023
Return-Path: <oracle@bogon.localdomain>
X-Original-To: oracle
Delivered-To: oracle@bogon.localdomain
Received: by bogon.localdomain (Postfix, from userid 10001)
id 8738341B51FA; Mon, 30 Jan 2023 18:00:25 0800 (CST)
From: "(Cron Daemon)" <oracle@bogon.localdomain>
To: oracle@bogon.localdomain
Subject: Cron <oracle@bogon> /hdd/scripts/backuparch.sh /hdd/orabak
Content-Type: text/plain; charset=UTF-8
Auto-Submitted: auto-generated
Precedence: bulk
X-Cron-Env: <XDG_SESSION_ID=4131>
X-Cron-Env: <XDG_RUNTIME_DIR=/run/user/10001>
X-Cron-Env: <LANG=en_US.UTF-8>
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/home/oracle>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=oracle>
X-Cron-Env: <USER=oracle>
Message-Id: <20230130100025.8738341B51FA@bogon.localdomain>
Date: Mon, 30 Jan 2023 18:00:18 0800 (CST)
cp: cannot stat ‘/hdd/orabak/*20230130*.CTL’: No such file or directory
cp: cannot stat ‘/hdd/orabak/*20230130*.SPFILE’: No such file or directory查看备份日志：
代码语言：javascript复制516 Deleting the following obsolete backups and copies:
517 Type Key Completion Time Filename/Handle
518 -------------------- ------ ------------------ --------------------
519 Backup Set 20 30-JAN-23
520 Backup Piece 20 30-JAN-23 /flash/fast_recovery_area/DEMO/autobackup/2023_01_30/o1_mf_s_1127488370_kxgvhm1c_.bkp
521 Backup Set 30 30-JAN-23
522 Backup Piece 30 30-JAN-23 /flash/fast_recovery_area/DEMO/autobackup/2023_01_30/o1_mf_s_1127488380_kxgvhwj0_.bkp
523 Backup Set 31 30-JAN-23
524 Backup Piece 31 30-JAN-23 /hdd/orabak/DEMO.56.1.20230130.CTL
525 Backup Set 32 30-JAN-23
526 Backup Piece 32 30-JAN-23 /hdd/orabak/DEMO.57.1.20230130.SPFILE
527 deleted backup piece
528 backup piece handle=/flash/fast_recovery_area/DEMO/autobackup/2023_01_30/o1_mf_s_1127488370_kxgvhm1c_.bkp RECID=20 STAMP=1127488371
529 Deleted 1 objects
530
531 deleted backup piece
532 backup piece handle=/flash/fast_recovery_area/DEMO/autobackup/2023_01_30/o1_mf_s_1127488380_kxgvhwj0_.bkp RECID=30 STAMP=1127488380
533 Deleted 1 objects
534
535 deleted backup piece
536 backup piece handle=/hdd/orabak/DEMO.57.1.20230130.SPFILE RECID=32 STAMP=1127498407
537 Deleted 1 objects
538
539 deleted backup piece
540 backup piece handle=/hdd/orabak/DEMO.56.1.20230130.CTL RECID=31 STAMP=1127498406
541 Deleted 1 objects
542
543
544 RMAN>
545发现针对控制文件和参数文件，在最后居然被删掉了。。
梳理脚本逻辑，确认是这条命令触发的删除：
代码语言：javascript复制delete noprompt obsolete;何为obsolete？目前策略中的 REDUNDANCY 设置为2，但是因为开启了自动的控制文件备份（其中也会同时包含参数文件），所以反而手工备份的都没有被传输到备份端。
另外，需要注意的是，这不是一个小问题，因为这会给正常恢复带来很大的麻烦；
试想，没有这两个文件，尤其是控制文件的备份存档到NAS，一旦主机crash，通过NAS上的备份就成为无稽之谈。
那么解决方案呢？也很简单，修改默认值，默认值为：
代码语言：javascript复制CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default因此对应了两种修改方式：
方式一：关闭RMAN中控制文件的自动备份；
代码语言：javascript复制CONFIGURE CONTROLFILE AUTOBACKUP OFF; 方式二：设置RMAN中控制文件自动备份的路径为我们备份的路径：
代码语言：javascript复制CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/hdd/orabak/AUTO_%F.CTL'; 4.ADG环境下的特殊配置
为了应对主备角色切换期间等场景，在主备库都配置上归档删除策略，确保未传到备库的归档不会被删除：
代码语言：javascript复制RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
new RMAN configuration parameters are successfully stored
