The current backup solution is Disk-To-Disk-To-Tape (D2D2T).
Here are the backups scheduled from cron.
$ crontab -l|grep backup
30 13 * * 0 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level0
30 13 * * 1,2,3,4,5,6 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level1
50 * * * * /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t arch
30 15 * * 6 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level0
10 15 * * 0,1,2,3,4,5 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level1
For small databases, it’s not a big deal; however, when the database size is terabytes it might not be an optimal solution to punish the database twice for each backup type.
Backing Up from the Fast Recovery Area (FRA) to Oracle Database Backup Cloud Service
The solution selected was – BACKUP DEVICE TYPE sbt RECOVERY AREA and here is a snippet of the shell script.
Please don’t ask me why a function is created for simple RMAN backup. It’s a loosing battle.
backup_database_inc() {
RECOVERY_WINDOW=`echo "show retention policy;"| $RMAN_BIN target / | awk '/CONFIGURE RETENTION POLICY TO/ {print $(NF-1);}'`
RMAN_CMDSTRING="
$RMAN_CONNECT_TARGET_CMD
show all;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
set encryption off;
run {
set command id to '$SCRIPT_NAME:LEVEL${BACKUP_LEVEL}:$BACKUP_TAG';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup device type disk tag '${BACKUP_TAG}'
incremental level ${BACKUP_LEVEL}
database
plus archivelog not backed up 1 times;
delete noprompt obsolete recovery window of 14 days device type disk;
}
set encryption on;
run {
set command id to '$SCRIPT_NAME:ARCH:$BACKUP_TAG';
backup device type sbt recovery area;
delete noprompt archivelog all backed up 1 times to disk;
delete noprompt backup device type sbt_tape completed before 'sysdate - 20';
}
exit;
"
Next, let’s review the backup from V$RMAN_BACKUP_JOB_DETAILS.
Notice INPUT_TYPE = RECVR AREA
$ sqlplus / as sysdba @ new_backup_review.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 19 19:28:30 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> select name, database_role from v$database
2 ;
NAME DATABASE_ROLE
--------- ----------------
XXXXXXX PRIMARY
SQL> alter session set nls_date_format = 'YYYY-MON-DD';
Session altered.
SQL> select
2 TRUNC(start_time) start_date,
3 input_type,
4 output_device_type output_type,
5 status,
6 round(sum(elapsed_seconds)/60) minutes,
7 round(sum(input_bytes)/1024/1024) input_mb,
8 round(sum(output_bytes)/1024/1024) output_mb
9 from V$RMAN_BACKUP_JOB_DETAILS
10 group by TRUNC(start_time), input_type, output_device_type, status
11 order by start_date asc, input_type asc, 2
12 ;
START_DATE INPUT_TYPE OUTPUT_TYPE STATUS MINUTES INPUT_MB OUTPUT_MB
----------- ------------- ----------------- ------------ ---------- ------------ ------------
2021-JUL-25 ARCHIVELOG * COMPLETED 4 3,362 408
2021-JUL-26 ARCHIVELOG * COMPLETED 13 11,214 1,375
2021-JUL-26 DB INCR * COMPLETED 2 2,766 841
2021-JUL-26 DB INCR SBT_TAPE COMPLETED 1 316 30
2021-JUL-27 ARCHIVELOG * COMPLETED 14 11,013 1,222
2021-JUL-27 DB INCR * COMPLETED 2 3,375 953
2021-JUL-27 DB INCR SBT_TAPE COMPLETED 1 237 25
2021-JUL-28 ARCHIVELOG * COMPLETED 15 10,827 1,118
2021-JUL-28 DB INCR SBT_TAPE COMPLETED 1 238 24
2021-JUL-28 DB INCR * COMPLETED 2 3,011 874
2021-JUL-29 ARCHIVELOG * COMPLETED 15 10,713 1,092
2021-JUL-29 DB INCR * COMPLETED 2 2,901 841
2021-JUL-29 DB INCR SBT_TAPE COMPLETED 1 236 23
2021-JUL-30 ARCHIVELOG * COMPLETED 15 10,601 1,011
2021-JUL-30 DB INCR * COMPLETED 2 2,783 813
2021-JUL-30 DB INCR SBT_TAPE COMPLETED 1 227 21
2021-JUL-31 ARCHIVELOG * COMPLETED 14 10,230 955
2021-JUL-31 DB INCR SBT_TAPE COMPLETED 3 26,311 3,828
2021-JUL-31 DB INCR * COMPLETED 2 3,004 819
2021-AUG-01 ARCHIVELOG * COMPLETED 14 10,937 1,197
2021-AUG-01 DB INCR SBT_TAPE COMPLETED 1 257 29
2021-AUG-01 DB INCR * COMPLETED 6 105,712 16,038
2021-AUG-02 ARCHIVELOG * COMPLETED 13 10,488 982
2021-AUG-02 DB INCR * COMPLETED 2 2,257 632
2021-AUG-02 DB INCR SBT_TAPE COMPLETED 1 236 23
2021-AUG-03 ARCHIVELOG * COMPLETED 14 10,675 1,074
2021-AUG-03 DB INCR SBT_TAPE COMPLETED 1 223 19
2021-AUG-03 DB INCR * COMPLETED 2 2,750 796
2021-AUG-04 ARCHIVELOG * COMPLETED 16 10,750 1,109
2021-AUG-04 DB INCR * COMPLETED 2 2,741 775
2021-AUG-04 DB INCR SBT_TAPE COMPLETED 1 237 23
2021-AUG-05 ARCHIVELOG * COMPLETED 17 10,493 980
2021-AUG-05 DB INCR * COMPLETED 3 2,738 788
2021-AUG-05 DB INCR SBT_TAPE COMPLETED 1 222 19
2021-AUG-06 ARCHIVELOG * COMPLETED 15 10,466 962
2021-AUG-06 DB INCR SBT_TAPE COMPLETED 1 222 19
2021-AUG-06 DB INCR * COMPLETED 2 2,604 750
2021-AUG-07 ARCHIVELOG * COMPLETED 15 10,915 1,195
2021-AUG-07 DB INCR * COMPLETED 2 2,784 780
2021-AUG-07 DB INCR SBT_TAPE COMPLETED 1 27,377 4,201
2021-AUG-08 ARCHIVELOG * COMPLETED 15 10,872 1,190
2021-AUG-08 DB INCR SBT_TAPE COMPLETED 1 252 27
2021-AUG-08 DB INCR * COMPLETED 7 104,131 15,833
2021-AUG-09 ARCHIVELOG * COMPLETED 13 10,472 967
2021-AUG-09 DB INCR * COMPLETED 2 2,313 657
2021-AUG-09 DB INCR SBT_TAPE COMPLETED 1 236 22
2021-AUG-10 ARCHIVELOG * COMPLETED 14 10,724 1,089
2021-AUG-10 DB INCR * COMPLETED 2 2,755 786
2021-AUG-10 DB INCR SBT_TAPE COMPLETED 1 223 19
2021-AUG-11 ARCHIVELOG * COMPLETED 16 10,753 1,094
2021-AUG-11 DB INCR SBT_TAPE COMPLETED 1 223 20
2021-AUG-11 DB INCR * COMPLETED 2 2,813 806
2021-AUG-12 ARCHIVELOG * COMPLETED 15 10,527 993
2021-AUG-12 DB INCR * COMPLETED 2 2,755 794
2021-AUG-12 DB INCR SBT_TAPE COMPLETED 1 224 20
2021-AUG-13 ARCHIVELOG * COMPLETED 15 10,541 997
2021-AUG-13 DB INCR * COMPLETED 2 2,625 774
2021-AUG-13 DB INCR SBT_TAPE COMPLETED 1 236 24
2021-AUG-14 ARCHIVELOG * COMPLETED 15 10,799 1,131
2021-AUG-14 DB INCR SBT_TAPE COMPLETED 1 25,945 3,837
2021-AUG-14 DB INCR * COMPLETED 2 2,953 851
2021-AUG-15 ARCHIVELOG * COMPLETED 14 10,832 1,151
2021-AUG-15 DB INCR SBT_TAPE COMPLETED 1 238 24
2021-AUG-15 DB INCR * COMPLETED 6 104,284 15,951
2021-AUG-16 ARCHIVELOG * COMPLETED 14 10,396 914
2021-AUG-16 DB INCR * COMPLETED 2 2,362 683
2021-AUG-16 DB INCR SBT_TAPE COMPLETED 1 224 20
2021-AUG-17 ARCHIVELOG * COMPLETED 19 10,678 1,059
2021-AUG-17 DB INCR * COMPLETED 2 2,577 721
2021-AUG-17 DB INCR SBT_TAPE COMPLETED 1 241 25
2021-AUG-17 RECVR AREA * COMPLETED 63 50,162 49,461
2021-AUG-18 ARCHIVELOG * COMPLETED 3 1,981 98
2021-AUG-18 ARCHIVELOG DISK COMPLETED 0 205 17
2021-AUG-18 RECVR AREA COMPLETED 0 0 0
2021-AUG-18 RECVR AREA * COMPLETED 125 146,534 76,160
2021-AUG-18 RECVR AREA SBT_TAPE COMPLETED 14 2,462 1,530
2021-AUG-19 RECVR AREA * COMPLETED 42 23,975 5,028
77 rows selected.
SQL> /* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
