Oracle - RMAN Backup Stratage with FRA and Tape
Now, we are planning to move our backup to AWS. The old backup strategy is not good for this situation.
Because we cannot just change DISK to SBT_TAPE, we also want to have a copy of backup on disk.
I decide:
- Use FRA to store backup locally for the recovery windows period.
- Backup FRA to AWS everyday after daily backup.
- For monthly / weekly backup, backup to AWS directly.
- Backup archivelog to AWS directly.
- Use FRA to obsolete local backup and archivelog.
- Using recovery window for obsolete of backup on disk.
- The recovery window is set to 7 days for Disk, and using 35 days in the script for Tape.
RMAN configuration:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
Never configure any default channel attribute for sbt_type, except for archivelog deletion policy.
It helps you to keep from mistake of delete all.
###########################################################
# backup_keep_to_tape.rman
###########################################################
# 1 DATE
# 2 MONTHLY/WEEKLY
# 3 KEEP PERIOD
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
backup
incremental level 0
as compressed backupset
filesperset=10
tag='&1_LEV0_&2'
database
keep until time 'sysdate+&3';
release channel ch01;
}
###########################################################
# backup_database_to_disk.rman
###########################################################
# 1 DATE
# 2 LEVEL 0/1/2
# 3 "CUMULATIVE"/""
run{
allocate channel ch01 device type disk;
backup
incremental level &2 &3
as compressed backupset
filesperset=10
tag='&1_LEV&2_DAILY'
database
include current controlfile;
release channel ch01;
}
###########################################################
# backup_fra_to_tape.rman
###########################################################
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
backup
filesperset=10
recovery area;
release channel ch01;
}
###########################################################
# backup_archive_to_tape.rman
###########################################################
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
backup
as compressed backupset
filesperset=10
archivelog all not backed up 1 times;
release channel ch01;
}
###########################################################
# backup_daily.rman
###########################################################
# 1 DATE
# 2 LEVEL 0/1/2
# 3 "CUMULATIVE"/""
@backup_archive_to_tape.rman
@backup_database_to_disk.rman &1 &2 "&3"
@backup_fra_to_tape.rman
@backup_archive_to_tape.rman
###########################################################
# backup_release_fra.rman
###########################################################
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
backup
filesperset=10
recovery area
delete input
;
release channel ch01;
}
###########################################################
# obsolete_fra.rman
###########################################################
run{
allocate channel ch01 device type DISK;
crosscheck backup DEVICE TYPE DISK;
crosscheck copy DEVICE TYPE DISK;
delete noprompt obsolete DEVICE TYPE DISK;
release channel ch01;
}
###########################################################
# obsolete_tape.rman
###########################################################
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
crosscheck backup DEVICE TYPE SBT_TAPE;
crosscheck copy DEVICE TYPE SBT_TAPE;
delete noprompt obsolete recovery window of 60 days DEVICE TYPE SBT_TAPE;
release channel ch01;
}
Due to RMAN backups to AWS as to a tape library by using OSB, and I am using OSB + mhvtl to verify my strategy.
Install mhvtl
# yum install mt-st mtx lsscsi sg3_utils lzo-minilzo # wget http://mirror.centos.org/centos/7/os/x86_64/Packages/lzo-devel-2.06-8.el7.x86_64.rpm # rpm -ivh lzo-devel-2.06-8.el7.x86_64.rpm # wget https://sites.google.com/site/linuxvtl2/mhvtl-2016-03-10.tgz?attredirects=0 --no-check-certificate # tar xvf mhvtl-2016-03-10.tgz # groupadd vtl # useradd -g vtl vtl # cd mhvtl-1.5 # make distclean # cd kernel # make # make install # cd .. # make # make install # chown -R vtl:vtl /opt/mhvtl/ # /etc/init.d/mhvtl start ## 2 libraries, 4 tape drive for each. # lsscsi -g [0:0:0:0] disk VMware, VMware Virtual S 1.0 /dev/sda /dev/sg0 [2:0:0:0] cd/dvd NECVMWar VMware IDE CDR10 1.00 /dev/sr0 /dev/sg1 [3:0:0:0] mediumx STK L700 0105 /dev/sch0 /dev/sg5 [3:0:1:0] tape IBM ULT3580-TD5 0105 /dev/st0 /dev/sg2 [3:0:2:0] tape IBM ULT3580-TD5 0105 /dev/st1 /dev/sg3 [3:0:3:0] tape IBM ULT3580-TD4 0105 /dev/st2 /dev/sg4 [3:0:4:0] tape IBM ULT3580-TD4 0105 /dev/st3 /dev/sg6 [3:0:8:0] mediumx STK L80 0105 /dev/sch1 /dev/sg11 [3:0:9:0] tape STK T10000B 0105 /dev/st4 /dev/sg7 [3:0:10:0] tape STK T10000B 0105 /dev/st5 /dev/sg8 [3:0:11:0] tape STK T10000B 0105 /dev/st6 /dev/sg9 [3:0:12:0] tape STK T10000B 0105 /dev/st7 /dev/sg10 ## mhvtl processes, 8 for tape drives and 2 for libraries. # ps -ef|grep vtl vtl 10813 1 0 22:59 ? 00:00:00 vtltape -q 11 -v vtl 10816 1 0 22:59 ? 00:00:00 vtltape -q 12 -v vtl 10832 1 0 22:59 ? 00:00:00 vtltape -q 13 -v vtl 10837 1 0 22:59 ? 00:00:00 vtltape -q 14 -v vtl 10840 1 0 22:59 ? 00:00:00 vtltape -q 31 -v vtl 10847 1 0 22:59 ? 00:00:00 vtltape -q 32 -v vtl 10850 1 0 22:59 ? 00:00:00 vtltape -q 33 -v vtl 10858 1 0 22:59 ? 00:00:00 vtltape -q 34 -v vtl 10863 1 0 22:59 ? 00:00:00 vtllibrary -q 10 -v vtl 10867 1 0 22:59 ? 00:00:00 vtllibrary -q 30 -v root 10965 8701 0 23:00 pts/4 00:00:00 grep --color=auto vtl
Configuration Files: /etc/mhvtl
Device Files: /opt/mhvtl
The size of tapes is 500MB in default. In order to change to, modify CAPACITY in /etc/mhvtl/mhvtl.conf, and then, delete all folder in /etc/mhvtl, restart mhvtl.
# pwd /opt/mhvtl # dump_tape -f G03037TA Media density code: 0x4a Media type code : 0x26 Media description : STK T10KA media Tape Capacity : 4294967296 (4096 MBytes) Media type : Normal data Media : read-write Remaining Tape Capacity : 4294967296 (4096 MBytes) Total num of filemarks: 0 Segmentation fault (core dumped)
Install Oracle Secure Backup
# mkdir -p /usr/local/oracle/backup
# cd /usr/local/oracle/backup
# /tmp/osb_12.2.0.1.0_linux.x64_release/setup
# obtool
Oracle Secure Backup 12.2.0.1.0
login: admin
Password: 12345678
# define the server also as mediaserver
ob> lshost
testenv admin,client (via OB) in service
ob> chhost --addrole mediaserver testenv
ob> lshost
testenv admin,mediaserver,client (via OB) in service
# create a user named oracle, which is the oracle user name, and allow rman and cmdline to login.
ob> lsuser
admin admin
ob> mkuser -p 12345678 -c oracle -U oracle -G dba -N no -e westzq@gmail.com -h *:*:*+rman+cmdline oracle
ob> lsuser
admin admin
oracle oracle
# create a media group
ob> mkmf -u -C -a rman-testenv
ob> lsmf -l rman-testenv
rman-testenv:
Keep volume set: content manages reuse
Appendable: yes
Volume ID used: unique to this media family
UUID: cc2f973a-23af-1037-ae7d-000c293757c5
# define library and driver
# lsscsi -g
[1:0:0:0] cd/dvd NECVMWar VMware IDE CDR10 1.00 /dev/sr0 /dev/sg1
[2:0:0:0] disk VMware, VMware Virtual S 1.0 /dev/sda /dev/sg0
[3:0:0:0] mediumx STK L700 0105 /dev/sch0 /dev/sg10
[3:0:1:0] tape IBM ULT3580-TD5 0105 /dev/st0 /dev/sg2
[3:0:2:0] tape IBM ULT3580-TD5 0105 /dev/st2 /dev/sg4
[3:0:3:0] tape IBM ULT3580-TD4 0105 /dev/st1 /dev/sg3
[3:0:4:0] tape IBM ULT3580-TD4 0105 /dev/st4 /dev/sg6
[3:0:8:0] mediumx STK L80 0105 /dev/sch1 /dev/sg11
[3:0:9:0] tape STK T10000B 0105 /dev/st3 /dev/sg5
[3:0:10:0] tape STK T10000B 0105 /dev/st5 /dev/sg7
[3:0:11:0] tape STK T10000B 0105 /dev/st6 /dev/sg8
[3:0:12:0] tape STK T10000B 0105 /dev/st7 /dev/sg9
ob> mkdev -t library -o -a testenv:/dev/sg10 lib01
ob> mkdev -t library -o -a testenv:/dev/sg11 lib02
ob> mkdev -t tape -o -a testenv:/dev/sg2 -l lib01 -d 1 tape01
ob> mkdev -t tape -o -a testenv:/dev/sg3 -l lib01 -d 2 tape02
ob> mkdev -t tape -o -a testenv:/dev/sg4 -l lib01 -d 3 tape03
ob> mkdev -t tape -o -a testenv:/dev/sg5 -l lib01 -d 4 tape04
ob> mkdev -t tape -o -a testenv:/dev/sg6 -l lib02 -d 1 tape05
ob> mkdev -t tape -o -a testenv:/dev/sg7 -l lib02 -d 2 tape06
ob> mkdev -t tape -o -a testenv:/dev/sg8 -l lib02 -d 3 tape07
ob> mkdev -t tape -o -a testenv:/dev/sg9 -l lib02 -d 4 tape08
# the device name may be changed, refer this article to change.
Oracle Secure Backup 12.1.0.1.0 - All Backup jobs to tape failing with Error message: Pending resource availability (Doc ID 2144552.1)
# after definition
ob> lsdev
library lib01 in service
drive 1 tape01 in service
drive 2 tape02 in service
drive 3 tape03 in service
drive 4 tape04 in service
library lib02 in service
drive 1 tape05 in service
drive 2 tape06 in service
drive 3 tape07 in service
drive 4 tape08 in service
ob> inventory -L lib01
ob> inventory -L lib02
# For testing
run{
allocate channel ch01 device type sbt parms 'ENV=(ob_media_family=rman-testenv)';
backup spfile;
}
# after backup, we can see our backup jobs and pieces
ob> lsvol -a
VOID OOID Seq Volume ID Barcode Family Created Attributes
173 173 1 rman-testenv-000001 G03001TA rman-testenv 03/07.23:13 never closes; content manages reuse
175 175 1 rman-testenv-000002 G03002TA rman-testenv 03/07.23:14 never closes; content manages reuse
ob> lspiece
POID Database Content Copy Created Host Piece name
100 o12201 full 0 03/07.23:13 testenv g8trsfk9_1_1
101 o12201 full 0 03/07.23:13 testenv c-766629926-20190307-00
102 o12201 full 0 03/07.23:14 testenv gbtrsfm9_1_1
103 o12201 full 0 03/07.23:14 testenv gatrsfm9_1_1
104 o12201 full 0 03/07.23:14 testenv gdtrsfnd_1_1
105 o12201 full 0 03/07.23:14 testenv gctrsfnd_1_1
106 o12201 full 0 03/07.23:15 testenv getrsfo6_1_1
107 o12201 full 0 03/07.23:15 testenv gftrsfo6_1_1
108 o12201 full 0 03/07.23:15 testenv c-766629926-20190307-01
After configuration, we can do testing now
RO="su - oracle -c "
RMAN="cd /home/oracle/rman; export ORACLE_SID=o12201; export ORAENV_ASK=NO; . oraenv; rman target / "
timedatectl set-ntp no
for YEAR in {2018..2018}
do
for MONTH in {1..7}
do
for DAY in {1..28}
do
for HOUR in {0,12}
do
echo $$
n=$(($DAY%7))
# echo $YEAR" "$MONTH" "$DAY" "$HOUR" "$n;
timedatectl set-time "$YEAR-$MONTH-$DAY $HOUR:00:00";
if [[ $n -eq 1 && $DAY -le 7 && HOUR -eq 0 ]]; then
echo "lev0 monthly backup"
$RO "$RMAN <<EOF
@backup_keep_to_tape.rman `date +%Y%m%d%H` \"MONTHLY\" 180
EOF
"
elif [[ $n -eq 1 && $DAY -ge 8 && HOUR -eq 0 ]]; then
echo "lev0 weekly backup"
$RO "$RMAN <<EOF
@backup_keep_to_tape.rman `date +%Y%m%d%H` \"WEEKLY\" 90
EOF
"
elif [[ $n -eq 2 && HOUR -eq 0 ]]; then
echo "lev0 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 0 \"\"
EOF
"
elif [[ $n -eq 3 && HOUR -eq 0 ]]; then
echo "lev2 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 2 \"\"
EOF
"
elif [[ $n -eq 4 && HOUR -eq 0 ]]; then
echo "lev1 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 1 \"\"
EOF
"
elif [[ $n -eq 5 && HOUR -eq 0 ]]; then
echo "lev1 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 1 \"CUMULATIVE\"
EOF
"
elif [[ $n -eq 6 && HOUR -eq 0 ]]; then
echo "lev2 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 2 \"\"
EOF
"
elif [[ $n -eq 7 && HOUR -eq 0 ]]; then
echo "lev1 daily backup"
$RO "$RMAN <<EOF
@backup_daily.rman `date +%Y%m%d%H` 1 \"\"
EOF
"
else
echo "archive log backup"
$RO "$RMAN <<EOF
@backup_archive_to_tape.rman
EOF
"
fi
done
done
done
done
With this configuration,
When you try to delete all obsolete with default recovery window:
RMAN> delete obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 7 days using channel ORA_DISK_1 Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 4073 09-MAY-18 Backup Piece 4419 09-MAY-18 5it2dqhb_1_2 Backup Set 4074 09-MAY-18 Backup Piece 4420 09-MAY-18 5jt2dqk1_1_2 Backup Set 4078 09-MAY-18 Backup Piece 4424 09-MAY-18 5nt2dqp7_1_1 Backup Set 4080 09-MAY-18 Backup Piece 4426 09-MAY-18 5pt2f4m4_1_1 ... Archive Log 1582 01-JUN-18 /u01/app/oracle/fast_recovery_area/o12201/O12201/archivelog/2018_06_01/o1_mf_1_1583_fk1kf66j_.arc Archive Log 1583 01-JUN-18 /u01/app/oracle/fast_recovery_area/o12201/O12201/archivelog/2018_06_01/o1_mf_1_1584_fk2vd3n1_.arc Backup Set 4269 01-JUN-18 Backup Piece 4671 01-JUN-18 bnt4bpa4_1_1 Archive Log 1584 02-JUN-18 /u01/app/oracle/fast_recovery_area/o12201/O12201/archivelog/2018_06_02/o1_mf_1_1585_fk45l43g_.arc Backup Set 4271 02-JUN-18 Backup Piece 4673 02-JUN-18 bpt4d3g4_1_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of delete command at 06/09/2018 00:02:38 RMAN-06091: no channel allocated for maintenance (of an appropriate type)
There is no way to delete the backup pieces on tape because of mistake.
When you try to delete backup:
RMAN> delete backup; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 3694 3517 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_06/o1_mf_s_970012848_f9w8409t_.bkp 3721 3540 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_09/o1_mf_s_970272137_fb45bspw_.bkp 3736 3551 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_10/o1_mf_s_970358449_fb6sn1d8_.bkp 3751 3562 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_11/o1_mf_s_970444849_fb9g01h0_.bkp 3766 3573 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_12/o1_mf_s_970531247_fbcyvj2w_.bkp 3781 3584 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_13/o1_mf_s_970617648_fbgm7jv1_.bkp 3808 3607 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_16/o1_mf_s_970876935_fbpjg7l2_.bkp 3823 3618 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_03_17/o1_mf_s_970963242_fbs4qbt3_.bkp 4722 4308 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/backupset/2018_06_05/o1_mf_ncnn1_2018060500_LEV1_DAIL_fkd2qn0c_.bkp 4723 4309 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_06_05/o1_mf_s_977961655_fkd2qqk1_.bkp 4735 4317 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/backupset/2018_06_06/o1_mf_nnnd2_2018060600_LEV2_DAIL_fkgq3cxf_.bkp 4736 4318 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/7E60BE07B7D65699E053651010537F9B/backupset/2018_06_06/o1_mf_nnnd2_2018060600_LEV2_DAIL_fkgq3h4b_.bkp 4737 4319 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/backupset/2018_06_06/o1_mf_ncnn2_2018060600_LEV2_DAIL_fkgq3kcl_.bkp 4738 4320 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/o12201/O12201/autobackup/2018_06_06/o1_mf_s_978048050_fkgq3lx3_.bkp Do you really want to delete the above objects (enter YES or NO)?
Only backup pieces on disk will be deleted. The backup pieces on tape are safe.
We don’t backup archive logs to disk, so even the all backup pieces on disk are deleted accidently, we never lose any archive log.
But “delete copy” accidently is unrecoverable. We need to archive redo logs to a remote node through TNS against this problem.
Without allocate channel explicitly, the recover will only conside the backupset on the disk.
This is what I want. If we want to also conside the backup on the tape, also allocate a channel to tape
run{
allocate channel ch01 device type SBT_TAPE parms 'ENV=(ob_media_family=rman-testenv)';
allocate channel ch02 device type disk;
restore database preview;
}
And there is a disvantage of using FRA with Tape together for READ ONLY tablespace or pluggable database.
Due to FRA will consider all the backupsets which have been copied to tape as removable, so, when there is no more space, some of backup pieces will be removed. Including the backup pieces of READ ONLY
When OPTIMITZATION set to ON, and read only tablespaces will never be backed up again, however, it is DEVICE TYPE specified.
If the backup pieces on FRA are deleted, RMAN will backup READ ONLY on next backup, and then, copy to TAPE
And the backup pieces of READ ONLY will never be obsoleted with recovery window retention policy.
So, we may have many copied of READ ONLY tablespaces.
In order to express, I removed the entries of WEEKLY and MONTHLY backup.
RMAN> list backup of pluggable database 'PDB$SEED' summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 2935 B 0 A SBT_TAPE 02-JAN-18 1 1 YES 2018010200_LEV0_DAILY 3740 B 0 A SBT_TAPE 02-APR-18 1 1 YES 2018040200_LEV0_DAILY 4472 B 0 A SBT_TAPE 23-JUN-18 1 1 YES 2018062300_LEV0_DAILY
I have three backups of PDB$SEED after obsolete.
The solution for this is that change pluggable database/tablespace to read write periodically.
After move to read write and then back to read only, them need to be backed up and again, and the old backup don’t need to restore database after out of recovery window.
For PDB$SEED, if we apply CPU/PSU patchset regularly, it will bring database out of read only mode internally.
