作為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 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