Trace Files -- 2 : Generating SQL Traces (another session)

Here are a few methods to trace another session to capture SQL statement executions.  All of these methods require the appropriate privilege --- which most DBAs seem to mean using SYS (which logs in AS SYSDBA).  I leave it to you to discover the privilege -- save to say that you do NOT need to login AS SYSDBA.


DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
This enables tracing for sessions of a specific Service_Name and *optionally* Module Name and Action Name.  This is useful where you define applications by Service Names and, optionally, use DBMS_APPLICATION_INFO to set Module and Action in a given session.

Thus, if in an HR session, I do :

Enter user-name: hr/hr@orcl

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_application_info.set_client_info('HR App Client');

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_module('Employee Module','Updating');

PL/SQL procedure successfully completed.

SQL> 

And, in another session (with the appropriate privileges), I do :

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(-                                      
> service_name=>'orcl',-
> module_name=>'Employee Module',-
> action_name=>'Updating');

PL/SQL procedure successfully completed.

SQL> 

the actions in the HR session(s) of that module and action are traced.  (Additional parameters WAITS and BINDS can also be set to TRUE to enable of Waits and Binds (Waits are set to TRUE by default)).
Note : If there are multiple sessions with the same combination of service_name, module_name, action_name, all the sessions are traced !

Tracing is disabled when the session itself uses DBMS_APPLICATION_INFO to change it's Module / Action settings.

Tracing is also disabled when the session that initiated the tracing executes :

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(-
> service_name=>'orcl',-
> module_name=>'Employee Module',-
> action_name=>'Updating');

PL/SQL procedure successfully completed.

SQL> 

Thus, this procedure allows tracing by the granularity of Service = Module = Action.  Unfortunately, many custom applications do NOT use DBMS_APPLICATION_INFO to set Module and Action.


DBMS_MONITOR.SESSION_TRACE_ENABLE
This is useful for tracing a single session and where  Module / Action information are not populated by the client.

The call is simple :

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(-
> session_id=>153,-
> serial_num=>33,-
> waits=>TRUE,-
> binds=>TRUE);

PL/SQL procedure successfully completed.

SQL> 

The disabling call is :

SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(-
> session_id=>153,-
> serial_num=>33);

PL/SQL procedure successfully completed.

SQL> 
>

Thus, this can be issued individually for each session.

.
.
.

Trace Files -- 1 : Generating SQL Traces (own session)

Beginning a new series of posts on Trace Files.


An SQL Trace captures SQL statements (including recursive SQL calls -- e.g. data dictionary lookups or triggers being executed, which are not "directly" visible to the client or explicitly executed by the client).   Optionally, it can capture Wait Events and Binds.

Tracing for Wait Events allows us to capture the Wait Events that occur within the duration of an SQL call.  Tracing for Binds allows us to capture the Bind Values that were passed by the Client (where the SQL code uses Bind Variables instead of Literals)


Here are a few methods to get the SQL Trace for one's own session (whether interactively in sqlplus or programmatically through any other client)


EVENT 10046
This is not recommended by Oracle although it seems to be widely in use.  Use with extreme caution as specifying the wrong event number or level when issuing an ALTER SESSION command can result in unpredictable behaviour and possible corruption.  I include it here not as a recommendation but only because I anticipate that I will be asked about this.  I do NOT recommend using this method.  (Levels 16 and 32 have not been tested by me)

This method is used with either of these commands :

ALTER SESSION SET EVENTS '10046 trace name context forever, level 1'; -- for the same behaviour as SQL_TRACE=TRUE
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'; -- for tracing Binds with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; -- for tracing Wait Events with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; -- for tracing both Binds and Wait Events with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 16'; -- to dump STAT lines for each execution  
ALTER SESSION SET EVENTS '10046 trace name context forever, level 32'; -- never dump execution statistics

After executing the desired SQLs to be traced, tracing is disabled with :

ALTER SESSION SET EVENTS '10046 trace name context off';

(I have updated the "context forever, level 0" to "context off"  which is the correct method.  This just shows that I haven't used this method of tracing for a long time !)

SQL_TRACE
The instance / session parameter SQL_TRACE is the oldest method and may still be in use although it has been deprecated in recent versions.

This is done with the SQL command :

ALTER SESSION SET SQL_TRACE=TRUE;

After executing the SQLs that need to be traced, it is then disabled with the SQL command :

ALTER SESSION SET SQL_TRACE=FALSE;

DBMS_SESSION.SET_SQL_TRACE
This PLSQL procedure is the proper alternative to setting the parameter SQL_TRACE.

This is done with

exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE);

The EXEC call allows the PLSQL procedure to be executed from the command line.

After executing the SQLs that need to be traced, it is then disabled with the command :

exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE);

DBMS_SESSION.SESSION_TRACE_ENABLE
This PLSQL procedure is the preferred method.  It also offers switches to enable/disable tracing for Wait Events and Binds separately.

This is done with

exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>FALSE);

Thus, the switch for tracing Waits is active but tracing Binds is deactivated.  (Optionally, Binds could also be traced with the binds switch set to TRUE).

After executing the target SQLs in the session, it is then disabled with the command :

exec DBMS_SESSION.SESSION_TRACE_DISABLE;

Thus, no flags need to be supplied to disable both Waits and Binds.

.
.
.

My YouTube Videos as introductions to Oracle SQL and DBA

From my teaching an 11g OCA program, I've developed (and will continue to add) a few videos for those who are just beginning with Oracle SQL and DBA and/or those intending to prepare for the SQL or 11g OCA exams.

These are being built at  http:///www.youtube.com/HemantKChitale   There are two separate PlayLists, one for Oracle SQL and one for Oracle 11g DBA - I   (i.e. corresponding to the OCA training and exams).


Happy Viewing.

.
.
.

RMAN -- 10 : VALIDATE

There are two different VALIDATE commands for Backups.   (These are different from RESTORE VALIDATE which I'd blogged about earlier in 10.2 here)

The first is BACKUP VALIDATE  which is useful to validate Datafiles to check for corruption.

The second is VALIDATE which can be used to check BackupSets.  (Although it, too, can be run against the DATABASE)

Here I use the first  form to check datafiles without actually creating a BackupSet :

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

Starting backup at 13-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 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
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35c9q_.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cd7_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cgr_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cht_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cj2_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:26:18
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14288        107649          14218763  
  File Name: /home/oracle/app/oracle/oradata/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              73392           
  Index      0              16290           
  Other      0              3678            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              15266        161256          14218883  
  File Name: /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              40232           
  Index      0              22741           
  Other      0              82913           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            21769           14217218  
  File Name: /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              21759           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              5349         35154           14211780  
  File Name: /home/oracle/app/oracle/oradata/orcl/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              10430           
  Index      0              2188            
  Other      0              17073           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1580         10500           5843748   
  File Name: /home/oracle/app/oracle/oradata/orcl/example01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              3947            
  Index      0              1110            
  Other      0              3859            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              2            12801           14126777  
  File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35c9q_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6730            
  Index      0              0               
  Other      0              6068            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              2            12801           14085691  
  File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cd7_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6738            
  Index      0              0               
  Other      0              6060            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              2            12801           14126777  
  File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cgr_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6738            
  Index      0              0               
  Other      0              6060            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              2            12801           14085691  
  File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cht_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6739            
  Index      0              0               
  Other      0              6059            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              277          896             13902238  
  File Name: /home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              154             
  Index      0              92              
  Other      0              373             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11   OK     0              371          12801           14126777  
  File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cj2_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              7263            
  Index      0              0               
  Other      0              5166            

channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              628             
Finished backup at 13-SEP-15

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

specification does not match any backup in the repository

RMAN> 
RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 13 19:32:43 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select * from v$database_block_corruption;

no rows selected

SYS>

The BACKUP VALIDATE command doesn't actually create a BackupSet. It simulates running a backup but does the additional job of checking the database blocks for corruption. The CHECK LOGICAL additionally checks for Logical Corruption. The SQL query on V$DATABASE_BLOCK_CORRUPTION can be used after RMAN completes execution as it would be populated with information about the blocks found corrupt.

On the other hand, the VALIDATE command can be used to check BackupSets.

RMAN> validate backupset 278;

Starting validate at 13-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp tag=TAG20150906T212547
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:06:57
Finished validate at 13-SEP-15

RMAN> 

SYS>select * from v$backup_corruption;

no rows selected

SYS>

As with the BACKUP VALIDATE not actually creating a BackupSet, the VALIDATE BACKUPSET doesn't actually restore a BackupSet (one or more BackupPieces from it) --- thus the messages "restored backup piece 1" is misleading.

.
.
.

RMAN -- 9 : Querying the RMAN Views / Catalog

The "data dictionary" for RMAN is in (a) V$ views from the controlfile and, if a Catalog Schema is implemented (b) Catalog Views.

A useful mapping of the two sets is

(for 11.2) at http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcviews001.htm#RCMRF1923

(for 12.1)
at http://docs.oracle.com/database/121/RCMRF/rcviews.htm#i77529


Sometimes (many a times ?) it is better to use these views than the RMAN LIST / REPORT commands.  But it might be a tad difficult to understand these views and write the appropriate queries.

SYSTEM>l
  1  select to_char(start_time,'DD-MON HH24:MI') StartTime,
  2  to_char(end_time,'DD-MON HH24:MI') EndTime,
  3  (end_time-start_time)*1440 RunMin,
  4  input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status
  5  from v$rman_backup_job_details
  6  where start_time > sysdate-32
  7* order by start_time
SYSTEM>/

STARTTIME             ENDTIME                  RUNMIN  READ_MB WRITE_MB INPUT_TYPE    STATUS
--------------------- --------------------- --------- -------- -------- ------------- -----------------------
10-AUG 15:11          10-AUG 15:16               4.32    2,690      860 DB FULL       COMPLETED
10-AUG 19:55          10-AUG 19:59               3.88    2,702      869 DB FULL       COMPLETED
30-AUG 16:58          30-AUG 16:59               1.28      851      248 DATAFILE FULL COMPLETED
06-SEP 21:25          06-SEP 21:33               8.07    2,808      902 DB FULL       COMPLETED

SYSTEM>

So, we extract a list of RMAN Backup Jobs. This V$ view listing spans Instance restarts !
Note, however, the INPUT_TYPE reported is based on the backup command.  For example, the 06-Sep backup command was a single "backup as compressed backupset database plus archivelog;" which actually created multiple BackupSets for ArchiveLogs, Database Files, ArchiveLog and Controlfile.  All of them appear together as one entry "DB FULL".  See my earlier posting "RMAN  --  1  : Backup Job Details" for another example of how you might misinterpret this view.

If it is single command, even the SUBJOB details view doesn't seem to be helpful.

SYSTEM>l
  1  select to_char(start_time,'DD-MON HH24:MI') StartTime,
  2  to_char(end_time,'DD-MON HH24:MI') EndTime,
  3  (end_time-start_time)*1440 RunMin,
  4  input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status
  5  from v$rman_backup_subjob_details
  6  where start_time > sysdate-32
  7* order by start_time
SYSTEM>/

STARTTIME             ENDTIME                  RUNMIN  READ_MB WRITE_MB INPUT_TYPE    STATUS
--------------------- --------------------- --------- -------- -------- ------------- -----------------------
10-AUG 15:11          10-AUG 15:16               4.32    2,690      860 DB FULL       COMPLETED
10-AUG 19:55          10-AUG 19:59               3.88    2,702      869 DB FULL       COMPLETED
30-AUG 16:58          30-AUG 16:59               1.28      851      248 DATAFILE FULL COMPLETED
06-SEP 21:25          06-SEP 21:33               8.07    2,808      902 DB FULL       COMPLETED

SYSTEM>

So, be careful when trying to interpret the results of a query on this view if you don't know how the backups are run.

This, below, is the query on the corresponding Recovery Catalog view :

RCAT_OWNER>l
  1  select to_char(start_time,'DD-MON HH24:MI') StartTime,
  2  to_char(end_time,'DD-MON HH24:MI') EndTime,
  3  (end_time-start_time)*1440 RunMin,
  4  input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status
  5  from RC_RMAN_BACKUP_JOB_DETAILS
  6* where start_time > sysdate-32  order by start_time
RCAT_OWNER>/

STARTTIME             ENDTIME               RUNMIN READ_MB WRITE_MB INPUT_TYPE    STATUS
--------------------- --------------------- ------ ------- -------- ------------- -----------------------
10-AUG 19:55          10-AUG 19:59            3.88   2,702      869 DB FULL       COMPLETED
30-AUG 16:58          30-AUG 16:59            1.28     851      248 DATAFILE FULL COMPLETED
06-SEP 21:25          06-SEP 21:33            8.07   2,808      902 DB FULL       COMPLETED

RCAT_OWNER>

(Remember the first 10-Aug backup doesn't show because it was before a RESETLOGS and the Recovery Catalog doesn't show backups prior a RESETLOGS that was issued befor the first Full Resync into the Recovery Catalog. See my previous post).

What about listing individual datafiles ?

SYSTEM>l
  1  select to_char(completion_time,'DD-MON HH24:MI') Complete_At, checkpoint_change#, resetlogs_change#,
  2  trunc(block_size*datafile_blocks/1048576) Total_Size, trunc(block_size*blocks/1048576) Backup_Size
  3  from v$backup_datafile
  4  where file#=1
  5  and completion_time > sysdate-32
  6* order by completion_time
SYSTEM>/

COMPLETE_AT           CHECKPOINT_CHANGE# RESETLOGS_CHANGE# TOTAL_SIZE BACKUP_SIZE
--------------------- ------------------ ----------------- ---------- -----------
10-AUG 15:14                    14158847          14082620        841         741
10-AUG 19:58                    14186110          14185666        841         741
30-AUG 16:59                    14198051          14185666        841         741
06-SEP 21:32                    14211304          14185666        841         741

SYSTEM>

RCAT_OWNER>l
  1  select to_char(completion_time,'DD-MON HH24:MI') Complete_At, checkpoint_change#, resetlogs_change#,
  2  trunc(block_size*datafile_blocks/1048576) Total_Size, trunc(block_size*blocks/1048576) Backup_Size
  3  from rc_backup_datafile
  4  where file#=1
  5  and completion_time > sysdate-32
  6* order by completion_time
RCAT_OWNER>/

COMPLETE_AT           CHECKPOINT_CHANGE# RESETLOGS_CHANGE# TOTAL_SIZE BACKUP_SIZE
--------------------- ------------------ ----------------- ---------- -----------
10-AUG 19:58                    14186110          14185666        841         741
30-AUG 16:59                    14198051          14185666        841         741
06-SEP 21:32                    14211304          14185666        841         741

RCAT_OWNER>

(Note once again, the backup before the RESETLOGS isn't included when querying the Recovery Catalog simply because the RESETLOGS was before the Full Resync).

And Backup Sets ?

SYSTEM>l
  1  select set_stamp, set_count,
  2  decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included
  5  from v$backup_set
  6  where completion_time > sysdate-32
  7* order by completion_time
SYSTEM>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CON
---------- ---------- ----------- ---------- --------------------- --------------------- ---
 887382703        291 ArchiveLog           1 10-AUG 15:11          10-AUG 15:11          NO
 887382704        292 Datafile             1 10-AUG 15:11          10-AUG 15:15          NO
 887382910        293 Datafile             1 10-AUG 15:15          10-AUG 15:15          NO
 887382945        294 Datafile             1 10-AUG 15:15          10-AUG 15:15          NO
 887382961        295 ArchiveLog           1 10-AUG 15:16          10-AUG 15:16          NO
 887382962        296 Datafile             1 10-AUG 15:16          10-AUG 15:16          YES
 887399620        299 Datafile             1 10-AUG 19:53          10-AUG 19:53          YES
 887399709        300 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NO
 887399713        301 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NO
 887399714        302 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NO
 887399715        303 Datafile             1 10-AUG 19:55          10-AUG 19:58          NO
 887399941        304 ArchiveLog           1 10-AUG 19:59          10-AUG 19:59          NO
 887399943        305 Datafile             1 10-AUG 19:59          10-AUG 19:59          YES
 889115340        306 Datafile             1 30-AUG 16:29          30-AUG 16:29          YES
 889117084        307 Datafile             1 30-AUG 16:58          30-AUG 16:59          NO
 889117160        308 Datafile             1 30-AUG 16:59          30-AUG 16:59          YES
 889737940        309 ArchiveLog           1 06-SEP 21:25          06-SEP 21:25          NO
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO
 889738419        311 ArchiveLog           1 06-SEP 21:33          06-SEP 21:33          NO
 889738421        312 Datafile             1 06-SEP 21:33          06-SEP 21:33          YES

20 rows selected.

SYSTEM>

RCAT_OWNER>l
  1  select set_stamp, set_count,
  2  decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included
  5  from rc_backup_set
  6  where completion_time > sysdate-32
  7* order by completion_time
RCAT_OWNER>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CONTROL
---------- ---------- ----------- ---------- --------------------- --------------------- -------
 887399620        299 Datafile             1 10-AUG 19:53          10-AUG 19:53          BACKUP
 887399709        300 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NONE
 887399713        301 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NONE
 887399714        302 ArchiveLog           1 10-AUG 19:55          10-AUG 19:55          NONE
 887399715        303 Datafile             1 10-AUG 19:55          10-AUG 19:58          NONE
 887399941        304 ArchiveLog           1 10-AUG 19:59          10-AUG 19:59          NONE
 887399943        305 Datafile             1 10-AUG 19:59          10-AUG 19:59          BACKUP
 889115340        306 Datafile             1 30-AUG 16:29          30-AUG 16:29          BACKUP
 889117084        307 Datafile             1 30-AUG 16:58          30-AUG 16:59          NONE
 889117160        308 Datafile             1 30-AUG 16:59          30-AUG 16:59          BACKUP
 889737940        309 ArchiveLog           1 06-SEP 21:25          06-SEP 21:25          NONE
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE
 889738419        311 ArchiveLog           1 06-SEP 21:33          06-SEP 21:33          NONE
 889738421        312 Datafile             1 06-SEP 21:33          06-SEP 21:33          BACKUP

14 rows selected.

RCAT_OWNER>

Note that the RC_BACKUP_SET view shows the type of controlfile backup (whether a BACKUP or a STANDBY) not whether it is included in the BackupSet. SET_STAMP and SET_COUNT are the Join to V$BACKUP_DATAFILE. I use the Join to show the backup executed earlier today :

SYSTEM>l
  1  select s.set_stamp, s.set_count,
  2  decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(s.completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included,
  5  file#, checkpoint_change#
  6  from v$backup_set s, v$backup_datafile d
  7  where s.set_stamp=d.set_stamp
  8  and s.set_count=d.set_count
  9  and s.completion_time > sysdate-1
 10* order by s.completion_time, file#
SYSTEM>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CON      FILE# CHECKPOINT_CHANGE#
---------- ---------- ----------- ---------- --------------------- --------------------- --- ---------- ------------------
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           1           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           2           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           3           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           4           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           5           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           6           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           7           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           8           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           9           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO          10           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO          11           14211304
 889738421        312 Datafile             1 06-SEP 21:33          06-SEP 21:33          YES          0           14211700

12 rows selected.

SYSTEM>

RCAT_OWNER>l
  1  select s.set_stamp, s.set_count,
  2  decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  s.pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(s.completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included,
  5  file#, checkpoint_change#
  6  from rc_backup_set s, rc_backup_datafile d
  7  where s.set_stamp=d.set_stamp
  8  and s.set_count=d.set_count
  9  and s.completion_time > sysdate-1
 10* order by s.completion_time, file#
RCAT_OWNER>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CONTROL      FILE# CHECKPOINT_CHANGE#
---------- ---------- ----------- ---------- --------------------- --------------------- ------- ---------- ------------------
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             1           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             2           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             3           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             4           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             5           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             6           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             7           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             8           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             9           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE            10           14211304
 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE            11           14211304

11 rows selected.

RCAT_OWNER>

So, for the BackupSet 310, I have datafiles 1 to 11 backed up between 21:25 and 21:33 today. Set 312 has a Controlfile (Auto)Backup. You may notice some slight differences in the query the RC_% views versus the V$ views.

What about BackupPieces ?

SYSTEM>l
  1  select s.set_stamp, s.set_count,
  2  decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  s.pieces, to_char(p.start_time,'DD-MON HH24:MI') Start_At, to_char(p.completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included,
  5  p.piece#, p.handle
  6  from v$backup_set s, v$backup_piece p
  7  where s.set_stamp=p.set_stamp
  8  and s.set_count=p.set_count
  9  and s.completion_time > sysdate-1
 10* order by s.completion_time, piece#
SYSTEM>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CON     PIECE#
---------- ---------- ----------- ---------- --------------------- --------------------- --- ----------
HANDLE
------------------------------------------------------------------------------------------------------------------------------------
 889737940        309 ArchiveLog           1 06-SEP 21:25          06-SEP 21:25          NO           1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T212539_byrhpncy_.bkp

 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NO           1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp

 889738419        311 ArchiveLog           1 06-SEP 21:33          06-SEP 21:33          NO           1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T213338_byrj5mgt_.bkp

 889738421        312 Datafile             1 06-SEP 21:33          06-SEP 21:33          YES          1
/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_09_06/o1_mf_n_889738421_byrj5q8h_.bkp


SYSTEM>

RCAT_OWNER>l
  1  select s.set_stamp, s.set_count,
  2  decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type,
  3  s.pieces, to_char(p.start_time,'DD-MON HH24:MI') Start_At, to_char(p.completion_time,'DD-MON HH24:MI') Completed_At,
  4  controlfile_included,
  5  p.piece#, p.handle
  6  from rc_backup_set s, rc_backup_piece p
  7  where s.set_stamp=p.set_stamp
  8  and s.set_count=p.set_count
  9  and s.completion_time > sysdate-1
 10* order by s.completion_time, piece#
RCAT_OWNER>/

 SET_STAMP  SET_COUNT BACKUP_TYPE     PIECES START_AT              COMPLETED_AT          CONTROL     PIECE#
---------- ---------- ----------- ---------- --------------------- --------------------- ------- ----------
HANDLE
------------------------------------------------------------------------------------------------------------------------------------
 889737940        309 ArchiveLog           1 06-SEP 21:25          06-SEP 21:25          NONE             1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T212539_byrhpncy_.bkp

 889737948        310 Datafile             1 06-SEP 21:25          06-SEP 21:33          NONE             1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp

 889738419        311 ArchiveLog           1 06-SEP 21:33          06-SEP 21:33          NONE             1
/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T213338_byrj5mgt_.bkp

 889738421        312 Datafile             1 06-SEP 21:33          06-SEP 21:33          BACKUP           1
/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_09_06/o1_mf_n_889738421_byrj5q8h_.bkp


RCAT_OWNER>

BackupSets 309 and 311 contain ArchiveLog backups while 312 contains the Controlfile (auto)backup.

In the earlier scripts joining V$BACKUP_SET with V$BACKUP_DATAFILE, I've already identified datafiles in each BackupSet (in this case only 1 BackupSet for datafiles today).  You could have your multiple datafiles spread across multiple BackupSets.

Question 1 :  Can you map Datafiles to BackupPieces ?  Answer : No.  Follow-up Question : Why not ?

Question 2 :  Can you write a script mapping individual ArchiveLogs with BackupSets ?  Please submit your scripts (a minimal listing of columns suffices).

.
.
.