RMAN QUERY TO FIND BACKUP DETAILS

 RMAN  VIEWS (WHEN NO RECOVERY CATALOG AVAILABLE) . i have provide the example as per 11gR2. 

To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the recover catalog.  (Work in Mount stage)

SQL> select session_key,

       input_type,

       status,

       to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,

       to_char(end_time,'yyyy-mm-dd hh24:mi')   end_time,

       output_bytes_display,

       time_taken_display

from v$rman_backup_job_details

order by session_key asc;

Below query will display the backup piece that hold the backup of archive log, datafile in the DISK or TAPE Drive.

Query to see Backed Up Archive Logs in the database (Work in Mount stage)

SQL> select recid,set_stamp,sequence#,first_change#,next_change#

                 from  v$backup_redolog;

Query against the V$BACKUP_PIECE view to find backup details of a particular archive log (Work in Mount stage)

  SQL>            select r.sequence#, p.handle from v$backup_piece p, v$backup_redolog r

            where r.set_stamp = p.set_stamp and r.set_count = p.set_count

              and r.sequence# = 63

Note: 63 is the sequence number.

Query against the V$BACKUP_PIECE view to find backup details of a particular datafiles (Work in Mount stage)

SQL> select d.file#, p.handle    from v$backup_piece p, v$backup_datafile d  where d.set_stamp = p.set_stamp  and d.set_count = p.set_count and d.file# = 3

Note: 3 is the datafile number.

SQL>  select table_name from dict where table_name like 'V$%RMAN%';

TABLE_NAME

------------------------------

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

V$RMAN_COMPRESSION_ALGORITHM

V$RMAN_CONFIGURATION

V$RMAN_ENCRYPTION_ALGORITHMS

V$RMAN_OUTPUT

V$RMAN_STATUS

8 rows selected.

V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.

V$RMAN_BACKUP_SUBJOB_DETAILS è  merges similar operations within an RMAN session into a single row. For example, if there are four BACKUP DATAFILE <n> commands, three RECOVERY COPY OF DATAFILE commands, and one BACKUP RECOVERY AREA command, this view will contain three rows - one each for BACKUP, ROLLFORWARD, and COPY_DISK_TO_TAPE operation.

V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.

SQL> select * from V$RMAN_BACKUP_TYPE;

    WEIGHT INPUT_TYPE

---------- -------------

         1 BACKUPSET

         2 SPFILE

         3 CONTROLFILE

         4 ARCHIVELOG

         5 DATAFILE INCR

         6 DATAFILE FULL

         7 DB INCR

         8 RECVR AREA

         9 DB FULL

V$RMAN_COMPRESSION_ALGORITHM è provides descriptions of supported compression algorithms. It is used by the RMAN client.

V$RMAN_CONFIGURATION è Information about RMAN persistent configuration settings.

V$RMAN_ENCRYPTION_ALGORITHMS è displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is AES128.

V$RMAN_OUTPUT è  displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can hold 32768 rows.

V$RMAN_STATUS è displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs which are in progress are stored only in memory while the finished jobs are stored in the controlfile