Oracle ADG环境下的RMAN备份策略

Alfred Zhao

发布于 2023-03-06 08:15:32

78500

代码可运行

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

运行总次数：0

代码可运行

作为IT运维人员，尤其是数据库岗位，数据的备份重于一切。

现在很多用户会有一个普遍误区，认为现在类似ADG这类灾备已经很完善，且实时性也更佳，往往就忽略了传统的备份效用。

但实际上，我们千万不能因为有了容灾建设就盲目忽略备份的作用，二者其实有着本质区别。很多场景，灾备都是无法替代传统备份的，二者是缺一不可的关系。

之前在

单实例Primary快速搭建Standby RAC参考手册(19.16 ADG)

中搭建了一套 Single Instance Primary + RAC Standby 的初始环境。

下面我们就给这套数据库环境制定备份策略。

1.主库备份策略

需求：数据库每天全备 + 归档每6小时备份一次；

crontab定时任务设置：

每天1:30执行数据库的全备，每6小时执行数据库归档日志的备份：

[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

#!/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 0

vi /hdd/scripts/backuparch.sh

#!/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 0

2.备库备份策略

需求：每10分钟清除4h之前的归档日志；

crontab定时任务设置：

[oracle@db01rac1 scripts]$ crontab -l
*/10 * * * * /u01/scripts/delarch.sh /u01/scripts

调用的相关脚本内容参考：

vi /u01/scripts/delarch.sh

#!/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 0

3.测试备份策略

在实际部署上面备份脚本后，验证阶段发现有报错信息：

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

查看备份日志：

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

发现针对控制文件和参数文件，在最后居然被删掉了。。

梳理脚本逻辑，确认是这条命令触发的删除：

delete noprompt obsolete;

何为obsolete？目前策略中的 REDUNDANCY 设置为2，但是因为开启了自动的控制文件备份（其中也会同时包含参数文件），所以反而手工备份的都没有被传输到备份端。

另外，需要注意的是，这不是一个小问题，因为这会给正常恢复带来很大的麻烦；

试想，没有这两个文件，尤其是控制文件的备份存档到NAS，一旦主机crash，通过NAS上的备份就成为无稽之谈。

那么解决方案呢？也很简单，修改默认值，默认值为：

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

因此对应了两种修改方式：

方式一：关闭RMAN中控制文件的自动备份；

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

方式二：设置RMAN中控制文件自动备份的路径为我们备份的路径：

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/hdd/orabak/AUTO_%F.CTL';

4.ADG环境下的特殊配置

为了应对主备角色切换期间等场景，在主备库都配置上归档删除策略，确保未传到备库的归档不会被删除：

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