in Oracle

RMAN — 5b : (More) Useful KEYWORDs and SubClauses

Here are a few more useful KEYWORDs and SubClauses

SECTION SIZE
SECTION SIZE allows you to split a single Database file into multiple sections. Note : A Database File span BackupPieces. The difference between BackupPieces and Sections is that the former are serially done while Sections can be done in-parallel. (SECTION SIZE cannot be used with MAXPIECESIZE). Here, I first show a datafile in 4 BackupPieces defined by SECTION SIZE.

SQL> select file_id, size_mb
2 from
3 (select file_id, bytes/1048576 size_mb
4 from dba_data_files
5 order by 2 desc)
6 where rownum = 1;

FILE_ID SIZE_MB
---------- ----------
2 1259

SQL>

RMAN> backup datafile 2 section size 400M;

Starting backup at 19-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 1 through 51200
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5020_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 51201 through 102400
channel ORA_DISK_1: starting piece 2 at 19-JUL-15
channel ORA_DISK_1: finished piece 2 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5sw2_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 102401 through 153600
channel ORA_DISK_1: starting piece 3 at 19-JUL-15
channel ORA_DISK_1: finished piece 3 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf76wl_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 153601 through 161152
channel ORA_DISK_1: starting piece 4 at 19-JUL-15
channel ORA_DISK_1: finished piece 4 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf8y7p_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885508385_btqf92c7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>
RMAN> list backup of datafile 2 completed after "trunc(sysdate)";


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 452.02M DISK 00:02:09 19-JUL-15
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14135401 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 68
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:02:09 19-JUL-15 YES TAG20150719T223055

List of Backup Pieces for backup set 68 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
71 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5020_.bkp
72 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5sw2_.bkp
73 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf76wl_.bkp
74 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf8y7p_.bkp

RMAN>

Note how the blocks of the datafile are allocated to each Section. Section 1 is for Blocks 1 to 51200. Section 2 is for Blocks 51201 to 102400. And so on for subsequent Sections.
Next, I run two channels for in-parallel backups of the sections, still comprising of one BackupSet for the datafile.

RMAN> run
2> {
3> allocate channel d1 device type disk;
4> allocate channel d2 device type disk;
5> backup datafile 2 section size 400M;
6> }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=40 device type=DISK

allocated channel: d2
channel d2: SID=44 device type=DISK

Starting backup at 19-JUL-15
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 1 through 51200
channel d1: starting piece 1 at 19-JUL-15
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 51201 through 102400
channel d2: starting piece 2 at 19-JUL-15
channel d1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp tag=TAG20150719T224411 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:26
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 102401 through 153600
channel d1: starting piece 3 at 19-JUL-15
channel d2: finished piece 2 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp tag=TAG20150719T224411 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:51
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 153601 through 161152
channel d2: starting piece 4 at 19-JUL-15
channel d2: finished piece 4 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp tag=TAG20150719T224411 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:03
channel d1: finished piece 3 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp tag=TAG20150719T224411 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:50
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885509127_btqg07qq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15
released channel: d1
released channel: d2

RMAN> list backupset 72;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 452.02M DISK 00:01:13 19-JUL-15
List of Datafiles in backup set 72
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14136475 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 72
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:01:13 19-JUL-15 YES TAG20150719T224411

List of Backup Pieces for backup set 72 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
81 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp
82 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp
84 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp
83 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp

RMAN>

Thus, we have BackupSet 72 consisting of the 4 BackupPieces. The BackupPieces were created using 2 Channels running in-parallel with different ranges of Blocks.

FORMAT (and the FRA)
If you use the FRA with db_recovery_file_dest, Oracle tracks usage against the limit specified by db_recovery_file_dest_size.
However, if you use the FORMAT clause, such backups are *not* tracked as being part of the FRA. This also means that Oracle would under-report usage of the FRA and wouldn’t be able to identify when the FRA nears the limit.

For example, I first report the FRA usage and then run Backups without and and then with the FORMAT clause. Note how the FRA usage report reflects the Backups without the FORMAT clause only.

SQL> set linesize 132
SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /NEW_FS/oracle/FRA
db_recovery_file_dest_size big integer 8G

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 32.96 22.4 34
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

RMAN> backup as compressed backupset tablespace hemant filesperset=1;

Starting backup at 19-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2vkb_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2yoc_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh31vh_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh34ys_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh382g_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510251_btqh3c9d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 33.54 22.92 40
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

RMAN> backup as compressed backupset tablespace hemant format '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/%U' filesperset=1;

Starting backup at 19-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/2vqcfk8t_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/30qcfk90_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/31qcfk93_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/32qcfk96_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/33qcfk9a_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510445_btqh9f6x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 33.65 23.5 41
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

Note how the first backup (which did NOT specify a FORMAT clause) consisted of 5 BackupPieces for the Datafiles plus 1 for the AutoBackup. The NUMBER_OF_FILES for BACKUP PIECE in v$flash_recovery_area_usage was updaetd from 34 to 40.
However, while the second backup with the FORMAT clause (actually pointing to the same physical directory) created 5 Datafile BackupPieces and 1 AutoBackup again, the count in v$flash_recovery_area_usage did NOT increment by 6 (the increment by 1 is for the AutoBackup).

Thus, for this second, with FORMAT clause, backup, while a LIST BACKUP command would show it and the backup would be usable for Restore scenarios, v$flash_recovery_area_usage does not track it. Therefore, Oracle hasn’t incremented the PERCENT_SPACE_USED either. This PERCENT_SPACE_USED is very important for Oracle to automatically purge older (i.e. OBSOLETE) backups when space usage hits critical limits.

TAG
The TAG clause allows us to specifically define our own Tags. Note my previous backups showed a TAG that indicates merely date and time as in :

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
------- -- -- - ----------- --------------- ------- ------- ---------- ---
28 B F A DISK 23-JUN-15 1 1 NO TAG20150623T170721
29 B F A DISK 23-JUN-15 1 1 NO TAG20150623T170846
30 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
31 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
32 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
33 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
34 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
35 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
36 B F A DISK 04-JUL-15 1 1 NO TAG20150704T131927
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
39 B F A DISK 08-JUL-15 1 1 NO TAG20150708T211118
40 B F A DISK 08-JUL-15 1 1 NO TAG20150708T215526
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
61 B F A DISK 12-JUL-15 1 1 NO TAG20150712T222957
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
67 B F A DISK 12-JUL-15 1 1 NO TAG20150712T223226
68 B F A DISK 19-JUL-15 4 1 YES TAG20150719T223055
69 B F A DISK 19-JUL-15 1 1 NO TAG20150719T223305
71 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224023
72 B F A DISK 19-JUL-15 4 1 YES TAG20150719T224411
73 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224527
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
75 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230119
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
81 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230411
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
87 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230725

RMAN>

However, I can use the TAG clause to identify specific Backups distinctively. Later, for the RESTORE command, the known TAG becomes useful as I can RESTORE FROM TAG. Here’s how I create backups with specific TAGs.

RMAN> backup as compressed backupset tablespace HEMANT TAG 'Hemant_19Jul15';

Starting backup at 19-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_HEMANT_19JUL15_btqhy6js_.bkp tag=HEMANT_19JUL15 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885511125_btqhyoo9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>
RMAN> list backup of tablespace hemant completed after "(sysdate-5/1440)";


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88 Full 33.82M DISK 00:00:07 19-JUL-15
BP Key: 100 Status: AVAILABLE Compressed: YES Tag: HEMANT_19JUL15
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_HEMANT_19JUL15_btqhy6js_.bkp
List of Datafiles in backup set 88
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
7 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
8 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
9 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
11 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

RMAN> list backup of tablespace HEMANT summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
88 B F A DISK 19-JUL-15 1 1 YES HEMANT_19JUL15

RMAN>

Notice how the latest backup of Tablespace HEMANT is tagged by the TAG I had specified during the BACKUP run.

completed after “..date/time clause”
Note how, in the listings above, I have used the ‘completed after “trunc(sysdate)” ‘ and ‘completed after “(sysdate-5/1440)” to specify a Date/Time as a filter.

Here’s another example to show Backups completed in the month of July-2015. (The two backups of 23-Jun-2015 are excluded).

RMAN> list backup summary completed after "to_date('JUL-15','MON-RR')";


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
30 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
31 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
32 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
33 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
34 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
35 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
36 B F A DISK 04-JUL-15 1 1 NO TAG20150704T131927
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
39 B F A DISK 08-JUL-15 1 1 NO TAG20150708T211118
40 B F A DISK 08-JUL-15 1 1 NO TAG20150708T215526
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
61 B F A DISK 12-JUL-15 1 1 NO TAG20150712T222957
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
67 B F A DISK 12-JUL-15 1 1 NO TAG20150712T223226
68 B F A DISK 19-JUL-15 4 1 YES TAG20150719T223055
69 B F A DISK 19-JUL-15 1 1 NO TAG20150719T223305
71 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224023
72 B F A DISK 19-JUL-15 4 1 YES TAG20150719T224411
73 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224527
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
75 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230119
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
81 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230411
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
87 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230725
88 B F A DISK 19-JUL-15 1 1 YES HEMANT_19JUL15
89 B F A DISK 19-JUL-15 1 1 NO TAG20150719T231845

RMAN>

This shows that I can use Date Format masks in my filter.

Here’s another example (note : if you use ‘between’, it seems that the two date formats must match, else no records are returned as in the first listing below):

RMAN> list backup completed between
2> "to_date('19-JUL-2015 22:45:00','DD-MON-YYYY HH24:MI:SS')"
3> and
4> "to_date(sysdate)";

specification does not match any backup in the repository

RMAN> list backup completed between
2> "to_date('19-JUL-2015 22:45:00','DD-MON-YYYY HH24:MI:SS')"
3> and
4> "to_date('19-JUL-2015 23:15:00','DD-MON-YYYY HH24:MI:SS')";


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 452.02M DISK 00:01:13 19-JUL-15
List of Datafiles in backup set 72
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14136475 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 72
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:01:13 19-JUL-15 YES TAG20150719T224411

List of Backup Pieces for backup set 72 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
81 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp
82 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp
84 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp
83 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 85 Status: AVAILABLE Compressed: NO Tag: TAG20150719T224527
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885509127_btqg07qq_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14136581 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 33.82M DISK 00:00:23 19-JUL-15
BP Key: 86 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230053
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230053_btqgx5vx_.bkp
List of Datafiles in backup set 74
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
7 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
8 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
9 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
11 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 87 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230119
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510079_btqgxzd8_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14137759 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
76 Full 7.41M DISK 00:00:02 19-JUL-15
BP Key: 88 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2vkb_.bkp
List of Datafiles in backup set 76
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14137868 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77 Full 7.40M DISK 00:00:02 19-JUL-15
BP Key: 89 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2yoc_.bkp
List of Datafiles in backup set 77
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 14137870 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
78 Full 7.38M DISK 00:00:02 19-JUL-15
BP Key: 90 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh31vh_.bkp
List of Datafiles in backup set 78
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 14137872 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
79 Full 7.43M DISK 00:00:02 19-JUL-15
BP Key: 91 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh34ys_.bkp
List of Datafiles in backup set 79
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 14137874 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
80 Full 7.91M DISK 00:00:01 19-JUL-15
BP Key: 92 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh382g_.bkp
List of Datafiles in backup set 80
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 14137876 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
81 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230411
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510251_btqh3c9d_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14137883 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
82 Full 7.41M DISK 00:00:01 19-JUL-15
BP Key: 94 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/2vqcfk8t_1_1
List of Datafiles in backup set 82
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14138235 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
83 Full 7.40M DISK 00:00:01 19-JUL-15
BP Key: 95 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/30qcfk90_1_1
List of Datafiles in backup set 83
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 14138237 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
84 Full 7.38M DISK 00:00:02 19-JUL-15
BP Key: 96 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/31qcfk93_1_1
List of Datafiles in backup set 84
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 14138239 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
85 Full 7.43M DISK 00:00:02 19-JUL-15
BP Key: 97 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/32qcfk96_1_1
List of Datafiles in backup set 85
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 14138241 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
86 Full 7.91M DISK 00:00:01 19-JUL-15
BP Key: 98 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/33qcfk9a_1_1
List of Datafiles in backup set 86
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 14138243 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
87 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 99 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230725
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510445_btqh9f6x_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14138250 Ckp time: 19-JUL-15

RMAN>

Thus, you can identify a list of backups by time ranges as well !
.
.
.