Oracle ADG环境下的RMAN备份策略 - AlfredZhao
source link: https://www.cnblogs.com/jyzhao/p/17099026.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
作为IT运维人员，尤其是数据库岗位，数据的备份重于一切。
现在很多用户会有一个普遍误区，认为现在类似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 [email protected] Mon Jan 30 18:00:25 2023
Return-Path: <[email protected]>
X-Original-To: oracle
Delivered-To: [email protected]
Received: by bogon.localdomain (Postfix, from userid 10001)
id 8738341B51FA; Mon, 30 Jan 2023 18:00:25 +0800 (CST)
From: "(Cron Daemon)" <[email protected]>
To: [email protected]
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: <[email protected]>
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
Recommend
-
9
自己写的RMAN备份脚本
-
3
rman generate script set until time
-
11
12c RMAN新特性restore/recover from service远程恢复
-
6
Oracle故障处理：Rman-06207&Rman-06214 精选 原创 1、在给客户系统巡检时通过r...
-
7
Oracle使用rman定时清除7天前的日志 2022-12-08 1 分钟阅读 这一篇正规的DBA看到肯定会呲之以鼻，但对于没有用过oracle的系统运维来说，就是必须知道的事情了。 公司的 Oracle 实例有两台数据库，定时用rman备份，但是没...
-
5
测试环境： 客户端：Oracle 11.2.0.1 服务端：Oracle 19.16 测试过程： 1.低版本客户端连接高版本数据库报错ORA-28040 使用oracle 11.2.0.1 的客户端，对19c的服务端进行连接时，报错：ORA-28040: No m...
-
5
正常情况按照标准配置的环境变量，只能grid用户查看RAC集群资源状态。 crsctl stat res -t 但是绝大部分操作其实都是oracle用户来操作，比如启停数据库，操作完成以后就需要检查下集群资源状态。 看到好多DBA在现场操作时就是...
-
3
ADG无法切换：报错 ORA-16467 2023-05-11 19:16 AlfredZhao
-
10
客户通过duplicate生产备库的方式创建cascade备库。 发现每次都会遇到两个文件报错，ORA-17628: Oracle error 19505错误，且每一次跑，报错文件不一样。 现在想帮客户验证，这属于是正常现象还是bug； 本文需要先模拟客户11.2.0.3环境，构建备库...
-
4
ADG级联备库环境PSU应用验证 2023-05-23 09:48 AlfredZhao
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK