Thinking Out Loud

October 31, 2013

What’s RMAN Backup Size

Filed under: 11g,RMAN — mdinh @ 5:35 am

So there I was, looking for a script to calculate the backup size.  After several thoughts, it was not exactly what I was looking for.

Here’s the one I created.

SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM 
(
SELECT 
  CASE
    WHEN s.backup_type='L' THEN 'ARCHIVELOG' 
    WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
    WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
    WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
  END type,
  TRUNC(s.completion_time) completion_time,
  p.bytes,
  s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND p.recid=s.recid
UNION ALL
SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
order by 1 asc,2,3
;

Note that the backup size did not have any calculation for LEVEL1.

A closer look at backup by file shows that BS Key 731 is a controlfile backup and not a datafile backup.

LAX:(SYS@db01)> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM
  2    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,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE status='A'
 16  AND p.recid =s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-OCT-30          ARCHIVELOG            2          0
2013-OCT-30          CONTROLFILE          36          0
2013-OCT-30          DATAFILECOPY       1696          0
2013-OCT-30          LEVEL0               63          0

LAX:(SYS@db01)>

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
722     B  0  A DISK        30-OCT-2013 20:47:32 1       1       YES        DINC0_WED
723     B  0  A DISK        30-OCT-2013 20:47:35 1       1       YES        DINC0_WED
724     B  0  A DISK        30-OCT-2013 20:47:45 1       1       YES        DINC0_WED
725     B  0  A DISK        30-OCT-2013 20:47:58 1       1       YES        DINC0_WED
726     B  0  A DISK        30-OCT-2013 20:48:01 1       1       YES        DINC0_WED
728     B  A  A DISK        30-OCT-2013 20:48:11 1       1       YES        AINC0_WED
729     B  F  A DISK        30-OCT-2013 20:48:20 1       1       NO         TAG20131030T204813
730     B  A  A DISK        30-OCT-2013 21:50:09 1       1       YES        MIB_UPDATE
731     B  1  A DISK        30-OCT-2013 21:53:46 1       1       YES        MIB_UPDATE
732     B  A  A DISK        30-OCT-2013 21:53:54 1       1       YES        MIB_UPDATE
733     B  F  A DISK        30-OCT-2013 21:54:05 1       1       NO         TAG20131030T215358

RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time             #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- -------------------- ------- ------- ---------- ---
1    724     B  0  A 1899045    30-OCT-2013 20:47:38 1       1       YES        DINC0_WED
2    725     B  0  A 1899051    30-OCT-2013 20:47:53 1       1       YES        DINC0_WED
3    722     B  0  A 1899041    30-OCT-2013 20:47:31 1       1       YES        DINC0_WED
4    723     B  0  A 1899043    30-OCT-2013 20:47:34 1       1       YES        DINC0_WED
5    726     B  0  A 1899055    30-OCT-2013 20:48:01 1       1       YES        DINC0_WED

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time             BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- -------------------- ------- - ------- ------- ---------- ---
1    13      1899034    30-OCT-2013 20:47:26 728     A 1       1       YES        AINC0_WED
1    14      1899064    30-OCT-2013 20:48:09 730     A 1       1       YES        MIB_UPDATE
1    15      1901013    30-OCT-2013 21:50:05 732     A 1       1       YES        MIB_UPDATE

List of Control File Backups
============================

CF Ckp SCN Ckp Time             BS Key  S #Pieces #Copies Compressed Tag
---------- -------------------- ------- - ------- ------- ---------- ---
1901124    30-OCT-2013 21:53:58 733     A 1       1       NO         TAG20131030T215358
1901091    30-OCT-2013 21:53:38 731     A 1       1       YES        MIB_UPDATE
1899073    30-OCT-2013 20:48:14 729     A 1       1       NO         TAG20131030T204813
List of SPFILE Backups
======================

Modification Time    BS Key  S #Pieces #Copies Compressed Tag
-------------------- ------- - ------- ------- ---------- ---
30-OCT-2013 20:01:18 733     A 1       1       NO         TAG20131030T215358
30-OCT-2013 20:01:18 729     A 1       1       NO         TAG20131030T204813

RMAN> list backup of database;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
722     Incr 0  1.37M      DISK        00:00:01     30-OCT-2013 20:47:32
        BP Key: 722   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9qonns33_1_1.inc0
  List of Datafiles in backup set 722
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1899041    30-OCT-2013 20:47:31 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_p9onf3nk.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
723     Incr 0  1.05M      DISK        00:00:01     30-OCT-2013 20:47:35
        BP Key: 723   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9ronns36_1_1.inc0
  List of Datafiles in backup set 723
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1899043    30-OCT-2013 20:47:34 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_paonf3on.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
724     Incr 0  38.05M     DISK        00:00:07     30-OCT-2013 20:47:45
        BP Key: 724   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9sonns3a_1_1.inc0
  List of Datafiles in backup set 724
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1899045    30-OCT-2013 20:47:38 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_pbonf3p6.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
725     Incr 0  21.10M     DISK        00:00:05     30-OCT-2013 20:47:58
        BP Key: 725   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9tonns3p_1_1.inc0
  List of Datafiles in backup set 725
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1899051    30-OCT-2013 20:47:53 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_pconf3pm.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
726     Incr 0  1.02M      DISK        00:00:00     30-OCT-2013 20:48:01
        BP Key: 726   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9uonns41_1_1.inc0
  List of Datafiles in backup set 726
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1899055    30-OCT-2013 20:48:01 /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key     File S Completion Time      Ckp SCN    Ckp Time
------- ---- - -------------------- ---------- --------------------
133     1    A 30-OCT-2013 21:53:02 1901076    30-OCT-2013 21:52:45
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
        Tag: MIB_UPDATE

134     2    A 30-OCT-2013 21:53:28 1901086    30-OCT-2013 21:53:10
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
        Tag: MIB_UPDATE

131     3    A 30-OCT-2013 21:51:54 1901024    30-OCT-2013 21:50:14
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
        Tag: MIB_UPDATE

132     4    A 30-OCT-2013 21:52:35 1901058    30-OCT-2013 21:51:59
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
        Tag: MIB_UPDATE

135     5    A 30-OCT-2013 21:53:38 1901090    30-OCT-2013 21:53:36
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
        Tag: MIB_UPDATE

RMAN>

$ du -scm /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-*FNO-*_a*
17      /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
1025    /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
257     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
1697    total
[oracle@lax:db01]/home/oracle
$

Update based on Coskan comment.  This could be because my database is small, but it does not have the granularity. The timing is much better.

LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> @bk
LAX:(SYS@db01)> set pages 1000
LAX:(SYS@db01)> select TO_CHAR(end_time, 'YYYY-MON-DD') dt, input_type, round(sum(output_bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  from v$rman_backup_job_details where status='COMPLETED' and end_time>trunc(sysdate)
  3  group by TO_CHAR(end_time, 'YYYY-MON-DD'), input_type
  4  order by 1, 2;

DT                   INPUT_TYPE            MB        MIN
-------------------- ------------- ---------- ----------
2013-NOV-01          DB INCR             1784          6

LAX:(SYS@db01)> 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,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE p.status='A'
 16  AND p.recid=s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-NOV-01          ARCHIVELOG            2          0
2013-NOV-01          CONTROLFILE          36          0
2013-NOV-01          DATAFILECOPY       1696          0
2013-NOV-01          LEVEL0               63          1

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 1 07:23:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=1452485914)

RMAN> list backup 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
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
749     B  0  A DISK        01-NOV-2013 06:52:38 1       1       YES        DINC0_FRI
750     B  0  A DISK        01-NOV-2013 06:52:47 1       1       YES        DINC0_FRI
751     B  0  A DISK        01-NOV-2013 06:53:05 1       1       YES        DINC0_FRI
752     B  0  A DISK        01-NOV-2013 06:53:47 1       1       YES        DINC0_FRI
753     B  0  A DISK        01-NOV-2013 06:53:51 1       1       YES        DINC0_FRI
755     B  A  A DISK        01-NOV-2013 06:54:07 1       1       YES        AINC0_FRI
756     B  F  A DISK        01-NOV-2013 06:54:18 1       1       NO         TAG20131101T065411
757     B  A  A DISK        01-NOV-2013 07:06:49 1       1       YES        MIB_UPDATE
758     B  1  A DISK        01-NOV-2013 07:10:26 1       1       YES        MIB_UPDATE
759     B  A  A DISK        01-NOV-2013 07:10:32 1       1       YES        MIB_UPDATE
760     B  F  A DISK        01-NOV-2013 07:10:42 1       1       NO         TAG20131101T071035

RMAN> exit
About these ads

3 Comments »

  1. why not looking at output bytes of v$rman_backup_job_details ?

    Comment by coskan — October 31, 2013 @ 11:53 pm | Reply

    • Thanks Coskan for stopping by and the suggestion. This could be because my database is small, but it does not have the granularity. The timing is much better.

      Comment by mdinh — November 1, 2013 @ 2:30 pm | Reply

  2. SYS@xxxx AS SYSDBA> l
    1 select start_time, end_time , input_bytes/1024/1024/1024, output_bytes/1024/1024/1024
    2 from v$rman_backup_job_details
    3* where to_char(start_time, ‘yyyy/mm/dd’) = ‘2014/12/13′
    SYS@xxxx AS SYSDBA> /

    START_TIME END_TIME INPUT_BYTES/1024/1024/1024 OUTPUT_BYTES/1024/1024/1024
    —————- —————- ————————– —————————
    2014/12/13 11:52 2014/12/13 18:44 4625.88281 2519.02832

    SYS@xxxx AS SYSDBA> select sum(bytes)/1024/1024/1024 from v$backup_piece where to_char(start_time, ‘yyyy/mm/dd’) = ‘2014/12/13′
    2 /

    SUM(BYTES)/1024/1024/1024
    ————————-
    3630.11266

    Value in v$backup_piece matches the output in of the sum of bytes after running the command ‘ls -l’
    of the backup pieces

    Comment by Joseph Amalraj — December 16, 2014 @ 5:38 pm | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 318 other followers

%d bloggers like this: