Thinking Out Loud

August 20, 2021

RMAN BACKUP RECOVERY AREA

Filed under: RMAN — mdinh @ 1:44 am

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

4 Comments »

  1. So what is the suggestion for terabyte size databases?

    Comment by Nick — August 20, 2021 @ 3:14 am | Reply

    • Depends on your RPO and RTO. Basically, L0 weekly and L1 cumulative daily. Backup is no good unless you can recover from it.

      Comment by mdinh — August 20, 2021 @ 4:03 am | Reply

      • I was thinking more along the lines of would you still consider doing Disk-To-Disk-To-Tape (D2D2T) or look at an alternative approach?

        For sure it is a lot of extra work for the database to backup twice, especially the full backup to FRA and then from FRA to TAPE.

        Just trying to think how best to ensure there is the ability to restore quickly from FRA back to the say the last 24 or 48 hours but not incur the extra hit each time when doing the Disk-To-Disk-To-Tape (D2D2T)

        Thanks!

        Comment by Nick — August 20, 2021 @ 5:04 am

      • There is also an option to BACKUP DEVICE TYPE sbt BACKUPSET ALL. Make sure BACKUP OPTIMIZATION ON. Typically, backup to SBT is when there are legal requirements. Certain entities are required to keep backup for up to 7+ yrs and tape backup may be stored offsite. You need to test your backup by using restore validate. Example: The database size is 2.4 TB. There are 1,495 backup pieces. Restore validate completed in ~1 hr. 1,495 backup pieces is too many as it would lead to many context switching. It comes down to size vs count and need to find a happy medium.

        Comment by mdinh — August 20, 2021 @ 11:38 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: