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$
4 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
Thanks Maaz for suggestions.
Hello .
How can I access to backup data from file system just like you ?
I searched in file system but I Didn’t find .
Hi Salman,
Please try list backup of database summary or list backup of database or list backup.
Thanks Michael.