How to determine RMAN backup size

Posted in: Technical Track

A time long ago, I wrote a SQL query to determine RMAN backup size. Here is another validation for the SQL query.

Why is this important? Because, as part of a backup review, it’s good to know the growth for the backup size in order to allocate space for backup and timing for times when there might be a performance degradation with backup.


SQL> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  FROM
  3  (
  4  SELECT
  5  CASE
  6    WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  7    WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  8    WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  9    WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
 10  END type,
 11  TRUNC(s.completion_time) completion_time, p.bytes, s.elapsed_seconds
 12  FROM v$backup_piece p, v$backup_set s
 13  WHERE p.status='A' AND p.recid=s.recid
 14  UNION ALL
 15  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 16  )
 17  GROUP BY TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 18  ORDER BY 1 ASC,2,3
 19  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2019-JAN-20          ARCHIVELOG          212          0
2019-JAN-20          CONTROLFILE         512          0
2019-JAN-20          LEVEL0            25943         53
2019-JAN-21          ARCHIVELOG          446          1
2019-JAN-21          CONTROLFILE         607          1
2019-JAN-21          LEVEL1              109          1
2019-JAN-22          ARCHIVELOG          484          1
2019-JAN-22          CONTROLFILE         607          1
2019-JAN-22          LEVEL1              185          1
2019-JAN-23          ARCHIVELOG          509          1
2019-JAN-23          CONTROLFILE         607          1
2019-JAN-23          LEVEL1              190          1
2019-JAN-24          ARCHIVELOG          464          1
2019-JAN-24          CONTROLFILE         607          1
2019-JAN-24          LEVEL1              190          1
2019-JAN-25          ARCHIVELOG          474          1
2019-JAN-25          CONTROLFILE         558          0
2019-JAN-26          ARCHIVELOG          247          0
2019-JAN-26          CONTROLFILE         607          1
2019-JAN-26          LEVEL1              338          2
2019-JAN-27          ARCHIVELOG          235          0
2019-JAN-27          CONTROLFILE         607          1
2019-JAN-27          LEVEL0            26036         53
2019-JAN-28          ARCHIVELOG          406          1
2019-JAN-28          CONTROLFILE         490          0
2019-JAN-28          LEVEL1              108          1

26 rows selected.

SQL>

We will be looking at LEVEL0 for 2019-JAN-27.

We can use the following commands to find info about backup:


list backup of database summary;
list backup tag LV0BKP; (change the tag accordingly)

 

Here is an example:

RMAN> list backup of database summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
95525   B  0  A DISK        2019-JAN-20 02:53:18 1       1       YES        LV0BKP
95578   B  1  A DISK        2019-JAN-21 02:01:22 1       1       YES        LV1BKP
95631   B  1  A DISK        2019-JAN-22 02:01:50 1       1       YES        LV1BKP
95684   B  1  A DISK        2019-JAN-23 02:01:51 1       1       YES        LV1BKP
95737   B  1  A DISK        2019-JAN-24 02:01:59 1       1       YES        LV1BKP
95838   B  1  A DISK        2019-JAN-26 02:02:36 1       1       YES        LV1BKP
95891   B  0  A DISK        2019-JAN-27 02:53:37 1       1       YES        LV0BKP
95944   B  1  A DISK        2019-JAN-28 02:01:24 1       1       YES        LV1BKP

RMAN> list backup tag LV0BKP;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
95525   Incr 0  25.34G     DISK        00:52:31     2019-JAN-20 02:53:18
        BP Key: 95525   Status: AVAILABLE  Compressed: YES  Tag: LV0BKP
        Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998013647_s95894_p1
  List of Datafiles in backup set 95525
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 7749128727 2019-JAN-20 02:00:49 +DATA/
  31   0  Incr 7749128727 2019-JAN-20 02:00:49 +DATA/

BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
95528   4.10M      DISK        00:00:01     2019-JAN-20 02:53:35
        BP Key: 95528   Status: AVAILABLE  Compressed: YES  Tag: LV0BKP
        Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998016814_s95899_p1

  List of Archived Logs in backup set 95528
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    199219  7749128684 2019-JAN-20 02:00:42 7749133741 2019-JAN-20 02:15:39
  1    199220  7749133741 2019-JAN-20 02:15:39 7749138859 2019-JAN-20 02:30:41
  1    199221  7749138859 2019-JAN-20 02:30:41 7749145564 2019-JAN-20 02:45:42
  1    199222  7749145564 2019-JAN-20 02:45:42 7749148473 2019-JAN-20 02:53:32
  2    197761  7749128688 2019-JAN-20 02:00:44 7749133746 2019-JAN-20 02:15:41
  2    197762  7749133746 2019-JAN-20 02:15:41 7749138865 2019-JAN-20 02:30:43
  2    197763  7749138865 2019-JAN-20 02:30:43 7749145560 2019-JAN-20 02:45:41
  2    197764  7749145560 2019-JAN-20 02:45:41 7749148462 2019-JAN-20 02:53:30
  2    197765  7749148462 2019-JAN-20 02:53:30 7749148614 2019-JAN-20 02:53:33

================================================================================
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
95891   Incr 0  25.43G     DISK        00:52:51     2019-JAN-27 02:53:37
        BP Key: 95891   Status: AVAILABLE  Compressed: YES  Tag: LV0BKP
        Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998618446_s96260_p1
  List of Datafiles in backup set 95891
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 7761624701 2019-JAN-27 02:00:48 +DATA/
  31   0  Incr 7761624701 2019-JAN-27 02:00:48 +DATA/

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
95892   Incr 0  1.78M      DISK        00:00:01     2019-JAN-27 02:53:43
        BP Key: 95892   Status: AVAILABLE  Compressed: YES  Tag: LV0BKP
        Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998621622_s96263_p1
  Control File Included: Ckp SCN: 7761648225   Ckp time: 2019-JAN-27 02:53:42

BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
95894   4.94M      DISK        00:00:00     2019-JAN-27 02:53:54
        BP Key: 95894   Status: AVAILABLE  Compressed: YES  Tag: LV0BKP
        Piece Name: /pub/ora-backupfs/oracle/nyprod/L0_NYPROD_t998621634_s96265_p1

  List of Archived Logs in backup set 95894
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    199960  7761624430 2019-JAN-27 02:00:43 7761631279 2019-JAN-27 02:15:43
  1    199961  7761631279 2019-JAN-27 02:15:43 7761637156 2019-JAN-27 02:30:42
  1    199962  7761637156 2019-JAN-27 02:30:42 7761643528 2019-JAN-27 02:45:42
  1    199963  7761643528 2019-JAN-27 02:45:42 7761648290 2019-JAN-27 02:53:51
  2    198522  7761623633 2019-JAN-27 02:00:39 7761631270 2019-JAN-27 02:15:37
  2    198523  7761631270 2019-JAN-27 02:15:37 7761637145 2019-JAN-27 02:30:36
  2    198524  7761637145 2019-JAN-27 02:30:36 7761643329 2019-JAN-27 02:45:33
  2    198525  7761643329 2019-JAN-27 02:45:33 7761648280 2019-JAN-27 02:53:49
  2    198526  7761648280 2019-JAN-27 02:53:49 7761648431 2019-JAN-27 02:53:53

RMAN> exit

Here’s a view from filesystem:


$ ls -lrth L0_NYPROD_t998618446_s96260_p1
-rw-r----- 1 oracle asmadmin 26G Jan 27 02:53 L0_NYPROD_t998618446_s96260_p1
$ ls -lrth L0_NYPROD_t998621622_s96263_p1 
-rw-r----- 1 oracle asmadmin 1.8M Jan 27 02:53 L0_NYPROD_t998621622_s96263_p1 
$ ls -lrth L0_NYPROD_t998621634_s96265_p1 
-rw-r----- 1 oracle asmadmin 5.0M Jan 27 02:53 L0_NYPROD_t998621634_s96265_p1$
email

Interested in working with Michael? Schedule a tech call.

2 Comments. Leave new

Hi Michael,

Highly informative!!
Just to add to your findings, using V$RMAN_BACKUP_JOB_DETAILS will provide us with status column along with backup size.

Regards,
Maaz

Reply
Michael Dinh
March 7, 2019 9:18 pm

Thanks Maaz for suggestions.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *